Excel Sum Function Question

[cmarty5445]

Trying to create a spreadsheet that tracks overtime at work.

Little background...Work 24 hr shifts as public safety dispatcher. We are also set up on an overtime rotation. Once you hit 6hr of overtime, you are rotated to the bottom and you go back to 0 hrs of overtime.

Trying to figure out which sum function to use to track overtime. Here's the problem:

Say I have 4 overtime shifts logged:

9/1/08 2hrs
9/2/08 2hrs
9/3/08 2hrs

I know you can use the sum function to calculate (obvious answer is 6), but one 6hrs is obtained, the person is dropped on the rotation list and overtime accumulation reverts back to 0hrs.

I want to know if there is a function available that will allow me to enter overtime and when 6hrs is obtained, the spreadsheet will revert back to 0hrs automatically.

Sounds confusing and its kinda tough to explain. I am willing to answer any questions in order to figure this out. It's been about 8 years since I have used excel, so please help in anyway you can!

Thank you!

[Mantas]

If I understood your requirements correctly, I'd use MOD function as the wrapper of the SUM function:

=MOD(SUM(TheRangeToBeSummed), 6)

Then:
* if the sum of overtime hours is 5, you'd get 5 as the answer.
* if the sum of overtime hours is 6, you'd get 0 as the answer.
* if the sum of overtime hours is 7, you'd get 1 as the answer.

Or do you expect something else?