Find Next Tuesday

[Oxygen Potassium]

I have a database that is used as a task tracker and creates due dates based on the frequency (daily, weekly, monthly, etc..). I'm having difficulties working out a formula for determining the date of the next Wednesday, whether it be this week (if today is Mon-Wed) or next week (if today is Thurs-Fri).



Right now I built a Function to return a specific date:

AddWorkdaysX(StartDate As Date, NumDays As Integer) As Date


I'm thinking something like IIF(date()<=Wednesday, Return this week Wednesday's date, Return next week Wednesday's date)

[CJ_London]

use the weekday function to determine the day of the week

weekday(date()) for today (Tuesday), returns 3

so a formula something like this

date()+iif(weekday(date())<4,4,11)-weekday(date())

[Micron]

Date + (8 - Weekday(date, vbTuesday))
However, if you run this on a Tuesday, you will get Tuesday's date. You might need to incorporate that with the DateDiff function to test for the number of days difference (it would be zero). That function would also enable you to get the number of days until Tuesday, which you didn't ask for. I thought you might be interested in using that somehow.
Edit: post said Wednesday, title said Tuesday. I went with Tuesday.

[orange]

Here is a function that should be helpful. You can use it to find the Next whatever from any Date.

Function GetNextYourDay(dteMydate As Date, MyDay As Integer) As Date
'dteMydate is the Date from which you are making the determination
'myDay refers to the integer value of the Day where 1=sunday,2 = monday,3 = tuesday....
 GetNextYourDay = [dteMydate] - WeekDay([dteMydate]) + MyDay + IIf(WeekDay([dteMydate]) >= MyDay, 7, 0)
End Function

So to get next Wednesday, in the immediate window

?getnextyourday(Date,4) '4 represents Wednesday and Date is today( 7 June 2016)
08/06/2016 '<-----Canadian regional setting

[Oxygen Potassium]

Thanks Micron.

My formula now looks like this:

IIf(Date()<=Weekday(Date(),3),AddWorkdaysX(Weekday (Date(),2),1),AddWorkdaysX(Date()+(8-Weekday(Date(),2)),1))

My problem now is the statement is always coming back as false

[Oxygen Potassium]

Thanks Orange I think that function solved my problem.

This is my formula to find the next Tuesday, which seems to work:

IIf(Weekday(Date())<Weekday(Date(),4),AddWorkdaysX (getnextyourday(Date(),2)-7,1),AddWorkdaysX(getnextyourday(Date(),2),1))

[orange]

There is no logic to handle "workdays". The function takes a date (dteMydate any date from which you do the measurement as parameter, and an integer from 1 thru 7 to indicate which day you want. The integer represents day from 1=sunday,2 - monday, 3=tuesday...7=saturday.

To get the Thursday following OrderDate, you would use

getnextyourday(OrderDate,5) which means get the next Thursday (day 5) after OrderDate.

[Micron]

and an integer from 1 thru 7

Don't forget 0 - system default.