Excel Tip of the Week #49 - Working with dates

Hello all and welcome to this week’s Excel Tip of the Week!  This week we have a Creator post in which we look at how to understand and work with dates in Excel.

Dates are weird



Excel has a strange way of understanding and working with dates.  For example, you may at some point have seen a CSV file or export from some other system in which you see a column like the one shown here.

This is a list of dates.  Excel stores and works with dates by using an indexing system.  01/01/1900 is assigned as “Day 1”, and all dates after that get the next number in line.  Today, 7th October 2014, is day 41919 since that fixed reference point.

Excel stores dates this way because it’s unambiguous and takes the minimum amount of memory.  When you have data that’s stored without formatting, such as in a CSV file, you might see the “naked” Excel numbers that represent different dates.  It’s important to suspect that any number in the 41-42 thousand range is possibly a modern-era date with bad formatting.

There is of course an option to see the dates “correctly”, by changing the format of the cells to one of the “Date” formats.  When you do this, Excel will still remember the dates in its own number system, but will display a more user-friendly day/month/year format.

 

What this means for working with dates

There are several important consequences:

Something that looks like a date to a person can’t be used as an input to a formula

If you type “31/12/2014” into a formula directly, then Excel will calculate 31 divided by 12 divided by 2,014.  To create a date that will be used as an input for a formula, you must either enter the date into a separate cell (where Excel will convert it for you) or use the DATE function.

You can add and subtract constants from dates

A formula such as A1+1, when A1 contains a date, will return the date 1 day after A1.  A1-5 would be 5 days before.

You can subtract one date from another

Entering two dates into a subtraction will calculate how many days there are between the two dates.

Excel can’t work with pre-1900 dates

The first date assigned a number is 01/01/1900.  Any dates in 1899 or earlier can’t be used.

Practise tasks

Have a go at these:

1) Identify which date is equivalent to the number 42000
2) Find out what date is 150 days after today, 7th October 2014
3) Calculate your own age in days

Previous post 
TOTW index
Next post

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.

Anonymous
  • Hi Adnan - because of the way that Excel interprets dates, pre-1900 dates simply aren't workable.  If you had to work with 19th century dates then you could make some tables and formulas to help with the calculations, but the basic operations are out of the picture.

  • Excel doesn't read the dates before 1900. eg if you enter 08/08/1880, the excel will treat this text but if it is entered as 08/08/1980, excel will treat it as date. Isn't unfair...?

  • A few years ago Excel switched from counting dates from 1 Jan 1904 (with 1 Jan 1904 = 0) to counting them from 1 Jan 1900 (1 Jan 1900 = 1), and so dates copied between Excel workbooks may be out by 4 years and 1 day. (1904 was originally chosen over the more intuitive 1900 because it was slightly easier to start the calculation from a leap year).

     

    If your dates are out, you can switch between the two systems by selecting File/Options/Advanced and scrolling down to the area copied below and checking or de-checking ‘Use 1904 date system’. As indicated, the selection applies to all the sheets in the workbook.