Counting Weekdays + Saturday, omit holidays

**bryceowen** · 07-30-2007, 10:47 AM

First off, let me admit my knowledge of worksheet functions extends to the help file. When I need something more complex, I look on the internet and find a formula that does what I need and try to learn something new from it. However, every now and then, I find a formula that uses functions in a way I don't fully understand. I approach the knowledge base of this forum with my question because, frankly, I can't figure this one out...

Originally I needed a formula that would count the number of days that have passed in a given month, sans Saturday/Sunday and company holidays. The following formula does this quite nicely:

Please Login or Register  to view this content.

Where C1 is a date that can be manually input and the range of A31:A41 is where I can input holidays to omit from the count.

I need a formula (either a modification of this or something new) that will allow the same functionality, but also count Saturday as a work day.

Also, if it isn't too much trouble, some kind of explanation as to what exactly the formula I posted does to determine which days are weekdays would be VERY much appreciated. Like I said, I try to learn from these formulas I find and I can't recall where I found this one to ask the author.

--edit--

Whatever workaround is presented, it can't use the NETWORKDAYS function. I have been unable to get the analysis pack to work on my computer, let alone getting it to work on the computers of the people I send this spreadsheet to.

**oldchippy** · 07-30-2007, 11:44 AM

Basically this formula says, look at the date in C1 then take away the date at the beginning of that month to calculate the number of days from the 1st of the month.
Then omit any days within that period greater than weekday 5 (Friday), so omit all Saturdays and Sundays that fall within that period.
Then also look at the holiday dates and if any fall within this period, also omit those.

So all you need to do to this formula is to increase the workday from 5 to 6 to include Saturday as a workday.

Please Login or Register  to view this content.

Hope this helps?

**Ron Coderre** · 07-30-2007, 11:55 AM

Perhaps something like this:

With
A31:A41 containing your list of holiday dates

C1: (a start date)
D1: (an end date)

This formula returns the number of workdays (Monday thru Saturday) in that date range LESS any holidays in that range:
E1: =SUM(INT((WEEKDAY(C1-{2,3,4,5,6,7})+D1-C1)/7))-SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX($A:$A,C1):INDEX($A:$A,D1)),$A$31:$A$41,0)))

Note: there are no spaces in that formula

**daddylonglegs** · 07-30-2007, 11:58 AM

Your current formula is slightly flawed because the last part

WEEKDAY(A31:A41)<>1

only checks that the holidays aren't Sundays, this part should really be

WEEKDAY(A31:A41,2)<6

otherwise you could get an incorrect count where the previous month contains a Saturday holiday,

You could amend the formula to count all days except Saturdays but I think you can use a slightly simpler formula, i.e. to count all weekdays (except holidays)

=SUMPRODUCT(--(WEEKDAY(C1+1-ROW(INDIRECT("1:"&DAY(C1))),2)<6),--ISNA(MATCH(C1+1-ROW(INDIRECT("1:"&DAY(C1))),A31:A41,0)))

To change this to count all weekdays and Saturdays (except holidays) change the 6 to a 7

**daddylonglegs** · 07-30-2007, 12:41 PM

Perhaps a more efficient formula, along similar lines to Ron's suggestion, to count all workdays in the calendar month up to and including C1, including Sats but excluding holidays

=DAY(C1)-INT((DAY(C1)-WEEKDAY(C1))/7)-1-SUMPRODUCT((WEEKDAY(A31:A41)>1)*(A31:A41>C1-DAY(C1))*(A31:A41<=C1))

**bryceowen** · 07-30-2007, 01:39 PM

I really appreciate everyone's help! Wasn't expecting such rapid replies. This forum is great!

Originally Posted by daddylonglegs

Perhaps a more efficient formula, along similar lines to Ron's suggestion, to count all workdays in the calendar month up to and including C1, including Sats but excluding holidays

=DAY(C1)-INT((DAY(C1)-WEEKDAY(C1))/7)-1-SUMPRODUCT((WEEKDAY(A31:A41)>1)*(A31:A41>C1-DAY(C1))*(A31:A41<=C1))

Could this formula also work to exclude Saturdays? I'm all about efficient formulas and this is the shortest by far (excluding NETWORKDAYS formulas).

**daddylonglegs** · 07-30-2007, 02:39 PM

Excluding Saturdays

=SUM(INT((DAY(C1)-WEEKDAY(C1-{1,2,3,4,5}))/7)+1)-SUMPRODUCT((WEEKDAY(A31:A41,2)<6)*(A31:A41>C1-DAY(C1))*(A31:A41<=C1))

**daddylonglegs** · 02-05-2008, 06:52 PM

Hello bryce, re your pm, here's an explanation of the following formula:

=SUMPRODUCT(--(WEEKDAY(C1+1-ROW(INDIRECT("1:"&DAY(C1))),2)<6),--ISNA(MATCH(C1+1-ROW(INDIRECT("1:"&DAY(C1))),A31:A41,0)))

let's start with this part

--(WEEKDAY($C$1+1-ROW(INDIRECT("1:"&DAY($C$1))),2)<6)

Let's assume C1 is today, 5th feb.

Then ROW(INDIRECT("1:"&DAY($C$1)))

becomes

ROW(INDIRECT("1:5"))

which generates an array from 1 to 5, i.e

{1;2;3;4;5}

[You don't strictly need INDIRECT for the formula to work in a single cell but it makes it more "robust" so that if you copy the formula or delete or insert rows it'll still work as intended]

So C1+1-{1;2;3;4;5}

just generates an array of all this month's dates so far, excel stores these as serial numbers so you get

WEEKDAY({39480;39481;39482,39483;39484},2)

WEEKDAY(date,2) returns a number from 1 to 7, 1 is Monday through to 7, Sunday so you get

{5;6;7;1;2}

representing Fri, Sat, Sun, Mon, Tue

of course when we add the test <6 we get

{TRUE;FALSE;FALSE;TRUE;TRUE}

i.e. TRUE for weekdays, false for weekends

-- then converts TRUE to 1 and 0 to FALSE so you get

{1;0;0;1;1}

Of course the second part works in a similar way.....

This

--ISNA(MATCH(C1+1-ROW(INDIRECT("1:"&DAY(C1))),A31:A41,0))

becomes

--ISNA(MATCH({39480;39481;39482,39483;39484},A31:A41,0))

so this matches each date in the month so far against the holiday list, if there's a match a number is returned [position of the matched date in the holiday list] otherwise we get #N/A, so in this case lets pretend that 1st february is a holiday in your list......you then get something like

--ISNA({4;#N/A;#N/A,#N/A;#N/A})

which gives

{0;1;1;1;1}

so now the SUMPRODUCT formula is reduced to

=SUMPRODUCT({1;0;0;1;1},{0;1;1;1;1})

The first array represents weekday/weekend 1/0 and the second workday/holiday 1/0.

We want to count the days which are both 1, i.e. weekdays which aren't holidays and SUMPRODUCT gives us that because the arrays are multiplied to give

{0;0;0;1;1}

and then summed to give 2

which is the number of working days so far in the month (assuming 1st Feb was a holiday)

regards
daddylonglegs