Date Formula Needed-Business Days

[Guest]

I need a formula that calculates business days, but the NETWORKDAYS function
is not adequate. I need the formula to return the number of days between two
dates, excluding a range of holidays. Since my store is open seven days a
week, the NETWORKDAYS function will not suffice. When I used Quattro Pro in
the past, it had a BDAY function, and it accepted parameters to indicate if
weedends were to be included or not. Is there a comparable function in Excel?

I am trying to calculate the number of days the store is open every week. I
thought of using NETWORKDAYS and simply adding 2 (Saturday and Sunday).
However, when holidays occur on a weekend, my formula returns the incorrect
number of days.

 

[Peo Sjoblom]

So you only need to subtract holidays from your dates?
Put the holiday dates in let's say H1:H10 (adapt if there are more or less
holidays than 10)


=A2-A1-(SUMPRODUCT(--(H1:H10>=A1),--(H1:H10<=A2)))

with start date in A1 and end date in A2

depending on how you count you might want to include one day, if so

=((A2-A1)+1)-(SUMPRODUCT(--(H1:H10>=A1),--(H1:H10<=A2)))


--
Regards,

Peo Sjoblom

(No private emails please)

 

[Biff]

Hi!

A1 = start date
B1 = end date
C1:C10 = list of holiday dates

=B1-A1-SUMPRODUCT(--(C1:C10>=A1),--(C1:C10<=B1))+1

Biff