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

tseward

New Member
Joined
Nov 18, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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/209/1/209/7/20209/14/20209/21/20209/28/202010/1/202010/5/202010/12/2020
week12344123
Days in week44 excluding holiday553255

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

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,627
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

New Member
Joined
Nov 18, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,627
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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,119,255
Messages
5,576,994
Members
412,759
Latest member
Jackuk127
Top