3 Replies Latest reply: Jun 27, 2013 2:21 PM by mistyj124
mistyj124 Level 1 Level 1 (0 points)

I am trying to calculate total hours worked for each day and each week. I want it to show as a decimal so it can easily be multiplied by and hourly pay. I have it displaying as hours and minutes right now. I used the formula: =((C6)-(B6)). The time is entered in as military time. I have read several threads and have not found a solution to fix my problem. Please help.


Mac OS X (10.6.8)
  • Wayne Contello Level 6 Level 6 (14,975 points)

    use the function dur2hours():

     

    Screen Shot 2013-06-27 at 1.39.39 PM.png

    C1=B1-A1

    D1=DUR2HOURS(C1)

     

    you can combine all into C1 like:

    C1=DUR2HOURS(B1-A1)

     

    I separated them for demo purposes

  • Jerrold Green1 Level 7 Level 7 (29,855 points)

    Misty,

     

    There's another way to get to decimal hours from a time of day difference that predates the introduction of the Duration format in Numbers. TIMEVALUE gives you the fractional day associated with a time of day, regardless of the date. (There can be an advantage to using TIMEVALUE if time of day values are entered on a different day than the day that the cell represents.) To do your calculate using the Date-independent TIMEVALUE function, you could write:

     

    =(TIMEVALUE(C6) - TIMEVALUE(B6)) * 24

     

    The multiplication by 24 converts from decimal day difference to decimal hours difference.

     

    Regards,

     

    Jerry

  • mistyj124 Level 1 Level 1 (0 points)

    Thank you! This is what I eventually figured out by accident. Thanks for your help.