Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Mondays in Date Range
I want to count the number of Mondays etc. in a date range. I found the
following on Chip Pearson's site... =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0)) Where A2 is the start date, B2 is the end and C2 is the weekday number Whilst this does the trick I would prefer a solution which is not an array formula. Can this be done another way e.g. with Sumproduct or similar and if so how please? Thanks a lot Kewa |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Mondays in Date Range
Here is one way of doing it:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A29)),2)={2})) A1 = the cell with the first date A29 = the cell with the last date Change to suit your needs... Regards, Ryan--- -- RyGuy "nospaminlich" wrote: I want to count the number of Mondays etc. in a date range. I found the following on Chip Pearson's site... =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0)) Where A2 is the start date, B2 is the end and C2 is the weekday number Whilst this does the trick I would prefer a solution which is not an array formula. Can this be done another way e.g. with Sumproduct or similar and if so how please? Thanks a lot Kewa |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Mondays in Date Range
On Thu, 28 Feb 2008 08:16:03 -0800, nospaminlich
wrote: I want to count the number of Mondays etc. in a date range. I found the following on Chip Pearson's site... =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0)) Where A2 is the start date, B2 is the end and C2 is the weekday number Whilst this does the trick I would prefer a solution which is not an array formula. Can this be done another way e.g. with Sumproduct or similar and if so how please? Thanks a lot Kewa The number of DOW (1= Sunday, 2 = Monday, ..., 7 = Saturday) between a start date (A1) and an end date (A2) is : =INT((A2-WEEKDAY(A2+1-DOW)-A1+8)/7) (credit to Daniel M.) --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Mondays in Date Range
You asked specifically for Mondays within a date range, so I'm not
sure why you need column C. Here's one way of doing it: =INT((B2-A2)/7)+IF(MOD(B2-A2,7)+WEEKDAY(A2,2)7,1,0) This assumes the dates are inclusive. Hope this helps. Pete On Feb 28, 4:16*pm, nospaminlich wrote: I want to count the number of Mondays etc. in a date range. *I found the following on Chip Pearson's site... =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0)) Where A2 is the start date, B2 is the end and C2 is the weekday number Whilst this does the trick I would prefer a solution which is not an array formula. *Can this be done another way e.g. with Sumproduct or similar and if so how please? Thanks a lot Kewa |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Mondays in Date Range
Thanks a lot for the very helpful replies.
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Mondays in Date Range
I just noticed a little tiny issue with my function if the last date is a
Monday. Try this: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A26)),1)={2})) I changed the second to last 2 to a one and it seemed to work... Regards, Ryan-- -- RyGuy "nospaminlich" wrote: Thanks a lot for the very helpful replies. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Within a Date Range | Excel Worksheet Functions | |||
Counting a range to see if it encompasses a given date | Excel Worksheet Functions | |||
Counting Unique Values by Date Range | Excel Worksheet Functions | |||
Counting if between date range | Excel Worksheet Functions | |||
Counting values within a Date Range | Excel Discussion (Misc queries) |