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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
<[email protected]> 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" <[email protected]>
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