Calculating Date Ranges

Posted on 2010-01-04
Last Modified: 2012-05-08

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)])
Question by:chris_desborough
    LVL 34

    Expert Comment

    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.

    LVL 13

    Expert Comment

    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}]
    LVL 13

    Expert Comment

    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])
    LVL 13

    Accepted Solution

    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. )
    LVL 16

    Assisted Solution

    ({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))
    LVL 1

    Author Closing Comment

    Tried both examples accepted and both worked.  I'll keep both of these in my bag of tricks.  Thanks.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now