Hi,
I have a list of dates showing if an employee is on vacation or study leave etc. In my attached example I would like to insert a formula into cell b4 that tells me if the employee has had 10 or more consecutive days off. This should exclude weekends, so need formula to ignore all "WE" cells in row 5.
I have tried to apply the following array formula "=SUM(IF(FREQUENCY(IF(D6:U6="Holiday",COLUMN(D6:U6)),IF(D6:U6<>"Holiday",COLUMN(D6:U6)))>=10,1))"
This does not work for 2 reasons:
a) Its not clever enough to ignore the weekend days
b) Its not clever enough to recognise that for example "STUDY Leave" also counts as a day off in addition to "HOLIDAY".
Can anyone help me complete this formula correctly so that cell B4 displays 1 in the given example
Of course if there is a better way to do this, then Im all ears
Thank you so much for your help
Ciaran Mc Mahon
Bookmarks