CODEWITHSUNDEEP
vbadatems-word
MyName
MyName

Reputation: 25

How to have Word VBA TypeText 45 days after today's date, but have it display the following Monday?

I'm trying to insert a future date for a visit, which will be 20 days after the current date, but if that date isn't on a Monday, I need it to be for the following Monday.

My code is currently:

.TypeText "We will visit your location the week of "
.TypeText Text:=Format(Date + 20, "mmmm d, yyyy, ")

That falls on Saturday, May 14th. I would like it to display The 16th, since that is the following Monday.

Upvotes: 1

Views: 180

Answers (1)

Sam
Sam

Reputation: 5721

Add this function

Function MondayOnOrAfter(StartDate As Date) As Date
    Dim ReturnDate As Date
    ReturnDate = StartDate
    Do While Weekday(ReturnDate) <> vbMonday
        ReturnDate = ReturnDate + 1
    Loop
    MondayOnOrAfter = ReturnDate
End Function

and call it like this

.TypeText Text:=Format(MondayOnOrAfter(Date + 20), "mmmm d, yyyy, ")

It adds one day at a time until it's Monday. It would be possible to solve this without a loop, but that code wouldn't be pretty.

Upvotes: 2

Related Questions