Formatting font colour for dates

[El Jagang]

I have a field called [Force Test] which is set up as a Date/Time. It represents the date where a test was taken. This test needs to be taken annually, so I'd like the date to appear in red if the taking of the test is past due, as well as having the date appear in green when there are 3 months or less before the date turns red.

The logic of the formula I want to write is:

If today's date is greater than 365 days after the date written in [Force Test], the font should be red, and if today's date is greater than 274 days after the date written in [Force Test], the font should be green.

I've tooled around using DateAdd: Value > DateAdd("d",365,[Force Test]) in order to make the font turn red, but that doesn't work.

I'm currently checking various sites to try to build an IF formula, but I don't know how I'd work in a DateAdd formula inside an IF. This goes far beyond what I've learned at school, which was really just the basics of Access.

Any help would be appreciated!

 

[ypma]

Just a thought have you considered using conditional formatting
Ypma

Further thought, as you only require two colours why not make the default font of the [Force Test] green and then only one conditional formatting would be require example < Date - 365 . If you have already solved this problem please let me know

Reread you question and realise above is not what is required . If you are using Conditional Formatting your second condition would be Value is between date()-365 and date() -274

hope this of of use

Ypma

 

[El Jagang]

Thanks for the reply.

I was trying to set this up using conditional formatting, although reading your answer made me realize I was trying to make it way more complicated than it needed to be. What you suggested worked like a charm! I appreciate the help, and so does my internship supervisor!