What are dates?
This may seem like a strange thing to ask but, as far as Excel is concerned, dates are numbers. By storing them as such, it gives us the ability to add or subtract days to/from a date, as well as get the difference between two dates. If dates were stored as text, this would not be possible. Storing dates as numbers also allows us to construct far more complicated formulas, based on results that we may want to know.
The first thing that we need to know is how to recognize if a date has been entered into a cell as a number, (which will be interpreted by Excel as a date,) or text, which won't. Probably the quickest is to look at the alignment of the cells.
By default, text aligns to the left of the cell, while numbers align to the right. So if you enter a date that Excel can't interpret, chances are that it will go the the left side of the cell. If Excel can interpret the value as a date, it will align right, and most likely change to one of the many pre-defined date formats. Having said this, a developer may have changed the defaults on the cells, so you can't always rely on this.
One quick way to make sure your date is actually a date is to create a formula to add a number to it. So say you have a date in cell B10. If you create a formula that reads =B10+1, it should return something other than #VALUE! If that is the returned value, you know your date is not entered as a true date. Another method is to give the cell a number format (like the comma style). If it changes to a value, then you're good. If not, it is not a date in Excel's eyes. (Press CTRL+Z to get the number format back once you prove it out.)
Entering Dates
Excel is quite good at interpreting dates when you enter them into a cell. Some of the formats that will be automatically converted to dates are:
- 03/29/08
- 03/29
- 03-29
- Mar 29
- Mar 29, 08
After entering each of these listed above, Excel will automatically convert the date into a real date, and store is as such. Now, by contrast, here are some data samples that Excel cannot convert:
- Mar 29 2008 (No punctuation)
- 03.29.08
- Mar 29.08
I cannot stress enough how important it is to get into the habit of entering your dates in shorthand. (i.e. 03/29)* The reason for this is twofold:
- It gets you in the habit of entering dates consistently
- The date will automatically convert into a (usually) slightly different format on the sheet, allowing you to see what doesn't change. (This does, of course depend on whether you set your number format to display this way.)
* It should be noted that this is a US convention, and international users may have to go with a dd/mm format.
What do the numbers mean?
If you were to enter Mar 29, 2008 in a cell and format it in the comma style, you'd see that the number equated to 39,536. So what's that all about? The answer is that this is the number of days since January 1, 1900.
Every date in Excel is stored in this format. Mar 30, 2008 will be 39,537, and so on. Knowing this makes it quite easy to understand why we can subtract dates from each other. If we look at the example below, it should make it very clear:
- March 29, 2008 - March 27, 2008 = 2
OR - 39,536 - 39,534 = 2
Be aware that while adding days to dates is also workable, you may want to resist the temptation to add two dates together. 39,536 + 39,534 would yield you June 25, 2116!
Some handy formulas for working with dates:
- =Today()
Enters the current day's date in the cell. This will update to the current date, every day! - =Now()
Enters the current day's date AND time in the cell. This will update to the second, each time the calculation in the sheet is refreshed! - =Day(date)
Distills the day out of a valid date. i.e. =Day(A9) where A9 contains March 29, 2008 would yield 29 - =Month(date)
Distills the month out of a valid date. i.e. =Month(A9) where A9 contains March 29, 2008 would yield 3 (for March) - =Year(date)
Distills the day out of a valid date. i.e. =Year(A9) where A9 contains March 29, 2008 would yield 2008
Try it yourself!
Try creating the formulas in the blue cells below to see if you can get the formulas to work!
This article was adapted for and contributed to CMA Update Magazine - Fall 2009
