Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Excel Time Sheet - Error in calculating total hours (https://www.askmehelpdesk.com/showthread.php?t=412996)

  • Nov 5, 2009, 10:09 AM
    MsMollie
    Excel Time Sheet - Error in calculating total hours
    I have this set up pretty basically, I believe. Column C are the days of the week, Column D is the date, Column E is the time clocked in, Column F is the time clocked out and Column G are the total hours worked for that day. In G18, I've set up (or so I think) the formula to calculate this employees total hours for the work week =(G4+G6+G8+G10+G12+G14+G16). As of now, my employee has worked 32 hours but it is showing only 7 hours total. Our work week runs from Sunday to Saturday and not all of the employees work the weekends. This employee did not work Sunday and after reading another discussion question, I've come to realize that the time in and time out for Sunday cannot just be blank. If that is the reason for my error in total hours, what formula or word or WHATEVER can I type in to make the value 0?

    (I thought a visual may help)

    -----C-----D-----E-----F-----G-----
    Sunday 1-Nov
  • Nov 5, 2009, 10:13 AM
    MsMollie
    Whoops! Still new to this lol One more time!


    -----C------D------E------F------G------
    Sunday 1-Nov 0:00
    Monday 2-Nov 9:00 4:00 7:00
    Tuesday 3-Nov 5:30 1:30 8:00
    Wed 4-Nov 7:30 5:30 10:00
    Thurs 5-Nov 7:00 1:00 6:00
    Friday 6-Nov 0:00
    Sat 7-Nov 0:00
    Total hours: 7:00
  • Nov 5, 2009, 02:01 PM
    JBeaucaire
    1 Attachment(s)
    Code:

      C                    D                  E                F        G
    Sunday                11/1/2009                       
    Monday                11/2/2009        9:00 AM            4:00 PM        7.00
    Tuesday                11/3/2009        5:30 AM            1:30 PM        8.00
    Wednesday        11/4/2009        7:30 AM            5:30 PM        10.00
    Thursday        11/5/2009        7:00 AM            1:00 PM        6.00
    Friday                11/6/2009                       
    Saturday        11/7/2009                       
                                                  TOTAL        31.00

    Be sure to enter time in E and F as actual time values, including the AM/PM values so they get cacl'd properly.

    Then the G1 formula is:
    =(F1-E1) * 24
    ... formatted as General. Copy that down.

    The total in G8 is simply:
    =SUM(G1:G7)

    By converting the values in column G back to standard decimal values, the TOTAL is easier.

  • All times are GMT -7. The time now is 12:01 AM.