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 ...
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)
      • luke-s-1993's avatar
        luke-s-1993
        Copper Contributor
        May 29, 2024

        JKPieterse 

         

        the D6 at the begining is it so it will be:

         

        =AND(=1*INDEX(TEXTSPLIT(D6," "),1)<>"", D6-TODAY()>=60) - Turns green

         

        is that what you mean?

         

        thanks.

Resources