Microsoft Office Tutorials and References
In Depth Information
Converting Text Dates to Dates
Comparing Date Systems
Complete the following case study to see firsthand how important date systems information really is:
Enter the number 1 in cell A1.
Select cell A1, and then press Ctrl+1 to access the Format Cells dialog.
Change the numeric formatting to display the number as a date, using the *Wednesday, March 14, 2001 type. On a
PC, you see that the number 1 is January 1, 1900.
Type 2 in cell A1. The date changes to January 2, 1900.
If you type in cell A1, you see Wednesday, February 29, 1900—a date that did not exist! When 60
Mitch Kapor was having Lotus 1-2-3 programmed in 1982, the programmers missed the fact that there
was not to be a leap year in 1900. Lotus was released with the mistake, and every competing
spreadsheet had to reproduce exactly the same mistake to make sure that the billions of spreadsheets using
dates produced the same results. Although the 1900 date system works fine and reports the right day
of the week for the 40,300 days since March 1, 1900, it reports the wrong day of the week for the 59
days from January 1, 1900, through February 28, 1900.
Now try this:
Select cell B5. Press Ctrl+; to enter today’s date in the cell.
Again, select cell B5. Press Ctrl+1 to display the Format Cells dialog.
3. Change the number format from a date to a number. Your date changes to a number in the 40,000 to 42,000
range, assuming you are reading this in the 2010–2013 time period.
This might sound like a hassle, but it is worth it. If you store dates as real dates (that is, numbers formatted to display as
a date), Excel can do all kinds of date math. For example, you can figure out how many days exist between a due date
and today by subtracting one date from another. You can also use the WORKDAY function to figure out how many
workdays have elapsed between a hire date and today.
Excel provides a complete complement of functions to deal with dates including functions
that convert data from text to dates and back. Excel stores times as decimal fractions of
days. For example, you can enter noon today as =TODAY()+0.5 and 9 a.m. as =TODAY()+0.375 .
Again, the number format handles converting the decimals to the appropriate display.
Converting Text Dates to Real Dates
The DATEVALUE function converts text that looks like a date into the equivalent serial
number. You can then use the Format Cells dialog to display the number as a date.
The text version of a date can take a number of different formats. For example, your
international date settings might call for a month/day/year arrangement of the dates. Figure 3.10
shows a number of valid text formats that can be converted with the DATEVALUE function.