0

I want to format a cell which is greater than 365 value. The problem is I put the formula of datedif(a1,b1,"d")&" "&"Days" and the result in the cell is 390. I want this to be red because it is greater than 365. I can format it if I only use datedif(a1,b1,"d") without & Functions; please help enter image description here

1 Answer 1

1

What I done to make this work:

enter image description here

  • Select C2:C4:

  • Use conditional formatting formula:

    =DATEDIF($A2,$B2,"d")>365
    

If you need this to be equal or higher then 365, then just change to >=.


A much better alternative (IMHO) is to keep your original formula in C2:C4 without the "days" part (=DATEDIF($A2,$B2,"d"), but change the formatting of these cells to a custom format:

0 "days"

Now you can simply add a conditional formatting of values higher then 365, AND you can do calculations with your cells since they simply still numbers looking like text instead of actual text.

enter image description here

See how the allignment is different too!

3
  • Thank you IMHO works like a charm
    – Ali Ahmed
    Dec 3, 2020 at 12:00
  • @AlySheikh, IMHO = In My Honest Oppinion, meaning I feel that the 2nd option is better =), but that asside I'm glad it worked. Feel free to accept the answer by clicking the checkmark to the left of the answer.
    – JvdV
    Dec 3, 2020 at 12:02
  • haha =)) i did not know that
    – Ali Ahmed
    Dec 3, 2020 at 12:26

You must log in to answer this question.

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