Business day interval calculation using WORKDAY and NETWORKDAYS

[EvanDef]

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

 

[barry houdini]

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]

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]

Sorry, it should be like this

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

 

[EvanDef]

Barry,

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