Help

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

Solved
Jump to Solution
409 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Yashwant_Sarava
4 - Data Explorer
4 - Data Explorer

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.

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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.

See Solution in Thread

3 Replies 3

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
16 - Uranus
16 - Uranus

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.

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