Formula for converting US dates to UK in Excel

If you've ever imported data from a US supplier into a (UK) spreadsheet you may have found that the dates and times have gone wrong in Excel. This can happen in various ways but a difficult form of this problem sees the first twelve days of the month characterised back to front (i.e. days marked as month and vice versa) whereas those afterwards are just marked as weird text. IN this scenario the time is included and serves to complicate things further

There are various ways of solving this, although some only really work if you encounter this at the start of using the sheet, but none are particularly simple to implement.

One way – and this should work at any stage and if your dates are mixed up – is to use a formula. Here's one I've written using the ground-breaking new "Let" function which essentially lets you use variables. So if the first dodgy date is in A2 you type:

=LET(stringx,IFERROR(TEXT(ROUNDDOWN(A2,0),"dd/MM/yyyy"),TRIM(LEFT(A2,10))),

           firstslash, FIND("/",stringx),

           monthx, LEFT(stringx,firstslash-1),

           dayx, MID(stringx,firstslash+1,FIND("/",stringx,firstslash+1)-1-firstslash),

           yearx, RIGHT(stringx,4),

DATEVALUE(dayx &"-"& monthx &"-"& yearx))

What I've done here is create 5 variables. The first converts both types of date (pre and post 12th of month) into text and saves that as a string, stringx. The next finds the location of the one for the position of the first slash and then the last 3 use those two to create new strings/variables for day, month and year. Finally you plug those into a DATEVALUE function et voila.