Datedif Expression

[Don C]

I have 3 entries on a form: TIME IN, TIME OUT, HOURS. In the TIME IN, I set
the Format as: Medium Time with an Input Mask for Medium Time. For TIME OUT,
I set the Format as: Medium Time with an Input Mask for Medium Time. For
HOURS, I set the Format as: Standard Number with this Control: Datedif(“nâ€
,[TIME IN],[TIME OUT])/60.

When I enter TIME IN as 12:00 AM (Mid Night), and TIME OUT as 07:30 AM, the
HOURS returns 7.5 which is right, but when I enter TIME IN as 07:30 PM, and
TIME OUT as 12:00 AM (Mid Night), the HOURS returns -19.5. It should be 4.5.
Also when I enter TIME IN as 12:00 AM and TIME OUT as 12:00 AM, I want HOURS
to return 24. This problem is confusing me. Can you help me?

 

[Wayne-I-M]

datediff will work fine - but you need to add the date or how will your
application know which day your using

12midnight to 12 midnight = 0 hours & 0 mins

but

12midnight today to 12 midnight tomorrow = 24 hours & 0 mins

The input masks and formats don't affect the data - just how it's entred and
seen.

May be an idea (to keep it simple) just to add an option group
Yesterday/today/tomorrow
but this may not be a good idea ?? as don't really understand your application

have a look at
http://www.mvps.org/access/datetime/date0008.htm
and
http://www.mvps.org/access/datetime/date0009.htm

 

[Linq Adams via AccessMonster.com]

This code checks for the situation you describe and makes an adjustment to
the DateDiff() results if the condition exists.

If StopTime <= StartTime Then
Me.TimeInMinutes = DateDiff("n", StartTime, StopTime) + 1440
Else
Me.TimeInMinutes = DateDiff("n", StartTime, StopTime)
End If

You can then parse the results into hours using

/60

if you wish. Having said this, it really is preferable when recording time to
include the date portion.