Coverting US dates to Uk dates

Use Data | Text to Columns | Delimited | remove all delimiters | then pick the Date format MDY


Regards, TMS

One process that TMShucks touched on is using text to columns, delimiters, other / and that will split it into three columns, then you could recombine them in the order you want.
another way would be a combination of left, mid and right and this should be simple enough it they are all in dd/mm/yyyy format such as 24/01/2033 rather than 24/1/2033. Are they all that way?

that will split it into three columns

No, it won't. It will convert American format (text) dates to UK format (numeric) dates in situ. No splitting and reassembling required.

if the date is in cell A2 and formatted always as dd/mm/yyyy, put this in B2 (or wherever)
=MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,4)

TMS, I'm referring to my version, not yours. I couldn't find a way to remove all delimiters on my 2010 version, not saying your rec is wrong, just referring to my version of how to do it.

@Sambo kid:

I couldn't find a way to remove all delimiters on my 2010 version

Select the American format text dates
Then:
1 Data on the main Ribbon
2 Text to Columns on the Data Ribbon
3 Delimited | Next
4 Untick all delimiters | Next
5 Date | MDY | Finish


Regards, TMS

A formula using LEFT, MID and RIGHT will return a text string so you'd need to get the components and use DATE

For example: =DATE(RIGHT(A1,4),LEFT(A1,1),MID(A1,3,2))

The formula in post 11 returns 4//1//2033 for 1/24/2033 which has 9 characters.


Regards, TMS

I've tried some versions of the mid, left right formulas and if your dates vary between mm and m and dd and d and yyyy and yy you will need some way to incorporate a find into them to find the "/" in all then go some varying distance on each. My skills aren't that good.
The one way I still find that works for me regardless of what order they are in is to use the text to columns function, Delimited >> delimiters >> other "/" then finish. But again, this solution splits them into three columns, then you'd just recombine them using a concatenation formula.
So if the values were in col A, after the split they'd be in col A, B and C. So it would be days in A, months in B and years in C. Then the concatenation would be =B2&"/"&A2&"/"&C2.

EDIT: And BTW, if you have values already in columns B and C, then you'd have to insert new columns in B and C and D so you have room to work and so you don't overwrite those values.

TMS version works in every version of Excel which has the Text to Columns feature and will not create three columns.

If you want a formula, use:

Formula:

Please Login or Register  to view this content.

Regards, TMS