How to convert US (MM/DD) to UK (DD/MM) date format in Excel?

The system at work is using US date format and whenever I export any data into excel I have faced challenges since Excel does not know how to treat date column if it’s entered in different format.

Hence I have sourced only a rather simple solution – read below.

Image

Insert US date format values in Column A and in Column B you will get respective UK date format values. Drag cells down to as many as need.

Formula for cell B2:

=IF(A2=””,””,IFERROR(IF(CELL(“prefix”,A2)=”‘”,DAY(A2)&”/”&MONTH(A2)&”/”&YEAR(A2),MONTH(A2)&”/”&DAY(A2)&”/”&YEAR(A2)),DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))))

Hope this helps!

Advertisements

Your Thoughts

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