I would like to set some cells that contain a date to change their font, color, font styles, etc… when the date +6 months has been reached.
Example:
If I set the date in the cell to 8/16/12, then in 6 months from now, I want the date to change to look like 8/16/12 or similar.
Any advice?
Thanks in advance.
You can do this with conditional formatting.
In the target cell choose Format/Conditional Formatting. Set the condition as “Formula is” and make the formula TODAY()>41322 where the number 41322 is the date 6 months from now expressed as a number. Create a new style as the cell style with the properties you want (e.g. bold, italics). When the formula evaluates true (6 months from now) the new format will be displayed.
You can get the number corresponding to the future date by typing the date in a cell then changing the format of the cell to Number.
I actually just used two cells. One that used =today() in A1 and another, B1 (with white lettering), that used =A1-180. After this I just used the conditional formatting to format the cell if the date was less than the value in B1.
If you need an automatic formula:
CellReference < EOMONTH(TODAY();-6)
I actually just used two cells. One that used =today() in A1 and another, B1 (with white lettering), that used =A1-180. After this I just used the conditional formatting to format the cell if the date was less than the value in B1.
With conditional formatting (as previously mentioned) I think you could also do something like:
If... "Cell value is" - "greater than" - "A1+180"
Where A1 is a reference to the current cell (whatever that might be).