conditional formatting based on date in field (1 Viewer)

mdnuts

Registered User.
Local time
Yesterday, 20:38
Joined
May 28, 2014
Messages
128
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.

Code:
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    12.9 KB · Views: 271
Last edited:

June7

AWF VIP
Local time
Yesterday, 16:38
Joined
Mar 9, 2014
Messages
5,423
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
 
Last edited:

mdnuts

Registered User.
Local time
Yesterday, 20:38
Joined
May 28, 2014
Messages
128
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

AWF VIP
Local time
Yesterday, 16:38
Joined
Mar 9, 2014
Messages
5,423
I edited my previous post after you read it. Might review again.
 

mdnuts

Registered User.
Local time
Yesterday, 20:38
Joined
May 28, 2014
Messages
128
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.

Code:
Date()>=[IRPExpDate]+365

man where was my mind on that one.
 

GPGeorge

Grover Park George
Local time
Yesterday, 17:38
Joined
Nov 25, 2004
Messages
1,776
oh for crying out loud.

Code:
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
 

Users who are viewing this thread

Top Bottom