week days over a split week at start or end of month

[tseward]

I can seem to calculate the number of days in a week using the Networkdays (excluding weekends default is ok as Saturday and Sunday) but I am trying to build a forecast that spread across month that say start in the middle of the week. As an example October 2020 would have a its first week of the month start on 10/1/2020 which is a Thursday, but essentially is week one but it only represents two actual work days i.e. Thursday and Friday.

Again for October 2020
week 1 10/1/2020 = 2 working days Thursday and Friday
week 2 10/5/2020 = 5 working days Monday through Friday
week 3 10/12/2020 = 5 working days Monday through Friday
week 3 10/19/2020 = 5 working days Monday through Friday
week 4 10/26/2020 = 5 working days Monday through Friday

when I use this formula =NETWORKDAYS(P2,EOMONTH(P2,0),T17:T21) I get 20 days where p2 is 10/5/2020 Row 2 is only derived by start of fiscal year 7/1/2020 and each week begins on a Monday. The error with this formula is it does not start on the first when in reality there are 22 working days in October

Is it possible to display the number of working days in a week for the last week of September then start a new column with the first week of October where the last week of September would be week 5 for that month and equal 3 working days?

9/1/20	9/1/20	9/7/2020	9/14/2020	9/21/2020	9/28/2020	10/1/2020	10/5/2020	10/12/2020
week	1	2	3	4	4	1	2	3
Days in week	4	4 excluding holiday	5	5	3	2	5	5

I need to use the days in the week to help build an accurate forecast.

 

[Tetra201]

Is this what you need?

Date

9/1/2020​

9/7/2020​

9/14/2020​

9/21/2020​

9/28/2020​

10/1/2020​

10/5/2020​

10/12/2020​

10/19/2020​

10/26/2020​

11/1/2020​

11/2/2020​

Week

1​

2​

3​

4​

5​

1​

2​

3​

4​

5​

1​

2​

Workdays

4​

4​

5​

5​

3​

2​

5​

5​

5​

5​

0​

5​

 

[tseward]

Is this what you need?

Date

9/1/2020​

9/7/2020​

9/14/2020​

9/21/2020​

9/28/2020​

10/1/2020​

10/5/2020​

10/12/2020​

10/19/2020​

10/26/2020​

11/1/2020​

11/2/2020​

Week

1​

2​

3​

4​

5​

1​

2​

3​

4​

5​

1​

2​

Workdays

4​

4​

5​

5​

3​

2​

5​

5​

5​

5​

0​

5​

yes this is what I was looking for?

 

[Tetra201]

Assuming your initial date (say, 9/1/20) is in cell B1 and your holiday dates are in a range named Holidays, try entering the following formulas:

Cell C1, =MIN(WORKDAY.INTL(B1,1,"0111111"),EOMONTH(B1,0)+1)
Cell B2, =NETWORKDAYS.INTL(EOMONTH(B1,-1)+1,WORKDAY.INTL(B1,1,"0111111"),"1111110")
Cell B3, =NETWORKDAYS(B1,MIN(WORKDAY.INTL(B1,1,"0111111"),EOMONTH(B1,0)+1)-1,Holidays)

Drag-copy the formulas to the right as needed.