within 2wks of currentdate

[ginkoba]

How do I write a function to highlight when it's within 2wks of currentdate. This is what I have currently and it is taking everything after the 14 days. I want everything from 0-14 not after the 14 days

if currentfieldvalue>=dateadd('d',14,currentdate)
then crYellow
ELSE
crNoColor

 

[MCuthill]

ginkoba,

I'm thinking you likely just need add an upper bound to your conditional formatting. I think the following should work:

IF currentfieldvalue >= dateadd('d',14,currentdate) AND currentfieldvalue < CurrentDate
THEN crYellow
ELSE
crNoColor

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

oops.

You want 0 to 14.... change the >= to <= ("greater than or equal to" TO "less than or equal to")

I was thinking you were looking for "14 days ago to current" in my original reply. My apologies.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[ginkoba]

Thanks MCuthill. So if current date is 6/24/13 then everything between now and 7/8/13 should highlight. I'm not seeing that.

 

[MCuthill]

ginkoba,

Only question I have surrounds "currentfieldvalue". In general terms, I have always seen the field name itself used in formula's and am unfamiliar with this expression. Assuming you have some Date or DateTime field, the above should work. If you change "currentfieldvalue" to the actual field name (in your original formula/post... ignore my first reply entirely, as I had misread your questions) do you obtain the sought results?

Your formula should be as you originally posted, but with the >= switched to <=. My apologies for the confusion caused by my first reply.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[ginkoba]

Mike thanks again. Here's what worked. I transposed the previous answer from you since I'm looking for 0-14.

IF currentfieldvalue > CurrentDate AND currentfieldvalue <= dateadd('d',14,currentdate)
THEN crYellow
ELSE
crNoColor

 

[MCuthill]

Ah indeed. I had pondered that as well along the way (just in case there are dates which trigger a negative result).

Glad you were able to piece my ramblings together and come up with something provided what you are seeking. Here's hoping I am more on the ball tomorrow, as my mind seems a bit scattered today (I blame it being Monday).

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."