As an update to this post: Workaround for "if false do nothing" + simile field change listeners + field instrospection
I have figured out a simpler and more stable approach to keep track of when a task was started (i.e. first moved from “To Do” to another column that isn’t “Done”), in addition to tracking when the task was created using the built-in Created timestamp and when the task was completed with a simple formula.
This allows for some powerful task tracking, like a Timeline view showing when tasks were started and created, and a Daily Overview of how many tasks were created today, how many were started, completed, were due today, etc. (examples at the end of the post).
The setup is a basic Tasks table, where tasks are rows that have a Stage column that can take on one of these values (including but not limited to); “To Do”, “Doing”, “Paused”, “Blocked”, “Done”. Of these values, “To Do” is considered the start point, i.e. tasks with their stage as “To Do” are considered to not yet have been started. Conversely, tasks with their stage as “Done” are considered to have been completed (and of course also started). All other values (e.g. “Doing”, “Paused”, etc.) are considered to be “in-progress” stages for which the task has been started but not yet completed (you could add any number of these “in-progress” stages without any change to the formulae).
To conform with how a real-world manual task tracking would work, we want to satisfy these rules:
- Changing the stage of a task between any of these “in-progress” stages should not modify the timestamp at which the task was started
- Changing a task to the “To Do” stage from any other stage should reset the Started and Completed timestamps to Blank
- Changing a task from the “Done” stage to any other stage should reset the Completed timestamp to Blank
- Changing a task from the “To Do” stage to the “Done” stage should set both the Started and Completed timestamps to now
The formulae for the Created and Completed columns are simple:
Created: thisRow.Created()
Completed: If(thisRow.Stage = "Done", thisRow.Stage.Modified(), "")
The formula for the Started column is slightly more involved, and requires another column in order to be able to track the value that the Started column originally had at the time right before the formula is triggered (upon a change to the stage for this row) and executed for this row. This would usually be disallowed by Coda with a Circular Reference error. Here they are:
Started: If(thisRow.Stage = "To Do", "", If(thisRow.[_Started Copy].IsNotBlank(), thisRow.[_Started Copy], Now()))
_Started Copy: thisRow.Started
The Started column formula can be thought of as being triggered when the stage of a row changes. If the stage is changed to “To Do”, then the value for the Started timestamp should always be reset to Blank (""). Otherwise, if the timestamp value that this row had for the Started column if the copy of this field (accessed via the _Started Copy column for this row to bypass the Circular Reference error) was not Blank, i.e. this row had an existing Started timestamp right before the formula was triggered, then keep this value. Otherwise, if the timestamp value that this row had for the Started column (the value in _Started Copy for this row) was Blank, then initialize this field to the datetime now (since this task was just now started). As a side note, you could replace Now() with thisRow.Stage.Modified() but I have observed in practice that this is not always accurate and sometimes gives outdated timestamps.
The leading underscore before the _Started Copy column is a hint that this column is internal and not meant for consumption (it’s a programming convention). I have hidden this column everywhere.
This approach accurately and reliably sets these three timestamps and persists their values permanently (unless of course the row’s stage is later modified), and completely satisfies the rules mentioned above. It works exactly as you would expect with no issue. The only downside is the additional column 
, but that is necessary at least until Coda allows field introspection or gives a named variable to store the original value that a field had right before the formula was triggered (would be awesome!)
Here are some examples of what can be achieved with this system:
