Counting calendar days, omitting holidays

I’m familiar with the NETWORKDAYS function in Excel and have been using it for years to calculate the time elapsed (in days) between two dates.

EX:
“=IF(NETWORKDAYS(A1,B1,Holidays!$A$1:$A$100)-1>=0,NETWORKDAYS(A1,B1,Holidays!$A$1:$A$100)-1,IF(ISBLANK(B1),NETWORKDAYS(A1,TODAY(),Holidays!$A$1:$A$100)))”

When I was asked to design a spreadsheet containing a calculation similar to the one above, but for calendar days, I figured it’d be easy enough, but I’m not so sure, now. The trick here is that we still need to omit our list of holidays from the calculation.

Is there another function (like NETWORKDAYS) for calendar days, or is it assumed that one should simply use “A1-B1?” If that’s the case, is there a way of excluding a list of dates from simple expressions (like “A1-B1”)?

Thanks so much for your help,

-J

Maybe =B1 - A1 - sumproduct( (Holidays>=A1) * (Holidays<=B1) ) (assuming the earlier date is in A1)

Thanks shg, for your response.

I’ve played around with the SUMPRODUCT idea for several hours and I think I’m getting close, but I don’t quite understand how to apply your equation to my spreadsheet. Based on your suggestion and other discussion threads found in the forums, the best I could do is this:

=(M26-F26)-(COUNTA(Holidays!$D$7:$D$80)-((SUMPRODUCT(--(Holidays!$D$7:$D$80<F26)))+(SUMPRODUCT(--(Holidays!$D$7:$D$80>M26)))))

The problem with this equation is that SUMPRODUCT counts the blank cells in the list of Holidays. Our Holiday list isn’t worked out entirely (and is likely to change throughout the whole project a bit). It would be ideal if blanks in the column wouldn’t affect the outcome.

I’ve attached a watered-down copy of the spreadsheet for your reference. Column “O” should contain the calculation. Everything above “O26” reflects the system currently in place; everything after contains my experimentation.

Am I getting close here, or is there a much better (cleaner) way of getting at this solution?

Again, thanks so much for the advice.

-J

I put =M26 - F26 - SUMPRODUCT( (Holidays!$D$7:$D$80>=F26) * (Holidays!$D$7:$D$80<=M26) ) in O26 and it worked fine.

You're absolutely right. Thanks. I think I must have accidentally deleted a space from your original equation.

What do those spaces do, by the way? Do they function at all like the double negatives I've seen in similar posts?

Thanks again, this is brilliant!

Spaces are not significant in formulas except in quoted strings. I add them for readability, nothing more.

You're welcome, glad it worked for you.