Counting Mondays in Date Range

G

Guest

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
 
R

ryguy7272

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---
 
R

Ron Rosenfeld

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
 
P

Pete_UK

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
 
R

ryguy7272

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--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top