IF Statement needed to change cell value based on input of another cell

[kschmit1]

Hello - I'll openly admit IF statements are still a learning curve for me so any help is appreciated, this is sort of an extension of a prior post I had presented.

I have a Due Date column which is manually input by the user.

Based upon this date, the Due Date timer starts a countdown using =A3-Today(), once the item hits 10 or less days conditional formatting turns it yellow, 5 or less days turns red.

Currently I am using a formula that when an entry is put in Completion Date the conditional formatting stops and the cell returns to no fill =IF(A3="","",A3-TODAY())

Ideally I would like Column B to not display negative days even if it has changed colors due to being completed. Any ideas on a formula I could use to have Column B - due date timer change from a countdown to either "Completed" or just completely blank, no data if a Completion Date has been entered?

Any help is much appreciated and I'm always happy to give kudos to those who solve my issue.

[dosydos]

=if(a3="","",if(c3<>"","",a3-today()))

[kschmit1]

Thanks dosydos...the formula is doing what I would like however the conditional formatting then appears to no longer work, I am using a formula to cause the fill (not that specific color matters, just referencing for you on my example):

=AND($B24<>"",$B24<=5,$C24="") this is red
=AND($B24<>"",$B24<=10,$C24="") this is yellow

I am using these rules so that is there is no due date input, the due date timer in column b does not display a large negative number which was happening when I was using a simple cell value less than or equal to conditioning.

Any ideas or adjustments to the above?

[kschmit1]

Sorry dosydos, jumped the gun on that one! The conditional is working on my real document, for some reason it did not apply on my example. You're a life saver!

[dosydos]

Glad it worked out