Formula help

[luke-s-1993]

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.

[#2]

Keep a single data type in a cell, if comments are needed, add a comments column.

[#3]

I would but the person I'm doing this for needs to add what level certificate they have on with the date.

[#4]

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

[#5]

@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?

thanks.