conditional formatting based on number of days between two dates

[fingermouse]

[TABLE="class: grid, width: 500, align: right"]
<tbody>[TR]
[TD]01/08/2018
[/TD]
[TD]27/08/2018
[/TD]
[TD]If the amount of days between A1 and B1 is less than or equal to 26, make B1 green in colour
[/TD]
[/TR]
[TR]
[TD]01/08/2018
[/TD]
[TD]28/08/2018
[/TD]
[TD]If the amount of days between A2 and B2 is greater than 26 but less than 31, make B2 amber in colour
[/TD]
[/TR]
[TR]
[TD]01/08/2018
[/TD]
[TD]01/09/2018
[/TD]
[TD]If the amount of days between A3 and B3 is greater than or equal to 31, make B3 red in colour
[/TD]
[/TR]
</tbody>[/TABLE]
Hi,

I need an excel formula which will change the colour of a cell based on the number of days between two dates.

I presume I would have to enter this formula in the 'new formatting rule' dialogue box in the conditional formatting menu.

(sorry,I cant seem to be able to add the table after the text! Also, the date format is DD/MM/YYYY.)

Any help much appreciated!

thanks, Cal.

 

[wideboydixon]

Steps:

1. Highlight the cells in Column B that you want to format
2. Choose Conditional Formatting -> New Rule from the menu
3. Select the option to "Use a formula to determine which cells to format"
4. In the formula box enter this: =($B1-$A1)<=26
5. Set the format to color the cell green
6. Click OK
7. Choose Conditional Formatting -> Manage Rules from the menu
8. Check the box that says "Stop If True"

Now repeat steps 2 to 6 with these formulas and different formats:

=($B1-$A1)<31
=($B1-$A1)>=31

WBD

 

[Special-K99]

Untested

Select the range to highlight

Conditional Formatting
New Rule
Use a formula to determine...

=(B$1-A$1)>=31 format as red
=(B$1-A$1)<31 format as amber
=(B$1-A$1)<=26 format as green

Format as required

 

[fingermouse]

Brilliant folks, many thanks!