# Thread: DateTime Grand Total Calculation

1. Registered User
Join Date
Jun 2006
Posts
8

## Unanswered: DateTime Grand Total Calculation

Okay, I've looked around on various areas on ways to do this, and am still stumped. So, I'll just pose the question outright here.

I'm building a database that tracks the the volunteers hours for people. I need to be able to show the current totals of hours worked for those people, by person.

I've got to the point of being able to calculate the total of the specific entry by "h" and "m" using the DateDiff. Also, I've been able to setup a UNION query to show a total at the bottom of hours or minutes, whichever I choose.

The problem is this, how would I calculate total hours and minutes worked specific by person, say, on a report? Also, show the grand total hours worked by all persons? I don't need to see the shifts worked necessarily, just the total hours calculated for all shifts. If there is a current shift working, with no end date (possibly using the nz function) calculate based on current time.

So, to make a long story longer, attempting to show calculated totals with a grand total on a report. Oh, and to sort by most hours / minutes worked with most being at the top.

Any takers?

2. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
nz() should handle currently running tasks as you suggest.

you make your life more difficult by formatting into hh:nn too early
consider:
SELECT person, sum(nz(outDateTime,now())-inDateTime) AS worked
FROM tblWhatever
GROUP BY person
ORDER BY sum(nz(outDateTime,now())-inDateTime) DESC;

at some later stage (? report) you can format the decimal-days sum into hh:mm or whatever pleases you.

izy

3. Registered User
Join Date
Jun 2006
Posts
8
Originally Posted by izyrider
nz() should handle currently running tasks as you suggest.

you make your life more difficult by formatting into hh:nn too early
consider:
SELECT person, sum(nz(outDateTime,now())-inDateTime) AS worked
FROM tblWhatever
GROUP BY person
ORDER BY sum(nz(outDateTime,now())-inDateTime) DESC;

at some later stage (? report) you can format the decimal-days sum into hh:mm or whatever pleases you.

izy
Thanks for the info, I'll give it a try. I've had pretty good luck so far with figuring out total time worked. I'm running into problems now with formatting the data to hh:nn. It gives an error, unless I use just "n". I can get total minutes then, and wonder if at that point it would be best to just divide by 60 to get hours. At any rate, thanks for the asistance.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
what data type is the variable that you are havign the problem with?

5. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
it gives an error ??
or it gives an unanticipated result?

the sum i proposed should end up with e.g. 10.255 decimal-days
= ten days and a little bit more than a quarter day
= 246hours 7minutes
so hh:nn will extract 06:07 and ignore the 240 hours from 10 complete days ...not helpful

have a go with something like
int(YourSum * 24) & ":" & format\$(((YourSum *24) - int(YourSum * 24))*60, "00")
which should deliver 246:07

izy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•