Calculating Time Values

  • Thread starter Thread starter Michaelcip
  • Start date Start date
M

Michaelcip

I'm trying to calculate the difference between 2 "Date/Time" fields to
display in a form. My Control Source is listed as '=[EndTime]-[StartTime]'.
This works accurately only if calculating THE SAME DAY. For e.g. if I start
on Mon. & end on Tues. it displays as if its THE SAME DAY (a Mon. end-time
brings the same values as a Tue. end-time.) (I have my field -
'txtTotalTime' - w/ a Format setting to "Short Time") Thanks in advance for
your response, Michael
P.S. the existing posts/comments from others didn't seem to work...
 
Take a look at Access HELP for the syntax related to the DateDiff()
function.

Regards

Jeff Boyce
Microsoft Access MVP
 
I'm trying to calculate the difference between 2 "Date/Time" fields to
display in a form. My Control Source is listed as '=[EndTime]-[StartTime]'.
This works accurately only if calculating THE SAME DAY. For e.g. if I start
on Mon. & end on Tues. it displays as if its THE SAME DAY (a Mon. end-time
brings the same values as a Tue. end-time.) (I have my field -
'txtTotalTime' - w/ a Format setting to "Short Time") Thanks in advance for
your response, Michael
P.S. the existing posts/comments from others didn't seem to work...
Click to expand...
Use the DateDiff() function and include the date as well as the time,
otherwise how would Access (or anybody) know that the time extends
over to the next day?
Note also that the result is a number value, (x hours), not a time
value (x O'Clock), so setting the value to a Time format would NOT be
appropriate. Also, this value ought not be saved in any table. Anytime
you need the value, calculate it, as below.
The expression would be (in an UNBOUND control):
=DateDiff("h",StartTime,EndTime)

= DateDiff("h",#10/1/2009 11:00 PM#,#10/2/2009 3:00 AM#)
returns 4 (hours)

The above returns a value in elapsed hours. If you need to calculate,
for example, the elapsed time between #10/1/2009 11:15 PM# and
#10/2/2009 3:25 AM#, then calculate the time in minutes and divide by
60:
=DateDiff("n",#10/1/2009 11:15 PM#,#10/2/2009 3:25 AM#)/60
returns 4.166 (hours)

Look up the DateDiff function in VBA help.

If you would like the elapsed time to compute as 4 hours, 10 minutes
then see:
http://www.accessmvp.com/djsteele/Diff2Dates.html
 
Back
Top