Adding Up Time Worked 1

[rcorbett]

I've been messing with this for days with little luck - hopefully some of you have some suggestions.

I have StartTime and EndTime fields in a table that are used to calculate when Volunteers start and finish. In a query I am trying to calculate the hours worked and then be able to sum up this time worked say over a months time.

I have been working with the following to calculate this difference but it won't let me sum up the time.
=Format([StartTime]-1-[EndTime],"ShortTime&quot

Thank you for any assistance

 

[Tranman]

Look up the Datediff function. It will let you figure out the difference (in any units you choose), of two different Date/Time fields.

To elaborate a little bit:

If your STARTTIME and ENDTIME fields are Date/Time fields, you can make a calculated "WORKTIME" field by saying:
WORKTIME:datediff("n",STARTTIME,ENDTIME) (the n will mean the difference is returned in minutes. you could also do hours, seconds, weeks, quarters; whatever)

Once you have a total time for each "work event", all you need is a summary query to get a weekly/monthly/whatever total.

Tranman

 

[PGTurner]

I haven't tried to sum it, but isn't it:

Format([StartTime]-1-[EndTime],"Short Time")

 

[Tranman]

That all depends on whether the original fields are text, date/time, or numbers.

If they are text, you cannot do math on text values, so you need to change each one individually to date/time (with the format statement), then figure the difference between them. Like so:
WORKTIME: DATEDIFF("n",format(starttime,"short time&quot,format(endtime,"short time&quot)

If the fields are date/time or numbers, you can do math on them, but when you return the "period" between the start and end times in short time format(with AM/PM), they don't really lend themselves to being added up. That is why I suggested datediff. It returns a numeric value (in minutes or whatever) that can be readilly summed up.

I'm curious why the "-1"???

Tranman

 

[PaulBricker]

I swear I'm not following you Tranman. One problem with the DateDiff function is it doesn't account for time that got from one day to the next. So if you put in
DateDiff("n",11:45 P.M, 12:15 A.M)
you will get a value of
- 1410
The reason the =Format(StartTime-1-EndTime,"Short Time&quot isn't working is because it returns a Text datatype for the time and if you try and sum it it will error out.
You can use this expression to calculate the difference between your start and end times and then in a new query, these will sum properly.

IIf([endtime]<[starttime],(([endtime]-[starttime])*24*60)+1440,([endtime]-[starttime])*24*60)

As for the -1 in the expression, subtracting the 1 from the StartTime returns the amount of time between StartTime and Midnight. Then subtracting EndTime from that returns the amount of time from Midnight to EndTime.

So if StartTime is 10:30 P.M. and Endtime is 12:15 A.M. then
10:30 - 1 Returns 1:30 (1 hour and 30 minutes) and then -EndTime adds the extra 15 minutes on to that. But it only works if you Format the answer for Short Time.
Great if you want just the difference between times but you will have to change the calculations if you are going to add stuff up.

Paul

 

[Tranman]

Paul,
I was picturing a date/time that had a date component (the way I use it, populated by now()). I can see how a &quot;time only&quot; date/time would need to be separated into just the part before midnight.
Tranman (Paul)