Conditional formatting by date

Feature Requests
1

Hello,

again I’m not sure if I’m not properly using the tool, but here is my need :slight_smile: :
I have a table for task tracking with one column = “due date” and one other with “status”
I’d like to track all tasks where status was switched to ‘Done’ or ‘Cancelled’ after the “due date”

Two needs :

  1. I’d like to have one column formatted as date, updating only when the status goes to these values. that would be an automated ‘completion date’
  2. I’d need conditional formatting to be able to compare two dates : ‘Due date’ and ‘completion date’. When I’m using dates, it’s only possible to compare to calendar dates (yesterday, some days…), but not to another column formatted as date.

for point 1 I can use column field ‘modified’ but that’s not so accurate as any modification to other fields will update this. It would be great to be able to restrict the modified to certain column.

thanks

2

I’m not sure what you mean, but I think you can create a view, set a filter condition where Status=Done and Status=Cancelled. So the first step is done: in this view, you only have “done” and “cancelled” tasks.

The second step is to set up a formula. The conditional formatting cannot compare two dates, it’s true. But a formula can. You can write

if({Due date} > {completion date}, 1, 0)

So for all those records where the completion date is over the due date, there’s a 0 in this column. If you don’t want to see this column, you can hide it in this view.
Then, go to “Color” (what we call ‘conditional formatting’), set up a new rule, let the row be colored if the formula column is 0.

image

Is this what you need?

3

Thanks for these ideas

For point 2, I had indeed this idea, but that requires a formula column where a conditional formatting with 2 date seems more straightforward.
Would the team think about adding this possiblity, having 2 ‘date’ columns compared

For point 1, I would like my team to just switch the status, not entering the completion date. So I would like to have an automated date entry when I change the status of the project. Is it clearer ?

ben

4

Yes the team can consider that but before we deliver this feature, you’ll have to do it with a formula…

For Point 1, maybe the “Modified time” can help?

5

I see these requests as:

  • Compare two date columns in the conditional formatting
  • Record the time of the change of one column
1 Like
6

‘Modified time’ is indeed one solution, but it’s not ideal as any change to complete some items without modifying the status will be taken in account.
In that case, a ‘modified time watching only one specified column’ would be great