I get this text data in an excel worksheet
16/10/2018 00:11:53
16/10/2018 00:41:52
16/10/2018 04:21:19
16/10/2018 04:30:37
it is "day/month/year space hour:minute:second"
I tried formatting as date or time but nothing happens (it shoud be converted to a floating point number representing date and time)
I need to extract the day and the hour (h:m:s) with excel functions
I cannot use the "convert text to columns" excel feature to separate the date and hour at the space, because it would create/overwrite neighbor colums, and I cannot move/overwrite the data columns (I'm not the only user of the worksheet)
I found a way:
multiply all values by 1 (copy 1 from any cell, paste special, choose "values" and "multiply") The dates will be turned into a floating number
Then format the data as "personalized format",
d/m/yyyy hh:mm:ss
then extract the hour with
=TIME(HOUR(A1),MINUTE(A1), SECOND(A1))
In spanish version:
=NSHORA(HORA(A1);MINUTO(A1);SEGUNDO(A1))
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加