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

[dlealb]

Hi All,

Seeking Assistance on this Please.


On Cell P3 i wanted to display a text like "6 Days" or "7 Days" else "5 Days" (This would be a nested if I Think

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


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

P.S. Attached is a sample excel File (Reposted the File Apologies on the Miss)

[AliGW]

You have not provided any expected results. Please add these manually to the cells in question and post again.

[dlealb]

Hi Ali,

Apologies on the Miss
We have re-posted the File

We appreciate the assistance

[AliGW]

OK - so now you need to EXPLAIN your expected results! We are good, but we are not mind-readers (you might wish to take this into account in your other thread, too).

[dlealb]

Right

So Basically what all this about is just a checker if the employee will be on a 6 day straight schedule for next week vs his/her current schedule or a 7 day sched or just a regular 5 day sched
This way we can adjust an employee's schedule and provide him a transition off as we fit or whatnot in case he/she will be on a 6 or a 7 day straight schedule.

[AliGW]

Yes, that bit is obvious! However, we need to know if the weeks are always defined in the same way - does the count always start with Saturday of the current week?

[AliGW]

This may be what you want:

=7-COUNTIF(H3:M3,"OFF")+COUNTIF(AN3,"OFF")&" days"

[dlealb]

In our Office our day starts on a Sunday so the Count starts on Sundays. The weeks are indeed defined as it is based on the attached File.
Every Friday we consolidate what other managers send over to our department and send it to our reporting team and we are the ones tasked to double check what our partner sites have provided us and if we find that the agent will be on a straight 6 or 7 days schedule we then adjust the agents schedule as we see fit.

[AliGW]

Did you try my suggestion in post #7?

Administrative Note:

I have now edited your last three posts and left edit notes. Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[dlealb]

Right QUICK REPLY

Your suggestion did the Trick madam
We appreciate the assistance this would save us a lot of time checking stuff manually

[AliGW]

No problem!