Using Dates in Formulas

If you enter dates in your worksheet in a proper date format, rather than a text format, you can use these cell references in formulas. For example, you may wish to determine what 60 days is from a previous date or how old a date is from the current date.

In order to understand how dates work you must first understand that each day is represented as a number. Day 1 was January 1, 1900. Day 2 was January 2, 1900, etc. January 1, 2008 is day 39,448.

You can determine the number of a date simply by formatting it as a number rather than as a date. Complete the following exercise to learn how dates work and why and how you can use them in formulas.

Try This...

To best understand date numbers, try this example. First we need to enter some dates to work with.

  1. In a new worksheet, press Ctrl+; to enter today's date


  2. Use the AutoFill handle to fill the series down to cell A12 (the dates should continue by 11 more days/dates).


  3. Widen the column if need be.


  4. We will copy this data to the next column so that we can format the display of the values differently in each column. Use the AutoFill handle to copy A1:A12 to B1:B12. Again you may need to widen column B. So far your results should look like this (but displaying different dates, of course). Notice that columns A and B contain the same values.
  5. Select column A and format it to display in the Long Date format.

    1. Select column A (click on the A column header)
    2. Display the Home ribbon and click on the drop list button in the top field within the number area . button
    3. Select the Long Date format.


  6. Select column B to format it using the General format. This setting will display the dates as numbers.


    1. Select column B (click on the B column header)
    2. Display the Home ribbon and click on the drop list button in the top field within the number area . button
    3. Select the General format. Notice that each date is represented by a sequential number.


    Understand that the data in both columns is identical, only the formatting is different.

  7. We will now add a simple formula to determine what the date is 60 days from each cell.

    1. In cell C1 enter A1+60 then press Enter.
    2. Use AutoFill to copy the formula down to cell C12. You may need to widen column C. Your results will look like this (but with different dates).
    3. We could have used cell B1 rather than A1 in the formula (because the data is identical); however, by using A1, Excel knew to show the results in a long date format rather than a general format.

Additional References