Understanding dates in Excel

We often need to work with dates in Excel.

Sometimes we want to record the date that something happened - we made a purchase, someone purchased from us, we issued an invoice, etc.  Other times we want to calculate using dates to work out for example, how many days since an event took place or what the date will be 28 days from now.

There are lots of things that Excel can do with dates but first of all it is important to understand how Excel works with dates.

When you see a date displayed in a cell, you are seeing a number with a date format applied to it.  Starting from 1st January 1900, each day is one whole number.  I am writing this blog on the 5th August 2013 which is day number 41491. Specific times of day are expressed as decimals  e.g. 12 noon on 5th August 2013 is 41491.5. This means that dates can be added and subtracted and used in formulas just like regular numbers, because as far as Excel is concerned they are just regular numbers.   

Of course Excel is more clever than that.  There are several built-in functions that allow you to work with dates.  Take a look on the Formulas tab in the Date & Time button to see a full list.  You can extract just the month or the year from a date, you can find the day of the week for any date, you can calculate the number of workdays (i.e. excluding weekends) between two dates or find the date that is a specific number of workdays from a given start date.  There are many more functions like these.

Excel also recognises dates as series which means that you can use the Autofill handle to complete a series of dates without having to type them all in.  The series does not have to be sequential days.  Try the following:

Enter today's date in a cell (Ctrl + ; is the quickest way to do this)

Click on the Autofill handle (the small square on the bottom right hand corner of the cell) and drag it over several cells to the right. 

You should find that Excel fills in sequential days. 

Before you click away from the selected cells, you will see the Autofill options button appear next to the selected cells.  Click on it.

You will see options to fill days, fill weekdays, fill months or fill years

Click on Fill months.  You'll see that the dates filled in now are the same day of sequential months

You can also fill in a custom interval between dates.

In a new row type in a date e.g. 01/01/2013.  In the next cell type in a date two weeks later e.g. 15/01/2013

Select both cells then drag the Autofill handle and Excel will continue to fill dates that are 14 days apart.

Now that you understand how Excel calculates dates you're ready for my next blog post where we will take a closer look at some of Excel's date functions.