• Status: Solved
• Priority: Medium
• Security: Public
• Views: 768

# Calculating Date Ranges

Hi,

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)])
0
chris_desborough
2 Solutions

Commented:
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.

James
0

Commented:
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.

@Currentstart
dim amonthago as date
dim currentstart as date
currentstart = datevalue(year(currentstart), month(currentstart), 01)
formula=currentstart

@currentend
dim amonthago as date
dim currentend as date
currentend = datevalue(year(currentend), month(currentend), 01)
formula=currentend

@lastyearstart
dim amonthago as date

dim lastyrstart as date

lastyrstart = datevalue(year(amonthago), month(amonthago), 01)
formula=lastyrstart

@lastyrend
dim amonthago as date
dim lastyrend as date
lastyrend = datevalue(year(lastyrend), month(lastyrend), 01)
formula=lastyrend

({Table.Field} in [{@currentstart} to {@currentend}] or
{Table.Field} in [{@lastyrstart} to {lastyrend}]
0

Commented:
And here's the selection formula, ready to go.

currentstart := datevalue(year(currentstart), month(currentstart), 01);
currentend := datevalue(year(currentend), month(currentend), 01);
datevar lastyrstart := datevalue(year(amonthago), month(amonthago), 01);
lastyrend := datevalue(year(lastyrend), month(lastyrend), 01);

({Table.Field}  in [currentstart to currentend]
or {Table.Field}  in [lastyrstart to lastyrend])
0

Commented:
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. )
0

Commented:
(
({Table.Field} in dateadd("ww",-8,currentdate) to currentdate or
)
0

Author Commented:
Tried both examples accepted and both worked.  I'll keep both of these in my bag of tricks.  Thanks.
0
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.