Ask Experts Questions for FREE Help !
Ask
    MsMollie's Avatar
    MsMollie Posts: 6, Reputation: 1
    New Member
     
    #1

    Nov 5, 2009, 10:09 AM
    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
    MsMollie's Avatar
    MsMollie Posts: 6, Reputation: 1
    New Member
     
    #2

    Nov 5, 2009, 10:13 AM
    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
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #3

    Nov 5, 2009, 02:01 PM
    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.
    Attached Files
  1. File Type: xls SampleTime.xls (13.0 KB, 319 views)

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

How to connect one excel sheet to other excel sheet [ 28 Answers ]

How to connect one excel sheet to other excel sheet.

How to calculate total hours scheduled in excel spreadsheet [ 8 Answers ]

Hi, I am trying to create a schedule for my employees and at the same time being able to calculate (or forcast for myself) the total hours that will be worked. e.g. A B C D E F G H I... P 1 NAME MONDAY...

Total hours from selected records [ 1 Answers ]

I have one table and the fields are start time and end time. I'm already find working hours from stime and etime. But I want total working hours for selected records. i.e I want total hours for 10 selected records.. in msacces

Connest two sheets like I have counts in sheet 1 and want total in sheet 2 [ 1 Answers ]

Connest two sheets like I have counts in sheet 1 and want total in sheet 2 .


View more questions Search