Date Stamp When Status is set to "Done"

[Matt_Petersen]

I am wondering how to create a formula that will set a date when a specific field is set to “Done” is this possible?

Something like IF{Order Status}=“Done”, TODAY()

This formula doesn’t work because I have no idea how to format it correctly.

Any help would be appreciated!

[Loic_Sanchez]

Hi @Matt_Petersen,

What you are looking for is for the field to be updated with the date of the day when the ‘Order Status’ field is updated, so a formula wouldn’t help you achieve that. I don’t believe there is a feature to cover that functionality so I would recommend creating a new request in the ‘product suggestions’ part of the forum.

[Kamille_Parks]

My work around is usually to do this in reverse:

Have a date field where you mark the “Order Completion Date” and a formula field to act as the {Status} field: IF({Order Completion Date},"Done","In Progress")

[Chinara_James]

Haven’t tested this, just writing from the top of my head but what if you have the field of status with your dropdown of done and another field with completed date and have that be a formula when a done is selected get datetime now

[Justin_Barrett]

That’s similar to what @Matt_Petersen was originally thinking. The problem is that both TODAY() and NOW() will continually update. The dates they produce won’t lock in based on the first time they’re set by the formula. Every time the table is updated, so will the date.

It might be possible to use Zapier or Integromat to do the date-setting, with a normal date field updated by the integration service when it sees the status change. However, it wouldn’t be an instant update depending on how frequently the service checks for changes. If that’s not a problem, then consider going that route. Otherwise I believe that what @Kamille_Parks suggested is probably the best alternative available for now.

[Chinara_James]

Yes you’re quite right @Justin_Barret, Kamille’s way is the best way.

[Olivier]

imagine a task table with a dedicated column for status “to do” ; “doing” ; “done” and a Kanban view.
It will be great if when a task is move to “doing” column, a “date_doing” column automatically updated because changing a date manually to change the status is not really a good option with kanban view :frowning: I tried zapier which work fine with a trigger on a new element in a “doing” view but unortunalty I have to pay additional credit :frowning:

[Matthew_Moran]

You can add a field that tracks date modified. One of the options for a “modified time” field is to specify which fields it is tracking.

So create a Modified time field called: OrderStatus_Mod (or whatever works for you). Set it to track your {Order Status} field.

Your formula could then be:
IF({Order status} =“Done”,DATETIME_FORMAT({OrderStatus_Mod},“MM/DD/YYYY”),"")

[Olivier]

thanks matthew it woks fine !
But in a workflow with 3 steps I’d like to keep and fix the date on each status
TODO_DATE; DOING_DATE; DONE_DATE. Since the mentioned technique is based on the last-modify field, when the status is DONE, the DOING DATE is empty

[Justin_Barrett]

You might consider using Integromat instead of Zapier. It’s a little trickier to figure out at first, but it gives you more operations at the free tier than Zapier (1000 operations per month in Integromat vs 100 tasks in Zapier), and their tier structure is less expensive overall.

The downside to making a similar setup in Integromat is that it no longer has a “New record in view” option, so you’re left to make a scenario that regularly checks changes to either a Created Time or Last Modified Time field. If you run the scenario using the free tier’s smallest allowed interval of 15 minutes, that’s 96 operations per day just to see if there’s a change, not counting the operations used when a change is found. The free tier only has 1000 operations, so you’ll eat up your allotment in about 10 days at that rate. If you can get by with fewer checks per day (either via a larger interval, or by limiting the time window when scenario is run; i.e. don’t run it overnight when you’re asleep), you can probably make it last a month, but frankly I would suggest their Basic tier. It’s half the cost of Zapier’s lowest paid tier, and gives you 10,000 operations (vs Zapier’s 750 tasks).