Spreadsheets: Converting Dates

A quick trick to build a function that addresses reformatting strings of numbers or text.


Editor’s Note:

To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.

Reader Doug H. wins an autographed copy of Excel for Auditors from CFO and MrExcel for his question: “Is there a way to build a function that addresses reformatting strings of numbers or text? For example, our I-Series software lists dates as an 8-character value/string in the following format, YYYYMMDD. I have to write a long formula as follows:
          DATEVALUE(MID(B2,5,2)&”/”&RIGHT(B2,2)&”/”&LEFT(B2,4)).”

Doug continues: “Is there an easier way [to solve this problem], or can I create a function that mirrors my formula instead of re-creating it every time I download information? This could be a huge time saver for me.”

Here’s a simple approach to dealing with Doug’s date-conversion problem.

Solving with Doug’s Formula

Doug is getting data that looks like the column shown in Figure 1. (See all three stages of Doug’s original data below.)

I love Doug’s formidable formula, yet it must be a pain to type. Furthermore, as you see in Figure 2, it provides the right answer in the wrong format. In order to get the right format, you have to choose a Date format from the dropdown on the Home tab of the ribbon as shown in Figure 3.

Fig. 1

16MrExcelFig1

Fig. 2

16MrExcelFig2

 

 

 

 

Fig. 3

16MrExcelFig3

 

 

 

 

 

 

 

 

 

 

 

 

The Amazing Solution

I was amazed that the following solution actually works. Follow these steps:

1. Select the range of cells containing dates in the YYYYMMDD format.

2. Choose Data, Text to Columns.

3. In Step 1 of the wizard, choose either Delimited or Fixed Width.

4. Click Next twice.

5. In Step 3 of the wizard, open the Date dropdown and choose YMD format as shown in Figure 4 (below).

6. Click Finish.

Fig. 4

16MrExcelFig4

The numbers will change to real Excel dates. If the column is not wide enough, as shown in Figures 5 and 6 (below), double-click between the column headings to widen the column. Miraculously, the column of 20101223 values is now replaced with real dates.



Fig. 5

16MrExcelFig5.

 

Fig. 6

16MrExcelFig6

 

Bill Jelen is the author of 32 books about Excel, including Guerilla Data Analysis Using Microsoft Excel. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right.

Discuss

Your email address will not be published. Required fields are marked *