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)

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

" wrote:

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)

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

"Mike H" wrote:

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

" wrote:

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)

Hi

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


--
Regards
Roger Govier

wrote in message
...
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)

Another one:

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

--
Biff
Microsoft Excel MVP


wrote in message
...
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)

On 8 Sep, 17:42, "T. Valko" wrote:
Another one:

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

--
Biff
Microsoft Excel MVP

wrote in message

...



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)- Hide quoted text -

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

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)

--
Regards
Roger Govier