convert a decimal to minutes and seconds

[dodger999]

guys,
i have a problem all i want to do is convert 1.60 into minutes and seconds not sure how to do it, it should be approx 1 minute 35 seconds ,can anybody help thanks

[protonLeah]

If you can give me the steps you would take to calc by hand w/pencil and paper, then I can show you the Excel functions for each step.

[dodger999]

in laymans terms i have 738 pallets i loaded 66 vehicle's taking 7 seconds for each pallet giving me 462 seconds to load my vehicles.when i divide by the pallets i would like it to give me the time in seconds not a decimal does this make sense sorry about this

[protonLeah]

With 462 in some cell, say D10, then:
to get the whole minutes, in some other cell you use the QUOTIENT function:

Please Login or Register  to view this content.

i.e. 462/60 = 7 (mins)
to get the remainder, you will use the MOD fn in another cell, thus:

Please Login or Register  to view this content.

remainder of 462 divided by 60 is 42 seconds

If you don't intend to use the results in further calculations you can combine the two functions in one cell as a STRING:

Please Login or Register  to view this content.

7 min, 42 secs

[teylyn]

in laymans terms i have 738 pallets i loaded 66 vehicle's taking 7 seconds for each pallet giving me 462 seconds to load my vehicles.when i divide by the pallets i would like it to give me the time in seconds not a decimal does this make sense sorry about this
Edit/Delete Message

If it takes 7 seconds for each pallet, how do you arrive at 462 seconds? 462 is the result of vehicles * seconds. But you said it takes 7 seconds to load a pallet, not 7 seconds to load a vehicle. What am I not getting?

If it takes 7 seconds to load a pallet, then

738 pallets * 7 seconds = 5166 seconds to load 738 pallets

Divide this by the number of vehicles to arrive at seconds per vehicle.

5166 seconds / 66 vehicles = 78.27272727 seconds per vehicle

If this result is in A1, then use

=time(0,0,A1)

and format the cell with [hh]:mm:ss to arrive at 0:01:18


But if it takes 7 seconds to load a vehicle (mighty fast, I'd think!!), then the calculation would be:

66 vehicles * 7 seconds = 462 total seconds

462 seconds / 738 pallets = 0.62601626 seconds per pallet

That can't be expressed in a time value, since it's less than a second.

Which will it be?

[zbor]

Or perhapes this:

=CONVERT(A1,"hr","mn")/1440

[DonkeyOte]

i have a problem all i want to do is convert 1.60 into minutes and seconds not sure how to do it, it should be approx 1 minute 35 seconds

Assuming your existing formula is correct then you can simply divide the value by 1440 (ie 24*60)

=(E6/(E7*E8))/1440

Apply format to E9 of mm:ss ... if you're interested in the ms then use mm:ss.000

[dodger999]

Thanks guys it was the divide part i used i did try that but forgot to format cell all solved

[ThreePointOneFour]

With 462 in some cell, say D10, then:
to get the whole minutes, in some other cell you use the QUOTIENT function:

Please Login or Register  to view this content.

i.e. 462/60 = 7 (mins)
to get the remainder, you will use the MOD fn in another cell, thus:

Please Login or Register  to view this content.

remainder of 462 divided by 60 is 42 seconds

If you don't intend to use the results in further calculations you can combine the two functions in one cell as a STRING:

Please Login or Register  to view this content.

7 min, 42 secs

Just wanted to say thank you for the help. My brain was cooking up here at work, so I figured I should google for a solution and signed up in this forum; this saved my evening.