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.

[TABLE="class: grid, width: 20"]
<tbody>[TR]
[TD]Monday[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Tuesday
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]HOLIDAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Thurday[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
etc...

 

[Rick Rothstein]

Assuming your list of days, Off and Holidays is in Column A starting at Row 1, put 1 in cell B1 and then put this formula in cell B2 and copy it down to the end of your list...

=IF(LEFT(A2,3)="Mon",1,IF(OR(LEFT(A2,3)={"OFF","HOL"}),"",LOOKUP(8,B$1:B1)+1))

 

[songo]

Thanks Rick for your quick reply!
Please help me with update of this formula, this formula will be used for many people and each month will be separate Sheet,
therefore I can’t put manually 1 on Monday, sometimes 1st day of the month might be Holiday or OFF, so 1st working day will be Tue or etc.
Actual time attendance schedule is as below:

[TABLE="width: 374"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Status[/TD]
[TD]My work day in week[/TD]
[/TR]
[TR]
[TD]01.09.2017[/TD]
[TD]WORK[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]02.09.2017[/TD]
[TD]OFF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03.09.2017[/TD]
[TD]WORK[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]04.09.2017[/TD]
[TD]OFF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05.09.2017[/TD]
[TD]WORK[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]06.09.2017[/TD]
[TD]HOLIDAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07.09.2017[/TD]
[TD]WORK[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]08.09.2017[/TD]
[TD]WORK[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]09.09.2017[/TD]
[TD]OFF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10.09.2017[/TD]
[TD]WORK[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]11.09.2017[/TD]
[TD]OFF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12.09.2017[/TD]
[TD]OFF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13.09.2017[/TD]
[TD]WORK[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]14.09.2017[/TD]
[TD]WORK[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]15.09.2017[/TD]
[TD]WORK[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]16.09.2017[/TD]
[TD]WORK[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]17.09.2017[/TD]
[TD]WORK[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

I could not think of a formula (at least not one that looked like it might go on forever), so I developed a macro for you to use...

Sub WorkDayEnumerator()
  Dim R As Long, N As Long, Data As Variant, Result As Variant
  Data = Range("A2", Cells(Rows.Count, "B").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 1)
  N = 1
  For R = 1 To UBound(Data)
    If Weekday(Data(R, 1)) = 2 Then N = 1
    If Data(R, 2) = "WORK" Then
      Result(R, 1) = N
      N = N + 1
    End If
  Next
  Range("C2").Resize(UBound(Result)) = Result
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (WorkDayEnumerator) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Tetra201]

Here is a formula to try:

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

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Date​

[/td][td]

Status​

[/td][td]

My work day in week​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

01/09/2017​

[/td][td]

WORK​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

02/09/2017​

[/td][td]

OFF​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

03/09/2017​

[/td][td]

WORK​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

04/09/2017​

[/td][td]

OFF​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

05/09/2017​

[/td][td]

WORK​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

06/09/2017​

[/td][td]

HOLIDAY​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

07/09/2017​

[/td][td]

WORK​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

08/09/2017​

[/td][td]

WORK​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

09/09/2017​

[/td][td]

OFF​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

10/09/2017​

[/td][td]

WORK​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

11/09/2017​

[/td][td]

OFF​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

12/09/2017​

[/td][td]

OFF​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

13/09/2017​

[/td][td]

WORK​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

14/09/2017​

[/td][td]

WORK​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

15/09/2017​

[/td][td]

WORK​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td]

16/09/2017​

[/td][td]

WORK​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

18​

[/td][td]

17/09/2017​

[/td][td]

WORK​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

19​

[/td][td]

18/09/2017​

[/td][td]

HOLIDAY​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

20​

[/td][td]

19/09/2017​

[/td][td]

OFF​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

21​

[/td][td]

20/09/2017​

[/td][td]

WORK​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

22​

[/td][td]

21/09/2017​

[/td][td]

WORK​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

23​

[/td][td]

22/09/2017​

[/td][td]

OFF​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

24​

[/td][td]

23/09/2017​

[/td][td]

WORK​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

25​

[/td][td]

24/09/2017​

[/td][td]

HOLIDAY​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

26​

[/td][td]

25/09/2017​

[/td][td]

WORK​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

27​

[/td][td]

26/09/2017​

[/td][td]

WORK​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

28​

[/td][td]

27/09/2017​

[/td][td]

OFF​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

29​

[/td][td]

28/09/2017​

[/td][td]

OFF​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

30​

[/td][td]

29/09/2017​

[/td][td]

WORK​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

31​

[/td][td]

30/09/2017​

[/td][td]

Work​

[/td][td]

4​

[/td][/tr]
[/table]

Formula in C2 copied down
=IF(B2<>"WORK","",COUNTIFS(A$2:A2,">="&A2-IF(WEEKDAY(A2)=2,0,WEEKDAY(A2-2)),B$2:B2,"WORK"))

M.

 

[Marcelo Branco]

Or this simpler formula

C2 copied down
=IF(B2<>"WORK","",COUNTIFS(A$2:A2,">="&A2-CHOOSE(WEEKDAY(A2),6,0,1,2,3,4,5),B$2:B2,"WORK"))

M.

 

[Rick Rothstein]

Or this simpler formula

C2 copied down
=IF(B2<>"WORK","",COUNTIFS(A$2:A2,">="&A2-CHOOSE(WEEKDAY(A2),6,0,1,2,3,4,5),B$2:B2,"WORK"))

Nice! But we can actually simplify that a bit more...

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

 

[Tetra201]

Here is my updated formula as well (I just removed the redundant condition):

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

In most cases, it returns values identical to those yielded by Rick's VBA solution and by Marcelo/Rick's formulas.

However, here is an example of where it differs: Sat 30-Sep-2017, Sun 1-Oct-2017, Mon 2-Oct-2017, all three are "WORK" days.
- My formula returns 1,1,1.
- Rick/Marcello's solutions return 1,2,1.

It's up to the OP to decide which result is the correct one.

 

[Rick Rothstein]

However, here is an example of where it differs: Sat 30-Sep-2017, Sun 1-Oct-2017, Mon 2-Oct-2017, all three are "WORK" days.
- My formula returns 1,1,1.
- Rick/Marcello's solutions return 1,2,1.

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."