trying to find the previous thursday before running a macro
in [Programming]
|
From: Brian S on 8 Apr 2010 10:09 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: Mike H on 8 Apr 2010 10:33 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: Mike H on 8 Apr 2010 10:39 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
From: Rick Rothstein on 8 Apr 2010 11:00 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: Mike H on 8 Apr 2010 12:21 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 > > . >
|
Next
|
Last
Pages: 1 2 Prev: Values of the last date Next: Run make table query from Excel with Access closed |