Dates

As we make entries on a worksheet in Excel, the data is temporarily stored in RAM until we save the file. AutoSave, depending upon the settings, will save the file periodically.

Working with Dates

Deleting the contents of a cell will only delete the contents. It will not change the formatting of the cell. For example, if we type a date in to a cell, Excel will automatically change the format of the cell to the DATE format. In addition, Excel will change the date typed to the serial date that represents the date typed. The serial date is the number of days that have passed since January 1, 1900. This is very handy for calculating the number of days between dates - simply subtract the two cells.

However, it can be confusing if you don't understand what happened. To demonstrate, I've typed 9/23/2013 in to cell A1 (below). Excel changes the format from GENERAL (the default) to DATE and changes my date (9/23/2013) to 41540 (the serial date representing 9/23/2013). The number 41540 (serial date) is the number of days since January 1, 1900. It appears in the EDIT line (red arrow below) exactly as I typed it. However, internally, Excel entered the serial date 41540.

Now, if we delete the contents of the cell (tap the delete key), then type in the number 7 in cell A1, the date January 7, 1900 appears in the cell. This is because the serial date 7 is 1/7/1900, the seventh day since January 1, 1900. The EDIT line changes to 1/7/1900, while Excel internally enters the serial date 7 in the cell.

If, on the other hand, we leave the original date of 9/23/2013 in the cell and change the formatting from DATE to GENERAL, we will see the serial date Excel entered (below). The serial date 41540 is the 41,540th day since January 1, 1900.

As we discussed in the last chapter, we can format cells to Currency, Percentage, and General - plus others.