Using Dates in Formulas
Microsoft Excel 2010
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, 2012 is day
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.
To best understand date numbers, try this example. First we need to enter some
dates to work with.
- In a new worksheet, press Ctrl+; to enter today's date
- Use the AutoFill handle to fill
the series down to cell A12 (the dates should continue by 11 more days/dates).
- Widen the column if need be.
- 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.
- Select column A and format it to display in the Long Date
- Select column A (click on
the A column header)
- Display the Home ribbon and click
on the drop list button in the top field within the number area .
- Select the Long Date format.
- Select column B to format it using the General format. This
setting will display the dates as numbers.
- Select column B (click on the
B column header)
- Display the Home ribbon and click on
the drop list button in the top field within the number area .
- 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.
- We will now add a simple formula to determine what the date is 60 days from
- In cell C1 enter A1+60 then press Enter.
- 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).
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.