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!