Absence Reporting

Hi All

I am lookking to build a google sheet that will calculate the periods of absence using a formula. A period of absence is defined as continued absence over planned shifts. People will have rest days during these (RDO). I need to exclude these ideally.


Below is a link to a google sheet with the example on of what the desired output is.

Any help would be greatly appreciated
https://docs.google.com/spreadsheets...gid=1023405449

Thank you in advance

D.

Some additional information:

In the example I only have 2 people, this will be used for hundreds
some members of the team work only weekends
Some member of the team work any 5 from 7 days however, this will always be seperated by RDO, Lieu Time Or Holiday

Thank you for the quick reply.

This sort of worked however, when applied to a more "Random working pattern this seem to break down. if some is to say work Sunday, Wednesday & Saturday,

If I enter this working pattern over the same period of time as shown in the sheet it would be 12 days / 1 Period of absence. Neither of the columns in the query match either of these so I am a little confused.

It is also worth noting that I do have a table of data with the absences in

This is simply the persons name, Person ID, Date, Absence type, Hours if this would be any easier to work with? I will update the sheet with a sample of this data too as I think that, that may help.

D

Hi janmorris

Thank you sooo much for this the solution works a like a charm for 99% of the team it is just the pesky public holidays that have thrown this out for some people.

Any suggestion on how to work around that would be amazing. Thank you again for your help so far!

Thanks
D