Ok guys Basically i have a spreadsheet using conditional formatting, and have it set so it changes color based on range of dates ie( if its beyond 365 days the cell turns red, if its under a year the cell turns green. Ive pasted one of the rules below in case i didnt explain clearly.




=AND(F4>=TODAY()-305,F4<=TODAY()) <-- and set color to green


What I want to do is have the Cell auto fill with Text " Up to Date" or Out of Date based on the date entered in source cell. Any help would be greatly appreciated.

Thanks

Have you tried using a nested IF statement?
=IF(A1>=TODAY()-305,"Up to Date","Past Due")

This is the exact Formula used in the Status Column of spreadsheet and it changes the color as i need based on date entry in source cell, but i also want it to autofill " Up to Date" at same time

=AND(F4>=TODAY()-305,F4<=TODAY())

is this possible?

You would have to enter an IF() formula in the cells additional to the Conditional Formatting you did...

so in the same cells you would enter formula:

=IF(AND(F4>=TODAY()-305,F4<=TODAY()),"Up to Date","Past Due")

Ok adding that as formula to cell itself worked however could you expand on how to create and statement to include if cell is blank? Thats way i dont get a False statement in there if no date is in other cell


Currently its either up to date or past due but if there is no date entered in source cell it defaults to past due

=IF(F4="","",IF(AND(F4>=TODAY()-305,F4<=TODAY()),"Up to Date","Past Due"))