This question was posted 4/1/21 (d/m/yy).
In Google Sheets, I have a table of all my tests, like below:
| Subject | Test | Date (d/m/yy) |
|---|---|---|
| C3 - Biology | 1 | 1/1/21 |
| C4 - Chemistry | 2 | 3/1/21 |
| C5 - Music | 3 | 4/1/21 |
| C6 - Physics | 4 | 6/1/21 |
In Column D, I have the formula =TODAY(), which gets today's current date.
In Column E, I have the formula =IFS(C3=D3,"Today",C3<D3,"Past",C3>D3,"Future"), which prints "Today" if the test is today, "Past" if it has passed, and "Future" if it is yet to happen.
In each row, the C3 and D3 values change to the row number.
I have these conditional formatting rules in C3:
=E3="Today"RANGE: C3=E3="Past"RANGE: C3=E3="Future"RANGE: C3
However, when I "drag" this cell to copy conditional formatting rules to C4, this happens:
=E3="Today"RANGE: C3:C4=E3="Past"RANGE: C3:C4=E3="Future"RANGE: C3:C4
I have tried putting these in the column of C, but it comes up as an error:
=E$="Today"=E$="Past"=E$="Future"