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

Are you ok with using functions from the ATP?

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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

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!

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))

Whe {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))

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


--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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!

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

"T. Valko" wrote in message
...
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))

Whe {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))

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


--
Biff
Microsoft Excel MVP

You're welcome, Max!

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
Many thanks for your fine alternatives & accompanying notes, Biff.
These work great, too.

"T. Valko" wrote in message
...
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))

Whe {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))

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


--
Biff
Microsoft Excel MVP

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))

Whe {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

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)" wrote in
message ...
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))

Whe {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

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" wrote:

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

Marvellous, TM. Thanks.

"Teethless mama" wrote in message
...
In B1: =NETWORKDAYS(A1,EOMONTH(A1,0))
In C1: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))))=7))

Select B1:C1 and copy down