Maximum Date across several fields 1

[racskelly]

I need help writting a formula:


Each row has the following information


ref_no | this_date | that_date | the_other_date

i would like to calculate max_date on the same row (max date of this, that and the other) as well a flag that indicates if max_date >= today+1year

any ideas?

 

[MrBillSC]

Try a formula like this for the maximum date:
// formula (@MaxDate)
If this_date > that_date and
this_date > the_other_date then
this-date else
If that_date > the_other_date then
that_date else
the_other_date

The flag formula would be like:
// formula (@MaxDateFlag)
EvaluateAfter(@MaxDate);

If {@MaxDate} >= DateAdd("yyyy",1,Today) + 1 then "Y" else "N"

MrBill

 

[racskelly]

Thanks Mr Bill... I thought of that... but what if i have dozens of date fields to check.. is there an easier way?

 

[lbass]

//{@maxdate}:
maximum([date1,date2,date3,date4])

//{@flag}:
if {@maxdate} >= dateadd("yyyy",1,currentdate) then
"Alert"

-LB

 

[racskelly]

what if some of the dates are null... if so, maximum doesn't seem to work

 

[racskelly]

can i use
maximum([if(not(isNull(field1))),if(not(isNull(field2)))..etc?

 

[lbass]

Set up the conditionals date formulas separately, as in:

if isnull({date1}) then
date(0,0,0) else
{date1}

Then use the formulas in the maximum formula instead of the dates.

-LB