Calculating the difference in time over midnight

[SlinkRN]

Hi!
I have a form that allows the user to input two times. I then need to have a calculated field that calculates how much time between the first and second time entered. The problem is, sometimes the second time entered is after midnight and therefore into the next day. Do I have to have the user input a date AND time for each time entered? Would the date be in a different text box than the time? BTW, the user will enter military time.
Any help would be appreciated, Slink

 

[bdc0765]

Had the same question a while back. Here is where I found the answer.

http://www.mvps.org/dmcritchie/excel/datetime.htm

Time Sheets, providing for start and end time through midnight
Time is recorded as fractional days, so 24 hours = 1 day.
The following logical formula tests start time (A1) against end time (B1) and adds 1 if A1 is greater than B1. If A1>B1 a true condition exists, value 1 (1=24 hours); otherwise, a false condition exists, value 0

=(A1>B1)+B1-A1 'End time - start time

 

[SlinkRN]

The answer you're referring to deals with Excel. I would love to be dealing with Excel, but I'm trying to learn Access. How would you do something like this in Access?

 

[Joe4]

How do you want to measure the difference? In years? days? hours? ...

If you want your difference in hours and minutes, the following formula will work for differences less than 24 hours. In order for this to work, your two time/date variables should be entered as General Date format (i.e. 6/19/94 5:23:34 PM). Obviously, you need to put this calculation in a query:

Difference: Format([Time2]-[Time1],"Short Time")

where Time1 and Time2 are just the names of my date/time variables.

 

[SlinkRN]

Thanks! Yes, I do want the calculation to come up with hours and minutes. None of the times will be > 24Hours, but may begin at 10PM and end at 2AM.
I'm not very good with Access (which is why I'm taking a class presently) so I'm not sure how to enter a date and time together. Would the date be in a different text box than the time? These dates and times would be entered in a form by a user and then a query could calculate the length of time - although I would like the answer to the calculation to show up in a label on the form if possible. Slink

 

[Joe4]

In a table, add a date/time field. Then select the General Date format. This will show what a date and time entered together looks like and how you want to enter them. You can then also create an Input Mask to force users to put it in this format.