Calculating the number of Fridays in a month

[Guest]

Hi all,

I am trying to write a spreadsheet, for budgeting purposes. I have the
months of the year in a row C3:N3, and I want to calculate the number of pay
days in each month so that I can calculate income/expenditure based on the
number of Fridays in a month. IE Some months there may be 5 Fridays, others 4
etc, then this number (4 or 5) would then be used in subsequent formulas for
income/expenditure for that month.

I know weekday() can tell me if a specific date is a Friday etc, but I am
not sure how to create a formula that can take a given month of a specific
year, as imput and return to me the number of Fridays in that month.

Can anyone suggest what I need to do?

Thanks,

Greg.

 

[Bob Phillips]

Assuming a full date in A1,

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

The 6 near the end is the Friday, that is the weekday of Friday, so change
this for other days

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Bob,

That was exactly what I was after. I don't think I would have ever come up
with anything like that. It has made my lil' spreadsheet work so much better.

Greg.

 

[Ron Rosenfeld]

Hi all,

I am trying to write a spreadsheet, for budgeting purposes. I have the
months of the year in a row C3:N3, and I want to calculate the number of pay
days in each month so that I can calculate income/expenditure based on the
number of Fridays in a month. IE Some months there may be 5 Fridays, others 4
etc, then this number (4 or 5) would then be used in subsequent formulas for
income/expenditure for that month.

I know weekday() can tell me if a specific date is a Friday etc, but I am
not sure how to create a formula that can take a given month of a specific
year, as imput and return to me the number of Fridays in that month.

Can anyone suggest what I need to do?

Thanks,

Greg.

With some date in the month in A1:

=4+(DAY(A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1+5))+35)>7)

will give the number of Fridays in a month.

For a different weekday, change the '5' near the end accordingly:

1:Monday 7:Sunday

Thanks to Daniel M.


--ron

 

[Daniel.M]

Hi Ron,

With some date in the month in A1:
=4+(DAY(A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1+5))+35)>7)
will give the number of Fridays in a month.
Thanks to Daniel M.

Thanks for the credit.
Here's an even shorter version:

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

DOW: 1:Monday 7:Sunday

Regards,

Daniel M.

 

[Ron Rosenfeld]

Thanks for the credit.
Here's an even shorter version:

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

DOW: 1:Monday 7:Sunday

Definitely a keeper!

--ron