Conditional Formatting/TextColor and Date Fields

[bdam]

Just wondering if I have my understanding of how conditional formatting using TextColor works in regards to dates correct or not.

What I want to do is be able to format the color of a date (red if greater then due date). That said, I also want to make use of FM's date format in layouts. For instance, in some places I need to just show day/month and not year.

Can I have both and still only use one calculation field? It seems that if my calculation result is set to text I have the color but not the ability to format the date in the layout. If it is set to calculate a date I have the layout format options but the color doesn't show.

Thanks,

Bryan

[-Queue-]

TextColor does not apply to dates, as far as I am aware. However, you can create a text calculation using the date and format the date within it using Month( ), Day( ), Year( ), and MonthName( ) functions, as desired.

[bdam]

Thanks Queue.

The reason I want/need to calculate an actual date is so that I can use it in further calculations. In my case I'm using these calculated dates to further (re)calculate due dates. So I need the date to be in a form that I can do date comparison and addition. However I also want to format it differently on different layouts. In one place I want the year and in another place I dont but in both places I want it red if it is late (based on another calculated date).

As I understand it now I would need two fields for this, one for each formatting.

Thanks,

Bryan

[-Queue-]

Yes you would, unless you were able to shrink the size of the field on one layout just enough to prevent the year portion from being seen. If you use, for example, Right( "0" & Month(datefield); 2 ) & "/" & Right( "0" & Day(datefield); 2 ) & "/" & Year(datefield), and so force the length of month and day to always be two, then you may be able to set the field dimensions large enough to always show the first 5 characters and hide the final "/" and the year following.

It may be necessary to use a monospaced font to achieve this, but I am not certain.

You could also have the calculation return a different formatting based on layout name. Then only one extra field would be required for as many formats as you like, as long as each layout only requires a single format.

[sabino]

If I may, I would like to suggest another approach; one which I have used successfully for a different purpose but still to bring focus & strong emphasis to certain critical data values.

Leave your fields as date-fields, so they can be formatted as such and can still also be used in date-calculations.

Rather than TextColor, have the "background" change color based on some calculation (date) result. so if for example, a payment were 31 days late the background would be yellow, if 60 days late it would be red.

I set it up this way:

-create g_BkgrndClrs

a global container with 2 (or more) repetitions. past into each Rep a different color to use. this container will be used as a de facto library resource which you can reference for other fields too.

- create Pymt_Bkgrnd

an unstored calculation of expression

=Case(DatePaid-DateDue>60;GetRepetition(gBgndClrs;1);DatePaid-DateDue>30;GetRepetition(gBgndClrs;2) ;GetRepetition(gBgndClrs;3))

- format DatePaid field in any Date formatting you wish, but . . . choose field to be transparent **no color**. note size of field

- place Pymt_Bkgrnd on layout, then position DatePaid field on layout stacked on top of same sized Pymt_Bkgrnd

- the field will now change color based on date entered in Payment date-field