UK date from a mixed format US date in Excel

Quite often if you open a CSV file in Excel with American date formats Excel will do its best to convert them (badly) and you end up with some in date format and some in text format.

This formula helps to sort it out.

DATEVALUE(IFERROR(MONTH(A2),SUBSTITUTE(MID(A2,FIND(“/”,A2)+1,2),”/”,””))&”/”&IFERROR(DAY(A2),SUBSTITUTE(LEFT(A2,2),”/”,””)&”/”&RIGHT(A2,2)))

So 6/21/16 becomes 21/06/2016  and 5/29/16 becomes 29/05/2016

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s