A formula to multiply hour and minutes to each rate?

**mishxco** · 06-06-2013, 05:05 AM

Let's say for example I have a work overtime of 1 hour and 15 minutes (1:15). For each overtime hour, the rate is $66.56, and for each overtime minute, the rate is $1.10 (The price is bit high because I'm using the value for my country, not the dollar rate).

In this case, the solution would be (# of hours x 66.56) + (# of minutes x 1.1) = overtime pay. Thus, (1 x 66.56) + (15 x 1.1) = 83.06 (the overtime pay that I would be receiving).

Is it possible to encode a formula in excel in this way? That if I input the hours and minutes of overtime, the specific cell would automatically solve like this?

I hope someone can help me! Thank you!

**Martijnvc** · 06-06-2013, 05:13 AM

If you're timestamp is in say cell A1, use =HOUR(A1) and =MINUTE(A1) to retrieve hours and minutes, than it's a matter of multiplying. Good luck!

**Fotis1991** · 06-06-2013, 05:14 AM

..1 x 6.56) + (15 x 1.1) = 83.06 (the overtime pay that I would be receiving).

The result is 23.06. Not 83.06..Correct?

Try this

=HOUR(C1)*6.56+MINUTE(C1)*1.1

In C1 exist (1:15). Format the result cell as General.

**daddylonglegs** · 06-06-2013, 05:28 AM

How is it 1.1 per minute if an hour is 6.56, shouldn't the figure for minutes be 0.11?

**mishxco** · 06-06-2013, 05:57 AM

@Mart: Thank you! You're a great help! Made me understand what to do

@Fotis: Haha right, edited it already, thanks! I copy pasted your formula, by the way haha

)

@daddylonglegs: I'm just saying it hypothetically

**Fotis1991** · 06-06-2013, 06:32 AM

......................

**daddylonglegs** · 06-06-2013, 06:38 AM

Originally Posted by mishxco

@daddylonglegs: I'm just saying it hypothetically

OK, but it can make a difference to the best calculation to use - if the rate per minute is equal to rate per hour/60 (which is what you would normally expect) then there's no need to use HOUR and MINUTE separately in the formula, you can use a calculation like

hours*24*rate, e.g. if hours are in C1 and hourly rate in D1 you can use just

=C1*24*D1

**mishxco** · 06-06-2013, 07:27 AM

Originally Posted by daddylonglegs

OK, but it can make a difference to the best calculation to use - if the rate per minute is equal to rate per hour/60 (which is what you would normally expect) then there's no need to use HOUR and MINUTE separately in the formula, you can use a calculation like

hours*24*rate, e.g. if hours are in C1 and hourly rate in D1 you can use just

=C1*24*D1

The hour and minute rate is different. We use overtime hour rate of $66.56, while the minute rate is $1.10. So I can't calculate is all based on only either hour rate or minute rate. It has to be separated.