0

I need to make an background for cell which contains date, if the range between dates is more than 100, then it should be colored as green if not the red. I used New formatting rule with this formula: =IF(DAYS(F1;E1)>=100)

so if date range is more than 100 days, then it must be green other wise red. How can i do it?

enter image description here

3
  • Why do you refer to columns E:F is your data is in columns A:B??? Dec 27, 2016 at 11:38
  • i moved them and forgot to change column, but still it doesn't work
    – Viktor
    Dec 27, 2016 at 11:48
  • Try evaluate results of your coloring condition in column C. This will show you whether things are set up correctly. After success, move the condition into conditional formatting dialog.
    – miroxlav
    Dec 27, 2016 at 13:05

1 Answer 1

1

I noticed that you don't have start and end dates in different columns (they seem to be mixed), so the function would not work.

Also when it comes to conditional formatting you should the answer should always be =True or =False, so using AND as the main function is the best option. Here is the function you needed.

=AND(IF($A1>$B1;DATEDIF($B1;$A1;"D");DATEDIF($A1;$B1;"D"))>100)

Mike

2
  • Thank You it worked, but how do i set other color if answer is false?
    – Viktor
    Dec 27, 2016 at 15:53
  • Just add another rule where the ending is <=100 and use the red color for that. Dec 27, 2016 at 16:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .