Conditional formatting entire row based on a "between" date condition.

[randall.stokes]

I am trying to build a roster to track when Soldiers are about to get out of the Army. There are three windows; 30 days until ETS, 60 days until ETS, and 90 days. In column D I have the date that they will get out of the Army and I wish to format the entire row a specific color based on the window in which they fall in. I know it is possiable, but for the life of me I can not figure it out, and I would like to do this with out VBA. Can you please help?


=INDIRECT("D"&ROW())<TODAY() p <> I've got this far for Soldiers that have already gotten out, but I can't make it work between two dates.

 

[jeffreybrown]

Hi Randall & Welcome to the forum,

In conditional formatting you can add three conditions and in this order top to bottom.

=D1<=TODAY()-90
=D1<=TODAY()-60
=D1<=TODAY()-30

Select your fill and you are ready to go

Better yet, if this list is big you could put =TODAY() in a cell, define the cell with a name (Date) and then instead of =D1<=TODAY-90 you can now have =D1<=Date-90

http://www.xldynamic.com/source/xld.CF.html#due

The benefit to this is only one cell updates to the date instead of many