Calculate number of Mondays betweeb 2 dates

[k.roberts]

I am looking for a formula that will calculate the number of Mondays
between 2 given dates (date will always be the 23rd of the month).

I am currently using =INT((D1-WEEKDAY(D1-1)-C1+8)/7)

This is working fine up until it hits a date where the 23rd falls on a
Monday.

e.g. 23rd Sept > 23rd Oct 2008 = 4 Mondays (correct)
23rd Oct > 23rd Nov 2008 = 4 Mondays (correct)
23rd Nov > 23rd Dec 2008 = 5 Mondays (correct)

and so on

But when I get to 23rd Jan to 23rd Feb 2009 (which falls on a Monday),
Excel is returning 5. I am wanting to see 4 at this point, same for
23rd Feb to 23rd March.

How can I amend this formula?
23rd Sept > 23rd Oct 2008 = 4 Mondays (correct)
23rd Sept > 23rd Oct 2008 = 4 Mondays (correct)

 

[Mike H]

Hi,

First date in a1
later date in a2
Try
=SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&TRUNC(A2-A1)+1)))=2,1,0))

the =2 bit is for monday change to =1 for Sundaye etc
It's an array so commit with CTRL+Shift + Enter not just enter

Mike

 

[Bob Umlas, Excel MVP]

Ctrl+shift+enter:
=SUM((WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=2)*1)

 

[Roger Govier]

Hi

One way
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=1))

 

[T. Valko]

Another one:

=INT((WEEKDAY(start-7,2)+end-start)/7)

 

[k.roberts]

Another one:

=INT((WEEKDAY(start-7,2)+end-start)/7)

--
Biff
Microsoft Excel MVP












- Show quoted text -

Thanks guys but these still trip up when they hit the months where a
Monday is also the 23rd! Extremely annoying but as there are only a
few a year I will manually amend for these months. Cheers anyway!
these formulas are good and I am sure will come in handy.

 

[Roger Govier]

Hi

Thanks guys but these still trip up when they hit the months where a
Monday is also the 23rd! Extremely annoying but as there are only a
few a year I will manually amend for these months. Cheers anyway!
these formulas are good and I am sure will come in handy.

Try
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=1))-
(WEEKDAY(A2,2)=1)