Calculating Differences in Times

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to calculate the time that it takes to complete an event; e.g., one
field has start times and another has finish times. Each field is currently
in a hh:mm:ss am or pm format. I would like to know the number of minutes it
takes for each participant to complete the event.
Unfortunately, I only know enough Access to perform these calculations using
an expression in a calculated field in a query, so fancy Visual Basic stuff
is way beyond me.
Thanks so much for any help you can provide!!
P.S. To make things even more complicated, some records do not have time
entries, so I need to return a null value (rather than an error message) if a
start or stop time is not included. I think I can figure this part out
myself, but if you have ideas, of course I'd appreciate them as well!
 
Access has a built-in DateDiff function that can calculate differences for
you.

To determine the difference in minutes between two times, use:

DateDiff("n", [StartTime], [StopTime])

Replace StartTime and StopTime with your field names.

To handle nulls, you could use:

IIf(IsNull([StartTime] Or IsNull([StopTime], Null, DateDiff("n",
[StartTime], [StopTime]))
 
It works like a charm!!! Thanks so much!
The only time I have a glitch is when the time goes from one day to the
next. For example, if Start Time is 10:53 pm and Stop Time is 12:43 am, I
get -1330 for the response. I figure I can use an IFF statement to convert a
negative number to the correct number of minutes. Is this a reasonable
approach or does Access have a better approach for this?
Again, thanks.

Douglas J Steele said:
Access has a built-in DateDiff function that can calculate differences for
you.

To determine the difference in minutes between two times, use:

DateDiff("n", [StartTime], [StopTime])

Replace StartTime and StopTime with your field names.

To handle nulls, you could use:

IIf(IsNull([StartTime] Or IsNull([StopTime], Null, DateDiff("n",
[StartTime], [StopTime]))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JoLeigh said:
I need to calculate the time that it takes to complete an event; e.g., one
field has start times and another has finish times. Each field is currently
in a hh:mm:ss am or pm format. I would like to know the number of minutes it
takes for each participant to complete the event.
Unfortunately, I only know enough Access to perform these calculations using
an expression in a calculated field in a query, so fancy Visual Basic stuff
is way beyond me.
Thanks so much for any help you can provide!!
P.S. To make things even more complicated, some records do not have time
entries, so I need to return a null value (rather than an error message) if a
start or stop time is not included. I think I can figure this part out
myself, but if you have ideas, of course I'd appreciate them as well!
Click to expand...
Click to expand...
 
JoLeigh,

If you want to include times than span midnight, I think a different
approach will work better... even though it's a bit of a mouthful:
Val(Format([Start Time]-1-[Stop Time],"Short
Time"))*60+Mid(Format([Start Time]-1-[Stop Time],"Short Time"),4)

Or, to include the stipulation about nulls...
IIf(IsNull([Start Time]+[Stop Time]),Null,Val(Format([Start
Time]-1-[Stop Time],"Short Time"))*60+Mid(Format([Start Time]-1-[Stop
Time],"Short Time"),4))
 
Back
Top