Hello everyone,

I need to determine the number of 1st Mondays or any weeday e.g. 5th
Fridays between a date range e.g. 1/1/2006 - 4/1/2006.

I've noticed a lot of threads for calculating just one month but none
for a period of time.

Thanks so much in advance


--
scwilly
------------------------------------------------------------------------
scwilly's Profile: http://www.excelforum.com/member.php...o&userid=18251
View this thread: http://www.excelforum.com/showthread...hreadid=535648

Try this:

A1: (any date)
A2: (the occurrence to calculate…eg 3 for 3rd occurrence)
A3: (The day to find…eg MON for Monday)

C1:
=DATE(YEAR(A1),MONTH(A1),1+7*A2)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-MATCH(A3,{"SUN","MON","TUE","WED","THU","FRI","SAT "},0)))

Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=535648

I'm basically trying to set up a table and I need to know how many 1st
Mondays occur in a month, how many 5th tuesdays occur in a given month
etc.

I need to work with a date range and not one fixed date like 1/1/2006
so I can count the number of times e.g. a 5th Tuesday occurs etc,
between 1/1/2006 and 4/1/2006

Thanks


--
scwilly
------------------------------------------------------------------------
scwilly's Profile: http://www.excelforum.com/member.php...o&userid=18251
View this thread: http://www.excelforum.com/showthread...hreadid=535648

Is this what you mean? Assuming that A2 contains the start date, and B2
contains the end date...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=4),--(DAY(ROW(INDIRECT
(A2&":"&B2)))=15))

....which will count the number of times a Thursday occurs on the 15th of
the month within a given period.

For a different weekday, change the 4 accordingly. For example, Mon=1,
Tue=2, Wed=3, etc.

For a different day of the month, change the 15 accordingly.

Hope this helps!

In article ,
scwilly wrote:

I'm basically trying to set up a table and I need to know how many 1st
Mondays occur in a month, how many 5th tuesdays occur in a given month
etc.

I need to work with a date range and not one fixed date like 1/1/2006
so I can count the number of times e.g. a 5th Tuesday occurs etc,
between 1/1/2006 and 4/1/2006

Thanks

OK, scwilly....let's try this:

A1: (the 1st of the month to start)
A2: (the 1st of the month AFTER the last month)
A3: (the occurrence to calculate…eg 3 for 3rd occurrence)
A4: (The day to find…eg MON for Monday)

C1:
=SUMPRODUCT(--(MONTH(DATE(YEAR(A1),MONTH(A1)+ROW(A1:INDEX(A:A,DA TEDIF(A1,A2,"m"),1))-1,1+7*A3)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+ROW(A1:INDEX(A:A,D ATEDIF(A1,A2,"m"),1))-1,8-MATCH(A4,{"SUN","MON","TUE","WED","THU","FRI","SAT "},0))))=MONTH(DATE(YEAR(A1),MONTH(A1)+ROW(A1:INDE X(A:A,DATEDIF(A1,A2,"m"),1))-1,1))))

----------------
Test 1:
A1: 1/1/2006
A2: 4/1/2006
A3: 5
A4: WED

C1 returns 1
There is only one 5th WED of the month in the range Jan_2006 thru
MAR_2006. That occurrence is in MAR_2006.

-----------------
Test 2:
A1: 1/1/2006
A2: 4/1/2006
A3: 4
A4: WED

C1 returns 3
There is a 4th WED of the month in each month in the range Jan_2006
thru MAR_2006.

Helping Yet?


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=535648

I think we are on the right track; however, I can't get the formula to
work I don't know if it is the "date dif" part or what, help??


--
scwilly
------------------------------------------------------------------------
scwilly's Profile: http://www.excelforum.com/member.php...o&userid=18251
View this thread: http://www.excelforum.com/showthread...hreadid=535648

You are somewhat on the right track instead of the "15th" of the month I
want to know how many e.g. 5th Tuesdays there are in a date range.
Help??


--
scwilly
------------------------------------------------------------------------
scwilly's Profile: http://www.excelforum.com/member.php...o&userid=18251
View this thread: http://www.excelforum.com/showthread...hreadid=535648