Need help figuring out formula to apply automatically apply date when single select field changes

[Yashwant_Sarava]

Hi there,

I’ve taken to Airtable recently to catalog all my acquired clients. I have a single select field called ‘$Flow’ that tells when the current status of the client in the sales funnel, for example: Made contact, Talking, Email, Won, and Lost.

What I want is a formula that can tell me exactly when I win a client over. I.E. when I turn the single select field from any other option to ‘WON’ the date (and time, if possible) should be captured in the formula column.

I used the formula: IF({$Flow}=“Won”, NOW())
But the date always resets every time I reopened the table, not when I originally turned the option to WON. Does anyone have any suggestions that could help?

Thanks
Yashwant.

[Mohamed_Swella1]

Hi @Yashwant_Saravanan1,

It is expected behavior since Now() is always Now :slightly_smiling_face:

What I suggest is keep the formula as it is but do the following:

1. Make a new view called Won
2. Make a new date field called Date Won
3. Using Zapier to look for new records in this view, let Zapier copy the value from the Formula field to the new field Date Won.
4. Hide the formula field
   Since Zapier will not trigger for the same record twice, it will copy the date once the status os changed to Won. However, if for any reason you change the status to Won by mistake and the zap triggers, there is no going back, you will have to adjust this field manually for this specific record.

If this answer helps, please mark it as Solution so others can see it and benefit from it.

BR,
Mo

[Kamille_Parks · Accepted solution]

Another approach not requiring Zapier would be to keep the general idea of your formula, but use LAST_MODIFIED_TIME() instead of NOW()

IF({$Flow} = "Won", LAST_MODIFIED_TIME({$Flow))

This should only show you the day/time when the status is changed to “Won”. If you change it to something else it will disappear. Assuming there isn’t another step after “Won” captured by this field, this should work fine.

[Yashwant_Sarava]

Hey! This is exactly what I was looking for thank you so much for this!