How Excel Handles Dates

Financial models are inevitably based on dates in some way, so you are trying to answer:

• what period are you forecasting?
• at what date can you repay a loan?
• when does the new product need to launch?
• does a sale fall into this financial year or the next?

The way Excel handles dates is different from the way a human would see it. Once you understand dates you will better understand how to make them work for you.

What is a valid Excel date?

Excel sees dates as a number, and the number it uses is based on the 1st January 1900.

Where you would see

• 1st Jan 2009,
• Excel sees 39 814 i.e. 39 814 days after the 1 January 1900.

Below what you see and what Excel actually sees. Note that 1 Jan 1890 is not a ‘date’ because Excel can’t handle dates before the 1 Jan 1900. Look at some of the other dates and what Excel sees.

The fact that it shows it to you as a date you can read is just Excel being nice to the human. Excel will always use this number to work with dates. So the first issue is whether what you see as a date is being seen by Excel as a date.

In a single spreadsheet you may see the following ‘dates’ in cells. These might have been typed in, imported from another system or sent to you from your colleague in another country:

• 18 June 1973
• 18/06/1973
• 1973/06/18
• 06/18/1973

How do we check if, and which one/s, Excel recognises as dates?

It comes back to what an Excel date is. It is just a number. If you can format these cells and change them into a number, then Excel has recognised it as a date. If you try and format it as a number, and it doesn’t change into a number, then no matter how much it looks like a date, it isn’t a date (according to Excel).

Another thing to be careful with is that a date that works on one computer, may not work on another computer. This is due to regional settings on a computer. Is your computer set up the recognise Day Month Year or Month Day Year? All these things can result in dates not acting the way you expect them to.

Working with Dates

As a result of how Excel sees dates, it is not as easy for Excel to extract information out of a date as it is for a human.

If I gave you a date of 18/06/1973 and asked you to tell me which month it is, you will easily say the 6th month or June.

But remember that Excel sees this date as 26 833. Can you pull out the month in this ‘date’?

So Excel has a number of special functions to work with Dates. We are going to cover some of the main ones that are used in Financial modelling but be aware that there are lots of different ones. Also note that TIME is treated in the same way. We don’t cover it for financial modelling but the same logic applies in that Excel sees time as a percentage of a 24 hour day. So you see 18h00, and Excel sees 0.75 i.e. 18 hours / 24 hours = 0.75 of a 24 hour day.

Below a video clip providing some more details

To follow along go to 7.1