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

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.


"Brian S" wrote:

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

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.


"Mike H" wrote:

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.


"Brian S" wrote:

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

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)

--
Rick (MVP - Excel)



"Mike H" wrote in message
...
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.


"Brian S" wrote:

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

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.


"Rick Rothstein" wrote:

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)

--
Rick (MVP - Excel)



"Mike H" wrote in message
...
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.


"Brian S" wrote:

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

.

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

"Mike H" wrote:

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.


"Rick Rothstein" wrote:

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)

--
Rick (MVP - Excel)



"Mike H" wrote in message
...
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.


"Brian S" wrote:

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

.

I guess this would suffice...

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

--
Rick (MVP - Excel)



"Mike H" wrote in message
...
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.


"Rick Rothstein" wrote:

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)

--
Rick (MVP - Excel)



"Mike H" wrote in message
...
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.


"Brian S" wrote:

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

.