# Business day interval calculation using WORKDAY and NETWORKDAYS

#### EvanDef

##### New Member
Hello Excel Gurus,

Can someone help with a bus day interval calculation involving WORKDAY and NETWORKDAYS calculations? If the status of the query is "Closed" and the end date is greater than the set interval, then show the number of days the query is late. If the status of the query is "Cancelled" leave the field blank. The formula I attempted includes the removal of holidays in the WORKDAY and NETWORKDAYS calculations. At first I neglected to include the Cancelled=blank to see if i could get it to work, but I had no success.

This is my feeble attempt.
=IF(AND(J2="Closed",WORKDAY(G2,17,[Holidays]) >17),NETWORKDAYS(G2,H2,[Holidays]),"Cancelled”)

I used the block of dates in column A and labelled them "Holidays" for the purpose of the equation.

I appreciate it!
-Evan

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### barry houdini

##### MrExcel MVP
Hello Evan, welcome to MrExcel

Are you saying that the "set interval" is 17 working days?

Perhaps this version will do it

=IF(AND(J2="Closed",H2>WORKDAY(G2,17,Holidays),NETWORKDAYS(G2,H2,Holidays)-17,"")

If that doesn't work for you then perhaps you can indicate what you expect the results to be for your examples

#### EvanDef

##### New Member
Hi Barry,

Thanks for the quick reply. The set interval for the start thru end date is 17 working days. When I used your formula I first received and error for placement of parenthesis. I assumed the parenthesis should be placed at the end like so:

=IF(AND(J2="Closed",H2>WORKDAY(G2,17,Holidays),NETWORKDAYS(G2,H2,Holidays)-17,""))

However, i am still getting an error. Could it be missing or placement of the parenthesis?

Thanks so much for your help

#### barry houdini

##### MrExcel MVP
Sorry, it should be like this

=IF(AND(J2="Closed",H2>WORKDAY(G2,17,Holidays)),NETWORKDAYS(G2,H2,Holidays)-17,"")

#### EvanDef

##### New Member
Barry,

Thanks so much for the help, that worked out perfect!

Replies
1
Views
370
Replies
1
Views
139
Replies
5
Views
346
Replies
5
Views
213
Replies
0
Views
876

1,171,158
Messages
5,874,103
Members
433,024
Latest member
ostosix

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back