Find out what Weekday we are then do something based on it

[RCMetrics]

Hi,

I do reporting based on a table that has sales by date.

I run my query on Mondays for the previous week (sunday to saturday)

this is the formula used to set the time frame wanted in my query:
(on the date field in table)

>=(Date()-9) And <=(Date()-3)

Which gives me "on mondays" the proper numbers and all.

What I would like is to be able to get the same results if I have to re-run something but later in the week

Say the table had something missing or corrections were made...
How to I avoid making changes to the query and get the formula to be smart enough to understand that if we are "thursday" to go backward 3 days and then do >=(Date()-9) And <=(Date()-3)

It would need to understand that if we are Sunday or Monday, business as usual... just get the past 7 days...

but on any other day... since the current week is not over, to give only results for the previous week...sunday to saturday.

Any ideas?

 

[Norie]

Perhaps this <a href ="http://www.mrexcel.com/board2/viewtopic.php?t=184972"> thread</a> can help.

 

[pritcham]

Hi

Here's an example of getting the date for the previous Monday, and the Sunday that's just gone:

DateAdd("d",Now(),0-(DatePart("w",Now())-1)) AS SundayJustGone, DateAdd("d",[SundayJustGone],-6) AS LastMonday;

Hope that helps
Martin