Using Dates in Excel

In Excel, Dates are stored as numbers, which allows for the addition, subtraction, or comparison of dates. Excel uses an integer system for dates with 1 being equal to 1/1/1900. To see an example of this, type the number 1 into a cell and then format it as a date—it will be displayed as 1/1/1900. You don’t have to worry about memorizing the corresponding number for a given date, because Excel automatically stores dates as numbers. For example, if you type 12/31/2013 into a spreadsheet, Excel will know that you are using a date and will store it as a number behind the scenes even though it is being displayed as a date.

Adding and Subtracting Dates in Excel

In the screenshot below, I typed the date 11/30/2013 into cell C3; in Cell D4 I also entered the same date, but formatted the cell as a number to show you the numeric value of the date, which is 41,608. Next, in Cell C4, i entered the formula = C3 + 1 (this formula will add one day to 11/30/2013 and will result in 12/1/2013). As you can see in the column to the right, the numeric value of 12/1/2013 is 41,609, or 1 more than the numeric value for 11/30/2013. To subtract a date simply replace the plus sign ( + ) in the example with a minus sign ( – ).

Excel Date Example

Using Dates in Formulas

The numeric system Excel uses for dates can make it problematic for typing dates directly into a formula. Using the example above, if I entered the formula = 11/30/2013 + 1 , Excel would evaluate this as a mathematical equation and it would take 11 divided by 30 divided by 2013 and add 1, which is clearly not the result I was looking for.

To solve this problem, you can either enter the date in the formula with double quotes, or type the date into an adjacent cell before performing your calculation. For example, =”11/30/2013″ + 1 will give me a result of 41,609, and when formatted as a date is equal to 12/1/2013.

Formatting Dates in Excel

To format a number as a date you can either press Ctrl + 1 to open the format cell dialogue box, shown below, or on the Home tab of the Ribbon you can change the format using the drop down box located in the number menu.

Excel Format Number Example

Excel Format Number Example

Leave a Reply