Date Range

[jtr9999]

I am trying to get a date to populate in a report if the currentdate is between a set of dates.

I tried to do something like, if the currentdate is >= March 15 and the currentdate is < June 14 then give me a certain date. I could not get this work. The result it gives me is always what is under else.

Is there a way I can do this?

Thanks
Jonathan

 

[lbass]

You should be using a record selection formula (report->selection formula->record) like this:

currentdate >= date(2008,3,15) and
currentdate < date(2008,6,14)

-LB

 

[jtr9999]

Can I not do it with a formula? I want to show if the currentdate falls with in, lets say, March 15 - June 14 a date will populate. if the currentdaye falls with in June 15 - Sept 13 a different date will populate and so on. The formula I tried is below. The "50 days after formulas" and the "1WkAfterQtr" formula uses the dateadd function.

if currentdate >= {@50 days after Q4} and currentdate < {@50 days after Q1} then
{@1WkAfterQtr1End}
else if currentdate >= {@50 days after Q1} and currentdate < {@50 days after Q2} then
{@1WkAfterQtr2End}
else if currentdate >= {@50 days after Q2} and currentdate < {@50 days after Q3} then
{@1WkAfterQtr3End}
else
{@1WkAfterQtr4End}

 

[lbass]

Sure, you can do that. If this formula isn't working correctly, you need to show the contents of the nested formulas so somebody can troubleshoot it.

-LB

 

[jtr9999]

I'm sure there is an easier way to do this but this is what I tried. The date is supposed to be one week after there quarter end. There quarter ends however changes from year to year. It always falls on the last friday of the month the quarter is in. There first, second, third, and fourth quarter months respectivly are July, October, January, and April. All my nested formulas seem to do what they are supposed to do. The formula in a previous post always returns the else. The nested formulas are listed below. The formula name is in bold. Thanks for any help

1WkAfterQtr1End = DateAdd ('ww', +1, {@Last Friday in July})
1WkAfterQtr2End = DateAdd ('ww', +1, {@Last Friday in October})
1WkAfterQtr3End = DateAdd ('ww', +1, {@Last Friday in January})
1WkAfterQtr4End = DateAdd ('ww', +1, {@Last Friday in April})

50 days after Q1 = DateAdd ('d', +50, {@Last Friday in July})
50 days after Q2 = DateAdd ('d', +50, {@Last Friday in October})
50 days after Q3 = DateAdd ('d', +50, {@Last Friday in January})
50 days after Q4 = DateAdd ('d', +50, {@Last Friday in April})

Last Friday in January
WhileReadingRecords;
Local DateVar DIn:= Date(year(currentdate),01,01);
Local NumberVar DOW:= 6;

DateVar DDow :=
if day(DIn) < 20
then DIn - DayOfWeek(DIn) + DOW
else DIn - DayOfWeek(DIn) - 7 + DOW;

if Month (DDow+35) = Month(DDow) then (DDow+35) else
if Month (DDow+28) = Month(DDow) then (DDow+28) else
if Month (DDow+21) = Month(DDow) then (DDow+21) else
if Month (DDow+14) = Month(DDow) then (DDow+14) else
if Month (DDow+ 7) = Month(DDow) then (DDow+ 7) else DDow

Last Friday in April
WhileReadingRecords;
Local DateVar DIn:= Date(year(currentdate),04,01);
Local NumberVar DOW:= 6;

DateVar DDow :=
if day(DIn) < 20
then DIn - DayOfWeek(DIn) + DOW
else DIn - DayOfWeek(DIn) - 7 + DOW;

if Month (DDow+35) = Month(DDow) then (DDow+35) else
if Month (DDow+28) = Month(DDow) then (DDow+28) else
if Month (DDow+21) = Month(DDow) then (DDow+21) else
if Month (DDow+14) = Month(DDow) then (DDow+14) else
if Month (DDow+ 7) = Month(DDow) then (DDow+ 7) else DDow

Last Friday in July
WhileReadingRecords;
Local DateVar DIn:= Date(year(currentdate),07,01);
Local NumberVar DOW:= 6;

DateVar DDow :=
if day(DIn) < 20
then DIn - DayOfWeek(DIn) + DOW
else DIn - DayOfWeek(DIn) - 7 + DOW;

if Month (DDow+35) = Month(DDow) then (DDow+35) else
if Month (DDow+28) = Month(DDow) then (DDow+28) else
if Month (DDow+21) = Month(DDow) then (DDow+21) else
if Month (DDow+14) = Month(DDow) then (DDow+14) else
if Month (DDow+ 7) = Month(DDow) then (DDow+ 7) else DDow

Last Friday in October
WhileReadingRecords;
Local DateVar DIn:= Date(year(currentdate),10,01);
Local NumberVar DOW:= 6;

DateVar DDow :=
if day(DIn) < 20
then DIn - DayOfWeek(DIn) + DOW
else DIn - DayOfWeek(DIn) - 7 + DOW;

if Month (DDow+35) = Month(DDow) then (DDow+35) else
if Month (DDow+28) = Month(DDow) then (DDow+28) else
if Month (DDow+21) = Month(DDow) then (DDow+21) else
if Month (DDow+14) = Month(DDow) then (DDow+14) else
if Month (DDow+ 7) = Month(DDow) then (DDow+ 7) else DDow

 

[lbass]

One issue is that for the clause:

else if currentdate >= {@50 days after Q2} and currentdate < {@50 days after Q3} then
{@1WkAfterQtr3End}

...you are saying:

if currentdate >= 50 days after October__, 2008 and currentdate < 50 days after January__, 2008

Somehow you have to build in the fiscal year. Maybe for the July and October DIns use: date(year(currentdate)-1,1,1) or use +1 for the other two quarters instead.

-LB

 

[jtr9999]

You're a genius. Thank You