Calculate number of sickness days with rolling shift patterns

[Aiburto]

I am looking to find out if there is a way to use excel formula to work out the cumulative number of working days a employee has been absent for when they work shift patterns

So if the shift patterns are 4 on 4off, 2 days week one and 5 days week day two and the last shift pattern we have is 2 days each week on a rolling rota

Trying to find out if there is a way to only count sickness days for the days they are supposed to work each rotation? Hope someone has some clever way to help!!

And if there is would I need to input each employees shift patterns and assign to them to calculate these?

 

[Aiburto]

Help please

 

[jasonb75]

There should be a way to do what you are asking for, however, I suspect that most people would be reluctant to try and devise a solution to such a task without seeing an example of your sheet layout first (you can use the XL2BB tool to upload a sample, link in my signature at the foot of this post.

 

[Aiburto]

So using Networkdays.intl is there anyway to link each employees shift patterns using the holidays function to count dates they are not rota on to work but also connect each networkdays to an employee number so that when it’s input on sickness tracker it pulls through the correct number of days they have been off for each occasion

employee number vlookup connected to pull through employee details

 

[Aiburto]

Start date sickness

total number of days

Looking to find out if the network intl can be connected to individual employees so when employee number are entered it pulls through specific networkdays with days Rota’d off attached for specific employees