Dynamically defining the {to} in a DATETIME_DIFF formula?

[Adam_Sigel]

I’m using Airtable to do some pipeline reporting and trying to create a field that shows the time a record has spent in its current stage. I have fields (all called “Moved to [Stage]”) that automatically get timestamped whenever a record moves into each stage, now I want to make one new field that I can show on all cards in the kanban view. Effectively, it’ll be DATETIME_DIFF(NOW(), {Moved to [Stage]}) but I keep getting syntax errors or NaN outputs.

Can I use a nested formula to define the {to} datetime?

[W_Vann_Hall]

I’m not quite following you, here. I take it {Moved to [Stage]} can refer to any of a number of stages — so how are you referencing {Moved to [Stage]}? Replace your DATETIME_DIFF() function with something simpler, like maybe YEAR({Moved to [Stage]}) and see if it works; if it fails, try wrapping {Moved to [Stage]} in a DATETIME_PARSE() function to ensure it’s a date.

[Adam_Sigel]

{Moved to [Stage]} does not return a date. It returns text with the current stage name, which I in turn want to use to lookup the date in the field with the same name. Let me try and explain a little better:

I have a field called “Moved to SQL” which has a verifiable datetime.

I have a field called Stage_Time which is based on the formula “Moved to “&{Stage}” and returns text, e.g. “Moved to SQL.”

I want to use the output of Stage_Time inside of a DATETIME_DIFF formula, so that my {to} is accurate based on the current Stage of the record, but cannot get it to work.

[Adam_Sigel]

I tried an alternative approach, which is a series of nested IF statements, but Airtable seems to be cutting me off at 2 nested IFs, even though I’ve read online that it supports more.

Here’s what I have that’s working for the first two stages:

IF({Sales Stage} = "SQL", DATETIME_DIFF(NOW(),{Moved to SQL}, 'd'), IF({Sales Stage} = "Estimate Sent", DATETIME_DIFF(NOW(), {Moved to Estimate Sent}, 'd')))

When I tried to change it to the following, it just ignores the change and reverts to the version above:

IF({Sales Stage} = "SQL", DATETIME_DIFF(NOW(),{Moved to SQL}, 'd'), IF({Sales Stage} = "Investment Estimate Sent", DATETIME_DIFF(NOW(), {Moved to Investment Estimate Sent}, 'd')),IF({Sales Stage} = "Application Started", DATETIME_DIFF(NOW(), {Moved to {Moved to Application Started}, 'd')))

Anyone have any ideas? The dynamic field reference would certainly be a “cleaner” solution but I’d be happy to do a long nested IF if Airtable would let me.

[Elias_Gomez_Sai]

I just noticed this repeated part:

The rest seems OK to me.

[Adam_Sigel]

Good catch, thanks. When I cleaned that up, Airtable still let me click Save, returned no error on the formula, and discarded it when I click Customize Field again.