Working with Dates in Excel

I’ve been a Microsoft Office Master Instructor for over 12 years now and I’ve seen some pretty silly things in Excel when it comes to working with dates. Most people don’t understand how dates are stored so they tend to do whatever it takes to get an answer to their problem. This result often leads to more work, heartache and pain further down the track or they simply just don’t get the right answer. In their eyes, ‘close enough is good enough’. Some of the Insidious, diabolical, mega formulas I’ve seen would leave mathematicians scratching their heads. Lets take a closer look at working with dates in Excel.

Firstly, do you know how dates are stored? If you don’t, follow these steps.

In a blank cell in Excel

1. Type in today’s date or Ctrl + ; (semicolon) for the keyboard shortcut fans.

   

   If you are typing out the date, only ever use a forward slash (/) or a dash (-) to separate the days, months and years. Definitely no full stops (.) e.g  5.1.2013 is just plain wrong. Excel looks at this number with 2 lots of decimal places, gives up and recognises it as text. This is easy to identify because the data will be left aligned in the cell. For this very reason it now means that you cannot reformat the cell to a different date format (in-built or custom date format) and you cannot use this date in a calculation as you would be able to with a valid date. 

2. Format that same cell to a number using format cells. Ctrl +  1 will get into format cells for the keyboard shortcut fans.

   

3. On the number tab, change the format from a date back to a number with 2 decimal places.

   

4. Click OK You should have a number over 40,000 in this cell.

   

   This represents the number of days since the 1st of January 1900. That’s it, dates are really just stored as numbers. As long as you remember this you can work with dates quite easily in Excel. If you want to confirm this, type the number 1 into another blank cell and format it back to a date – use the long date format. It will show you (Sunday, 1 January 1900). That’s day 1 as far as Excel is concerned.

To take this concept a little further let’s make another little change. Click back on the cell that was originally today’s date. This cell still should be displaying a number. Change the 2 zeros after the decimal place to .25 (for example 41278.25). If you are wondering what this represents, read on. If a date is stored as the number of days since the 1st of January 1900 then a .25 would represent a quarter of a day. That’s 24 hours divided by 4 or 6 hours…. which is really 6am. Quite simply put, whole numbers represent the date and the decimal places represent time. If you want to confirm this do the following.

1. Ensure the cell with the number is still selected.
2. Go to format cells (Ctrl + 1)
3. Select the Number tab
4. Select Custom at the bottom of the list
5. Under Type: remove the 0.00
6. Type or copy and paste the following custom date and time format: d/mm/yyyy h:mm am/pm
7. Click OK

Voila! You can now see how Excel stores dates and time. Treat your dates and times as normal numbers. For instance, if you wanted to work out how old someone is , use the following formula. Let’s assume their birthday is in cell A1.

The formula would look something like this:
=INT((TODAY()-A1)/365.25)

Let’s break it down. TODAY()-A1 will calculate the number of days old the person is. For some of us, that’s really a bit scary. We then divide this by 365.25 to work out how many years old they are. The .25 is used to cater for the leap year ever 4 years. Then finally, INT is the integer function which changes any numeric result to an integer or whole number. It basically ignores all decimal places. Think of it as chopping them off. This leaves us with their age.

For more information from Microsoft on working with custom date formats click here or

Attend an Excel Intermediate Training Course in Sydney at Raising the Bar