CODEWITHSUNDEEP
excel
Si8
Si8

Reputation: 9235

Use the date difference and change background color based on result

I am trying to do some calculation with date and display an output based on that.

For example:

Cell [A1] has the value 10/20/2013

Cell [A2] should display today's date and also have the following calculation:

If (DATEDIF(A1 and TODAY) is <= 30 days) {
     A2 displays today's date
     A2 text color is WHITE
     A2 cell Background should be RED
}
If (DATEDIF(A1 and TODAY) is > 30 days) {
     A2 displays today's date
     A2 text color is GREEN
     A2 cell background should be WHITE
}

I tried the following:

IF (DATEDIF(A1, TODAY(), "d"), A2.Interior.ColorIndex = 36, A2.Interior.ColorIndex = 31)

I am thinking I am missing something here... Can someone please assist me in completing the script.

enter image description here

Upvotes: 1

Views: 8198

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Use this formula in cell A2

=IF(DATEDIF(TODAY(), A1,"d")<=30,TODAY(),IF(DATEDIF(TODAY(), A1,"d")>30,TODAY(),""))

Or you can also use this formula in Cell A2

=IF(OR(DATEDIF(TODAY(),A1,"d")<=30,DATEDIF(TODAY(),A1,"d")>30),TODAY(),"")

and then in the Conditional Formatting | Use a formula to determine which cells to format

use the formula

=DATEDIF(TODAY(), A1,"d")<=30

and

=DATEDIF(TODAY(), A1,"d")>30

And set the relevant format. This is how your conditional formatting will look once you set the rules.

enter image description here

And this is how your A2 will behave

enter image description here

Upvotes: 1

Related Questions