Date compare formula/Compare a date with “today”

[mark12010]

Lets say I have a field “Treatment plan review date” and I want compare that date with “Today”. I want the report to show:
If “Treatment plan review date” is five days away from today to output “treatment plan due in 5 days” or if “Treatment plan review date” is four days away from today to output “ treatment plan due in 4 days” down to treatment plan date = today then output “Treatment plan due today”. Finally if “Treatment plan review date” is past today output “Treatment plan Overdue” .

 

[fisheromacse]

i think an if-then like below might do it:

if DateDiff("d",{table.RevDate},currentdate) = 5 then "Tx Plan due in 5 days"
else
if DateDiff("d",{table.RevDate},currentdate) = 4 then "Tx Plan due in 4 days"
else
if DateDiff("d",{table.RevDate},currentdate) = 3 then "Tx Plan due in 3 days"
else
if DateDiff("d",{table.RevDate},currentdate) = 2 then "Tx Plan due in 2 days"
else
if DateDiff("d",{table.RevDate},currentdate) = 1 then "Tx Plan due in 1 days"
else
if DateDiff("d",{table.RevDate},currentdate) = 0 then "Tx Plan due today"
else
if DateDiff("d",{table.RevDate},currentdate) < 0 then "Tx Plan overdue"

 

[Turkbear]

Hi,
If that field is actually a Date or DateTime field and not some other format, then try something like this, using Crystal's special fields ( CurrentDate) :

If ({TreatmentplanReviewDate} > CurrentDate) ) 
then
"Treatment Plan Overdue"
Else
If ({TreatmentplanReviewDate} - CurrentDate) )
Then
"Treatment Plan Due Today"
Else
"Treatment Plan Due in " + ToText({TreatmentplanReviewDate} = CurrentDate) ) + " Days"

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
Please Revise my posting to:

If ({TreatmentplanReviewDate} > CurrentDate) ) 
then
"Treatment Plan Overdue"
Else
If ({TreatmentplanReviewDate} - CurrentDate) )
Then
"Treatment Plan Due Today"
Else
"Treatment Plan Due in " + ToText({TreatmentplanReviewDate} - CurrentDate) ) + " Days"

Cut and Paste sometimes leads to missing a change that was needed.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Turkbear, I think you need an equal sign for "Due Today" condition, though.

In fisheromacse's post, I think currentdate and the date field need to be in the reverse positions.

-LB

 

[fisheromacse]

i think i have them in the correct order.
for due dates prior to the current date, it returns a positive #, for those after the current date, a negative #.

but i do miss the obvious at times.

 

[lbass]

If the plan is "due in five days" then the due date would be after the currentdate though.

-LB

 

[Turkbear]

Hi,
Yep..When I replaced the = with - in the last condition, I also inadvertently replaced the correct = sign in the second one.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[fisheromacse]

lb, correct as usual.