• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 766
  • Last Modified:

Calculating Date Ranges


I've built a report that needs to extract data in a date range for the last 8 weeks.  Now I have an issue when the new year rolls around.  My current formula is below.  I need the report to pick up data from 2 months ago, last month and last month the year before.  For example it's Jan 2010, I need to search on Dec 09, Nov 09 and Dec 08.  

({Table.Field} in [dateSerial(year(CurrentDate) - 1, month(CurrentDate) - 2, 1) to dateSerial(year(CurrentDate) - 1, month(CurrentDate), day(currentdate) - 1)] or
{Table.Field} in [dateSerial(year(CurrentDate), month(CurrentDate) - 2, day(currentdate) -1) to dateSerial(year(CurrentDate), month(CurrentDate), day(currentdate) - 1)])
2 Solutions
I'm not sure what you're looking for.  You mentioned the last 8 weeks, but then you threw "last month the year before" in there.

 Are you looking for anything in the last 8 weeks (literally), ending today or yesterday (Which?), plus any date in the previous month, in the previous year (any date in Dec, 2008 in your example)?

 FWIW, whatever you're looking for, I'd probably use the DateAdd function for at least part of it.  It gives you more control over the dates (I think).  For example, DateAdd ("ww", -8, CurrentDate) would give you the date 8 weeks before today (on 01/05, that would be 11/10).  But maybe that's just me.  I've never really used DateSerial, so I'm just not comfortable with it.

Okay, breaking it down.  I'm doing this in basic syntax because that's what I'm familiar with.

Either you need to build a nasty looking selection formula, or a set of formulas. Here's the formula set.

dim amonthago as date
dim currentstart as date
amonthago = cdate(dateadd("m",-1,currentdate))
currentstart = cdate(dateadd("m",-1,amonthago))
currentstart = datevalue(year(currentstart), month(currentstart), 01)

dim amonthago as date
dim currentend as date
amonthago = cdate(dateadd("m",-1,currentdate))
currentend = cdate(dateadd("m",+1,amonthago))
currentend = datevalue(year(currentend), month(currentend), 01)
currentend = cdate(dateadd("d",-1,currentend))

dim amonthago as date

dim lastyrstart as date

amonthago = cdate(dateadd("m",-1,currentdate))
amonthago = cdate(dateadd("yyyy",-1,amonthago))
lastyrstart = datevalue(year(amonthago), month(amonthago), 01)

dim amonthago as date
dim lastyrend as date
amonthago = cdate(dateadd("m",-1,currentdate))
amonthago = cdate(dateadd("yyyy",-1,amonthago))
lastyrend = cdate(dateadd("m",+1,amonthago))
lastyrend = datevalue(year(lastyrend), month(lastyrend), 01)
lastyrend = cdate(dateadd("d",-1,lastyrend))

Then your formula becomes.

({Table.Field} in [{@currentstart} to {@currentend}] or
{Table.Field} in [{@lastyrstart} to {lastyrend}]
And here's the selection formula, ready to go.

datevar currentstart := cdate(dateadd("m",-1,cdate(dateadd("m",-1,currentdate))));
currentstart := datevalue(year(currentstart), month(currentstart), 01);
datevar currentend := cdate(dateadd("m",+1,cdate(dateadd("m",-1,currentdate))));
currentend := datevalue(year(currentend), month(currentend), 01);
currentend := cdate(dateadd("d",-1,currentend));
datevar amonthago := cdate(dateadd("yyyy",-1,cdate(dateadd("m",-1,currentdate))));
datevar lastyrstart := datevalue(year(amonthago), month(amonthago), 01);
datevar lastyrend := cdate(dateadd("m",+1,amonthago));
lastyrend := datevalue(year(lastyrend), month(lastyrend), 01);
lastyrend := cdate(dateadd("d",-1,lastyrend));

({Table.Field}  in [currentstart to currentend]
or {Table.Field}  in [lastyrstart to lastyrend])
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

And in fact, using dateserial as in your current one, you can make the assumption that there are 12 months in a year....

({Table.Field} in [dateSerial(year(CurrentDate) , month(CurrentDate) - 14, 1) to dateSerial(year(CurrentDate) , month(CurrentDate)-12, day(currentdate) - 1)] or
{Table.Field} in [dateSerial(year(CurrentDate), month(CurrentDate) - 2, day(currentdate) -1) to dateSerial(year(CurrentDate), month(CurrentDate), day(currentdate) - 1)])

However, this only gives you the results you describe if run on the first day of the month (i.e. last month and the month before. )
({Table.Field} in dateadd("ww",-8,currentdate) to currentdate or
({Table.Field} in date(year(dateadd("yyyy",-1,currentdate)),month(dateadd("m",-1,currentdate)),1) to dateadd("d",-1,date(year(dateadd("yyyy",-1,currentdate)),month(currentdate),1))
chris_desboroughAuthor Commented:
Tried both examples accepted and both worked.  I'll keep both of these in my bag of tricks.  Thanks.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now