Calculate Business day based on multiple criteria

[nikhil0311]

Date	Holiday	Business Day
Friday, November 1, 2019	No	1
Monday, November 4, 2019	Yes	-
Tuesday, November 5, 2019	No	2
Tuesday, November 5, 2019	No	2
Tuesday, November 5, 2019	No	2
Wednesday, November 6, 2019	Yes	-
Thursday, November 7, 2019	No	4
Friday, November 8, 2019	No	5
Monday, November 11, 2019	No	6
Tuesday, November 12, 2019	No	7
Wednesday, November 13, 2019	No	9
Monday, December 2, 2019	No	1
Tuesday, December 3, 2019	Yes	-
Wednesday, December 4, 2019	Yes	-
Thursday, December 5, 2019	No	2
Friday, December 6, 2019	No	3
Monday, December 9, 2019	No	4

In column A we have dates. We have instances where the same date have multiple entries

Column B we have Holiday Status as Yes or No.

Based on below criteria, we have to calculate Business day in column C

The starting day of every month should be BD = 1

If we have duplicate date then the BD should repeat. For example in row 5 and 6 we have the same date i.e. 5th november so the business day should be the same for both dates

If Holiday is equals to "Yes" then BD sahould be "-". And Business day should continue to be in line with above i.e. for date 1st Nov BD is 1, 4thNov holiday = Yes so BD is "-" and then 5th Nov BD should be 2 please refer the final BD in column C

 

[mabbutt]

You need to make sure that your dates are formatted correctly.

=IF(B1="Yes","-",IF(WEEKDAY(A1,2)< 6,NETWORKDAYS(A1-DAY(A1)+1,A1),"Weekend"))

 

[Tetra201]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=IF(OR(B2="Yes",WEEKDAY(A2,2)>5),"-",NETWORKDAYS(EOMONTH(A2,-1)+1,A2,IF($B$2:$B$18="Yes",$A$2:$A$18,0)))

 

[mabbutt]

Duplicate post.