Prev: Values of the last date
Next: Run make table query from Excel with Access closed
From: Brian S on 8 Apr 2010 12:40
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" <MikeH(a)discussions.microsoft.com> wrote in message
> > news:922CC728-7727-4807-B6C8-480953A28F3D(a)microsoft.com...
> > > 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
> >
> > .
> >
From: Rick Rothstein on 8 Apr 2010 13:04
I guess this would suffice...

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

--
Rick (MVP - Excel)



"Mike H" <MikeH(a)discussions.microsoft.com> wrote in message
news:2904AA94-AFE9-4754-9889-24951E8D687D(a)microsoft.com...
> 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" <MikeH(a)discussions.microsoft.com> wrote in message
>> news:922CC728-7727-4807-B6C8-480953A28F3D(a)microsoft.com...
>> > 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
>>
>> .
>>
First  |  Prev  | 
Pages: 1 2
Prev: Values of the last date
Next: Run make table query from Excel with Access closed