Calculating the number of Fridays in a month
 

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.

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)


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

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.

"Bob Phillips" wrote:

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)


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

On Mon, 28 Feb 2005 15:53:04 -0800, Greg Ward
wrote:

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

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.

On Wed, 2 Mar 2005 10:21:20 -0500, "Daniel.M"
wrote:

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