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

RCMetrics

Board Regular
Joined
Oct 28, 2005
Messages
95
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?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Perhaps this <a href ="http://www.mrexcel.com/board2/viewtopic.php?t=184972"> thread</a> can help.
 

pritcham

Board Regular
Joined
Oct 26, 2005
Messages
128
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,083
Messages
5,570,122
Members
412,305
Latest member
Mozz
Top