Counting business days in excel excluding bank holidays

**SuperHoops** · 01-08-2013, 04:19 PM

Hi all.......Please help

I took over a spreadsheet from a colleague and now am having trouble with it

In 2012 the formula entered counted the number of working days (mon - fri) and excluded bank holidays

=IF(B2="","",IF(B2="OPEN","",SUM(P2-H2)-SUMPRODUCT(--(HOLIDAY>H2),--(HOLIDAY<=P2)

Since we have moved into 2013 it is now counts weekends

If i could find what the HOLIDAY list was I cold change it I guess but cant find that even

Any ideas

Cheers in advance

**Pepe Le Mokko** · 01-08-2013, 04:23 PM

Why not have a go at the NETWORKDAYS function ? ( you'll need the Analysis Toolpack installed in XL2003)

**jeffreybrown** · 01-08-2013, 04:24 PM

Why not use NETWORKDAYS for this?

=NETWORKDAYS(A1,A50,B1:B10)

A1 = Start date
A50 = End date
B1:B10 = List of holidays

**daddylonglegs** · 01-08-2013, 04:28 PM

Originally Posted by SuperHoops

In 2012 the formula entered counted the number of working days (mon - fri) and excluded bank holidays

There's nothing in the formula that excludes weekend dates (unless you include all Saturdays and Sundays in the holiday list)

**SuperHoops** · 01-08-2013, 04:35 PM

Wow thanks guys, that NETWORKDAYS formula is so much easier

The only problem I have now is that I don't want the the result to count the start date as the 1st day (i.e if start date is 03/01/2013 then I want the first counted day on 04/01/2013

Sorry to be a pain....can I just add -1 at the end of the formula ??

Thanks again for all your help