CONVERT US DATES TO UK DATES IN EXCEL/CSV
(you don't really want to be changing your locale settings)
This is a 2 stage process:
To stop Excel trying to autoconvert some dates into UK format and then messing up the conversion, In Excel highlight the column and go to Data > Text to Columns.
- Select Delimited, Next
- Clear all the Delimiter tick boxes, Next
- Select data type of ‘Text’, Next
Don’t try and just set this via the formatting menu (right-click format cells), it will not work.
Don't select Date on the 'Text to columns' either as this will convert US dates using UK settings, use 'Text'
[Also see dunxd's answer for importing csvs as well. This will also work if imported as txt.]
2. In a spare close-by column copy the following formula. Replace references to F2 with your starting cell. Format the column in whatever date format you choose.
=DATEVALUE(CONCATENATE((IF(MID(F2,4,1)="/",MID(F2,3,1),IF(MID(F2,2,1)="/",MID(F2,3,2),IF(MID(F2,6,1)="/",MID(F2,4,2),MID(F2,4,1))))),"/",IFERROR((IF(SEARCH("/",LEFT(F2,2)),LEFT(F2,1),LEFT(F2,2))),LEFT(F2,2)),"/",IF(ISERR(FIND("/",RIGHT(F2,3),1)),RIGHT(F2,4),RIGHT(F2,2))))
Note: this should work with dates in the following formats:
- 1/1/14
- 01/1/14
- 1/01/14
- 01/01/14
- 1/1/2014
- 01/1/2014
- 1/01/2014
- 01/01/2014
If your date is not separated by “/”s replace this delimiter in the formula above.
Hope it helps.
UPDATE 17-2-2014
As requested a bit of a breakdown:
Datevalue and Concatenate just create the final string in UK date format, so to the interesting bit:
a. Get the US Day (mid value)
((IF(MID(F2,4,1)="/",MID(F2,3,1),IF(MID(F2,2,1)="/",MID(F2,3,2),IF(MID(F2,6,1)="/",MID(F2,4,2),MID(F2,4,1)))))
The most tricky part. We are unsure of the date format. We know that there will be two "/" somewhere but we need to find where. The most tricky part is in a data such as 1/1/10 the second "/" could be as early as the 4th chr, where in a date like 10/10/10 the first "/" is the 3rd, where the second is the 6th chr. We need a way to tell the first and second "/" apart. Its lucky that a '1/1/' format is the only way a "/" could be the 4th chr so we can use this:
- If "/" is the exact 4th chr (meaning d and m will be single chr),
return the 3rd chr as the US day value.
- If not (meaning that there must be at least one 2chr number in the US
day or month), if the 2nd chr is a "/", then return the 3rd and 4th
chr as the US day value.
- If not, if the 6th chr is a "/" (meaning the date must be 2 doubles
for day and month ie. '10/10/'), return the 4 & 5th chr as the US day
value, otherwise return just the 4th chr as the US day value.
b. Get the US Month (left value)
IFERROR((IF(SEARCH("/",LEFT(F2,2)),LEFT(F2,1),LEFT(F2,2))),LEFT(F2,2))
Fairly simple,
- If "/" exists in the leftmost 2 chr of the string, Return just the 1st chr
- if not, return the first 2 chr
- if error, its the same as not finding "/", so return the first 2 chr as well
c. Get the Year (right value)
IF(ISERR(FIND("/",RIGHT(F2,3),1)),RIGHT(F2,4),RIGHT(F2,2))
Simple,
- if "/" exists in the last 3 chr of the string, return last 2 chr, if not return last 4 chr, (4 chr year or 2 chr year).
Hope this helps.
When you say "supplied in US format", are you importing a CSV file? – RedGrittyBrick – 2012-04-04T11:50:05.783
Yes, it's a CSV file with lines of the form: 3/ 1/2012, 09:01 , 18:58 ,9.4,0.6
Where 3/1/2012 is 3rd of March – deworde – 2012-04-04T11:54:31.287
What is your system locale (as in what locale is set in Region & Language Options)? – dnbrv – 2012-04-04T12:01:54.283