Solved Sum Total Hours Over 24 (1 Viewer)

[MattBaldry]

Hi all,

Does anyone know of a way to mimic the Excel [h]:mm:ss in an Access query?

This is for our capacity planner to show how much time has been allocated to a job.

I have the below from Access, and in Excel it sums to a total of 80:58:20 if I format as [h]:mm:ss, but I cannot get access to display this the same in Access.

Access displays the below as 00:58:20 ( I am using "hh:nn:ss")

Qty	Group	Labour
300	Production	00:00:30	2:30:00​
100	Production	00:02:50	4:43:20​
200	Production	00:00:12	0:40:00​
800	Production	00:00:06	1:20:00​
650	Production	00:01:30	16:15:00​
1300	Production	00:00:06	2:10:00​
50	Production	00:00:00	0:00:00​
50	Production	00:00:00	0:00:00​
400	Production	00:00:06	0:40:00​
1800	Production	00:00:12	6:00:00​
1650	Production	00:00:30	13:45:00​
150	Production	00:04:00	10:00:00​
3450	Production	00:00:12	11:30:00​
50	Production	00:01:00	0:50:00​
50	Production	00:01:30	1:15:00​
3600	Production	00:00:06	6:00:00​
400	Production	00:00:30	3:20:00​
			80:58:20​

~Matt

 

[theDBguy]

You may have to use a custom function in Access to do the same.

 

[mike60smart]

Hi all,

Does anyone know of a way to mimic the Excel [h]:mm:ss in an Access query?

This is for our capacity planner to show how much time has been allocated to a job.

I have the below from Access, and in Excel it sums to a total of 80:58:20 if I format as [h]:mm:ss, but I cannot get access to display this the same in Access.

Access displays the below as 00:58:20 ( I am using "hh:nn:ss")

Qty	Group	Labour
300	Production	00:00:30	2:30:00​
100	Production	00:02:50	4:43:20​
200	Production	00:00:12	0:40:00​
800	Production	00:00:06	1:20:00​
650	Production	00:01:30	16:15:00​
1300	Production	00:00:06	2:10:00​
50	Production	00:00:00	0:00:00​
50	Production	00:00:00	0:00:00​
400	Production	00:00:06	0:40:00​
1800	Production	00:00:12	6:00:00​
1650	Production	00:00:30	13:45:00​
150	Production	00:04:00	10:00:00​
3450	Production	00:00:12	11:30:00​
50	Production	00:01:00	0:50:00​
50	Production	00:01:30	1:15:00​
3600	Production	00:00:06	6:00:00​
400	Production	00:00:30	3:20:00​
			80:58:20​

~Matt

Hi Matt
What does the Value 300 equate to for Qty
and 00:00:30 for Labour ?

 

[Josef P.]

A replacement/extension to Format function:

https://github.com/AccessCodeLib/AccessCodeLib/blob/master/text/StringTools.bas#L115

Usage: FormatX(2, "[hh]:nn:ss")

 

[MattBaldry]

Hi Matt
What does the Value 300 equate to for Qty
and 00:00:30 for Labour ?

Hi Mike,

This would be 30 seconds per operation, 300 operations for 150 minutes which makes 2 hours 30 minutes 0 seconds. Anything that is under 24 works perfectly.

~Matt

 

[MattBaldry]

A replacement/extension to Format function:

AccessCodeLib/text/StringTools.bas at master · AccessCodeLib/AccessCodeLib

Reusable VBA code for Microsoft Access applications - AccessCodeLib/AccessCodeLib

github.com

Usage: FormatX(2, "[hh]:nn:ss")

Perfect. I now get 81:18:20 for this job. Thank you.

~Matt

 

[Gasman]

Perfect. I now get 81:18:20 for this job. Thank you.

~Matt

I though it was meant to be 80:58:20 ?

 

[The_Doc_Man]

Just to clarify...

You actually CAN add times in a date variable and the amount of time WILL be correct. Dates would (presumably) be correct through 31-Dec-9999 as long as we don't have another event like the conversion from Julian to Gregorian time. (That's the event that moved the date of New Year's Day away from the Winter Solstice.)

The problem is formatting. Access date/time formatting functions do not have the ability to format more than 23 hours in an hh:nn:ss format. What they do instead is convert things to dates and times if more than one day total time is indicated. However, addition of elapsed times in a Date variable is still valid from a numeric and functional viewpoint.

As noted by Josef P., the solution is to "roll your own" date formatting routine.

 

[MattBaldry]

I though it was meant to be 80:58:20 ?

Hi Gasman,

There was another column I didn't show. There is also a setup time for each operation which is a fixed time over the job no matter the quantity.

If an operation takes an hour to setup, this is shared over the qty of the build, not the operations qty. Not every operation has a setup, in this case there is only 1 hour between all the operations. I knew if the ops timings worked, the setup would too, so I didn't show that part.

~Matt