Excel.Tips.Net: Converting European Dates to US Dates

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:

Article Continued Here

This post is excerpted with permission from Excel.Tips.Net

Subscribe to the Free ExcelTips Newsletter with the links below

(for those using Excel 97 through 2003)

(for those using Excel 2007 through 2010)

122 total views, 2 views today

(Visited 24 times, 1 visits today)

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.