day and time convertion

[chris.howes]

boss now wants me to convert dd:hh:mm:ss into total time eg

02:02:30:59 comes out as 50 hours 30 minutes 59 seconds

thanx in advance

 

[JE McGimpsey]

one way:

Format/Cells/Number/Custom [hh]:mm:ss

 

[chris.howes]

*boss now wants me to convert dd:hh:mm:ss into total time eg

02:02:30:59 comes out as 50 hours 30 minutes 59 seconds

thanx in advance *

have tried this and it will not reformat the cell just leaves th
original data there 02:02:30:59 will not convert i

 

[Michael Bednarek]

boss now wants me to convert dd:hh:mm:ss into total time eg

02:02:30:59 comes out as 50 hours 30 minutes 59 seconds

You posted this question already about 3 hours ago. Doesn't Ron
Rosenfeld's answer, which he posted 2 hours later, work for you?

Just in case: his answer was:
=LEFT(A1,FIND(":",A1,1)-1)+TIMEVALUE(RIGHT(A1,8))
and format as [h]:mm

Now you also specify to have seconds in the output; format as [h]:mm:ss

If you literally want "50 hours 30 minutes 59 seconds", feel free to ask
again.

 

[Michael Bednarek]

one way:

Format/Cells/Number/Custom [hh]:mm:ss

That doesn't work here (Excel 11) for the data the OP describes.

 

[JE McGimpsey]

have tried this and it will not reformat the cell just leaves the
original data there 02:02:30:59 will not convert it

If you have a string "02:02:30:59" then reformatting will not work,
since the value is not a number.

Use Ron's answer:

=LEFT(A1,FIND(":",A1,1)-1)+TIMEVALUE(RIGHT(A1,8))

in an empty column, and copy down.

Then copy the converted values, select the original values and choose
Edit/Paste Special, selecting the Values radio button. Format with

Format/Cells/Number/Custom [h]:mm:ss.

If you instead want 50 hours 30 minutes 59 seconds, format as

Format/Cells/Number/Custom [hh] "hours" mm "minutes" ss "seconds"