DATEADD() Function To Find Closest Day Of Week, One Month Later.

Posted on 2004-08-05
Last Modified: 2012-08-13
Hey Experts,

I have a date challenge.  I'm writing an ASP application that uses dateadd() to get the date of the next month and that is fine.  I use this:

nextmonth = dateadd("m",1,date)

My issue is that I need the nextmonth value to be on the same day of the week as the starting date, for example, today is Thursday, 8/5/2004, the above function returns Saturday, 9/5/2004.  I really need to calculate the closest Thursday to the nextmonth value which would be 9/2/2004.

I hope that someone has solved this issue and can be of help.

Question by:glenwr
  • 3
  • 2
  • 2
  • +2
LVL 14

Expert Comment

ID: 11725122
why not adding 4 weeks?

nextmonth = dateadd("d",28,date)
LVL 22

Accepted Solution

neeraj523 earned 250 total points
ID: 11725210

Try this

currDate = "05-Aug-04"
currDay = WeekDay(currDate)

nextDate = DateAdd("m",1,currDate)
nextDay = WeekDay(nextDate)

if nextDay >= currDay Then
      nextDate = DateAdd("d",-(nextDay-currDay),nextDate)
      nextDate = DateAdd("d",(currDay-nextDay),nextDate)
End if

LVL 58

Expert Comment

ID: 11725221
That will work for 4 week months but what about 5 week months?

response.write getFriday(datetocheck,weekday(datetocheck))

function getFriday(strdate,daytofind)
   tmpdate= year(olddate) & "/" & month(olddate) & "/" & "1"
   for count=1 to 7
      if daytofind=weekday(tmpdate) then exit for
end function
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.


Author Comment

ID: 11725292
You are right Gary.

I have been using:

nextmonth = dateadd("ww",4,date)

The application is working with the recurrence patterns in the Outlook Calendar.  I'm not sure that each monthly recurrence is exactly 4 weeks apart.  For example, if an appointment is booked on the 5th Thursday of a month (which can happen), then if the next month only has 4 Thursdays, will Outlook book the appointment on the 4th Thursday on next month?

Let me test the solutions.  You guys rock!

Author Comment

ID: 11725847
OK Guys,

I've tested the code snipets that were posted.  When I bounce the results against the Outlook calendar, what I really need to do is find the nth occurence of the date/day in the next month.  For example, today is the first Thursday of August, the next date I will need is the first Thursday in September, this can be done with dateadd("ww",4,date)
.  But, in the case where my starting date is the fifth Thursday of a month, I will need the last Thursday of the next month, if there are less than 5 Thursdays.

Now that I have enough information to be specific, any ideas?

Thanks Again

Expert Comment

ID: 11727153
Try this out

Function DayOccurence(dteDate)
  Dim intN

  intN = int(day(dtedate) / 7) + 1

  Select Case intN
    Case 1
        DayOccurence = "First"
    Case 2
        DayOccurence = "Second"
    Case 3
        DayOccurence = "Third"
    Case 4
        DayOccurence = "Fourth"
    Case 5
        DayOccurence = "Fifth"

  End Select
End Function

Expert Comment

ID: 11727169
Sorry didn't read the whole question.

Expert Comment

ID: 11730860
Try this one.

Dim dteStart, isLastWeek, intWeekNo, intNextMonth

dteStart = date()
intNextMonth = dateadd("mm",1 ,dteStart)
intWeekNo = int(day(dteStart) / 7) + 1
dteEnd =   dateadd("ww",intWeekNo ,dteStart)

DO WHILE month(dteEnd) <> intNextMonth
  dteEnd = dateadd("ww",-1,dteEnd)
LVL 22

Expert Comment

ID: 11732886
Hello @glenwr

Have you tested my post ?? if yes.. what output u got ??


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Age between date range query (SP) 13 70
JSON error 4 71
Query to get the soonest date out of a few records 2 51
Why Bundles is not working on certain files - ASP MVC 3 60
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question