How many fridays in any given month?

[logique]

How can I get the number of Fridays in any given month starting from any given date?

 

[dk]

Hello and welcome to the board

This formula appears to work. It assumes that the start date is in cell A2. In order to use this the Analysis Toolpak must be installed. You can do this by clicking Tools, Add-ins and then checking the Analysis Toolpak addin.

<font color="#000080" face="Arial Alternative" size="1">=IF(WEEKDAY(A2)=6,INT((EOMONTH(A2,0)-A2)/7)+1,INT((EOMONTH(A2,0)-A2)/7))</font>[/i]

HTH,
Dan

 

[Juan Pablo González]

Hello, do you need the TOTAL number of fridays in the month ? or the ones that are left from the "given date" ?

 

[Juan Pablo González]

If you need the total fridays in the month, no matter what, try this:

=4+(WEEKDAY(DATE(YEAR(A2),MONTH(A2)+1,0),2)>=5)

 

[logique]

Thank You guys for your help. But neither formular works for the whole year.

Pablo's formula works for the whole year except for the month of June.

DK's formula works on June but is off on other months.

I need to find how many fridays in each month no mather what the given date is for that month.

 

[Legacy 139]

There must surely be a simpler formula but this seems to work :-

=1+INT((EOMONTH(A2,0)-(DATE(YEAR(A2),MONTH(A2),1)+MOD(6-WEEKDAY(DATE(YEAR(A2),MONTH(A2),1)),7)))/7)



EDIT : This is a bit shorter :-

=1+INT((EOMONTH(A2,0)-(EOMONTH(A2,-1)+1+MOD(6-WEEKDAY(EOMONTH(A2,-1)+1),7)))/7)
This message was edited by Tikas A. Planck on 2002-06-08 08:13
This message was edited by Tikas A. Planck on 2002-06-08 15:12
This message was edited by Tikas A. Planck on 2002-06-08 15:13

 

[Aladin Akyurek]

On 2002-06-08 06:21, logique wrote:
Thank You guys for your help. But neither formular works for the whole year.

Pablo's formula works for the whole year except for the month of June.

DK's formula works on June but is off on other months.

I need to find how many fridays in each month no mather what the given date is for that month.

=SUMPRODUCT((WEEKDAY(DATE(YEAR(A2),MONTH(A2),ROW(INDIRECT("1:"&DAY(DATE(YEAR(A2),MONTH(A2)+1,0))))))=6)+0)

where A2 houses a date and 6 is the serial number that refers to Friday.

 

[logique]

Thanks again guys.

Problem solved.

Planck's formula does what I need.

 

[logique]

Aladin's formula also works.

Thank You!

 

[Legacy 139]

On 2002-06-08 08:31, logique wrote:
Thanks again guys.

Problem solved.

Planck's formula does what I need.

My constant works too!