Access 2010 - time difference formula

Question

I need to find the time difference between two different times. 

I am creating a database for my clinic. I need to know the number of hours that have passed since a referral was received and the patient was seen. 

I have two fields, both with the following date/time format 00/L<LL/9900, hh:mm

Can anyone help me find the right function and syntax for my problem?

Answer 1

You might like to take a look at TimeArithmetic.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates the a number of functions for the addition, subtraction and summation of values of date/time data type.

Answer 2

The format of the field is irrelevant. A Date/Time value is stored as a number, a count of days and fractions of a day since an arbitrary start time (#12/30/1899 00:00:00# if you're interested).

To calculate elapsed time you can use the DateDiff function: e.g. in a Query you can type

Elapsed: DateDiff("h", [ReferralReceived], [WhenSeen])

using your actual fieldnames of course.

Note that this returns integer hours, the number of hour boundaries crossed between the times. If you need hours and fractions of an hour, use "n" (miNutes, "m" is Months) in the DateDiff expression and divide by 60.

Didn't you ask this question a week or so ago...?