Forum Discussion

luke-s-1993's avatar
luke-s-1993
Copper Contributor
May 28, 2024

Formula help

Hi, I am having a problem where I'm using a formula to change the colour of a cell based on a date:

 

=AND(D6<>"", D6-TODAY()>=60) - Turns green

=AND(D6<>"", D6-TODAY()>=1, D6-TODAY()<60) - Turns orange

=AND(D6<>"", D6-TODAY()<=0) - Turns red.

 

the problem I'm having is that some of my cells will need text at the end of the dates but when I add for example 28/05/24 Approved, instead of turning red because of the date it's showing as white as if the formula is not working.

 

Any help would be greatful.

 

Thanks.

excel
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    May 28, 2024
    Keep a single data type in a cell, if comments are needed, add a comments column.
    • luke-s-1993's avatar
      luke-s-1993
      Copper Contributor
      May 28, 2024
      I would but the person I'm doing this for needs to add what level certificate they have on with the date.

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        May 28, 2024
        Try replacing the D6 in your formula with this: =1*INDEX(TEXTSPLIT(D6," "),1)

Resources