Convert Hours to Minutes and Seconds

Hello all,

I am trying to use the Mod function to convert the following:

I have time in hours in A1 as 12.66 and I need it to convert to 12 hours in A2, 39 minutes in A3, and 36 seconds in A4. What formula can I use to achieve this?

Thanks.

Originally Posted by Special-K

Just use the built in Excel functions

In A2
=HOUR(A1)
In A3
=MINUTE(A1)
In A4
=SECOND(A1)

That won't work if the actual number in cell A1 is 12.66 (not a time serial number). Try it.

I concur with Special K's approach but if you have a decimal figure like 12.66 in A1 then you'll need to use

=HOUR(A1/24)
=MINUTE(A1/24)

and

=SECOND(A1/24)

but if the hours might be over 24 then better to use

=INT(A1) for hours

Hmmm, I thought I tried that and it didn't work... But it does. Sorry for the confusion. Use the formulas from daddylonglegs post.

Thanks jasoncw and daddylonglegs formulas both worked. But i have another question because i am so new to excel. I am trying to do the same thing but instead I'm converting X dollars into dollars, quarters, dimes, nickels, and pennies. Any suggestions?

Well, I hope I don't make myself look too inexperienced again... But I do not believe Excel has any type of built in currency coin counter function. If they do, that would be interesting to see, but if not, here you go:

[A1] Currency in dollars

[A2] Dollars:
=INT(A1)

[A3] Quarters:
=INT((A1-A2)*100/25)

[A4] Dimes:
=INT(((A1-A2)*100-(A3*25))/10)

[A5] Nickels:
=INT(((A1-A2)*100-(A3*25)-(A4*10))/5)

[A6] Pennies:
=(A1-A2)*100-(A3*25)-(A4*10)-(A5*5)

Do you mean in to the smallest number of each denomination?

E.g. $8.88

is 8 dollars
3 quarters
1 dime
3 pennies?

Here's one way to do it

If your dollar amount is in A3 then in B2 across put in figures in dollars to represent your denominations so B2 has 1, C2 has 0.25, D2 has 0.1, E2 has 0.05, F2 has 0.01

[representing dollars, quarters, dimes, nickels and pennies respectively]

In B3

=INT(A3/B$2)

In C3 copied across to F3

=INT(ROUND(($A3-SUMPRODUCT($B$2:B$2,$B3:B3))/C$2,2))

You can adapt this for more denominations, e.g. $5, $10, $50 bills etc.

you guys are amazing. i have one last question, i promise. on the original formula where i converted time in hours to hours, minutes and seconds...how would i convert lets say 8000 seconds into hours, minutes and seconds?

thank you, thank you!!!

As before there are several ways but I'd use

=INT(A1/3600)

=MINUTE(A1/86400)

=SECOND(A1/86400)