Microsoft Office Tutorials and References
In Depth Information
Understanding Excel Date and Time Formats
sheets exist with dates in them. If Microsoft ever corrects this prob-
lem, there will again be rioting in the streets.
The odds of this problem actually affecting you are slim. You would
need to be calculating a date span from before February 28, 1900 to
after March 1, 1900. Because Excel can handle dates going back only
to January 1, 1900, only 49 possible starting dates can cause prob-
You are allowed to string together any combination of these codes with a
space, comma, slash, or dash. It is valid to repeat a portion of the date
format. For example, the format dddd, mmmm d, yyyy shows the day portion
twice in the date and would display as Thursday, March 5, 2015.
Although the date formats are mostly intuitive, several difficulties exist in
the time formats. The first problem is the M code. Excel has already used M
to mean month. In a time format, you cannot use M alone to mean minutes. The M
code must either be preceded or followed by a colon.
There is another difficulty: When you are dealing with years, months, and
days, it is often perfectly valid to mention only one of the portions of the
date without the other two. It is common to hear any of these statements:
• “ I was born in 1965. ”
• “ I am going on vacation in July. ”
• “ I will be back on the 27th. ”
If you have a date such as March 5, 2015 and use the proper formatting code,
Excel happily tells you that this date is March or 2015 or the 5th. Tech-
nically, Excel is leaving out some really important information — the 5th of
what? As humans, we can often figure out that this probably means the 5th
of the next month. Thus, we aren ’ t shocked that Excel is leaving off the fact
that it is March 2015.