Business day interval calculation using WORKDAY and NETWORKDAYS

EvanDef

New Member
Joined
Jan 6, 2014
Messages
21
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”)

excel2.jpg


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

excel1.jpg


I appreciate it!
-Evan
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jan 6, 2014
Messages
21
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
Joined
Mar 23, 2005
Messages
20,825
Sorry, it should be like this

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

Forum statistics

Threads
1,136,351
Messages
5,675,264
Members
419,558
Latest member
Ryanms

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top