Dear Excel Guru's Kindly need your help to get the consecutive 02 days OFF and 04 days OFF count for my staff roster separately.
I want to get the values as shown in green cell. Attached excel file is for your reference and above image as well.
I want to get the values as shown in green cell. Attached excel file is for your reference and above image as well.
Days_OFF_Formula_Help.xlsx | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
1 | ||||||||||||||||||||
2 | Result Needed | |||||||||||||||||||
3 | Name | 01-Nov | 02-Nov | 03-Nov | 04-Nov | 05-Nov | 06-Nov | 07-Nov | 08-Nov | 09-Nov | 10-Nov | 11-Nov | 12-Nov | 13-Nov | 2 Consective OFF | 4 Consective OFF | ||||
4 | Jack | M | M | M | M | M | OFF | OFF | M | M | M | OFF | M | M | 1 | 0 | ||||
5 | Mike | M | M | M | M | M | OFF | OFF | OFF | OFF | M | M | M | M | 0 | 1 | ||||
6 | Tim | M | M | M | M | OFF | OFF | M | M | M | M | M | OFF | OFF | 2 | 0 | ||||
7 | ||||||||||||||||||||
8 | M | 09:00 - 17:00 | ||||||||||||||||||
9 | ||||||||||||||||||||
Sheet1 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
F8 | Cell Value | contains "M" | text | NO |
F8 | Cell Value | ="OFF" | text | NO |
D4:P6,G8 | Cell Value | contains "M" | text | NO |
D4:P6,G8 | Cell Value | ="OFF" | text | NO |