1st Monday of a month in date range??

S

scwilly

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
 
R

Ron Coderre

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
 
S

scwilly

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

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

Thank
 
D

Domenic

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

Ron Coderre

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,DATEDIF(A1,A2,"m"),1))-1,1+7*A3)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+ROW(A1:INDEX(A:A,DATEDIF(A1,A2,"m"),1))-1,8-MATCH(A4,{"SUN","MON","TUE","WED","THU","FRI","SAT"},0))))=MONTH(DATE(YEAR(A1),MONTH(A1)+ROW(A1:INDEX(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?
 
S

scwilly

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

scwilly

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

scwilly

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
 
R

Ron Coderre

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
 
S

scwilly

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
 
R

Ron Coderre

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
 
S

scwilly

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

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

scwilly

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

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

scwilly

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

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

scwilly

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

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

Ron Coderre

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 th
StartDate and entire month of the EndDate?

Regards,
Ro
 
R

Ron Coderre

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 th
StartDate and entire month of the EndDate?

Regards,
Ro
 
R

Ron Coderre

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
 
G

Guest

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
 

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

Similar Threads

Calculate number of Mondays betweeb 2 dates 6
Finding the date on the 'nth' Monday in this Month in this Year 1
mean of range of dates 3
Calculate the first monday of a month in the future or in the past 3
SUMPRODUCT - (amended) Exclude LAST Row of Matched Criteria (Month & Year) 2
Counting Mondays in Date Range 5
Can't get NETWORKDAYS to calc elapsed banking days in a month 2
Calculate a Date 6

Top