date LastSixMonth

imfarhan

Board Regular
Joined
Jan 29, 2010
Messages
102
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,621
Office Version
365
Platform
Windows
Can you clarify what you mean exactly?

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

imfarhan

Board Regular
Joined
Jan 29, 2010
Messages
102
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
Joined
Apr 28, 2004
Messages
75,621
Office Version
365
Platform
Windows
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
Joined
Jan 29, 2010
Messages
102
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,846
Messages
5,483,276
Members
407,390
Latest member
jenniferjohns

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top