Monthly workdays in a given month

[jazzista]

Hello: I am trying to create a weekday number by month excluding weekends and holidays. I created a table with a helper column with weekdays, weekends and holidays. Please take a look at column H of the attached file so you can see the logic: Example 1/1/2018 = Holiday 1/2/2018 WD 1 Jan 2018 1/3/2018 WD 2 Jan 2018 and so on. I started to hardcode each workday and I wonder if this can be done automatically. Thanks in advance



https://www.dropbox.com/s/vn2bjuhjpkuwzq4/workday.xlsx?dl=0

 

[shg]

I can't tell from what you posted what you're trying to achieve.

 

[steve the fish]

I think its something like:

=IF(F2="Holiday","Holiday","WD "&NETWORKDAYS(DATE(YEAR(A2),MONTH(A2),1),A2,$N$19:$N$28)&TEXT(A2," mmm yyyy"))

 

[jazzista]

Steve the fish. Thanks for the formula! It worked. Thanks a million! Very elegant solution. Straight to the point. Just a quick question: Is there a way to turn off the weekends? Right now if you look at Friday ( 1/5/2018) ,Saturday ( 1/6/2018) and Sunday ( 1/7/2018) picks ups the same workday ( Wd4) for all 3 days. I guess it is technically the weekends are the same workdays as Fridays. Thanks again!

 

[steve the fish]

Sure. In much the same way as we did for holiday use another if for weekend and the dash.

 

[jazzista]

I will try that. Thanks again. Really appreciated your help