Need due date to result on a monday..?

[Tack]

Need Ur Help!
In Excel, I need to get VBA to result a future "due date" based on the current day as a starting point(which would be monday thru friday) and adding a user-input number of weeks (2, 3, 4, and so on) and the actual resulting "due date" must be the "Monday" of that week in the future.

Any ideas?
Tack
davied01@nashvillewire.com

 

[jmarler]

I don't know if this will work in Excel's VBA, but I do know that it will work in standard VB (so I'm guessing that it will work in Excel).



Private Function GetMondayDate() As Date


Dim intWeeks As Integer
Dim dteDate As Date
Dim intDays As Integer


'** Get some data to work with. Take todays date
'** and add 3 weeks to it. Normally, these would
'** be passed into the function.
intWeeks = 3
dteDate = Date


'** Add the number of desired weeks to the date.
dteDate = DateAdd("ww", intWeeks, dteDate)


'** Determine how many days to subtract - Here
'** Monday is set as the first day of the week.
intDays = Weekday(dteDate, vbMonday) - 1


'** Finally, adjust the actual day so that
'** it falls on a monday.
dteDate = DateAdd("d", -intDays, dteDate)


'** Just to verify the results, I added a debug.assert.
'** Not really needed, but I like them.
Debug.Assert WeekdayName(Weekday(dteDate, vbMonday), , vbMonday) = "Monday"


'** Finally, return the results.
GetMondayDate = dteDate

End Function
- Jeff Marler B-)

 

[Tack]

Alli-Kazoo,...SHAZAAM !! ...It werks !!

Thanks friend.
TACK

PS: Is there no exclusive VBA forum at this site?

 

[MatsHulten]

Perhaps VBA Visual Basic for Applications (Microsoft) - forum is what you are searching for...

-Mats