Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
'08-'11 Days of the Wk per Month
I have an array of date ranges from 2008-2011 and would like to
calculate how many "Mondays" per month take place within this range. For example, range #1 6/29/09 - 8/16/09 consists of 7 weeks over 3 months. Is there a way I may seperate the # of weeks per month, June - 1 "Monday", July - 4 "Mondays", August - 2 "Mondays" (1 + 4 + 2 = 7) As there is 500+ various date ranges, any assistance would be appreciative. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
'08-'11 Days of the Wk per Month
If you want the total count of Monday's from a start date to an end date
(inclusive): A1 = start = 6/29/2009 B1 = end = 8/16/2009 =INT((WEEKDAY(A1-1,2)+B1-A1)/7) Or, the generic syntax to count other weekdays: =INT((WEEKDAY(start-n,2)+end-start)/7) Where n = day of the week: Monday = 1 through Sunday = 7 If you want a break-out summary then that'll take some work! -- Biff Microsoft Excel MVP "Maverick_TG" wrote in message ... I have an array of date ranges from 2008-2011 and would like to calculate how many "Mondays" per month take place within this range. For example, range #1 6/29/09 - 8/16/09 consists of 7 weeks over 3 months. Is there a way I may seperate the # of weeks per month, June - 1 "Monday", July - 4 "Mondays", August - 2 "Mondays" (1 + 4 + 2 = 7) As there is 500+ various date ranges, any assistance would be appreciative. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
'08-'11 Days of the Wk per Month
B1=6/29/09
B2=8/16/09 $A$1:$A$40 includes yr dates =SUM(IF((WEEKDAY($A$1:$A$40,2)=1)*($A$1:$A$40=B1) *($A$1:$A$40<=B2), 1,0)) the result is 6 (in my Excel 2003) array-enter the formula i.e. CTRL+SHIFT+ENTER HIH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel to make the days cary over month to month automaticly | New Users to Excel | |||
Number of days in month counted from shortened name of month & yea | Excel Worksheet Functions | |||
Days per month for calculating storage days | Excel Worksheet Functions | |||
function to fill all days of month to end of month | Excel Worksheet Functions | |||
function to fill all days of month to end of month | Excel Worksheet Functions |