• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 768
  • 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])
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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