Formula help to locate work schedule day off pattern

Hello, I would be grateful for some assistance. I'm looking for a formula that could identify via an excel work schedule anyone who takes three (3) of the same days off in a given month; no matter the hours, etc. The only exclusion would be for a sick or personal leave day which is represented by a "S" or "PL" or "PLv". The hours can range for instance if someone requested 3 hrs of personal leave off the code on the schedule would look like "3PL", etc. If someone were to have 3 of the same days off, I was hoping that the word "ALERT" would appear in a column off to the right of the schedule. I've attached a sample workbook. Thank you for any suggestions!

edit: Forgot the "DO" represents the individuals normal day off and that too would need to be excluded from the formula.

Well I've been trying to figure a work around to do what I need, but it appears I'm stuck.. I'm attaching another workbook upon which I am trying to use a sumproduct isnumber search to identify named ranges, etc for "V" and "C" on my normal schedule for each day of the week. Now I need a formula to look and add any values from CE12 through DI12 but only if there is a "Mon" in the row range from CE3 over to DI3. Any suggestions? I'm trying to put this particular formula in cell DJ12.

Thanks for your time!

Pl explain with more examples and details what is required.

Kvsrinivasamurthy-Thank you for interest at looking at this problem. Initially I was trying to figure out a formula that could identify if an employee had requested the same day of the week off three or more times in a given month. The employers policy is you can only request the same day of the week off twice (example two Fridays if the employees normal scheduled days off are Saturday and Sunday, giving them a long weekend, etc.). The only exceptions to this are if an employee uses a personal leave day or sick day (symbols on schedule will contain "PL" and "S" in them).

As I couldn't personally figure out a formula that could look at the "Mon", "Tue", "Wed", etc in one row and then the individual employee row (i.e. Test2, etc) and see if there are any vacation ("V") or compensatory ("C") time off requests that fall on the same day of the week, that exceed 3 or more times.

When I initially posted sample 1 without any suggestions, I tried to figure a work around which is indicated in "sample2" using named ranges and sumproduct formulas to put a "1" if a "C" or "V" is located. This unfortunately resulted in me having to recreate and use a lot of cells to do this. Then in column DJ12 It was My thought to similarly as what was requested initially is have a formula that could count how many 1's were in a Monday column, Tuesday column, etc and if it had 3 or higher then I could create that "alert" notification I originally wanted if such a situation arose. Hopefully this explains a little better. I can try to post a spreadsheet with more descriptions also. Thanks again for your time looking at this.

This may be the expected formula.
In DJ12 and dragged across.

Please Login or Register  to view this content.