Attempting to use dates to determine text color.

[Ben Feingold]

This is the formula I'm using and it doesn't appear to be working. The value "Q2_EndDate" is a date field and I think this might be the reason. How do I make a date field into a value like the one "Get(CurrentDate)" returns?

The logic here is:

If today's date is before the last day of the quarter, set the calculation text to gray. Otherwise, just do the calculation.

Case(

Get(CurrentDate) < Q2_EndDate;

TextColor (calculation ; RGB(204;204;204));

calculation

)

[comment]

"doesn't appear to be working" is not a good description of a problem. But the most often cause with these things is forgetting to make the calculation unstored.

[Lee Smith]

If I'm understanding you correctly, you are trying to change the color of the Date in a Date Field.

Unfortunately, Text Styles will only work with Text Fields. You can do this in version 9, using Conditional Formatting, but this type of formatting can not be seen in earlier versions if shared with say 8.5

You will need to use a flag field instead. There has been lots of topics in the past about how to set a flag based on dates, etc.

If this is a text field, try something like;

Case ( Q2_EndDate < Get ( CurrentDate ) ; TextColor ( text ; RGB ( 204 ; 204 ; 204 ) ); TextColor ( text ; RGB ( 0 ; 0 ; 0 ) ) )

or do a search for +Flag +Field should do it, let us know if you can't find what you are wanting.

Edited November 18, 2008 by Guest

[Ben Feingold]

Sorry for not being more descriptive:

Rather then turn gray if the current date (Get(CurrentDate)) is less then the date of the end of the quarter (Q2_EndDate), which it is, the text stays black. If I reverse the "if" so it reads "Get(CurrentDate) > Q2_EndDate" (did it just to see what would happen) it works; the text changes color.

This doesn't make sense to me as November 08 is certainly less then January 09.

[Ben Feingold]

I'm trying to change the color of a number calculation (total days in school divided by the total possible days) based on a predefined date field (the date the quarter ends) and the current date.

If the current date is BEFORE the end of the quarter, the result of calculation should be grayed out.

As I understand it, the command Get(CurrentDate) returns a number value based on how many days have passed since January 1, 0000.

I think the problem is that my End of Quarter Date is not entered in this fashion. What I need to know is how to convert a "normal" date (M/D/Y) to the same format as Get(CurrentDate).

At least I think that's what I need to know. :o

[comment]

If Q2_EndDate is a date field, or a calculation field with the result type set to Date, the comparison with Get(CurrentDate) should work as expected.

As I understand it, the command Get(CurrentDate) returns a number value based on how many days have passed since January 1, 0000.

No, the function returns a result of type Date. True, it can be very easily converted to the number of days elapsed since Jan 1, 0001 (not 0000), but that is not the initial result.

[Lee Smith]

Try this calculation

Let ( [

ND = Q2_EndDate - Get(CurrentDate) ] ;

Case (ND > 0; TextColor ( ND ; RGB ( 204 ; 204 ; 204) ); TextColor ( ND ; RGB ( 0 ; 0 ; 0 ) ) ) )

HTH

Lee

[Vaughan]

Make sure the calculation field is unstored.

[Ben Feingold]

Thank you for all your help everyone. It's always good to bounce problems off other people.

I figured it out. The problem wasn't with my calculations. The problem was in my relationships. (No, my wife and I are fine)

I was trying to pull data from an unrelated table. Once I corrected the relationship, I was able to get the desired result.