Hello,
I do have a file with rota for associates with columns as a day of month and rows with names. As value use IN or OFF on certain days.
Occasionally I use 3rd value as OT for overtimes.
I need a formula which will indicate how many consecutive days was the associate working, and give the highest number.
I've managed to get it working for value IN only. I need to count both OT and IN as consecutive value.
Tried with IF(AND... but it doesn't work.
I do have a file with rota for associates with columns as a day of month and rows with names. As value use IN or OFF on certain days.
Occasionally I use 3rd value as OT for overtimes.
I need a formula which will indicate how many consecutive days was the associate working, and give the highest number.
I've managed to get it working for value IN only. I need to count both OT and IN as consecutive value.
Excel Formula:
{=MAX(FREQUENCY(IF(D45:AH45="IN",COLUMN(D45:AH45)),IF(D45:AH45<>"IN",COLUMN(D45:AH45))))}
Tried with IF(AND... but it doesn't work.