Calc Help: Date to Color

[Rich S]

Hi, it's been a while! Miss me? Nahhhhh, I didn't think so.

Here's the problem: I have a field (CurrentDate, which is formulated as[Get(CurrentDate)]) that reflects today's date (08.24.2006). There's another field, DueDate (a Date field), that has a due date of 08.30.2006. With a calculation of DueDate-CurrentDate, a black "6" will appear in the DaysToGo field. Conversely, if I put 08.10.2006 in the DueDate Field, the result is "-14" since I passed the due date by 14 days. Cool.

Now, what I want to happen is that if there are "x" days left before the due date, the DaysToGo field will reflect the number of those days in black; if time slips away so the DueDate has passed, I want the DaysToGo field's number to turn red.

To throw another wrench in the works, I'd also like to use the Abs command so there's no minus sign before the number in the DaysToGo field if the DueDate has passed.

As always, thanks in advance for solving this major brain hernia for me!

Ciao,

Rich

[agtjazz]

See if this will work for you. I had to add another calculated field called display_Daystogo (but maybe you can test this method and eliminate the extra field). I used TextColor and a if statement

display_Daystogo=

If ((days to go>0);TextColor(Abs(days to go);RGB(0;0;0));TextColor(Abs(days to go);RGB (255;0;0)))

Hope that helps or sparks some ideas

Edited August 24, 2006 by Guest

[Rich S]

Worked like a charm! Thank you!!

[Rich S]

Okay, another brain hernia: I used the above calc for two fields like this:

SC_Months_Left =

If ((SC_Months_Left_calc>0);TextColor(Abs(SC_Months_Left_calc);RGB(0;0;0));TextColor(Abs(SC_Months_Left_calc);RGB (255;0;0)))

...and...

SC_Months_Left_calc =

Month (SC_Date_Due) - Month (Get(CurrentDate))

Now, the month count is correct, but when I enter an "overdue" date, the month's color doesn't turn red unless I left-click my mouse in the SC_Months_Left field; otherwise, it's black. So, how do I get it to turn red without having to click in its field?

[agtjazz]

Are both fields (SC_months_left_calc & SC_Months_Left) unstored?

[Rich S]

Yes, they're both unstored...and have to be else if I were to change the storage option of either field, FM balks at it (see the attached error message).

UPDATE: I found the problem--they're both number fields. However, the format of one of the fields was set to Decimal instead of General, so that's what hung things up; when it was changed to General, the calc worked.

Thanks for trying to help!

Edited August 30, 2006 by Guest
Solution found. :D