How to advance a date to the next specific weekday?

[CaraM]

I wasn't sure how to search this - I've done searches, but none really address what I'm trying to accomplish -- but if you have another thread to refer me to, I'll go there! Otherwise, Ideas, please!

I am moving my "to do" list from an online service that's raising its fees to an Excel spreadsheet with some VBA to automate things.

I have, among others, these columns: completed, description, recurrence, due date.

When I double-click in the "completed" column, if there is a recurrence pattern in that column, I want to advance the due date to the next correct date.

In other words, if today (Wednesday) 10/31 I complete a task with a recurrence pattern of MTWR (mon, tue, wed, thu), I want the due date to change to Thursday 11/1.

When I complete that task TOMORROW, however, I want the due date to change to Monday 11/5.

What's the most efficient way to handle patterns and date calculations like this?

I'll also have SATSUN for "do every saturday and sunday," etc.

Because the weekday numbers rotate, I've not been successful at determining a pattern that I can use every -- or even MOST -- times.

Thanks for any tips!

 

[jmacleary]

Hi there. Whilst this is in no way an elegant solution, you could try something along the lines of creating a 2-dimensional table/array 8 columns, first column being the pattern. Then fill in the next 7 columns with how many days to advance. You can then use this in a vlookup to take todays date and find the correct add-on for the number of days to the next date. The formula to use would be something like: =VLOOKUP(N3,A$1:H$25,MOD(WEEKDAY(TODAY()),7)+2,FALSE)

and the table would be set up as:

[TABLE="width: 523"]
<colgroup><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD]sat[/TD]
[TD]sun[/TD]
[TD]mon[/TD]
[TD]tue[/TD]
[TD]wed[/TD]
[TD]thu[/TD]
[TD]fri[/TD]
[/TR]
[TR]
[TD]SMT[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]WTF[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]SATSUN[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]MTWR[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 523"]
<colgroup><col><col><col span="6"></colgroup><tbody>[TR]

[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[CaraM]

Thank you, JMacLeary.

I'd rather do this in VBA. How would you translate your idea to code, without having to add all those extraneous columns to my task list?

 

[CaraM]

JMacLeary, thanks for your efforts. Your idea gave me an idea and, while I'm sure I'll find some problems with it, it's working for now. I tried to reply to your message but I'm not sure it went through (something about quotas).

I built a multidimensional array (to act as a "temp table" with the vb-weekday abbrev in one col and the abbreviation I'll use to refer to it in my patterns in the other. Not sure I can illustrate this here, but
vbSunday SUN
vbMonday M
vbTuesday T
...etc

My recurrence pattern might be MTWR (mon,tue,wed,thu) or SATSUN (weekends only), etc.

I wrote a function:

Function GetNextDate(startdate As Date, pattern As String)
Dim startdateplus As Date: startdateplus = DateAdd("d", 1, startdate)
Do Until InStr(1, LCase(pattern), LCase(arrWkDays(Weekday(startdateplus), 2))) > 0
startdateplus = DateAdd("d", 1, startdateplus)
Loop
GetNextDate = startdateplus
End Function

I feed in my task's current startdate and my task's recurrence pattern, then loop through every subsequent date until I find a day where the pattern for that date's weekday, from my array, is found in my recurrence pattern. So if today is THU and my pattern is MTWRF, I get to Friday and tomorrow is a match. If today is THU and my pattern is SATSUN I get to Saturday and there's my match.

This doesn't accommodate some of the things I think I'll want to do, with recurrence, but it's a start!

Again,thanks for helping me...

 

[jmacleary]

Sounds like a workable idea - well done.