Counting Fridays in a month

[Michael Singmin]

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

 

[Bob Phillips]

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)

 

[Ron Rosenfeld]

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

 

[Michael Singmin]

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

===================================================================

 

[Ron Rosenfeld]

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]

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)

 

[Norman Harker]

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)

 

[Michael Singmin]

Sorry Ron,

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

Thanks,

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

 

[Harlan Grove]

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.