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.
![]() |
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.
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
I'm using Microsoft office excel 2007, and I tried and this does not work in my spreadsheet.
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.
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.
All times are GMT -7. The time now is 03:40 AM. |