Extract Number of Weekdays (Mon-Fri) & Saturdays for a particular Month/Yr

[Max]

In A1 down are 1st of month real dates formatted as mmm-yy, eg:
Jul-08
Aug-08
etc

In B1 down, I would like to extract the number of weekdays (Mon-Fri)
and in C1 down, the number of Saturdays
for the month/yr listed in col A

Eg for Jul-08 in A1, result in B1: 23, in C1: 4

Thanks

 

[T. Valko]

Are you ok with using functions from the ATP?

 

[Teethless mama]

In B1: =NETWORKDAYS(A1,EOMONTH(A1,0))
In C1: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))))=7))

Select B1:C1 and copy down

 

[Max]

Marvellous, TM. Thanks.

 

[Max]

Are you ok with using functions from the ATP?
Thanks, Biff. I'm ok with that. TM has provided a way to get there. But I'm
game to try your alternatives, ATP or otherwise, all of which can only
enrich the newgroups further. Ride on!

 

[T. Valko]

Non-volatile, non-ATP alternatives:

Weekdays (M - F):

=SUM(INT((WEEKDAY(A1-{1,2,3,4,5},2)+DATE(YEAR(A1),MONTH(A1)+1,0)-A1)/7))

Where: {1,2,3,4,5} = days of the week: Mon = 1 through Sun = 7

Saturdays:

=INT((WEEKDAY(A1-6,2)+DATE(YEAR(A1),MONTH(A1)+1,0)-A1)/7)

Or:

=4+(DAY(A1-DAY(A1)+35)<WEEKDAY(A1-DAY(A1)-6))

Where: -6 (in both formulas) = day of the week: Mon = 1 through Sun = 7

 

[Max]

Many thanks for your fine alternatives & accompanying notes, Biff.
These work great, too.

 

[Rick Rothstein \(MVP - VB\)]

Non-volatile, non-ATP alternatives:

Weekdays (M - F):

=SUM(INT((WEEKDAY(A1-{1,2,3,4,5},2)+DATE(YEAR(A1),MONTH(A1)+1,0)-A1)/7))

Where: {1,2,3,4,5} = days of the week: Mon = 1 through Sun = 7

Although the OP's question has been satisfied, I figured I would post this
non-volatile, non-ATP, array-entered** alternative for the archives....

=COUNT(IF(--WEEKDAY(--(TEXT(A1,"yyyy-mm-")&TEXT(ROW($1:$31),"00")),2)<6,1,""))

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

This formula differs from yours in that it does not require the date in A1
to be the first of the month... A1 can contain any date within the month and
the formula will return the number of weekdays (Monday thru Friday) in that
month.

Rick

 

[T. Valko]

You're welcome, Max!

--
Biff
Microsoft Excel MVP

Many thanks for your fine alternatives & accompanying notes, Biff.
These work great, too.

 

[Max]

Thanks, Rick, for your weekdays number extraction offering.
The flexibility that the source data in col A can be any date within the
month is good.

Would you have the equivalent to get the Saturdays number, too?

 

[Rick Rothstein \(MVP - VB\)]

The same basic array-entered formula can be used (again, with the date in A1
being any date in the month). With the option second argument (the 2) added
to the WEEKEND formula, the weekdays are are numbered 1=Monday,
2=Tuesday,..., 5=Friday, 6=Saturday and 7=Sunday. For your first question,
the test was <6 (meaning, count the weekdays whose numbers are 1, 2, 3, 4
and 5. To count the number of Saturdays, we just change the <6 to =6 and the
array-entered** formula will count the Saturdays....

=COUNT(IF(--WEEKDAY(--(TEXT(A1,"yyyy-mm-")&TEXT(ROW($1:$31),"00")),2)=6,1,""))

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

If you wanted to count the number of Sundays, you would change the test to
=7 instead of =6. If you wanted the number of weekend days, you would change
the test to >5.

Rick

 

[Max]

Excellent flexibility there, Rick.
Thanks for the detailed explanations!

 

[Rick Rothstein \(MVP - VB\)]

Maybe you would like the full explanation as to how the function works then.
The reason the formula needs to be array-entered is so this part of it,
ROW($1:$31), will be iterated and output 1, 2, 3,...etc..., 30 and 31. The
TEXT function that it is embedded in will force each of those values to be a
2-digit (leading zero where necessary) value. That 2-digit value is
concatenated with the 4-digit year and 2-digit (leading zero where
necessary) month, with dashes separating each part. So, for a date in, say,
February 2007, the concatenated values generated for each value from
ROW($1:$31) that is induced from array-entering the formula would be
these...

2007-02-01
2007-02-02
2007-02-03
.....
.....
2007-02-28
2007-02-29
2007-02-30
2007-02-31

The only part of the above that would change for any month in 2007 is the
middle number (in this case, the 02). Yes, I know there is no 29th, 30th or
31st in February... we'll discuss that in a moment. The reason for
generating dates in the above format is because it is an internationally
recognized universal date format and Excel knows that. So, when I multiply
each of those concatenated, date-looking strings by the double unary (--),
Excel will convert them into real Excel dates if it can. For those values
like 2007-02-30 which are not a real date, Excel will generate a #VALUE!
error. The next thing the formula does is attempt to find the WEEKEND
numbers for each of the generated dates, producing the numbers I posted in
my previous reply for the real dates and #VALUE! errors for those generated
"dates" that are not real. Next, the IF function tests each of those
generated WEEKDAY numbers to see they meet the condition being tested for
(<6 for the weekday question) and generates a 1 when the conditions are met
and the empty string when they are not (that is, dates during the weekend
and #VALUE! errors). Finally, the COUNT function, which only counts numeric
results, counts all the 1s that were generated from processing each date
generated when ROW($1:$31) was iterated by the array evaluation process
induced by array-entering the formula.

Rick

 

[Max]

Thanks, Rick for the full explanations!