Excel How to use LEFT function on a Date formatted cell

Question

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:

Answer 1

Use this:

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