Converting minutes+seconds into decimal

We have a company spreadsheet that is used for updating cycle times on parts and we enter the new cycle times in minutes + seconds so that we can update it in the database. They had set this up before me starting however it is using a formulae of "=HOUR(C125) + MINUTE(C125) / 60 + SECOND(C125) / 3600" which was working until it came to trying to put something in higher than 24 as it would reset back to 0 and then continue so if it was 26 it would come back as "2.00" assuming its because it has the hours but how would i get it to turn minutes + seconds into a decimal?

Attatched is the document and at the bottomis where i've been trying to see what the issue was.

Thanks

Perhaps try =IF(C194>TIME(24,0,0),TEXT(C194,"[h]:mm:ss")*24,C194) and format as number

You can use this formula instead in D27:

=C27*24

then copy down by double-clicking the fill handle (the small black square in the bottom right hand corner of the cursor), with D27 selected.

This will take account of any hours above 24 - check out D100. However, C86 will need to be changed.

Hope this helps.

Pete

Originally Posted by etaf

does this help
=INT(C196)*24+HOUR(C196) + MINUTE(C196) / 60 + SECOND(C196) / 3600

This seems to be working but is there a way to get it in C196 to appear as "29:59"/"29.59" as when using this it comes back as " 29:59:00"

Originally Posted by Pete_UK

You can use this formula instead in D27:

=C27*24

then copy down by double-clicking the fill handle (the small black square in the bottom right hand corner of the cursor), with D27 selected.

This will take account of any hours above 24 - check out D100. However, C86 will need to be changed.

Hope this helps.

Pete

So this is working for the decimal side but i have another question on how i get the "CYCLE TIME" column to show correctly as in, if i enter anything above 24 in here then it will go back to 0 and count up, so if i entered 25:25 (25 minutes 25 seconds) the decimal value is correct but in the cycle time column it shows as "01:25" when i'm wanting it to show as "25:25" any help?

You should use a Custom Format for that column of:

[hh]:mm

The square brackets ensure that the time displays do not wrap at 24 hours.

Hope this helps.

Pete