Running Count of Hours

[Guest]

I maintain an overtime database for several departments. All hours are
formatted as date/time (short time) in tables, forms, queries, & reports.
The report looks fine until the total hours go beyond 24. How can I format my
hours, so that they will display the correct total hours? My Report should
display total hours by employee, department, and company total overtime
hours. Thanks for any assistance.

 

[Allen Browne]

See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

The article explains how to use DateDiff() to get the difference in minutes,
and then display the value as hours and minutes.

 

[Guest]

Thanks for your timely reply Allen, I really appreciate it. I followed the
directions on your posting and it worked for part of my report. The
individual employee hours are counted correctly. One of two departments
counts correcly, but the other is 30 mins short making my company grand total
30 mins short as well. I used DateDiff in my query to get all time in
minutes, which I checked, and are good. I added a textbox in my report to
convert total minutes to hours & minutes. This worked great for the
employees. I added textboxs to the remaining two sections. This is what I
have in the "Data:

=Sum([Minutes])\60 & Format([Minutes] Mod 60,"\:00") - department total.
=Sum([Minutes])\60 & Format([Minutes] Mod 60,"\:00") - company grand total

Any help is always greatly appreciated. Thanks again.

 

[Allen Browne]

So you are getting the correct number of minutes shown, but then the
expressions don't display the hours:minutes text correctly?

I can't imagine why the display would be faulty. I guess it's possible that
Access somehow misundersood the data type of the Minutes, but I don't see
why it would work for one and not the other. The idea is:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your timely reply Allen, I really appreciate it. I followed the
directions on your posting and it worked for part of my report. The
individual employee hours are counted correctly. One of two departments
counts correcly, but the other is 30 mins short making my company grand
total
30 mins short as well. I used DateDiff in my query to get all time in
minutes, which I checked, and are good. I added a textbox in my report to
convert total minutes to hours & minutes. This worked great for the
employees. I added textboxs to the remaining two sections. This is what I
have in the "Data:

=Sum([Minutes])\60 & Format([Minutes] Mod 60,"\:00") - department total.
=Sum([Minutes])\60 & Format([Minutes] Mod 60,"\:00") - company grand total

Any help is always greatly appreciated. Thanks again.

See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

The article explains how to use DateDiff() to get the difference in
minutes,
and then display the value as hours and minutes.

 

[Guest]

Again, my thanks. I read the additional links and double checked all my work.
I figured out my mistake. I was using one textbox for all my calculations. I
ended up creating two textboxes per section, one to display total minutes,
the other to convert those minutes to "hours:minutes" format. It is awesome.
Thanks Again.

So you are getting the correct number of minutes shown, but then the
expressions don't display the hours:minutes text correctly?

I can't imagine why the display would be faulty. I guess it's possible that
Access somehow misundersood the data type of the Minutes, but I don't see
why it would work for one and not the other. The idea is:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your timely reply Allen, I really appreciate it. I followed the
directions on your posting and it worked for part of my report. The
individual employee hours are counted correctly. One of two departments
counts correcly, but the other is 30 mins short making my company grand
total
30 mins short as well. I used DateDiff in my query to get all time in
minutes, which I checked, and are good. I added a textbox in my report to
convert total minutes to hours & minutes. This worked great for the
employees. I added textboxs to the remaining two sections. This is what I
have in the "Data:

=Sum([Minutes])\60 & Format([Minutes] Mod 60,"\:00") - department total.
=Sum([Minutes])\60 & Format([Minutes] Mod 60,"\:00") - company grand total

Any help is always greatly appreciated. Thanks again.

See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

The article explains how to use DateDiff() to get the difference in
minutes,
and then display the value as hours and minutes.

I maintain an overtime database for several departments. All hours are
formatted as date/time (short time) in tables, forms, queries, &
reports.
The report looks fine until the total hours go beyond 24. How can I
format
my
hours, so that they will display the correct total hours? My Report
should
display total hours by employee, department, and company total
overtime
hours. Thanks for any assistance.