Converting Dates in MS Excel

A reader asked if there was any formula in Excel that would convert a date shown in the European format of day-month-year to the U.S. version of month-day-year. Technically, it may not be necessary to do this. Excel maintains dates as numeric values and displays them using various formats. If the dates are numeric values in your worksheet, then you can simply change the format and the dates will be displayed in the U.S. format.

The day you see in a worksheet could instead be a text value rather than a numeric value. To see if the date is truly an Excel date or text value, you can change the format of the cell or cells to General.

To change the format of the cells to General, Click on Format | Cells and click on the Number tab.

If it is text, you will see no difference in the display. If it is a date value, the date will have changed to a number that represents the number of days since whatever base date your system is using, which is usually January 1, 1900.

If your dates are truly date values, you can simply change the format of the cell or cells to whatever date format you would like to use.

If, however, your dates are text values, you will need to convert them to true date values so that they can be formatted as described above. You can do this by using a formula to do the conversion for you.

If you have a text date in cell A1 in the format of dd/mm/yyyy, then you can use the formula below:

=DATE (VALUE (RIGHT (A1),4), VALUE (MID(A1,4,2,)), VALUE (LEFT(A1,2)))

The result of the formula above is a date serial number that is recognized and can be formatted by Excel.

Suppose you have a list of mixed dates in your worksheet. Look at the list below:

If these numbers were entered into an Excel worksheet, the first twelve dates (1/1/08 through 12/1/08) are parsed by Excel as January 1, 2008 through December 1, 2008. The next five dates are parsed as text since Excel doesn't by default recognize that the dates are in d-m-y format. If you have a lot of dates like this, you can quickly convert them to real dates without the use of formulas.

Follow the steps below:

• Select all cells containing the dates -- both the date and text values.  
   
• Start the Text to Columns wizard by selecting Text to Columns from the Data Menu on your Standard toolbar.  
   
• In version 2007, select Text to Columns from the Data tab of the Ribbon.   Word will display the first step of the Convert Text to Columns wizard.  
   
• Choose Fixed Width and click Next.   If you see any column break indicators in the dialog box, dispose of them by double-clicking on them. You do not want these indicators because you do not want MS Excel to think you have static breaking places for your data.  
   
• Click Next.  
   
• In the Column Date Format section of the dialog box, click the Date radio button.  
   
• Click the drop-down arrow next to date format.  
   
• Select DMY.  
   
• Click Finish.

Your data is now converted to date values that Excel recognizes and can work with.

When you become a member at CarolsCornerOffice.com, you have access to this and many, many more articles that include screenshots. Don't delay: visit us today!