Excel How to use LEFT function on a Date formatted cell

0 votes

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

Dec 19, 2022 in Others by Kithuzzz
• 38,020 points
619 views

1 answer to this question.

0 votes

Use this:

=TEXT(E1,"hh:mm:ss")
answered Dec 19, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10, 2022 in Others by gaurav
• 23,260 points
7,706 views
0 votes
1 answer

How to use the INDIRECT function in Excel

Try this: =INDEX(INDIRECT($A$1&"!$1:$1048576"),MATCH($B$1,INDI ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,720 points
814 views
0 votes
1 answer
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,720 points
662 views
0 votes
1 answer

Date difference: different results in Excel vs. Python

To calculate the date difference, we can ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
1,094 views
0 votes
0 answers

Convert one date format into another in PHP

How can I convert one date format ...READ MORE

Jun 20, 2022 in PHP by Kithuzzz
• 38,020 points
276 views
0 votes
0 answers

Convert to date format dd/mm/yyyy

I have the following date: 2010-04-19 18:31:27. I ...READ MORE

Jul 30, 2022 in PHP by Kithuzzz
• 38,020 points
718 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,720 points
930 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,720 points
3,575 views
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,720 points
945 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP