Why do dates and times seem to display incorrectly?

I see a lot of questions about how Excel and Access use date and time values.

There is good news - it's not your fault! The problem is that in fact Excel and Access don't know how to tell the time, or read a calender.

Well, kind of.

The value you see is really just a normal number, but formatted to look like a date or time. Both applications work pretty much the same, so I'll just refer to Excel.

For TIME functions, the real number Excel is using is a fraction of the value 1. So 12:00 midday is the value 0.5

To work out for example the time elapsed between 9.15 am and 4.25 pm is simply a case of 'converting' the time to decimal (0.3854 and 0.6840) and making a normal subtraction (result 0.2986). If you format that result as time, you get 07:10, which is the correct answer.

When I say 'converting', in fact the opposite is true. Excel never thought it was anything but a decimal - it just looked like the time due to the format applied to that cell.

The date works similarly. The real data is just a normal number. Usually it is a count of days since 1st January 1900, so where you see 01 may 2001, the number underneath it is actually 37,012. In this way it is easy to subtract one date from the other.

The date display requires a little more thought though. There are some choices.

If you want to know how many days there were between 1st May and 6th May, you can type in those dates, take the former from the latter and get the answer 5. But you need to format the result properly. If you format it as a date, you will get the result 5th Jan 00. This is perhaps not helpful. Likewise, if you want to add a number of days to a date, make sure the result is formatted as a date, or you'll get a meaningless number!

This leads to consideration of two functions - NOW and TODAY.

Problems can arise from using the NOW function. The data it generates is not only the date, but the time as well. It is very literal. A NOW() will generate a decimal of the number of days + a fraction for the time. So 9.15 am on 1st May 2001 will actually be the number 37,012.3854

If you format the cell as the time, you will get pretty much the right result. If you format it as a date, you may not. This is because formatting as a date will round the number to the nearest whole. So a NOW() fomatted just as a date will only be correct until Midday. After that the fraction becomes greater than 0.5, and it will roll to the next day.

If you want to have just the date, you should use the TODAY() function. If you want just the time, you can use NOW(), and format just to show time.

You can see all of this in action by changing the formats of cells from times/dates to decimals.

One last thing - the time 24:00 does not exist in these applications. It will be recognised as text, not a time. The correct number is 00:00. If you need (as I have done) to use 24:00, you can get around it by using an extra cell for an IF calculation - e.g. =If(a1 = "24.00",1,+a1).

Hope this helps.