Excel Formula or VBA Macro for employee schedule

[JonEh]

Hello,

I've been working on a formula to count the amount of consecutive days an employee has worked, and reset the count if a certain amount of days off has been met(criteria below). I need something that can check the previous month which is on a separate sheet to ensure the count isn't interrupted coming into a new month each time. The template I'm using is extremely similar to this: https://templates.office.com/en-us/Employee-absence-schedule-TM03987167 --- The criteria for days off is explained in the image below. I would also like a function to send an email to certain individuals if an employee has worked past 18 days, this way we can be sure employees aren't over-worked. A day worked is simply marked with an "x" and left blank if the employee has not worked.

Any help with this would be MUCH appreciated, thank you!

​

 

[JonEh]

Bump - Still seeking assistance.

 

[JonEh]

Bump.

 

[JonEh]

Bump

 

[JonEh]

Bump.

 

[JonEh]

Bump ...

 

[MartyS]

Questions:

What is a work week? 3 days, 5 days, 6 days, 7 days?

If "Work Week" is a 5 day week, are weekends included on your spreadsheet?

If "Work Week" is a 5 day week or less, are weekends part of the calculation?

If "Work Week" is a 6 day week, or less, do the days not included in "Work Week" affect the calculation? If so how?

If an employee is entitled to one day of rest per work week, how is it possible to have 3 or 4 consecutive work weeks? Instead of entitled, does this mean "accrues"?

If Employers must allow an employee 4 consecutive days of rest after each period of 24 consecutive work days, would that be made of up 24 days in a row, 4 lots of 6-day-work-weeks, almost 5 lots of 5-day-work-weeks?

Could you provide some sample data with accurate manual entries to demonstrate?