How to get Excel to convert dates from European to American
February 25, 2008 2:39 PM Subscribe
How do I convert European dates to American in Excel?
I'm having a totally boring but incredibly annoying work problem: I need to sort some data by date, and specifically by year, but the 1/2 dates I have are in American format (02/25/08), and 1/2 are in European (25/02/08). How can I convert the European dates to American normalcy?
I've tried the 'format cells' options, but Excel doesn't seem to recognize the European dates as dates.
Anyone faced this before? Know what to do?
I'm having a totally boring but incredibly annoying work problem: I need to sort some data by date, and specifically by year, but the 1/2 dates I have are in American format (02/25/08), and 1/2 are in European (25/02/08). How can I convert the European dates to American normalcy?
I've tried the 'format cells' options, but Excel doesn't seem to recognize the European dates as dates.
Anyone faced this before? Know what to do?
Excel is most likely treating the European dates as text.
The following formula is given with the assumptions:
- A1 is the cell with a European date
- Day and month are always padded to two digits with a leading zero where necessary
- Year is in two-digit format and represents 2000-2099
Additional complications may exist if the two-digit year represents a different 100-year range, or if Excel is treating European dates where the day is less than 12 as American dates (e.g., your spreadsheet has 07/02/08 which is meant to be February 7 but Excel reads as July 2). Let me know if either of these are the case and I'll see if I can come up with something.
posted by DevilsAdvocate at 2:52 PM on February 25, 2008
The following formula is given with the assumptions:
- A1 is the cell with a European date
- Day and month are always padded to two digits with a leading zero where necessary
- Year is in two-digit format and represents 2000-2099
=DATE(2000+VALUE(RIGHT(A1,2)),VALUE(MID(A1,4,2)),VALUE(LEFT(A1,2)))Additional complications may exist if the two-digit year represents a different 100-year range, or if Excel is treating European dates where the day is less than 12 as American dates (e.g., your spreadsheet has 07/02/08 which is meant to be February 7 but Excel reads as July 2). Let me know if either of these are the case and I'll see if I can come up with something.
posted by DevilsAdvocate at 2:52 PM on February 25, 2008
Ouch - hang on, you've got a set of dates some of which are in US mm/dd/yyyy format, and some are in EU dd/mm/yyyy format?
Do you have anything that will indicate which particular dates are in which format (e.g. a source, or some specific information in another column)?
The solutions listed above (maybe with a bit of tweaking) will probably be OK if either your data is all in the one ("wrong") format, or you can identify which records are wrong... but if not, then I'm afraid that you're going to be totally out of luck.
For example, if column A has the following dates in it, and some are USian and others are EUian:
05/03/2000
09/08/2004
16/02/2005
10/29/2007
For the first two, there's no way to tell manually which is which, let alone have the computer do it automatically.
The third and fourth you could write an IF statement to check whether the first two digits are >12 (in which case it's an EU date, and that's the day field) or whether it's the middle two digits are >12 (and hence is the day part of a US date).
If you can give us any more info, maybe someone will be able to come up with a bright spark of inspiration... but brace yourself for the possibility that you may have to try and re-source all your data :(
posted by Chunder at 3:39 PM on February 25, 2008
Do you have anything that will indicate which particular dates are in which format (e.g. a source, or some specific information in another column)?
The solutions listed above (maybe with a bit of tweaking) will probably be OK if either your data is all in the one ("wrong") format, or you can identify which records are wrong... but if not, then I'm afraid that you're going to be totally out of luck.
For example, if column A has the following dates in it, and some are USian and others are EUian:
05/03/2000
09/08/2004
16/02/2005
10/29/2007
For the first two, there's no way to tell manually which is which, let alone have the computer do it automatically.
The third and fourth you could write an IF statement to check whether the first two digits are >12 (in which case it's an EU date, and that's the day field) or whether it's the middle two digits are >12 (and hence is the day part of a US date).
If you can give us any more info, maybe someone will be able to come up with a bright spark of inspiration... but brace yourself for the possibility that you may have to try and re-source all your data :(
posted by Chunder at 3:39 PM on February 25, 2008
This thread is closed to new comments.
http://www.eggheadcafe.com/software/aspnet/30568676/convert-european-date-for.aspx
reverse
posted by Sk4n at 2:49 PM on February 25, 2008