Calculating consecutive working dates

Hi All,

I have a difficult question on hand. Please see the attached excel example.

Background
In the attached excel, it shows 31 days in Jan 2020. The numbers (B2:AF4) represent the number of dutuies in each calender date.

Rules
According to company rules:
1.a person can have more than 1 duties on the same day
2.a person cannot work for 7 consecutive days

Questions

What excel function/VBA code can help to attain the below listed results? I willl use 1 person (Anna) to illustrate:

- Check the values from 1 Jan to 7 Jan (B2:H2)
- if there is one cell's value is not equal to to 0, then pop up message box "7 consecutive working days"
- if is at lease one cell's value equal to 0, then continue to check the values from 2 Jan to 8 Jan (C2:I2)
- The last checking process should end after checking the cell's value from 25 Jan to 31 Jan


Thanks for your input

Cross-posted at: https://www.mrexcel.com/board/thread...dates.1121117/
Please read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html

I've reinstated your request. There is no problem in having the same query on two forums just as long as you tell us that this is the case.

If the problem has been solved on the other forum, then do us the courtesy of sharing the solution here and marking the thread as solved.

Please do not remove posts in future. Thanks.

There is no such Office version as 360 - please update your user profile to 365. Thanks.