DateDiff: Overnight?

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

Guest

How can I get a time difference through the DateDiff function if the End time
rolls over to the next morning? i.e. Start = 8:00 PM End = 2:00 AM
 
starttime = dateserial(2005,11,1) + timeserial(20,0,0)
endtime = dateserial(2005,11,2) + timeserial(2,0,0)
?format(endtime - starttime,"hh:nn")
06:00
 
starttime = dateserial(2005,11,1) + timeserial(20,0,0)
endtime = dateserial(2005,11,2) + timeserial(2,0,0)
?format(endtime - starttime,"hh:nn")
06:00
Click to expand...

=DateDiff("h",#11/2/2005 08:00:00 PM#,#11/3/2005 02:00:00 AM#)
 
That works if you only want hours. If there are minutes involved, you will
not get them.
 
AccessARS said:
How can I get a time difference through the DateDiff function if the End time
rolls over to the next morning? i.e. Start = 8:00 PM End = 2:00 AM
Click to expand...


If your time values included the date, you would not have
this problem. Why are the date parts missing?
 
That works if you only want hours. If there are minutes involved, you will
not get them.
Click to expand...

The idea is the same. Include the date with the time.
If the user want hours and minutes, all he need do is use the above
expression for hours and then use:

=DateDiff("n",#11/2/2005 08:00:00 PM#,#11/3/2005 02:12:00 AM#) mod 60

to get the remaining minutes (12 in the above example).
 
Thanks fred, Since the time span will not exceed 24 hours I was able to
divide this formula by 1440(minutes per day) and format the whole thing as
"Short Time" which gives me exactly what I need in hours and minutes for this
part of my formula...

Format((DateDiff("n",Format(Now(),"mm/dd/yyyy") & " " &
PndCk18In.Value,Format(Now()+1,"mm/dd/yyyy") & " " &
PndCk18Out.Value)/1440),"Short Time")
 
Back
Top