Conditional formatting a date range

  • Thread starter Thread starter JC
  • Start date Start date
J

JC

If I have a column of dates that are manual entered what is the formula to
conditionally format them based on a date range of three months before the
current date to the current date and another three months after the current
date to the current date?
 
Assume the dates are in column A, starting with A1. Highlight all the
dates, with A1 as the active cell, and click on Format | Conditional
Formatting. In the dialogue box you should select Formula Is rather
than Cell Value Is and then enter this formula:

=AND(A1>=TODAY()-91,A1<=TODAY())

Click on the Format button and choose the format you want - you might
like to click on the Patterns tab (for background colour) and choose
bright yellow, for example. Then click OK and then Add to set up the
second condition, choose Formula Is again and enter this formula:

=AND(A1<=TODAY()+91,A1>TODAY())

Then click the format button, and maybe this time choose a bright
green background. Click OK twice to exit the dialogue box.

The formulae will automatically adjust to suit the other cells
highlighted, so you should see your effects straightaway.

Hope this helps.

Pete
 
Back
Top