Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find the month | Excel Worksheet Functions | |||
Find the MIN of Month and Day only | Excel Discussion (Misc queries) | |||
I would like to find the last day of a month | Excel Worksheet Functions | |||
How do i find the first value every month | Excel Discussion (Misc queries) | |||
Find out first Friday every month | Excel Worksheet Functions |