How do I find how many of a certain day are in a given month

[Paris2459]

I need to find out how many of a certain day are in a given month.
Ex: I need to know how many Thursdays are in each month for a calendar year
so I would need to find how many Thursdays are in Jan, Feb, Mar etc

 

[Lars-Åke Aspelin]

I need to find out how many of a certain day are in a given month.
Ex: I need to know how many Thursdays are in each month for a calendar year
so I would need to find how many Thursdays are in Jan, Feb, Mar etc

If you have the year, e.g. 2010, in cell A1,
the month (1 for Jan, 2 for Feb etc) in cell A2
and the type of day (1 for Sun, 2 for Mon,..., 7 for Sat) in cell A3,
try the following formula:

=SUMPRODUCT((WEEKDAY(DATE(A1,A2,ROW($1:$31)))=A3)*(MONTH(DATE(A1,A2,ROW($1:$31)))=A2))

Hope this helps / Lars-Åke

 

[Gareth_Evans (InterCall EMEA)]

Hi,

I would have the years dates (ie: 1st Jan - 31st Dec) in column A and then
type in Friday - which was the 1st Jan - through to Friday in column B and
auto fill down.
Then you could use some type of count function to count the occurences.

Not sure if there's an auto way to get the dates in, sure there will be.
Then just assign the relative day of the week and go from there.

I don't believe there's an function/formula to do everything of the shelf,
you'll need to set up something first.

 

[Glenn]

I need to find out how many of a certain day are in a given month.
Ex: I need to know how many Thursdays are in each month for a calendar year
so I would need to find how many Thursdays are in Jan, Feb, Mar etc

One way...with any date in A1, this will return the number of Thursdays in that
month:

=IF(MONTH((A1-DAY(A1)+1)-WEEKDAY((A1-DAY(A1)+1))+ 5 +
(7*(WEEKDAY((A1-DAY(A1)+1))> 5 ))+28)=
MONTH((A1-DAY(A1)+1)-WEEKDAY((A1-DAY(A1)+1))+ 5 +
(7*(WEEKDAY((A1-DAY(A1)+1))> 5 ))),5,4)


Change the first four "5"'s to whatever day you wish to count (Sunday = 1,
Monday = 2, etc.), leaving the last 5 and 4 alone.

 

[Teethless mama]

Enter
A1: 1/1/2010
A2: 2/1/2010
select both A1&A2 copy down to A13

In B1: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2-1)))=5))
copy down to A12

1 for Sunday, 2 for Monday......,5 for Thursday and so on...

 

[Paris2459]

Thanks to all the replies. The last solution worked for me!!

 

[T. Valko]

With any date in cell A1...

The general formula is:

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

DOW is a weekday number from 1 to 7. Where:

1 = Monday
2 = Tuesday
3 = Wednesday
4 = Thursday
5 = Friday
6 = Saturday
7 = Sunday

So, to count how many Friday's are in January 2010:

A1 = some date in January 2010 like 1/1/2010

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