Always return next Thursday's date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

XP - 09 Nov 2006 19:24 GMT

I'm usually pretty good at calculating dates, but for some reason, I'm
blanking on this one.

I need a function such that it always returns next Thursday's date, based on
today.

So, if today is Friday, I get next Thursday's date; if today is Wednesday, I
get next Thursday's date (tomorrow); if today is Thursday, then I get, you
guessed it, NEXT Thursday (a week from today)...etc.

I like using separate functions, so a function that can be called and return
my date would be ideal, but the code is the main thing. IF anyone can post me
a solution in VBA I would sure appreciate it. Thanks!

Reply to this Message

John Spencer - 09 Nov 2006 19:45 GMT

Really quick and dirty function

Public Function NextThursday(DateIn)
'Handles nulls, invalid dates, and strings or date inputs.

Dim rtnVal As Date

If IsDate(DateIn) = False Then
NextThursday = DateIn
Else
rtnVal = DateAdd("d", 5 - Weekday(DateIn), DateIn)
If rtnVal <= DateValue(DateIn) Then rtnVal = DateAdd("d", 7, rtnVal)
NextThursday = rtnVal
End If

End Function

> I'm usually pretty good at calculating dates, but for some reason, I'm
> blanking on this one.
[quoted text clipped - 13 lines]
> me
> a solution in VBA I would sure appreciate it. Thanks!

Reply to this Message

kingston - 09 Nov 2006 20:06 GMT

Assuming your system is set so that weeks start on sunday (weekday 1):

If Weekday(Date()) < 5 then
NextThursday = Date() + (5-Weekday(Date()))
Else
NextThursday = Date() + (12-Weekday(Date())
End If

>I'm usually pretty good at calculating dates, but for some reason, I'm
>blanking on this one.
[quoted text clipped - 9 lines]
>my date would be ideal, but the code is the main thing. IF anyone can post me
>a solution in VBA I would sure appreciate it. Thanks!

Reply to this Message