find dates between a range

# Thread: find dates between a range

1. uc
Member
Join Date
Dec 1969
Posts
63

## find dates between a range

input:-<BR>from date : 01-jan-2000<BR>To date : 01-feb-2002<BR>which day : third sunday for month june<BR><BR>Question: between the above mentioned years,<BR>Find all the dates falling on third sunday for month of june<BR><BR>output :18-june-2000<BR> 17-june-2001<BR> 16-june-2002<BR><BR>Pls help me

2. Senior Member
Join Date
Dec 1969
Posts
1,424

## RE: find dates between a range

Start by working out which YEAR&#039;s you need to evaluate. Do that by working out which year to start at and which year you need to end at. In the example that you gave, you would start in the year 2000 and end in 2002.<BR><BR>Do you know how to work that out programatically?

3. Senior Member
Join Date
Dec 1969
Posts
404

## RE: find dates between a range

NOT elegant, but quick:<BR>&#060;!--Explain--&#062;<BR>3d Sunday can only be between the 15 and 21<BR>AND Weekday(AnySunday) = 1<BR><BR>&#060;!--Code--&#062;<BR>yStart = Year(lowDate)<BR>yEnd = Year(highDate)<BR><BR>for iYear = yStart to yEnd<BR>For j = 15 to 21<BR> Select case Weekday(DateSerial(iYear,6,j)<BR> Case 1<BR> &#039;bingo<BR> Exit for<BR> Case Else<BR> End Select<BR>Next<BR>Next<BR>

4. uc
Member
Join Date
Dec 1969
Posts
63

## RE: find dates between a range

Hi,<BR> the years are just examples, there could be any range<BR>for example: 01-jan-98 to 01-july-2000

5. uc
Member
Join Date
Dec 1969
Posts
63

## RE: find dates between a range

Hi ned,<BR>i couldnt get what u said, could you be a little more elaborate.<BR><BR>Thanx.<BR>uc

6. Senior Member
Join Date
Dec 1969
Posts
404

## RE: find dates between a range

The Weekday function returns a number from 1 to 7 indicating the day of the week. i.e. Sunday = 1, Monday = 2, etc.<BR>Since you are only interested in Sunday you are trying to find those dates whose weekday is 1. There are a whole set of vbConstants that go with this (or any date function) vbSunday, vbMonday, etc. which correspond to 1,2,3,etc.<BR><BR>So, I did 2 nested loops.<BR><BR>In the 1st loop you are looping through the years.<BR>For iYear = Year(lowest Date of interest) to Year(highest date of interest)<BR><BR>In the second loop, you are using the values 15 to 21, because the 3rd occurence of any given weekday can only fall within this range.<BR>June of course is the 6th month.<BR><BR>The DateSerial function takes arguments of(Year, Month Day) and allows you to perform arithmetic operations to get a specified date.<BR><BR>For j = 15 to 21<BR> Select Case Weekday(DateSerial(iYear, 6, j)<BR> &#039;evaluate the weekday of the date specified by iYear, 6, j<BR> Case 1<BR> &#039;You have found exactly what you are looking for - <BR> a Sunday that occurs between the 15 and the 21<BR><BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
404

## RE: find dates between a range

Or if you need a more generic form:<BR><BR>Sub DoSomethingWithDates(startDate, endDate, iMonth, iDayOfWeek, iOccurenceOfInterest)<BR><BR>Dim iOccurenceCount, iYear, j, iLastDateOfMonth<BR><BR>for iYear = Year(startDate) to Year(endDate)<BR> iLastDateOfMonth = DateSerial(iYear, iMonth + 1, 0)<BR> iOccurenceCount = 0<BR> For j = 1 to iLastDateOfMonth<BR> Select Case Weekday(DateSerial(iYear,iMonth,j)<BR> Case iDayOfWeek<BR> j = j + 7 &#039;start counting by 7&#039;s<BR> iOccurenceCount = iOccurenceCount + 1<BR> if iOccurenceCount = iOccurenceOf Interest Then<BR> &#039;do something<BR> Exit For<BR> End If<BR> End Select<BR> Next<BR>Next<BR><BR>End Sub

8. Senior Member
Join Date
Dec 1969
Posts
404

## One little correction

iLastDateOfMonth = DAY(DateSerial(iYear, iMonth + 1, 0))<BR>

9. uc
Member
Join Date
Dec 1969
Posts
63

## RE: find dates between a range

Ned,<BR>Thanx for ur response.<BR>Could you give me the actual constants passed to the function.<BR>coz i keep getting the typemismatches...<BR><BR>

10. uc
Member
Join Date
Dec 1969
Posts
63

## RE: help me

&nbsp;<BR>ned, <BR>i need the output as mentioned below, dates for all years between the range. Thanx<BR><BR>output :18-june-2000 <BR> 17-june-2001 <BR> 16-june-2002 <BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•