Comparing Dates in a Range

[seller]

I am not sure where to even start with this so here is the situation...
I have to return the corresponding quarter information depending on a user entered start date parameter. So if a user picks a start date of 3-13-2002 I would like to return a value of Quarter 3.

Quarter 1 2002-10-08
Quarter 2 2002-01-03
Quarter 3 2002-03-12
Quarter 4 2002-05-24

I would like to use this a part of the record selection process and not have to ask the user to specify an end date range, just the start date.

Is this possible?

TIA

Dave

 

[dgillz]

Use two formulas, startdate and enddate:

@startdate=
If Month({?startdate}) in 1 to 3 then Date(year({startdate}),1,1) else
If Month({?startdate}) in 4 to 6 then Date(year({startdate}),4,1) else
If Month({?startdate}) in 7 to 9 then Date(year({startdate}),7,1) else
If Month({?startdate}) in 1 to 3 then Date(year({startdate}),10,1)

The enddate formula is almost the same except it of course would return a date of 3/31/YYYY, 6/30/YYYY, 9/30/YYYY or 12/31/YYYY.

Then use both of these date formulas in your record selection formula, or you could put the if-then else sequence in your record selection formula.






Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[chapst]

If you just need to return the relevant quarter number for the date entered try this formula:

[2,2,2,3,3,3,4,4,4,1,1,1][Month ({?startdate})].

If you need to specify the word 'quarter' before the number, your array would be:

["Quarter 2","Quarter 2","Quarter 2","Quarter 3",...etc.

Make the formula equal to the parameter field in your record selection.

hth

chapst