Need a formula to check shifts in a roster

**arifmasum** · 12-24-2012, 03:28 PM

Hi All,

I am working with a roster and can not understand which formula to use for checking shifts one after another. I have attached sample roster file here.

My Sifts are: SM = Super Morning, M = Morning, E = Evening, SE = Super Evening, N = Night, OFF = Off Day.

Conditions are:
1. Any employee will not join for SM or M shift after SE shift.
2. Any employee will not join for SM shift after E shift.
3. Any Female employee will not join for SE shift.

I need formula to check these conditions. The function may return "Check" or "OK" in the cell it works in. Please help if you can. Thanks in advance.

Arif

**Andrew-R** · 12-24-2012, 03:43 PM

In cell K3 try:

=IF(AND(OR(C3="Male",COUNTIF(D3:J3,"SE")=0),SUMPRODUCT((D3:I3="E")*(E3:J3="SM"))=0,SUMPRODUCT(--(D3:I3="SE"),--(ISNUMBER(FIND("M",E3:J3))))=0),"OK","Check")

**arifmasum** · 12-25-2012, 01:43 AM

Thanks a lot Andrew-R for your quick reply. The formula works fine with condition 2 and 3. But it does not work with condition1 which is any employee will not join SM or M shift after doing SE shift. Can you please help me to write that formula too.. Thanks a lot again.

**arifmasum** · 12-25-2012, 01:46 AM

Well now it works fine with all conditions.. I removed those (--) from the formula that's why it was not working. can you please explain the formula?? that will be very much helpful for all of us.. Regards.