Microsoft Office Tutorials and References

In Depth Information

**Dates and Times in Excel**

Dates and Times in Excel

Date calculations can drive people crazy in Excel. If you gain a certain con-

fidence with dates in Excel, you will be able to quickly resolve formatting

issues that come up.

Here is why dates are a problem. First, Excel stores dates as the number of

days since January 1, 1900. For example, June 30, 2015 is 42185 days after 1/1/

1900. When you enter 6/30/2015 in a cell, Excel secretly converts this entry

to 42185 and formats the cell to display a date instead of the value. So far,

so good. The problem arises when you try to calculate something based on the

date.

When you try to perform a calculation on two cells when the first cell

is formatted as currency and the second cell is formatted as fixed numeric

with three decimals, Excel has to decide if the new cell inherits the currency

format or the fixed with three decimals format. These rules are hard to figure

out. In any given instance, you might get the currency format or the fixed with

three decimals format, or you might get the format previously assigned to the

cell with the new formula. With numbers, a result of $80.52 or 80.521 looks

about the same. You can probably understand either format.

However, imagine that one of the cells is formatted as a date. Another cell

contains the number 30. If you add the 30 to the date, which format does Excel

use? If the cell containing the new formula happened to be previously as-

signed a numeric format, the answer suddenly switches from a date format to

the numeric equivalent. This is frustrating. It is confusing. You start with

June 30, 2015, add 30 days, and get an answer of 42215. This makes no sense to

an Excel novice. It forces many people to give up on dates and start storing

dates as text that looks like dates. This is unfortunate because you can
’
t

easily do calculations on text cells that look like dates.

Here is a general guideline to remember: If you work with dates in the range

of the years 2000 to 2020, those numeric equivalents are from 36,526 through

44,196. If you do some date math and get a strange answer in the 35,000
–
45,000

range, Excel probably has the right answer, but the numeric format of the an-

swer cell is wrong. You need to select Date from the Number drop-down on the

Home tab to correct the format.

The Excel method for storing dates is simple when you understand it. If you

have a date cell and need to add 15 days to it, you add the number 15 to the