Problem: I hate Excel dates. I try to do a calculation and I get a number like 42342. Or I try to calculate the number of days between an invoice and a payment and I get an answer like January 15, 1900.
Strategy: It will take five minutes to understand how Excel stores dates. Open a blank worksheet. Type a number in the range of 40000 to 42000 in cell A1. Select that cell. Hold down the Ctrl key while you drag the fill handle down for several cells. At the bottom of the list, enter a 1. Over in column C, enter =A1 and copy it down. You should have two identical columns of numbers.
- Two sets of numbers.
Select column C. On the Home tab, open the General dropdown and choose Long Date. Column C will change to show dates in the modern era, plus January 1, 1900.
- Format the numbers as dates.
You haven't changed the value stored in column C. Cell C1 still contains 41687. You have told Excel to treat the cell as a date and so it calculates a weekday, month, day, and year when applying the formatting.
Additional Details: Excel stores dates as the number of days elapsed since January 1, 1900. Assuming that you are reading this book in the 2014"2018 timeframe, whenever you see a number in the 41640"43465 range, you might be seeing a date cell that is not formatted as a date.
When I say that you should use "real" dates, I mean to store a number like 40600 in the cell and use numeric formatting to display that number as a date. The main advantages of real dates are that you can easily change the format of the date, and you can easily do any calculations that you need with the dates. You can not do calculations when you have dates that are stored as text.
Gotcha: While Excel is really fast at converting 40600 to a month, day, year, it does a notoriously bad job of deciding whether to format the result of a formula as a number or as a date. Here are two examples:
Go to the bottom of your dates in column C and calculate =C8-C1. This formula should calculate the number of elapsed days between the two dates. The correct answer is 7. Excel gets the correct answer, but because that column was previously formatted to show long dates, you will see the 7 converted to Saturday, January 7, 1900.
- The correct result of 7 is incorrectly formatted as a date.
To solve the problem, go back to the numeric formatting dropdown on the Home tab and choose Number. The result will now appear as 7 or 7.00. The problem in this case was that you entered a formula that should return a number in a column that had previously been formatted to show dates.
A similar problem is that sometimes, you might enter a function that should return a date. The formula bar will show the formula, and the worksheet cell will show the serial number.
- Excel didn't think to format this as a date.
To solve the issue, format the cell as a short date.
Here, the problem was that you entered a formula that should return a date in a cell that was formatted as General.
Gotcha: 80% of the time, Microsoft takes control and automatically formats your cell with the correct format.
- Change from General format to Short Date.
Go to a blank cell and enter =DATE(2014,7,1) and Excel will automatically format that cell to convert the serial number to a short date. But, this trick does not work with any function that originated in the old Analysis ToolPak.
Bottom line: Be prepared to have to change the formatting, either from General to a Date or from a Date back to Number.