How to conditional format in excel based on date ranges

Question

I am trying to format an excel document using conditional formatting

if cell is less than 1035 days from todays date make green if cell is between 1036 days and 1095 days date make orange if cell is more than 1095 from todays date make red

What formulas would I need?

Thanks

Answer 1

Say your dates start in B1 and go to B100.

Select B1, then select conditional formatting - New Rule - Use a formula to determine which cells to format.

For the highlight green example formula, use:

=DATEDIF($B1,NOW(),"d")<1035

Hit the format... button and fill to corresponding color.

Hit "OK"

Then in the manage rules menu, in the Applies To column, enter the range of cells you want that to apply to. i.e. =$B$1:$B$100

For the highlight orange example formula, use:

=AND(DATEDIF($B1,NOW(),"d")>1035,DATEDIF($B1,NOW(),"d")<1095)

For highlight red example formula, use:

=DATEDIF($B1,NOW(),"d")>1095