My X-No working day in the weekend (without offs, holidays, sick leaves etc)

[songo]

Hello guys,


I am trying to figure out formula that will show number of my work day in week, I don't have standard schedule.
Instead i can work Mon,Tue,OFF,Thu,Fri,OFF,Sun or it can be: Mon,OFF, Tue, HOLIDAY,OFF, Fri,Sat,Sun.
Networkday - only counts total days, workday - also doesn't give what i want.


Like on the table below, 2nd column is final result of formula what i am trying to acomplish.

Monday	1
Tuesday	2
Wednesday	3
OFF
Friday	4
OFF
Sunday	5
Monday	1
HOLIDAY
Wednesday	2
Thurday	3
OFF
OFF
Sunday	4
Monday	1

<tbody>
</tbody>

etc...

 

[Marcelo Branco]

That situation should not be able to occur. In Message [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3 [/URL] , the OP said "...this formula will be used for many people and each month will be separate Sheet."

Yes, this was clearly defined in post #3 .
But if it were not so, your/our formula could be easily adapted
=IF(B2<>"WORK","",COUNTIFS(A$2:A2,">="&MAX(DATE(YEAR(A2),MONTH(A2),1),A2-WEEKDAY(A2,3)),B$2:B2,"WORK"))

M.

 

[Rick Rothstein]

Yes, this was clearly defined in post #3 .
But if it were not so, your/our formula could be easily adapted
=IF(B2<>"WORK","",COUNTIFS(A$2:A2,">="&MAX(DATE(YEAR(A2),MONTH(A2),1),A2-WEEKDAY(A2,3)),B$2:B2,"WORK"))

A little more compact and two less function calls...

=IF(B2<>"WORK","",COUNTIFS(A$2:A2,">="&MAX(A2-DAY(A2)+1,A2-WEEKDAY(A2,3)),B$2:B2,"WORK"))

 

[Tetra201]

That situation should not be able to occur. In Message #3 , the OP said "...this formula will be used for many people and each month will be separate Sheet."

Ah, I see. Somehow, I paid more attention to your phrase "at least not one that looked like it might go on forever"

 

[Tetra201]

A little more compact and two less function calls...

=IF(B2<>"WORK","",COUNTIFS(A$2:A2,">="&MAX(A2-DAY(A2)+1,A2-WEEKDAY(A2,3)),B$2:B2,"WORK"))

... And you came essentially to my formula from Post #7 ; mine has fewer references to A2...

 

[Rick Rothstein]

... And you came essentially to my formula from Post [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7]#7 [/URL] ; mine has fewer references to A2...

Well, it is a little different (your function does not use MAX for example). However, in looking at the formula you posted in Message #6 (not #7 by the way), it can be simplified a little bit more while removing a function call and a reference to A2 in the process...

=IF(B2<>"WORK","",COUNTIFS(A$2:A2,">="&A2-WEEKDAY(A2,3),B$2:B2,"WORK"))

 

[Tetra201]

Well, it is a little different (your function does not use MAX for example). However, in looking at the formula you posted in Message #6 (not #7 by the way)...

My bad -- it should read Post #9 .

I have used MIN instead of MAX in order to have fewer references to A2.

 

[Rick Rothstein]

My bad -- it should read Post #9 .

I have used MIN instead of MAX in order to have fewer references to A2.

I referenced the wrong formula... I forgot we were taking about resetting the count at the beginning of the month (which the OP did not need). The simplified version of your Message #6 formula that I posted in Message #15 still works for the OP's original question though.

 

[songo]

Guys, thanks so much for all your efforts!
I am all setup now