problem converting time stored in text datatype to time format in EXCEL

15 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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))

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Leyenda
    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?
    15 pointsBadges:
    report
  • CompleteITProfessional
    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!
    350 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following