Formula help

Copper Contributor

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.



4 Replies
Keep a single data type in a cell, if comments are needed, add a comments column.
I would but the person I'm doing this for needs to add what level certificate they have on with the date.

Try replacing the D6 in your formula with this: =1*INDEX(TEXTSPLIT(D6," "),1)

@Jan Karel Pieterse 


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?