I'm assuming Due_Date is a field of type Date, not text here.
Use conditional formatting to set two conditions, each with slightly different formulas.
Define the first condition as
( Due_Date - 30 ) < Get ( CurrentDate )
And specify your Yellow text or fill color.
Define the second condition as
( Due_Date - 10 ) < Get ( CurrentDate )
And specify your red color.
The conditional formats will evaluate and their formats will be appled from top to bottom in the list of conditional formats, so make sure the "red" condition is listed below the "yellow".
THANK YOU yet again PhilModJunk. I couldn't think of the right way to put in the condition.
I hope you never get tired of helping me and all the others!
Stop the merry go round. I seem to have just run into a problem. On my form I used your formula in the conditional formatting this way:
( Due_Date - 60 ) < Get ( CurrentDate) field fill color = "green"
( Due_Date - 30 ) < Get ( CurrentDate) field fill color = "yellow"
( Due_Date - 10 ) < Get ( CurrentDate) field fill color = "red"
However, my due date is Aug-31-2011. by my calculations this over 200 days away from today (Feb. 7, 2011) and yet the field is turning "green"
I tried changing the values and for some reason it works and keeps the field white if I change the value more than 30 and upto 52. But as soon as I change it to:
( Due_Date - 53 ) < Get ( CurrentDate) it changes the field to green. Can you help me figure this one out?
What type is Due_Date? Are you sure that it's of type date and not of type text?
Yes, the fields all say "Date". Should I change them to "TimeStamp"?
I'm curious to see what happens after midnight EST (my time zone), if it makes a difference to the formula.
I did format the date to "Custom" so it shows "Aug-31-2011". Could this be a problem?
The layout based data formatting does not change the value stored in the field internally so it is not a factor here.
This worked for me: http://www.4shared.com/file/Pjrqv9Yn/DeadlineConditionalFormats.html
Download that Demo file and compare it to yours. I used fill colors but text colors would work the same.
Well, this is quite embarrassing. It seems when I copied the first field and changed the field it was getting the information from I forgot to check if the conditional statement had been changed as well.
So yes, you are right it did work, but next time I will check if I have changed the conditional statement as well. LOL