Date conversion to hh:mm:ss in Excel

[Charley71]

Is there a quick way to convert this:

1 day 00:02:07
1 day 01:00:57
1 mon 10 days 03:12:16
1 day 00:03:01
1 day 00:06:30

to hh:mm:ss

Thanks

 

[AnotherHiggins]

[h]:mm:ss

the brackets tell Excel to return total number of hours, even if it is greater than 24.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Charley71]

That doesn't seem to be working like I need it to. Some fields contain something like this:

1 day 06:34:21
1 mon 10 days 03:30:15
1 day 5 hours 00:12:50

I tried seperating each section and then changing them to
[h]:mm:ss but for the field that contains 1 mon, it changes it to 24:00:00. If you have any ideas they would be appreciated.

 

[SkipVought]

How many days is 1 mon????

Depends on the phase of the moon, the color of cheese & price of baloney.

"I tried seperating each section ..."

You're goint to have to convert EVERYTHING to DAYS.

faq68-5827



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Charley71]

I am realizing that now as well. I just asked the same question of the person I was helping. Thanks for the information anyway.

 

[xlbo]

Yup - those "times" are pretty useless in their current format as excel will interpret them as text

If you can get just number of days for each, converting to hh:mm:ss should be a doddle

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question