Date range calc & conditional formatting...

[Protour Travel]

You folks will soon discover that I'm probably dumb as a rock and not nearly as useful. With that out of the way, here goes...

I have a checkbox set with such items as "Drain Fresh Water, Drain Water Heater, Open Sink Valves".

I also have a date field.

What I would like to do is conditionally format the checkbox field to have a red background if the date field falls between Nov-1 of the current year and Mar-31 of the next.

Thanks for the welcome and as before, thanks in advance for your assistance.

[comment]

Try:

Let (

y = Year ( Get (CurrentDate) )

;

Date ( 11 ; 1 ; y ) ≤ Datefield

and

Datefield ≤ Date ( 3 ; 31 ; y + 1 )

)

[Protour Travel]

Thanks for the quick response.

I tried formatting both the date field and the checkbox field with this calculation to no avail.

I formatted the checkbox field using Options | Calculated Field.

[comment]

I formatted the checkbox field using Options | Calculated Field.

I am not sure what you mean by that. Conditional formatting is done on the layout: select the field, then Format > Conditional….

[Protour Travel]

You are right and the more I think about this I realize that it may not be possible to do.

Basically, if the date in the date field falls between Nov-30 & Mar-31 (winter months), I want the checkbox field to conditionally format with a red background as a reminder to perform these chores. Once an X is entered in the checkbox field, the red background would go away. In other words, the date range mentioned is the only time the checkbox items come into play. See attachment (I hope).

Hope this clears things up a little.

[comment]

I don't see why this wouldn't be possible. However, your first request is different from the current one: originally, you wanted red only if the date is in the winter months of the current year (including the first 3 months of the next year); now you seem to want red if the date is in the winter months of any year.

[Lee Smith]

Does this help?

I modified the file so that the date will also change colors

PTT.fp7.zip

Edited August 27, 2010 by Guest

[comment]

I don't think you can use a wild card that way.

[Lee Smith]

I wasn't sure about that, but it works on the example?

[comment]

Does it? What do you get when the date is January 1?

[Protour Travel]

Lee,

It works perfectly. Thank you so much!

Maybe one of these days I'll get FMP figured out.

[Lee Smith]

Shoot, I thought I had discovered a undocumented feature.

:

[Lee Smith]

As usual, comment is correct.

If you use the Let Calculation he provided earlier in your date field, it works perfect.

Lee