Google-sheets – Conditional format a cell with a graduated colour when a range of other cells are completed with text

I would put the progress-value into a column, and then use conditional formatting based on that to set the cell background. And then you can filter on the progress column directly.

(I've never liked the idea of just colouring cells and then trying to extract the value of them: in principle, if you want to record some data then do it directly.)

There might be a better way, but try three conditional format rules on C1 in this order:

1. "Custom formula is"; Value: =IF(A1="Done",TRUE,FALSE): set background to White

2. "Date is after"; "exact date..."; Value: =NOW()+3: set background to Green

3. "Cell is not empty": set background to Red. This is the default if the cell is not "Done" and the date is not after three days from now.

You could also keep rule 1, and then have rule 2 being a Colour Scale. You might need to put an extra cell to convert NOW() into a day count, though: =NOW()-3 for example.