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

  • Thread starter Thread starter Paris2459
  • Start date Start date
P

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
 
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
Click to expand...

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
 
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.
 
Paris2459 said:
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
Click to expand...


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.
 
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...
 
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))
 
Back
Top