Excel: How to use LEFT function on a Date formatted cell?

user_17597252 0
Viewed 258

I am trying to extract the time string from date and time in a cell. I apply the LEFT function in my Excel: in Cell E1, the date and time are shown "2017/9/1 12:02:01".In Cell D1, I used =LEFT(E1,8) to extract the string "12:02:01", but it returned "42979.00". It seems like the cell is treated as a number, not a date and time format. How should I fix this? I run Excel on iOS.

Here is a screenshot of the cells D1 and E1:

enter image description here

format excel date
1 Answers

If it is date formatted the use below formula-

=TEXT(E1,"hh:mm:ss")
user_55147470
Related