In your 2nd file, change your custom cell format from yyyy/mm/dd hh:mm:ss to yyyy-mm-dd hh:mm:ssHello @bosco_yip, I have tried that already but the below step doesn't seem to have any effect.. I hope it should change the format of the date to (2020-10-21 12:20:00) instead it still shows (21-10-2020 12:20). Attached the sheet for your reference.
Select A1:A16 >> Custom Cell Format, enter : yyyy-mm-dd hh:mm:ss >> OK
Then, use "Text to Column" to active the cells (See post #.4)@bosco_yip , I could see its already in the correct custom format.View attachment 73059
= LET(
datetime, SUBSTITUTE(DateText, ".","-"),
date, DATEVALUE(datetime),
time, TIMEVALUE(datetime),
date+time )
= LET(
datetime, SUBSTITUTE(DateText, ".","-"),
date, DATEVALUE(datetime),
time, TIMEVALUE(datetime),
IF({1,0}, date, time) )