Trying to Calculate hours worked over given week.

[tstep53]

Hello,

Sort of self taught here and struggling. Excel not really my strong suit. Working on an employee schedule that allows me to add up the hours scheduled for the week. Each day is in split cell format (B1= time in, C1= time out). This carries out for the week D & E, F & G, etc. I have the formula for subtracting time in from time out and then adding it through the whole week for a total.

=HOUR((C4-B4)+(E4-D4)+(G4-F4)+(I4-H4)+(K4-J4)+(M4-L4)+(O4-N4))

To test as I wrote I used 4 hour days for all 7 days. Clock on at 12:00 PM and leave at 4:00 PM. Formula works up to day 5 (K4-J4) with a value of 20, but then suddenly when day 6 and 7 are calculated I get a value of 4, rather than 28.

Am I missing something, or is there another formula I should be using?

Thanks,
tstep

 

[Tetra201]

Try custom-formatting the result cell as [h]:mm.

 

[Flyboy65]

Assuming your sign in/sign out start in A1/A2 and carry across, enter this formula in O3
=SUM((B3-A3)+(D3-C3)+(F3-E3)+(H3-G3)+(J3-I3)+(L3-K3)+(N3-M3))

 

[tstep53]

Ok, I custom formatted the result cell as suggested, but result is "0:00". Desired result should be 28:00. Either way the cell is formatted, the result was correct up until the last 2 days in the formula. Up through 5 days I had a result of 20 hours on the week. When adding 2 more days (4 hours each = 8 more hours) I should show 28 in result cell. Instead, the formula quits adding correctly and gives "4" as result with general formatted cell and 0:00 when custom formatting.

 

[arthurbr]

Are you sure you custom formatted as [h]:mm ?
28 hrs will return 4 if formatted as usual (hh:mm)

 

[Tetra201]

Ok, I custom formatted the result cell as suggested, but result is "0:00". Desired result should be 28:00. Either way the cell is formatted, the result was correct up until the last 2 days in the formula. Up through 5 days I had a result of 20 hours on the week. When adding 2 more days (4 hours each = 8 more hours) I should show 28 in result cell. Instead, the formula quits adding correctly and gives "4" as result with general formatted cell and 0:00 when custom formatting.

Oops, I somehow overlooked the HOUR(...). Just remove it and apply the custom format.

 

[tstep53]

Are you sure you custom formatted as [h]:mm ?
28 hrs will return 4 if formatted as usual (hh:mm)

Even if the result cell is general to give me a general number, that's fine. My issue is that my formula is not working. Should I use a different formula?

=HOUR((C4-B4)+(E4-D4)+(G4-F4)+(I4-H4)+(K4-J4)+(M4-L4)+(O4-N4))

Assume the following, the employee works 7 days a week. Employee logs in at 12 PM and logs out at 4 PM each day. 4 hours/day @ 7 days = 28. When I did not get the desired outcome, I backed up and entered each day to the formula one day at a time. First day = 4. I added the second day = 8, etc. after 5 days (K4-J4) I had 20 hours worked. So far, so good. Then I added day 6 and suddenly the result was 8, and when I added day 7, I got 4. The formatting and result cell were fine up through 5 days, then suddenly it went wrong. Is there a different formula I should be looking at, or am I missing something with my current formula?

 

[Flyboy65]

Try the SUM function instead of the HOUR function. Seems to work for me.

 

[Tetra201]

Try the SUM function instead of the HOUR function. Seems to work for me.

No need to use SUM(...) here -- the plus signs in the formula do the job. See Post #6.