Datediff Function

  • Thread starter Thread starter Don C
  • Start date Start date
D

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.

I forgot to say I have an entry of DATE WORKED with the Format set to Short
Date and Control set tot DATE WORK on the form also, but because I am a new
user of Access I don’t Know how to write the Datedif expression to include
the DATE WORKED in the Datedif expression and if I need to have 12:00 AM, IN
to 12:00 AM, OUT, returned as 24 in the HOURS.
 
Understand that the date/time datatype hold both values even though you are
only displaying the time. So what Access sees is:

12/30/1899 7:30 PM
12/30/1899 12:00 AM

which is 0 days and the time, so it is displaying the correct answer. What
you meant to say is:

01/31/09 7:30 PM
02/01/09 12:00 AM

and that difference is 4.5 hours.

You need to enter the date AND time in the field if you want correct
answers.
 
Back
Top