There is a method to convert these column in data format?-CodePudding

IMAGE OF EXAMPLE

Is there a method to convert these column in data format? (gg/mm/aaaa hh:mm:ss)

DATE : 20220601 >>>> 2022/06/01 HOUR : 3047 >>>>> 00:30:47 (hh:mm:ss)

I have serious problem with column B, i need to convert it in (hh:mm:ss). Someone can help me?

The final result should be "01/06/2022 00:30:47"

CodePudding user response：

If you have Excel 365 you can use this formula:

=LET(HourText,TEXT(B2,"000000"), 
DateText, A2,
HourFinal,TIME(LEFT(HourText,2),MID(HourText,3,2),RIGHT(HourText,2)),
DateFinal, DATE(LEFT(DateText,4),MID(DateText,5,2),RIGHT(DateText,2)),
DateFinal   HourFinal)

It first takes the text-hour and the text date.

Text hour is formatted as hhmmss - to have the zeros for hour if missing. Then it is easier to return the true hour.

Adding both values (date hour (yes this is mathematical addition) returns the date.

You can then format the date as you like, e.g. as dd/mm/yyyy hh:mm.ss

CodePudding user response：

Try-

=TEXTJOIN("/",TRUE,MID(A1,{1,5,7},{4,2,2})) & " " & TEXTJOIN(":",TRUE,MID(RIGHT("00"&B1,6),{1,3,5},{2,2,2}))

For pure date value use below function then use cell format to show your desired date/time format.

=DATEVALUE(TEXTJOIN("/",TRUE,MID(A1,{1,5,7},{4,2,2}))) TIMEVALUE(TEXTJOIN(":",TRUE,MID(RIGHT("00"&B1,6),{1,3,5},{2,2,2})))