Hello group,

I have seen in clever past postings for "First Tuesday" and "Next
Monday" for the worksheeet.

Is there a one line worksheet formula that will deliver the number of
Fridays in a given month ?

One may assume certain givens eg. the day of the week of the 1st of
that month and/or the number of days in that month.

Thanks,

Michael Singmin

Michael,

Assuming a date in A1,

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

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Michael Singmin" wrote in message
...
Hello group,

I have seen in clever past postings for "First Tuesday" and "Next
Monday" for the worksheeet.

Is there a one line worksheet formula that will deliver the number of
Fridays in a given month ?

One may assume certain givens eg. the day of the week of the 1st of
that month and/or the number of days in that month.

Thanks,

Michael Singmin

On Sat, 19 Jun 2004 00:04:04 +0200, Michael Singmin wrote:

Hello group,

I have seen in clever past postings for "First Tuesday" and "Next
Monday" for the worksheeet.

Is there a one line worksheet formula that will deliver the number of
Fridays in a given month ?

One may assume certain givens eg. the day of the week of the 1st of
that month and/or the number of days in that month.

Thanks,

Michael Singmin

Here's another method, again with any date in the month in A1:

=4+(DAY(A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),5,4,3,2,1,7,6)+28)28)


--ron

Greetings Bob & Ron,

Bob, I am astounded by your fomula. Care to briefly explain the
thinking behind it. Also what if I want to count Mondays, what do I
change ?

Ron, I think there must be an error because your formula does not
even evaluate.

Many thanks,

Michael

================================================== =================
Michael Singmin wrote:

Hello group,

I have seen in clever past postings for "First Tuesday" and "Next
Monday" for the worksheeet.

Is there a one line worksheet formula that will deliver the number of
Fridays in a given month ?

One may assume certain givens eg. the day of the week of the 1st of
that month and/or the number of days in that month.

Thanks,

Michael Singmin

On Mon, 21 Jun 2004 19:48:26 +0200, Michael Singmin wrote:

Ron, I think there must be an error because your formula does not
even evaluate.

It works fine for me. Probably you are entering it incorrectly, or don't have
a true date in A1.

What do you see?

If you just see the formula, then you have entered it as text rather than as a
formula. Perhaps either you are entering a leading space, or you are entering
a <cr in the middle thrown in by your newsreader.


--ron

Michael,

It's quite straight-forward, when broken down. It's theory is to get the day
of the last Friday in the month, and by taking the integer value of that day
plus 6 divided by 7, you get the number of Fridays.

First we get the first of the next month DATE(YEAR(A1),MONTH(A1)+1,1)
Then we get the day of the week of the first of nexyt month
WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2)) based upon a Saturday week stgarts
Subtract the latter from the former to get the last Friday
Then add 6, divide b y 7 and take teh integer value

For Mondays, just use

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

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Michael Singmin" wrote in message
...
Greetings Bob & Ron,

Bob, I am astounded by your fomula. Care to briefly explain the
thinking behind it. Also what if I want to count Mondays, what do I
change ?

Ron, I think there must be an error because your formula does not
even evaluate.

Many thanks,

Michael

================================================== =================
Michael Singmin wrote:

Hello group,

I have seen in clever past postings for "First Tuesday" and "Next
Monday" for the worksheeet.

Is there a one line worksheet formula that will deliver the number of
Fridays in a given month ?

One may assume certain givens eg. the day of the week of the 1st of
that month and/or the number of days in that month.

Thanks,

Michael Singmin

Hi Michael!

Here's the collection based on Ron's formula:

With a date in A1:

Number of:
Mondays
=4+(DAY(A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),1,7,6,5,4,3,2)+28)28)
Tuesdays
=4+(DAY(A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),2,1,7,6,5,4,3)+28)28)
Wednesdays
=4+(DAY(A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),3,2,1,7,6,5,4)+28)28)
Thursdays
=4+(DAY(A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),4,3,2,1,7,6,5)+28)28)
Fridays
=4+(DAY(A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),5,4,3,2,1,7,6)+28)28)
Saturdays
=4+(DAY(A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),6,5,4,3,2,1,7)+28)28)
Sundays
=4+(DAY(A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),7,6,5,4,3,2,1)+28)28)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Sorry Ron,

It was the leading space that I did not notice.
I copied it directly from your posting.

Thanks,

Michael
================================================== ========

Ron Rosenfeld wrote:

On Mon, 21 Jun 2004 19:48:26 +0200, Michael Singmin wrote:

Ron, I think there must be an error because your formula does not
even evaluate.

It works fine for me. Probably you are entering it incorrectly, or don't have
a true date in A1.

What do you see?

If you just see the formula, then you have entered it as text rather than as a
formula. Perhaps either you are entering a leading space, or you are entering
a <cr in the middle thrown in by your newsreader.


--ron

"Bob Phillips" wrote...
Assuming a date in A1,

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

Too long. Try

=4+(MOD(WEEKDAY(A1-DAY(A1),3)-WDN,7)-DAY(A1-DAY(A1)+32)2)

where WDN is the weekday number for 0=Monday, ..., 6=Sunday.

--
To top-post is human, to bottom-post and snip is sublime.