Access 2010 - Date/Time Format

[carrietm]

I need to find out how long it takes associates to place their first order.
Both fields have a date and time format. When I subtract the date & time of the first order from the start date & time I get a number with a lot of decimal places. You've helped me with something similar before so I thought that I could use the expression builder to create the short time but it seems to be subtracting just the time and not the date and time, which is a problem because it could take a couple of days for the first order. Here are a couple of examples.

Start Date & Time = 07/15/2013 11:59 and 1stOrder = 07/17/2013 10:21 and it's giving me 1.93204861111008, formatting as short time 22:22.
Start Date & Time = 08/20/2013 16:22and 1stOrder = 08/30/2013 14:16 and it's giving me 2.90967593592642, formatting as short time 21:49.

[HansV]

First an explanation: Access stores dates and times as numbers:
A date is stored as the number of days since December 30, 1899. For example today (November 26, 2013) is stored as 41604.
A time is stored as a fraction of 1 day. For example, 6:00 AM is stored as 0.25 since 6 hours = 6/24 day.
So November 26, 2013 6:00 AM is stored as 41604.25.

In your first example the difference is 1.932... days, i.e. 1 day plus 22 hours and 22 minutes.
Access does not have a built-in format to display this correctly - it only has formats for calendar dates and clock times.

You could use the following in a query:

Diff: Int([1stOrder]-[Start Date & Time]) & " day(s) " & Format([1stOrder]-[Start Date & Time],"hh:mm")

or in the Control Source of a text box on a form/report:

=Int([1stOrder]-[Start Date & Time]) & " day(s) " & Format([1stOrder]-[Start Date & Time],"hh:mm")

A variant:

Diff: Int([1stOrder]-[Start Date & Time]) & " day(s) " & Hour([1stOrder]-[Start Date & Time]) & " hour(s) " & Minute([1stOrder]-[Start Date & Time]) & " minute(s)"

or somewhat shorter:

Diff: Int([1stOrder]-[Start Date & Time]) & "d " & Hour([1stOrder]-[Start Date & Time]) & "h " & Minute([1stOrder]-[Start Date & Time]) & "m"

[carrietm]

That is amazing! December 30, 1899 seems like an odd day to start with but that's probably a class for another day. Iol

Thank you for taking the time to break it down.
:-)

[HansV]

It has to do with the way our calendar works, and with efficiency in calculations.

It would have been logical to store December 31, 1899 as 0, so that the first day of the 20th century, January 1, 1900, would be 1, and January 2, 1900 would be 2, etc. In fact, that's the way Excel stores dates.

The earth revolves around the sun in a bit more than 365 days. For practical reasons, our calendar year is 365 whole days, not 365 days and a few hours. In the long term, this would cause the seasons to "drift" along the calendar. To prevent that, years divisible by 4, such as 2008 and 2012, have an extra day, February 29: so-called leap years. That works quite well, but it overcompensates slightly. So there are exceptions: years divisible by 100, such as 1900 and 2100, are not leap years: February has only 28 days. And years divisible by 400, such as 2000, are an exception to the exception: they *are* leap years.

So all years between 1901 and 2009 that are divisible by 4 are leap years. But 1900 isn't.
For the developers who created the date functions for Visual Basic, that posed a problem: they'd have to make an exception for 1900 in many calculations. On the other hand, they didn't want to treat 1900 as a leap year, as Excel does (Excel thinks that February 1900 had 29 days; there are historical reasons for that). As a compromise, they let the date system start one day earlier, so that December 31, 1900 is 366, and not 365, just as if it was a leap year, while still leaving February with only 28 days, as it should be. This simplifies date calculations.

(Confused yet? )

(Remark: some people will say that the 20th century began on January 1, 1901, but let's not go into that here.)

[carrietm]

My peers and I are amazed that you could break something so complex down in such a way that we could understand it. Bravo!
I hope there won't be a pop quiz later though!

[Rudi]

Some interesting facts in that reply Hans...but I won't commit them to memory - I'll leave it for to the computer to figure that out!