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)

[TABLE="width: 782"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]SUN[/TD]
[TD]MON[/TD]
[TD]TUE[/TD]
[TD]WED[/TD]
[TD]THU[/TD]
[TD]FRI[/TD]
[TD]SAT[/TD]
[/TR]
[TR]
[TD]09:00-18:00[/TD]
[TD]09:00-18:00[/TD]
[TD]09:00-18:00[/TD]
[TD]09:00-18:00[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]09:00-18:00[/TD]
[/TR]
[TR]
[TD]09:00-18:00[/TD]
[TD]09:00-18:00[/TD]
[TD]09:00-18:00[/TD]
[TD]09:00-18:00[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]09:00-18:00[/TD]
[/TR]
[TR]
[TD]08:00-17:00[/TD]
[TD]08:00-17:00[/TD]
[TD]08:00-17:00[/TD]
[TD]08:00-17:00[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]08:00-17:00[/TD]
[/TR]
</tbody>[/TABLE]


In the table below SUN would be Cell AH2 so on and so forth (This would be employees previous week schedule)
[TABLE="width: 586"]
<colgroup><col span="6"><col></colgroup><tbody>[TR]
[TD]SUN[/TD]
[TD]MON[/TD]
[TD]TUE[/TD]
[TD]WED[/TD]
[TD]THU[/TD]
[TD]FRI[/TD]
[TD]SAT[/TD]
[/TR]
[TR]
[TD]11:00-20:00[/TD]
[TD]11:00-20:00[/TD]
[TD]11:00-20:00[/TD]
[TD]11:00-20:00[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]11:00-20:00[/TD]
[/TR]
[TR]
[TD]11:00-20:00[/TD]
[TD]11:00-20:00[/TD]
[TD]11:00-20:00[/TD]
[TD]11:00-20:00[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]11:00-20:00[/TD]
[/TR]
[TR]
[TD]10:00-19:00[/TD]
[TD]10:00-19:00[/TD]
[TD]10:00-19:00[/TD]
[TD]10:00-19:00[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]10:00-19:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[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