Convert US date-time to UK date format

[scarlett3]

I have American date-time data, such as the following:

02/01/2019 13:00:00

which is 1pm, 1st February 2019.

Can anyone help with a formula to extract only the date element in UK format?

Thanks.

 

[AlanY]

try

=TEXT(A1,"mm/dd/yyyy")

 

[scarlett3]

Thanks

 

[AlanY]

you're welcome

 

[ClaireS]

The reply above will extract US date only from US date/time cells, and produce the result as text.

To find the best way of dealing with your US/UK conversion, can I ask what version of Excel you are using and what process you are using to get the US dates into your workbook? Presumably they are coming in from some kind of external source?

Also, when the dates come in, are they all text values, or are some of them parsed as (incorrect) dates?

​

The easiest way to deal with the problem would be to process them during your import procedure, but the method will vary depending on the version. If this is not practical, then if you answer the questions above, I will give you a formula. I presume you want the dates to be valid, numerical, British dates in dd/mm/yyyy format.

Looking forward to your answer.

 

[ClaireS]

My apologies, @AlanY, your formula works fine converting US dates to UK ones, but the result is text, and it only works if the (US) day <= 12 because that goes in the UK month position, and if >12 it results in an invalid date, parsed as a text entry.

My formula is:

=IF(ISNUMBER([COLOR=#ff0000]A2[/COLOR]),DATEVALUE(TEXT([COLOR=#ff0000][FONT=Verdana]A2[/FONT][/COLOR],"mm/dd/yyyy")),DATE(MID([COLOR=#ff0000][FONT=Verdana]A2[/FONT][/COLOR],7,4),LEFT([COLOR=#ff0000][FONT=Verdana]A2[/FONT][/COLOR],2),MID([COLOR=#ff0000][FONT=Verdana]A2[/FONT][/COLOR],4,2)))

Substitute your reference cell for A2 throughout)

This produces the actual date value (43497 for 1st Feb 2019) which can then be formatted as a date of the desired appearance using the ribbon bar.

The IF function looks for a number (valid but incorrect date). If this is true, it takes the TEXT of this date, in US format, and converts that back into a date using the DATEVALUE function, thus arriving at a UK date. If the entry is NOT a number, then it is text, and you can build a DATE by taking the MID 4 characters, starting with the 7th character, for the year, the LEFTmost 2 characters for the month, and the MID 2 characters, starting at the 4th, to give you the day.

 

[AlanY]

My apologies, @AlanY, your formula works fine converting US dates to UK ones, but the result is text, and it only works if the (US) day <= 12 because that goes in the UK month position, and if >12 it results in an invalid date, parsed as a text entry.

My formula is:

=IF(ISNUMBER([COLOR=#ff0000]A2[/COLOR]),DATEVALUE(TEXT([COLOR=#ff0000][FONT=Verdana]A2[/FONT][/COLOR],"mm/dd/yyyy")),DATE(MID([COLOR=#ff0000][FONT=Verdana]A2[/FONT][/COLOR],7,4),LEFT([COLOR=#ff0000][FONT=Verdana]A2[/FONT][/COLOR],2),MID([COLOR=#ff0000][FONT=Verdana]A2[/FONT][/COLOR],4,2)))

Substitute your reference cell for A2 throughout)

This produces the actual date value (43497 for 1st Feb 2019) which can then be formatted as a date of the desired appearance using the ribbon bar.

The IF function looks for a number (valid but incorrect date). If this is true, it takes the TEXT of this date, in US format, and converts that back into a date using the DATEVALUE function, thus arriving at a UK date. If the entry is NOT a number, then it is text, and you can build a DATE by taking the MID 4 characters, starting with the 7th character, for the year, the LEFTmost 2 characters for the month, and the MID 2 characters, starting at the 4th, to give you the day.

well spotted, thanks