Convert text to hours/minutes/seconds

[JacobH123]

hi, i need to convert a cell containing text to a time. The output is for the time it has taken to complete a run so needs to be hh:mm:ss rather than a time of day.

I have attached a file and a sample of the output i'm looking for.

Thanks in advance for your help.

Jacob

[AliGW]

In your output cell:

=TIME(0,LEFT(D2,2),RIGHT(D2,2))

Custom format the column as [hh]:mm:ss.

[JacobH123]

brilliant, thank you so much

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[JacobH123]

I've just noticed a different case where the original text value contains an hour field. eg - 3:31:17. I'm guessing this needs a different formula as is not working with the current one.

Do let me know if i need to open a new thread or if there is a way to reopen this thread as i could not see how.

Thanks

[AliGW]

Remove the SOLVED link (reverse of the way you added it!) and provide an updated sample workbook

You could try this:

=IF(LEN(D2)<6,TIME(0,LEFT(D2,2),RIGHT(D2,2)),TIME(SUBSTITUTE(LEFT(D2,2),":",""),SUBSTITUTE(MID(D2,3,2),":",""),RIGHT(D2,2)))

[KOKOSEK]

I've just noticed a different case where the original text value contains an hour field. eg - 3:31:17. I'm guessing this needs a different formula as is not working with the current one.

Use AliGW formula for 'version' without hours in text and display as time if text contains hours:

Please Login or Register  to view this content.

[JacobH123]

oh great, thanks a lot. saving my day

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who helped.