Date Calculations

[skynight]

Two problems I would really appreciate help with:

I have lots of date fields, some are before today, some after. I want to have a field that will calculate and display the next date after today, and another field that will calculate and display the last date in the list prior to today. The two closest dates to today's date in two separate fields.

A second problem is that I have two start dates for a project. I have a calculation that looks at these dates and works out a Due Date 90 days ahead for each of them. I only display the later date. The problem is that if those start dates have not been entered (the fields are empty) the calculation is still done and the Due Date displays a 7 or 8 digit number. I want the Due Date field to remain blank if the start date(s) have not been entered.

Help please!

Edited September 24, 2006 by Guest

[Genx]

... We need to know more about your data structure, from the way you make it sound, all your fields are in the same table, this will make your life a lot harder.

For the second one, just add...

If(not IsEmpty(your datefield) or not IsEmpty(your second datefield) ; Your main calc)

[skynight]

On the first problem, yes the fields are all in the same table. How does that change things? It should be a simple fix, telling the calculation to display the next date after the current date? Or the last date before the current date?

In the second problem, I tried putting the suggested fix into the calculation I already had, but it then tells me that functions are needed, or wrong, and it will not work.

Here are the calculations, could you please look at them and maybe try them to see where the problem is?

My calculation:

If (Eval Completed CFIT;Eval Due CFIT-Eval Completed CFIT;TextColor (Eval Due CFIT-Get(CurrentDate);RGB(0;0;255)) )

Suggested fix:

If(not IsEmpty(your datefield) or not IsEmpty(your second datefield) ; Your main calc)

Combined:

If(not IsEmpty(EvalCompCFIT) or not IsEmpty(ReEvalCompCFIT) ; Eval Completed CFIT;Eval Due CFIT-Eval Completed CFIT;TextColor (Eval Due CFIT-Get(CurrentDate);RGB(0;0;255)) )

Thanks for the assist..

Edited September 25, 2006 by Guest

[Genx]

... no your main calc should be "If (Eval Completed CFIT;Eval Due CFIT-Eval Completed CFIT;TextColor (Eval Due CFIT-Get(CurrentDate);RGB (0;0;255)) )" not just "Eval Completed CFIT;Eval Due CFIT-Eval Completed CFIT;TextColor (Eval Due CFIT-Get(CurrentDate);RGB (0;0;255)) "

As for your first problem, how many fields in total because i'm a bit confused.

[skynight]

I tried the suggestion, and the calculation was accepted, but the result still showed the number of days remaining from year 0001. I want the field to be blank if the two fields that are the basis for the calc are empty. Any further tweaks I can try?

This is the calc:

If(not IsEmpty(${2ndAnnual completeSafety}) or not IsEmpty(${2ndAnnualDue Safety}) ;If (${2ndAnnual completeSafety};${2ndAnnualDue Safety}-${2ndAnnual completeSafety};TextColor (${2ndAnnualDue Safety}-Get(CurrentDate);RGB(0;0;255))))

Thanks, Glen.