-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Networkdays (with only Sunday as Weekend + holiday list)
- Thread starter nandivada
- Start date
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
Maybe try (the rather complicated):
=NETWORKDAYS(A1,A2,B1:B3)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N(A1)&":"&N(A2))))=7))-SUMPRODUCT(--(WEEKDAY(B1:B3)=7))
where A1 contains the start date, A2 the end date and B1:B3 the holidays.
=NETWORKDAYS(A1,A2,B1:B3)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N(A1)&":"&N(A2))))=7))-SUMPRODUCT(--(WEEKDAY(B1:B3)=7))
where A1 contains the start date, A2 the end date and B1:B3 the holidays.
Upvote
0
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<7))-SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),B1:B3,0))),--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<7))
...where A1 contains the start date, A2 contains the end date, and B1:B3 contains the holidays.
Hope this helps!
...where A1 contains the start date, A2 contains the end date, and B1:B3 contains the holidays.
Hope this helps!
Upvote
0
Andrew Poulsom said:
Hi,
thank you for taking time to answer my query.
I am getting a negative answer (-1), on the following scenario
Start Date: 02-10-04
End Date: 03-10-04
Holidays
02-10-04
09-10-04
22-10-04
Please check.
thanks
Prakash
Upvote
0
Yogi Anand
MrExcel MVP
- Joined
- Mar 12, 2002
- Messages
- 11,454
Hi Prakash:
Welcome to MrExcel Board!
Let us try ...
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&A2)))>1)+0)-SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),HolidayList,0))))
where A1 and A2 house your dates of interest, and range HolidayList houses your Holidays.
Welcome to MrExcel Board!
Let us try ...
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&A2)))>1)+0)-SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),HolidayList,0))))
where A1 and A2 house your dates of interest, and range HolidayList houses your Holidays.
Upvote
0
Similar threads
- Solved
