Query from day of week

R

rbeach

I need assistance in creating the formula to select one week ago Wednesday
through Thursday of the current week. This query needs the ability to be run
on any day of the week. Below is the formula I have in place but it returns 8
days ago through yesterday. If this is run on Wednesday it works correctly
but I need it to be run on any weekday.
 
R

rbeach

My apologies but I stated "one week ago Wednesday through Thursday" below but
it should read "one week ago Wednesday through Tuesday".
 
D

Daryl S

Rick -

If today is Monday, do you want the period to be the Wednesday from two
weeks ago through the Tuesday from last week? Or do you want to end always
on the current week, even if the Tuesday has not passed yet?

If you always want to end on this week's Tuesday, then the logic would be to
subtract the number of days passed in this week (Weekday()) and add 3 for
Tuesday. And to start on the prior Wednesday, then subtract the number of
days passed in this week plus one week (Weekday() + 7) and add 4 for
Wednesday. The result would look like this:

= (Date()-Weekday(Date())-7+3) And <= (Date()-Weekday(Date())+2)
Or simpler:
= (Date()-Weekday(Date())-4) And <= (Date()-Weekday(Date())+2)

If you want to go to the prior week's data if today is Sunday or Monday,
then use this:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top