DateDiff Problem with Hours

[voslica]

Please tell me why Access and Excel are calculating the hours this way...
The number of hours between 18:00 hours (6:00 p.m.) and 24:00 hours (12 a.m.) should be 6 hours. Access and Excel are both returning a datediff value of 18 hours. If I change the end time to 12 p.m. (12:00 hours), then Access/Excel returns 6 hours but midnight is not 12:00 p.m. nor is it 12:00 hours.

Help!!!!!!!

 

[neileg]

Date/time in Access or Excel is not a true datatype. Its a cast or a view. Date/time is really stored as a decimal number with the integer part representing days and the fractional part as hours. So you need to be aware of this. If you enter 18:00, this is actually stored as 0.75.

Plus there is the traditional issue about which day 12pm falls on. If you enter 12pm with no date, does this mean midnight at the start of the day, or midnight at the end of the day? Access assumes the first. So you enter 12pm and Access stores 0.00. I understand the Army never uses midnight, it's always 23:59 to address this problem.

So you calculate the difference between 18:00 (0.75) and 12pm (0.00) and you get 0.75, which you now understand to be 18:00.

Just to add to the issue, if you try and enter 24:00 instead of 12pm, Access doesn't recognise this as a valid entry!

 

[boblarson]

Plus, one of the ways to get past the time issue is to use a full date/time value instead of time alone. So, if your field can be 8/28/2007 6:00:00 PM and the ending time 8/29/2007 12:00:00 AM then the date difference when using hours will be:

DateDiff("h", #8/28/2007 6:00:00 PM#, #8/29/2007 12:00:00 AM#)

And the result will be 6 hours

Remember that MIDNIGHT is 12:00:00 AM NOT PM.