Understanding Excel Dates and Times

Most Excel users have, at some time, entered dates and times into their spreadsheets, but relatively few users fully understand the way that Excel stores times and dates.

This can often lead to confusion, and it prevents users from benefiting from a whole range of useful operations and formulas involving Excel dates and times.

Dates and Times Stored as Numbers

Excel actually stores dates and times as simple numbers, with each date numbered consecutively, and each time encoded as a fraction of a day. When you think about it, this makes perfect sense, as it means you can logically add and subtract dates and times.

For example, on the windows operating system:


1st January 2010 is represented by the integer 40179
2nd January 2010 is represented by the integer 40180

and so on.

Times are represented by a fraction of a day. Therefore:


00:00hrs is represented by the decimal 0.0
06:00hrs is represented by the decimal 0.25
12:00hrs is represented by the decimal 0.5
18:00hrs is represented by the decimal 0.75

and similarly, all the times in between those shown above are represented by the fraction of a day that they occur at.

Logically therefore, a date and time in Excel is represented by a number that is made up of an integer (representing the date) and a decimal (representing the time). For Example:


18:00hrs on 1st January 2010 is represented by the number 40179.75
12:00hrs on 2nd January 2010 is represented by the number 40180.5
 
Now consider the addition and subtraction of dates and times. If I want to calculate the date that is 56 days after 1st January 2010, I simply need to add the integer 56 to the date 1st January 2010. Internally, Excel adds the integer 56 to 41079 (the integer representation of 1st January 2010) and gets the value 40235, which represents the date 26th February 2010.

Similarly, if I have the date and time 12:00hrs on 2nd January 2010 and I want to subtract 18 hours from this time, I just need to subtract the Excel time 18:00 from the date and time 12:00 on 2nd January 2010. Internally Excel subtracts 0.75 (representing the time 18:00) from 40180.5 (representing 12:00hrs on 2nd January 2010) and gets the result 40179.75 - i.e. the date and time 18:00hrs on 1st January 2010

Formatting Excel Dates and Times

It is also important to understanding the formatting of dates and times in Excel. As dates and times are just numbers in Excel, the formatting of the number is the only thing that enables us to interpret it as a date or time.

Regardless of the cell formatting, if you type '1-January-2010 12:00' into an Excel cell, that cell actually contains the numerical value 40179.5. However, the formatting of the cell defines how this value is displayed. Therefore:


If the cell is formatted as Excel's 'General' type, it will display 40179.5
 
If the cell is formatted as a date, it will display 01/01/2010 (or a similar selected date format)

If the cell is formatted as a date and time, it will display 01/01/2010 12:00 (or a similar selected date & time format)

If the cell is formatted as a currency, it will display $40,179.50 (or a similar selected currency format)
 
This often leads to users thinking that an Excel formula returning a date or time hasn't worked correctly. However, often the problem can be solved by simply changing the cell's formatting.

To change a cell formatting, simply right click on the cell and select the Format Cells... option. The required formatting can be selected from the list in the Number tab of this menu.

When you consider how much the numerical coding of dates and times contributes to the versatility of Excel, it is well worth users spending a few minutes familiarising themselves with these simple concepts.

To learn more about Excel Dates and Times or for other Excel help, visit www.ExcelFunctions.net

Grab a Copy of My Excel - Hands On Guide!

Microsoft Excel Functions And Formulas Guide

Discover more about using Functions and Formulas - CLICK HERE!