Comparing Dates

[tobypsl]

Hi

Using CR8.5 I have teh following expression in the formula for a report field;

if isnull ({RJOBUD.Date1} and {RJOBUD.Date3}and{RJOBUD.Date4}) then ('FIRST') else
if CurrentDate-({RJOBUD.Date1}or{RJOBUD.Date3}or{RJOBUD.Date4}) > 7 then ('REPEAT') else {RJOBUD.Date1}

basically I have 3 dates fields, the output of the formula field is dependant on the contents of the date fields. There are 3 possible values for the formula field based on the following conditions;

1. If all 3 are empty then I would like the expression to be FIRST.
2. At times some of the fields will contain dates if ALL of the dates present are greater than 7 days old I would like the field expression to be REPEAT.
3. If one of the dates present is not more than 7 days old I would like the expression to equal the most current date.

the first half of the formula is fine but the second half is wrong - the last bit (ie. slecting the most current date) I wouldn't know how to do anyway ....

as always - ideas welcome,

thanks

 

[synapsevampire]

Try:

if isnull ({RJOBUD.Date1})
and
isnull({RJOBUD.Date3})
and
isnull({RJOBUD.Date4}) then ('FIRST')
else
if CurrentDate-({RJOBUD.Date1}) > 7
and
CurrentDate-({RJOBUD.Date3}) > 7
and
CurrentDate-({RJOBUD.Date4}) > 7 then ('REPEAT')
else
if {RJOBUD.Date1} > {RJOBUD.Date3}
and
if {RJOBUD.Date1} > {RJOBUD.Date4} then
{RJOBUD.Date1}
else
if {RJOBUD.Date3} > {RJOBUD.Date1}
and
if {RJOBUD.Date3} > {RJOBUD.Date4} then
{RJOBUD.Date3}
else
if {RJOBUD.Date4} > {RJOBUD.Date1}
and
if {RJOBUD.Date4} > {RJOBUD.Date3} then
{RJOBUD.Date4}
else
cdate(1920,1,1)

The reason why I added in the last else is because you haven't allowed for all posiibilities.

What if all dates are within the last 7 days and they are all the same?

Anyway, this should help you understand what needs to be done.

-k

 

[tobypsl]

I just replaced teh formula with the following;

if isnull({RJOBUD.Date1}) and isnull({RJOBUD.Date3}) and isnull({RJOBUD.Date4}) then 'FIRST' else
if CurrentDate-({RJOBUD.Date1}) > 7 and CurrentDate-({RJOBUD.Date3}) > 7 and CurrentDate-({RJOBUD.Date4}) > 7 then'REPEAT'
else ' '

it places a blank ' ' in the field rather than the most current date if the criteria meet case 3 above ... would still be interested to know how to put most current date in instead of a blank ..

 

[tobypsl]

simultaneous posting ... thanks synapsevampire, will give that a go ...