I received a worksheet from an IMA member asking why the dates in column A couldn’t be formatted into MM/DD/YYYY format. When I looked at the spreadsheet, the Effective Date column contained values such as 9152015. While the human brain can recognize that this likely means September 15, 2015, Excel does not.
The problem is that Excel stores dates as the number of days that elapsed since January 1, 1900. For example, the date November 15, 2015, is actually stored in Excel as 42,323. To see this for yourself, find any worksheet with real dates and use Ctrl+` to toggle into Show Formulas mode. The date cells will display the serial number being stored behind the date. Press Ctrl+` again to toggle back to showing the dates instead of the serial numbers.
TEXT TO COLUMNS
In Figure 1, cell A2 contains the value 9,152,015. If you count that many days after January 1, 1900, you end up with a date in the year 26956. Since Excel only handles dates from 1900 through 9999, this particular date falls 16,956 years too late to be formatted as a date. But clearly, the effective date of this insurance policy is meant to be in the year 2015 instead of the year 26956.
Figure 1
If the values in column A all contained eight digits, the Text to Columns command on the Data tab would successfully convert these values to the correct date. But they don’t (see Figure 2). You might think that you could apply a custom number format of 00000000 to force the cells to display eight digits, but the Text to Columns command works on the values stored in the cells, not the values displayed via a custom number format.
Figure 2
One method for converting the values to real dates is illustrated in Figure 1. In column B, the formula =TEXT(A2,”00000000”) will convert the numbers in column A to an eight-digit number. This forces a leading zero to appear for all dates in January through September. Text to Columns doesn’t work with a column of formulas, so you then have to convert the formulas to values.
One fast way to convert formulas in column B to values in column C involves these steps:
- Select the data in column B.
- Hover the mouse over the right edge of the selection rectangle.
- Right-click the right edge and drag the selection rectangle over to column C.
- When you let go of the mouse button to complete the drag, the Alternate Drag and Drop menu appears. Choose Copy Here as Values Only (see Figure 3).
Figure 3
Once the numbers in column A have been converted to eight-character text in column C, you can use Text to Columns. In the first two steps of the wizard, click Next to accept the default settings. In the third step, choose the Date option and specify that the date is in MDY format.
When you click Finish, the column will be converted to dates and correctly formatted, as shown in column D of Figure 1.
USING A FORMULA
Is it possible to avoid Text to Columns and convert the number in column A using a formula? Yes, but typing the formula would probably take longer than using Text to Columns.
One approach is to use the =DATE(year,month,day) function. For example, =DATE(2015,11,15) would return 11/15/2015. It’s simple enough to find the year portion of the date by using =DATE(RIGHT(A2,4), but the formulas to extract the month and the day are complex. For the first three quarters of the year, the month is LEFT(A2,1), but it’s LEFT(A2,2) during the last quarter. Similarly, the day would be MID(A2,3,2) in January through September and MID(A2,4,2) in October through December.
You could write the formula as =DATE(RIGHT(A2,4),LEFT(TEXT(A2,”00000000”),2),MID(TEXT(A2,”00000000”),3,2)). Or perhaps as =DATE(RIGHT(A2,4),LEFT(A2,LEN(A2)-6),MID(A2,LEN(A2)-5,2)).
Another approach is to use the DATEVALUE() function. To use it, however, you would have to coerce the number in cell A2 to appear as a date by using a custom number format. You’ve already seen how the custom number format 00000000 forces the number to display as eight digits. You can insert a special character in the custom format by using a backslash followed by the character. For example, 00A00B0000 would cause cell A2 to appear as 09A15B2015. Of course, you don’t want to insert A and B in the date. You want to insert a forward slash. The custom number format becomes 00/00/0000. Putting this all together, the formula =DATEVALUE(TEXT(A2,”00/00/0000”)) would successfully convert the numbers in column A to the correct serial number for the date (see Figure 4). Change the cell format to a date, and you’re done.
Figure 4
SF SAYS
Excel doesn’t allow dates earlier than 1900, frustrating genealogists and historians. And if you enter 11/15/29, Excel assumes the year is 2029. But enter 11/15/30, and Excel assumes 1930.
November 2015
