Conver scientific format timestamp to date excel-CodePudding

I have difficulty in converting the timestamp recorded in excel. The format is in scientific, I would like to convert to date format in excel.

Scientific = 2.02207E 13 (in number format 20220715030838)

I expect the result will be 15-Jul-2022 03:08

Thanks

CodePudding user response：

Use below formula to convert date/time value.

=--TEXTJOIN("/",TRUE,MID(A1&"",{1,5,7},{4,2,2})) (--TEXTJOIN(":",TRUE,MID(A1&"",{9,11,13},{2,2,2})))

Then use cell format to display results as desired format. Or can use TEXT() function like-

=TEXT(--TEXTJOIN("/",TRUE,MID(A1&"",{1,5,7},{4,2,2})) (--TEXTJOIN(":",TRUE,MID(A1&"",{9,11,13},{2,2,2}))),"dd-mmm-yyyy hh:mm")

CodePudding user response：

My answer is here, make sure that singe digit date should come with 0 as highlighted in the pic. Either you can customise the format or use Text function. If you want seconds as well then use Right(A2,2) function in Second argument in the Time function. Hope it clears.

=TEXT(INT(DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))) TIME(MID(A2,9,2),MID(A2,11,2),),"dd-mmm-yyy hh:mm")

=INT(DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))) TIME(MID(A2,9,2),MID(A2,11,2),) #customise the format.