Excel Problem - Days in a month between two dates-CodePudding

I have a date range where I would like to find out the number of working days (excluding weekends and holidays) that fall in a range of months. Example below.

enter image description here

I understand this may involve a number of if/networkdays but struggling to complete this. Any help appreciated.

CodePudding user response：

If you have Excel 365 you can use this formula

I am assuming that your table starts in A1 and that C1, D1 etc contain first of month date, e.g. 1.1.2022 etc..

=LET(startMonth,C$1,
endMonth,EDATE(startMonth,1)-1,
startPeriod,MAX(startMonth,$A2),
endPeriod,MIN(endMonth,$B2),
IF(startPeriod < endPeriod,NETWORKDAYS(startPeriod,endPeriod),0)
)

Setting startMonth and endMonth is more for readability of the calculations of startPeriod and endPeriod.

startPeriod will be 28.1.22 for the january column as it is greater than 1.1.22. But for febuary it will be 1.2.22 and so on.

Calculating the the networkdays is based on these two dates.

If you want to add holidays refer to the help of NETWORKDAYS