# date LastSixMonth

#### imfarhan

##### Board Regular
Hiya,

The following function pulls the A DayBefore of the Last Monday.

DateAdd("d",-((DatePart("w",Now())-2)),Now()-8) = 03-July-2011

Problem
Now I would like to use the same function and pull exact date of last 6month i.e. 03/01/2011?
Thanks for you help!

Regards
FArhan

Optional
To calcuatel Last Monday of the week.
Now() = 07-July-2011

DateAdd("d",-((DatePart("w",Now())-2)),Now()-7) AS LastMonday,
= 04/07/2011 14:22:22

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Norie

##### Well-known Member
Can you clarify what you mean exactly?

Is it the first Monday of the month 6 months ago?

#### imfarhan

##### Board Regular
Sorry , the query runs on every Monday. for Example the next due date to run this query on 11th July so the query should pull the value of (LastMonday or Previous Monday) To (LastSunday)

"A"
LastMonday = 04/07/2011 14:22:22
DateAdd("d",-((DatePart("w",Now())-2)),Now()-7) AS LastMonday
,

"B"

LastSunday = 10/07/2011 23:59:59
CDate(Format(DateAdd("d",-((DatePart("w",Now())-1)),Now()),"dd/mm/yyyy") &' '&"23:59:59") AS LastSunday

"C" = LastMonday -1 day
DateAdd("d",-((DatePart("w",Now())-2)),Now()-8) AS LastMonday-1,

As you see on "C" to calculate the value a "day before of LastMonday" I simply use the Query "A" and instead of -7 used -8 which works fine.

What I need to do now?

can I use the same "C" function to get the date of 6 month before which should be "03/01/2011"

Example if we assume today is 11-July-2011

My query use two criteria , Weekly (LastMonday to Last Sunday) which is fine and
(04-July-2011) to (10 July-2011) working fine

other critieria should use (LastSixMonth date from LastMonday-1) to (LastMonday-1)
(03-Jan-2011) To (03-July-2011)

Problem in how to calcuate red bit which should be exactly six month before than 03-July-2011

I hope it does make sense now
Many thanks
Farhan

Last edited:

#### Norie

##### Well-known Member
Farhan

Your title mentions sixMonth and the first part of your post seems to be asking how to get a date six months ago.
Farhan said:
Problem
Now I would like to use the same function and pull exact date of last 6month i.e. 03/01/2011?
Thanks for you help!
I'm confused.

#### imfarhan

##### Board Regular
I'm so stupid
I was using the wrong place argument in red fonts

Code:
``````[COLOR=black]Wrong Argument[/COLOR]
[COLOR=darkred]=DateAdd("m",DateAdd("d",-((DatePart("w",Now())-2)),Now()-8),-6) [/COLOR]

[B]Right Query/Argument[/B]
[COLOR=green]=DateAdd("m",-6,DateAdd("d",-((DatePart("w",Now())-2)),Now()-8))[/COLOR]``````

The -6 argument should come before before not at the end

Thanks for your help
Regards
F

Threads
1,102,359
Messages
5,486,385
Members
407,544
Latest member
mguevara