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

scwilly:

I'm not sure what part of the DATEDIF function is causing you
problems.

Try Chip Pearson's website:

http://www.cpearson.com/excel/datedif.htm

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

Your formula worked is there anyway to do it without using the first of
the months for the ranges e.g. 1/1/2006 - 3/1/2006 so that I could use
e.g. 1/2/2006 - 3/4/2006.

Thanks again for all of your help


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

scwilly:

I'm pretty sure I don't understand your question. Nope...definitely
don't understand.

Wouldn't the nth occurrence of a weekday in a month mean the whole
month?

I think I'll need some examples using start/end dates and what you
expect to find.

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

Example:

How many 5th Fridays are between 5/1/2006 and 6/30/2006

The answer should be 1, your formula works if I change the end date to
7/1/2006 but not for 6/30/2006 which I need and would love.

It is more for a date range where the begin and end date could fall on
days other than the first of the month.

Thanks again, cheers


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

scwilly:

The end date problem might be easily solved by changing the formula to
ensure that the end date is the 1st of the next month.

I'm still unclear about the start date. What are some
non-first-of-the-month scenarios and what values would you expect. From
your previous posts I infer that this situation might arise:

StartDate: 1/21/2006
EndDate: 4/19/2006
Occurrences: 4
WeekDay: TUE

What count would you expect and why? Or is that not a valid scenario?

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

Thank GOD you are still around. I am trying to change the start and stop
dates so that in the formula they don't always have to be the first of
the months.

I looked at the datedif function I don't know how to change it so that
I don't have to use e.g. 1/1/2006, 3/1/2006 etc.


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

Thank GOD you are still around. I am trying to change the start and stop
dates so that in the formula they don't always have to be the first of
the months.

I looked at the datedif function I don't know how to change it so that
I don't have to use e.g. 1/1/2006, 3/1/2006 etc.


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

Thank GOD you are still around. I am trying to change the start and stop
dates so that in the formula they don't always have to be the first of
the months.

I looked at the datedif function I don't know how to change it so that
I don't have to use e.g. 1/1/2006, 3/1/2006 etc.


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

Now that you know I'm thankful you are still around the scenario you
outlined IS what I'm looking for.

I would expect to get 4 thursdays for that range and occurence. Now how
can we change your formula to account for the range you gave me an
example for?


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

scwilly:

Possibly my last question...

In the example scenario:

StartDate: 1/21/2006
EndDate: 4/19/2006
Occurrences: 4
WeekDay: TUE

You indicated that there would be four 4th-Tuesdays.

I'm guessing that those four 4th-Tuesdays would be:
Jan_24th
Feg_28th
Mar_28th
Apr_25th

If that's true, do you mean that we should use the entire month of the
StartDate and entire month of the EndDate?

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

scwilly:

Possibly my last question...

In the example scenario:

StartDate: 1/21/2006
EndDate: 4/19/2006
Occurrences: 4
WeekDay: TUE

You indicated that there would be four 4th-Tuesdays.

I'm guessing that those four 4th-Tuesdays would be:
Jan_24th
Feg_28th
Mar_28th
Apr_25th

If that's true, do you mean that we should use the entire month of the
StartDate and entire month of the EndDate?

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

scwilly:

Possibly my last question...

In the example scenario:

StartDate: 1/21/2006
EndDate: 4/19/2006
Occurrences: 4
WeekDay: TUE

You indicated that there would be four 4th-Tuesdays.

I'm guessing that those four 4th-Tuesdays would be:
Jan_24th
Feg_28th
Mar_28th
Apr_25th

If that's true, do you mean that we should use the entire month of the
StartDate and entire month of the EndDate?

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

Apologies, MS Newsgroupers

For some unknown reason, one click on the ExcelTip forum posting link
results in 3 posts to the newsgroup.

Annoying.

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:


scwilly:

Possibly my last question...

In the example scenario:

StartDate: 1/21/2006
EndDate: 4/19/2006
Occurrences: 4
WeekDay: TUE

You indicated that there would be four 4th-Tuesdays.

I'm guessing that those four 4th-Tuesdays would be:
Jan_24th
Feg_28th
Mar_28th
Apr_25th

If that's true, do you mean that we should use the entire month of the
StartDate and entire month of the EndDate?

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

scwilly Wrote:
Example:

How many 5th Fridays are between 5/1/2006 and 6/30/2006

The answer should be 1, your formula works if I change the end date to
7/1/2006 but not for 6/30/2006 which I need and would love.

It is more for a date range where the begin and end date could fall on
days other than the first of the month.

Thanks again, cheers

Hi scwilly, how are you getting 1 for the above? If you include the
start and end dates should it not be 2, 31st March and 30th June? Or
are you not counting the start and end dates as part of the range?
Assuming you are you could use this formula

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")=A4),--(ABS(DAY(ROW(INDIRECT(A1&":"&A2)))-A3*7+3)<4))

works for any start/end dates


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

scwilly Wrote:
Example:

How many 5th Fridays are between 5/1/2006 and 6/30/2006

The answer should be 1, your formula works if I change the end date to
7/1/2006 but not for 6/30/2006 which I need and would love.

It is more for a date range where the begin and end date could fall on
days other than the first of the month.

Thanks again, cheers

Hi scwilly, how are you getting 1 for the above? If you include the
start and end dates should it not be 2, 31st March and 30th June? Or
are you not counting the start and end dates as part of the range?
Assuming you are you could use this formula

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")=A4),--(ABS(DAY(ROW(INDIRECT(A1&":"&A2)))-A3*7+3)<4))

works for any start/end dates


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

Voila that works!! Thank you both RON and DADDYLONGLEGS for all of your
help.

Regards,


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