Calculate Days worked at reset the count when specific criteria is matched

[Alcore]

Hello everyone,

Thank you in advance. I have been working on this for a while today and can't sort it out. Has been a long time since I have tried to do anything complex in Excel.
So this is a days worked tracker I want to create for work.

Sheet 1 - Row one begin employee names. Column 1 is the date. We have numbers assigned to equal Reg Day, OT day, Sick and so on that are entered for each employee each day.

B C D E F
1 1 1 1 1 1
2 1 1 1 1 1
3 1 1 2 1 1
4 1 1 1 1 1
5 1 1 1 1 4

Sheet 2 - I have this sheet set up to take all the "values" for Reg day and OT day and turn them into "1" for days worked and "0" for days where no time was put in.

1 0 0 0 0 0
1 0 0 0 0 0
1 1 0 1 0 0
1 0 0 1 0 0
1 0 0 0 0 0
0 1 0 1 1 1

What I want to do is, and this can be on a separate sheet if it is easier, have Excel count the days worked up, 1,2,3,4,5, but the count needs to reset back to 1 under certain conditions. I will try and explain the conditions the best I can. So the days worked will work as followed;

- if you work 1 - 6 days you need to take 1 off then it will reset to 0
- if you work 7 - 12 days you need to take 2 consecutive days off then it will reset to 0, if the days off are not consecutive, ex. work 10, took 1 day off then back to work for 1 day, your
total days worked would actually be 12 days, the day off counts as a day worked
- if you work 13 - 18 days you need to take 3 consecutive days off then it will reset to 0, non consecutive follows the rule above
- if you work more than 18 days you need to take 4 consecutive days off then it will reset to 0, non consecutive follows the rule above

I hope that makes sense to people. I don't care if this takes multiple sheets to do or not. Would be very thankful for any assistance with this matter.

[bebo021999]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Alcore]

Attached is a copy. Hope this helps. See OP for days off rules. Thank you.

[JeteMc]

Hello Alcore and Welcome to Excel Forum.
If I understand correctly then I believe that this will do what you want.
Rows 2:3 are populated using: =IF('S2'!B2="0", 0, SUM(B1,1))
Row 4 is populated using: =IF('S2'!B4="0", 0, IF(AND(B3=0,B2>=7,B2<=12),SUM(B2,1),SUM(B3,1)))
Row 5 is populated using: =IF('S2'!B5="0", 0, IF(AND(B4=0,B3>=7,B3<=18),SUM(B3,1), IF(AND(B3=0,B4=0,B2>=13,B2<=18),SUM(B2,1),SUM(B4,1))))
Rows 6 and down are populated using:

Please Login or Register  to view this content.

Note the behavior of the formula in S59:S75
If modifications are needed please upload a smaller file that also displays examples of each of the scenarios given in the first post.
Let us know if you have any questions.

[Alcore]

Thank you very much for the response. This is almost exactly what I was looking for. Change the last formula a bit. =IF('S2'!B6="0", 0, IF(AND(B5=0,B4>=7),SUM(B4,2), IF(AND(B4=0,B5=0,B3>=13),SUM(B3,3),IF(AND(B3=0,B4=0,B5=0,B2>=19),SUM(B2,4),SUM(B5,1)))))

Thank you for the assistance with this, it is greatly appreciated.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your fist post. I hope that you have a blessed day.