I have a calendar and I'm trying to calculate the hours worked.
In cells C7 and D7 are start and stop times for Saturday and E7 start , F7 stop... for Monday. This repeats across to Sunday at O7 and P7.
in Q6 is this formula
I subtract the lunch break as set in B4 as 0:30, only for weekdays, as weekends are short shifts without a lunch break.
I have a custom formatting on all the cells, [h]:mm
Interestingly, if I enter 07:00 in E7 and 15:30 in F7 I get 6:00, but if I enter those times for all 5 weekdays I get 40:00
If I enter those times in two days, say Monday and Tuesday, then I get 14:30
<colgroup><col><col span="14"><col></colgroup><tbody>
</tbody>
When doing the lit review, I see several references to adding times, and differences, but not when they are combined. I thought it was a formatting issue. If I cut the formula down to only one day, then the value is correct, but when I have many of the (H7-G7-$B$4) I get the error/wrong value.
Thanks,
D
In cells C7 and D7 are start and stop times for Saturday and E7 start , F7 stop... for Monday. This repeats across to Sunday at O7 and P7.
in Q6 is this formula
Code:
=SUM((D7-C7),(F7-E7-$B$4),(H7-G7-$B$4),(J7-I7-$B$4),(L7-K7-$B$4),(N7-M7-$B$4),(P7-O7))
I subtract the lunch break as set in B4 as 0:30, only for weekdays, as weekends are short shifts without a lunch break.
I have a custom formatting on all the cells, [h]:mm
Interestingly, if I enter 07:00 in E7 and 15:30 in F7 I get 6:00, but if I enter those times for all 5 weekdays I get 40:00
If I enter those times in two days, say Monday and Tuesday, then I get 14:30
0:30 | Sun | Mon | Tue | Wed | Thu | Fri | Sat | ||||||||
1 | 2 | 3 | 4 | 5 | 6 | 7 | Hrs Worked | ||||||||
Amy | 6:00 | 8:00 | 7:00 | 15:30 | 7:00 | 15:30 | 9:00 | 15:30 | 7:00 | 15:30 | 7:00 | 15:30 | 9:00 | 12:00 | 43:00 |
Beth | 7:00 | 15:30 | 7:00 | 15:30 | 7:00 | 15:30 | 7:00 | 15:30 | 7:00 | 15:30 | 40:00 |
<colgroup><col><col span="14"><col></colgroup><tbody>
</tbody>
When doing the lit review, I see several references to adding times, and differences, but not when they are combined. I thought it was a formatting issue. If I cut the formula down to only one day, then the value is correct, but when I have many of the (H7-G7-$B$4) I get the error/wrong value.
Thanks,
D