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
Search JabSto ::




Custom Search