Help with Formula to Count if an Employee will be on a 6 day straight sched or 7 days Straight Sched

[dlealb]

Hi All,

Would an expert on this Please.


On Cell P3 i wanted to display a text like this "6 Days" or "7 Days" else "5 Days"

I have a schedule that looks Like this: (The name of the employee would be on Cell B2)

Cell H2 would be Sunday (This would be the Current Week)

SUN	MON	TUE	WED	THU	FRI	SAT
09:00-18:00	09:00-18:00	09:00-18:00	09:00-18:00	OFF	OFF	09:00-18:00
09:00-18:00	09:00-18:00	09:00-18:00	09:00-18:00	OFF	OFF	09:00-18:00
08:00-17:00	08:00-17:00	08:00-17:00	08:00-17:00	OFF	OFF	08:00-17:00

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

In the table below SUN would be Cell AH2 so on and so forth (This would be employees previous week schedule)

SUN	MON	TUE	WED	THU	FRI	SAT
11:00-20:00	11:00-20:00	11:00-20:00	11:00-20:00	OFF	OFF	11:00-20:00
11:00-20:00	11:00-20:00	11:00-20:00	11:00-20:00	OFF	OFF	11:00-20:00
10:00-19:00	10:00-19:00	10:00-19:00	10:00-19:00	OFF	OFF	10:00-19:00

<colgroup><col span="6"><col></colgroup><tbody>
</tbody>

 

[Special-K99]

On Cell P3 i wanted to display a text like this "6 Days" or "7 Days" else "5 Days"

But the employee is only working 4 days straight this week. Sun Mon Tue Wed with Thu Fri OFF.

"Count if an Employee will be on a 6 day straight sched or 7 days Straight Sched"
Is this irrespective of when the week starts?

"In the table below SUN would be Cell AH2 so on and so forth"
AH2 ??? Is this a typo?
So the current week is in column H but the previous week is all the way across in column AH ??? 26 columns to the right?
Whats in the remaing columns from O-AG ?

Use this and post some data so we can see the layout properly

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html