Make a Date with Excel

In commemoration of today being date night, this week’s tip is all about how Excel handles dates.

Using Excel is all about using text and numbers, and Dates in Excel are just numbers.

Today, May 15, 2010, in Excel’s world is, 40,313. Tomorrow will be 40,314. But when you see dates in Excel, it is usually smart enough to format the date exactly as you enter it (5/14/2010), but store a “serial number” in the background.

The benefit of dates being serial numbers is they are easier to manipulate. You can just add 1 to a date to show the next day. Or subtract 7 from a date to show that day last week. Or subtract 5/10/2010 from 5/3/2010 to get 7 days. (You may have to reformat your answer to make it look like just 7 otherwise you will probably get 1/7/1900 because Excel usually adds a format to your formula by taking one of the formats from a cell in the formula.)

There is a library of formulas you can use to manipulate dates. But I’ll save that tip for another day.

Note however, the first day of the world, in Excel, is 1; January 1^st, 1900. So even though you can type December 31^st, 1899 in as a text string, Microsoft will not recognize it as a date. To most of us that’s not a problem, but Al Gore won’t be able to chart his historical temperature data proving Global Warming…

 As always feel free to send questions/comments/tips to me to include in future Tip of the Weeks.

-Danny