conditional formatting based on date in field

[mdnuts]

I've got a number of date fields but focusing on one where the text box is named "IRPExpDate". I want to display conditional formatting based on the date in the box. The target being highlight when it approaches 60 days from 1 year past the date in the text box, then 30 days and finally when 1 year has been met or gone by. I have the following but it's not highlighting correctly. it just shows the 305-334 range even if the date is greater than a year old.

Value >= [IRPExpDate] + 365
Value is Between [IRPExpDate]+335 and [IRPExpDate]+364
Value is Between [IRPExpDate]+305 and [IRPExpDate]+334

any idea where I'm going awry? I've tried alternating the order of the list which didn't change anything.

 

[June7]

Why does the first criteria use BETWEEN?

Need to test if IRPExpDate falls in range of some future date - that means calculating from current date.

Perhaps:
>= Date() + 365
>= Date() + 335
>= Date() + 305

 

[mdnuts]

Why does the first criteria use BETWEEN?

The other criteria make less sense.

Perhaps:
>= Date() + 365
Between Date() + 335 and Date() + 364
Between Date() + 305 And Date() + 334

i had handjammed it - rather badly. I updated the post and included a screengrab.

 

[June7]

I edited my previous post after you read it. Might review again.

 

[mdnuts]

Why does the first criteria use BETWEEN?

Need to test if IRPExpDate falls in range of some future date - that means calculating from current date.

Perhaps:
>= Date() + 365
>= Date() + 335
>= Date() + 305

oh for crying out loud.

Date()>=[IRPExpDate]+365

man where was my mind on that one.

 

[GPGeorge]

oh for crying out loud.

Date()>=[IRPExpDate]+365

man where was my mind on that one.

Are you concerned about this working over leap years, which have 366 days, not 365 days?

If so, a more accurate method will be to use the DateAdd() function instead of simple math based on the 365 day criteria.

>= DateAdd("yyyy", 1, Date())
>= DateAdd("yyyy", 1, Date()) - 30
>= DateAdd("yyyy", 1, Date()) - 60