If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I find how many of a certain day are in a given month
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 |
#2
|
|||
|
|||
How do I find how many of a certain day are in a given month
On Tue, 9 Feb 2010 13:35:01 -0800, Paris2459
wrote: 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 |
#3
|
|||
|
|||
How do I find how many of a certain day are in a given month
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. |
#4
|
|||
|
|||
How do I find how many of a certain day are in a given month
Paris2459 wrote:
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. |
#5
|
|||
|
|||
How do I find how many of a certain day are in a given month
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" wrote: 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 |
#6
|
|||
|
|||
How do I find how many of a certain day are in a given month
Thanks to all the replies. The last solution worked for me!!
"Glenn" wrote: Paris2459 wrote: 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. . |
#7
|
|||
|
|||
How do I find how many of a certain day are in a given month
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. Whe 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)) -- Biff Microsoft Excel MVP "Paris2459" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|