Home > IT Answers > Microsoft Windows > problem converting time stored in text datatype to time format in EXCEL
Leyenda
15 pts.
 problem converting time stored in text datatype to time format in EXCEL
Microsoft Excel 2007, microsoft excel functions
Hi, Anyone has any idea to convert the time stored in text to time format in excel e.g. 0838 0825

Software/Hardware used:
excel
ASKED: March 1, 2012  3:51 AM
UPDATED: March 7, 2012  11:54 AM
RELATED QUESTIONS
Answer Wiki:
if it is in the same format i.e4 characters format, we can create formula to separate first two characters with last two characters separated by colon. for example if cell H3 contains 0838 we can write formula in a result cell as : =CONCATENATE(LEFT(H3,2),":",RIGHT(H3,2))
Last Wiki Answer Submitted:  March 1, 2012  7:11 am  by  shivasanjay   1,080 pts.
All Answer Wiki Contributors:  shivasanjay   1,080 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Thanks! It’s working great. Sorry i am not an expert in excel
say if there are records from H1 to H3
e.g. 0838 – H1
12:50 – H2
09:11- H3

How do i apply this formula?

Leyenda
 15 pts.
 

Try the formula using this:
=CONCATENATE(LEFT(H1,2),”:”,RIGHT(H3,1))
This will convert 0838 to 08:38.
You can then Copy the cell and Paste Values into another cell to force the format as a time.

Hope it helps!

CompleteITProfessional
 340 pts.