Converting differently-formatted US dates to UK dates

**Glayva** · 09-10-2012, 04:51 AM

The external database I am pulling data from shows dates in the US format and when I download the CSV it strips out any initial zero and, in these instances, also the '20' from 2012.

So, whereas 01/17/2012 comes through as 17/01/2012, 07/06/2012 comes through as 6/07/12. (See attached).

Unfortunately I can't amend the database settings.

I can't seem to find a simple way to convert these back to UK dates and have been trying to work out the smallest amount of steps / formulae required to get these to UK format.

Does anyone have any ideas?

Glayva

**Glayva** · 09-10-2012, 05:02 AM

Actually - I messed that explanation up didn't I... Sorry

For some reason some of the cells are stripping out the zeros and '20's but others aren't. Probably best to see the attached spreadsheet for info.

Thanks

**Ace_XL** · 09-10-2012, 05:06 AM

Try...

=IF(ISNUMBER(A1),A1,TEXT(A1,"dd/mm/yyyy"))

and format as dd/mm/yyyy

**Glayva** · 09-10-2012, 05:08 AM

Thanks Ace_XL but that just returns 00/01/1900

**Fotis1991** · 09-10-2012, 05:08 AM

Hi

One way could be using the Text to Columns option in your Excel sheet panel.

**Glayva** · 09-10-2012, 05:19 AM

Thanks Fotis1991 - I used this on the cells which had numbers stripped out and concatenated the text in a final cell.

**[email protected]** · 09-10-2012, 05:26 AM

I think the other workaround would be....
1) convert all in one format with formula =IF(ISNUMBER(A1),A1,TEXT(A1,"dd/mm/yy"))
2) paste special values
3) Delimit as date format (DMY); do not use any other seperator

**Pete_UK** · 09-10-2012, 05:27 AM

Is this working properly, though? In your example you have 3 values that have been converted into dates, i.e. 7/01/2012, 7/02/2012 and 7/03/2012. However, I suspect that the "real" dates should be 1/07/2012, 2/07/2012 and 3/07/2012 - it's just that the numbers are small enough to be recognised as dates in the UK, but the month and day have been transposed. Do you want a formula that can give you the correct dates in all cases?

Pete

**Pete_UK** · 09-10-2012, 05:33 AM

Put this formula in B1:

=IF(ISNUMBER(A1),DATE(YEAR(A1),DAY(A1),MONTH(A1)),DATE("20"&RIGHT(A1,2),LEFT(A1,SEARCH("/",A1)-1),MID(A1,SEARCH("/",A1)+1,2)))

format as a date (dd/mm/yyyy), and then copy down.

Hope this helps.

Pete

**Kevin UK** · 09-10-2012, 06:06 AM

Hi Glayva

Assuming data is in A1:

=IF(ISNUMBER(A1),A1,DATEVALUE(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&"20"&RIGHT(A1,2)))