Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Require a formula (https://www.askmehelpdesk.com/showthread.php?t=348620)

  • May 1, 2009, 09:14 PM
    rdiamond
    Require a formula
    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.
  • May 1, 2009, 11:17 PM
    JBeaucaire

    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
  • May 2, 2009, 08:42 AM
    rdiamond

    I'm using Microsoft office excel 2007, and I tried and this does not work in my spreadsheet.
  • May 2, 2009, 12:06 PM
    Zazonker
    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.
  • May 2, 2009, 04:24 PM
    JBeaucaire
    Quote:

    Originally Posted by Zazonker View Post
    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 05:32 AM.