Dates, time and duration, the truth in Excel
Date handling in Excel can be confusing and it’s not your fault. Microsoft could do a better job. Here’s a quick guide to how Excel handles dates and date arithmetic with the settings you should know.
What you see isn’t what’s saved
What Excel shows in a cell is only a version of what’s actually saved in a cell.
What appears as a whole number might have a undisplayed fraction. A cell showing 12 might have 12.45 saved in it. A percentage is actually saved as a fraction, for example a cell showing 50% has 0.5 saved.
Excel date and times
That applies even more with date and times. What’s visible in a cell is only a representation of a number value saved in the cell.
Excel thinks of dates as numbers counting from midnight on 1 January 1900
The number 1 in a Date cell appears as 1 Jan 1900.
Times are fractions of a whole number. 0.5 is displayed as noon. 0.59 is 2:14pm etc.
Date and Time arithmetic
Storing dates/times as numbers makes some date arithmetic easy.
To find out the number of days between two dates, just subtract one from the other.
To get the date 10 days either side of a certain date, just add or subtract 10.
To get the time an hour before a cell saved time, subtract 1/24 (hours) , 1/1440 (minute) or 1/86400 (second). Or use the Hour(), Minute() or Second() functions.
Things get more complicated if you want to add a calendar month or year.
Displaying dates and times
Separating the stored date from the displayed cell means it’s easy to change the look of the worksheet without changing the fundamentals.
There are many different data display formats used across the globe. The default is that Excel will show a date in either short or long form according to the Windows regional setting on that computer.
However, you can override that within a worksheet and many Excel developers do.
Here’s the same date, Xmas Eve 2015 displayed many different ways by Excel. All these cells have the same value (42362) but different data formatting.
The major source of confusion is the different short date formats which use numbers only.
M/D/Y is common in the USA but elsewhere the order is D/M/Y . if you see 12/3/2000 is that 12 March or 3 December?
Our standard practice is to show all short dates with a three letter month. There’s no risk of confusion when you see 12 Mar 00 or 3 Dec 2000 regardless of where you are.
The Excel date format for that is dd MMM yy or yyyy in Format : Custom
How Windows changes Excel dates
The default view of Dates in Excel is decided by Windows. The Regional Setting in Windows is used by Excel to choose the proper date format.
Here’s some dates in Excel using the default Short Date format alongside the Control Panel | Windows Region setting
As you can see, the dates are formatted M/d/yyyy in the American fashion.
Now we’ll change the region to English (Canada) and click Apply. Immediately the look of the Excel dates changes a lot. We didn’t ‘touch’ Excel at all, just a Windows setting but it was enough to radically change the look of the Excel cells.
There are good reasons for this kind of regional flexibility (it applies to other Excel formats but its most noticeable in Dates). It allows a single worksheet can adapt to local expectations.
But it can also lead to a lot of confusion, especially for a lot of Excel novices who don’t expect dates to suddenly change appearance like that.
If you want your date formats to be fixed, regardless of Windows regional settings, choose a Custom date format.
Time Duration in Excel
Time durations are possible in Excel but they aren’t obvious. There’s no ‘Duration’ formatting option alongside Date and Time.
See our past ‘Mere Mortals’ article, Entering Time Durations in Excel.
Excel’s problem with the year 1900
A quick note about Excel’s problem with the year 1900 … it’s wrong!
Excel thinks that 1900 is a leap year with 366 days/29 February but it’s not.
“The rule is that if the year is divisible by 100 and not divisible by 400, leap year is skipped. The year 2000 was a leap year, for example, but the years 1700, 1800, and 1900 were not.”Smithsonian
Be careful if you’re doing any date arithmetic in Excel involving or including the year 1900 because it’ll be one day off.
Text to Excel Date conversion by adding Zero with Paste Special
Simple text with day, month and year to Excel date conversion
Converting Text with month and year into Excel dates
Date formatted charts in Excel