PDA

View Full Version : Require a formula


rdiamond
May 1, 2009, 09:14 PM
I know I got paid $220 in 11:00 hours( I'm using the 24:00 format in the total time column.)
And I know the answer is $20/hr, but what is the formula to calculate that hourly rate.

JBeaucaire
May 1, 2009, 11:17 PM
Since time is calculated as a 24th fraction of a day, you have to reverse that for reverse logic such as this.

A1:
$220.00

B1:
11:00 (11 hours 0 minutes)

C1:
=(A1/B1)/24

rdiamond
May 2, 2009, 08:42 AM
I'm using Microsoft office excel 2007, and I tried and this does not work in my spreadsheet.

Zazonker
May 2, 2009, 12:06 PM
You probably got the right answer but can't see it. Excel quite likely formated C1 for you in time format and your answer shows as 0:00. Change the format to currency and you should see $20.

There are a number of additional issues when dealing with time in Excel. Just remember that to Excel, 11:00 means 11 am by default, not 11 hours of elapsed time.

JBeaucaire
May 2, 2009, 04:24 PM
Just remember that to Excel, 11:00 means 11 am by
default, not 11 hours of elapsed time.

That's "sort of" correct. If you let Excel display a time value AS time, yes, it will display 11:00 as 11:00 AM, unless you override that display without the AM/PM.

But you have to understand that TIME is actually calculated numerically as 1/24 of the number 1. That's why we multiply the actual numeric value by 24 to get a normal decimal.

If you have a time of 12 noon in A1, click that cell and change the format to GENERAL you will see a value of .5 appear. 11:00 AM would show as .458333.

So, multiply 11:00 AM (.458333) by 24 and the result is 11, an integer.

Pretty cool once you get a hold of that logic. That's why my formula DOES work once the cell is formatted properly.

Click the cell with my formula in it and press CTRL-1 to open the format cell box. Set the number format to general.