How to Convert DD/MM/YY HHMM to DD/MM/YY HH:MM

[sactownhero]

Hi all,

I have searched all over the internet to try and find a formula that will convert dd/mm/yy hhmm to dd/mm/yy hh:mm.

For example, I wish to convert 03/28/20 0859 to 03/28/20 08:59.

Is this possible?

Thanks in advance!

[MrShorty]

Assuming these are text strings and they are consistently dd/mm/yy hhmm, then something like =LEFT(A1,11)&":"&RIGHT(A1,2) will output the text with the ":" inserted in between the hour and minute portion.

If you need to actually convert to a date/time serial number, something like =DATEVALUE(LEFT(A1,8))+TIMEVALUE(MID(A1,10,2)&":"&RIGHT(A1,2)) will convert to a date time serial number. IMPORTANT -- The DATEVALUE() function will use your system's regional settings to interpret the date text. Where you are in the US, if your system's regional settings are set to a typical MDY US setting, then this won't work. If you need something that will work independent of your system setting, then you will probably want to use the DATE() function instead of DATEVALUE() =DATE(VALUE(MID(A1,7,2)),VALUE(MID(A1,4,2)),VALUE(LEFT(A1,2)))+TIMEVALUE(...). Also note that, in either case, Excel is currently set up to interpret 2 digit years less than about 29 as 20yy, but years greater than 29 will be interpreted as 19yy -- just in case you expect this formula to ever see dates later than about a decade in the future.