trying to find the previous thursday before running a macro

[Brian S]

The title pretty much says it all. I am trying to find the previous thursday
that occur before I run a macro. The previous thursday date is part of the
file name, so I need that to open the previous file. Most of the time I will
run the macro on Wednesday, so something like now()-6 could work, but I would
rather not make it not day dependent.

Thanks,
B

 

[Mike H]

Brian.

I assume that if 'Today' is Thursday you want todays date or if nit the
previous Thursday.

LastThursday = Date + WorksheetFunction.Choose(Weekday(Date), -3, -4, -5,
-6, 0, -1, -2)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Hi,

I should have added if you want the previous Thursday even if today is
Thursday then change the 0 (Zero) in the formula to -7
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

Two things... First, VB has its own built-in Choose function, so you don't
have to call out to the worksheet's version of the function. Second, you can
use a straight calculation rather than using the Choose function...

LastThursday = Date + 1 - Weekday(Date, vbThursday)

 

[Mike H]

Rick,

I forgot about Choose in Vb, thank's for that.

The reason i did it that way was I wasn't sure what the OP wanted if it was
Thursday, did he want 'today' or 7 days earlier i.e the previous Thursday.
How would you change your code to provide the latter?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Brian S]

Thanks for the responses. Mikes worked so I didn't try yours Rick.

Yes if today is Thursday I still wanted it to show the previous thursday.

Thanks again.
Brian

 

[Rick Rothstein]

I guess this would suffice...

LastThursday = D + 1 + 7 * (Weekday(D) = 5) - Weekday(D, vbThursday)