By Allen Wyatt for Excel.Tips.Net
Linda asked if there is a formula that will convert a date shown in the European fashion of day/month/year to the US version of month/date/year. Truth be told, this may not be necessary. You see, Excel maintains dates, internally, as numeric values and then simply displays them using various formats. If the dates are truly dates—numeric values—in the worksheet, then you can simply change the format and the dates will be displayed in the way common to the US.
Of course, the date you see in a worksheet could be a text value, instead of a date value. You can test whether the date is really an Excel date or a text value by changing the format of the cell (or cells) to General. (Do this using the Number tab of the Format Cells dialog box.) If it is text, you’ll see no change in the display. If it is a date value, the date should change to a number that represents the number of days since whatever base date your system is using (typically January 1, 1900).
If your dates are truly date values, then simply change the format of the cell (or cells) to whatever date format you want to use. Again, this is done using the Number tab of the Format Cells dialog box.
If your dates are text values, then you will need to convert them to true date values (non-text) so that they can be formatted as just described. You can do this by using a formula to do the conversion. If you have a text date in cell A1 in the format dd/mm/yyyy, then you can use the following formula:
This post is excerpted with permission from Excel.Tips.Net
Subscribe to the Free ExcelTips Newsletter with the links below