Using DATETIME_DIFF for multiple dates in ONE field

rhymeslikeruns · ‎Mar 10, 2021

Hello :wave: everyone!

Wonder if anyone can help me. Background is that we run a startup where trial users do a free session then come back and do a paid session. I want to understand:

a) the average time between the free session and the first paid session
b) then the time between subsequent paid sessions

So I’ve got a single lookup field with a list of dates in it:

(10/1/2020, 8/24/2020, 9/28/2020, 10/8/2020, 9/24/2020, 12/14/2020, 10/5/2020, 10/27/2020, 10/18/2020, 11/19/2020, 8/31/2020, 9/7/2020, 10/15/2020, 10/12/2020, 11/5/2020, 10/19/2020, 11/12/2020, 11/9/2020, 10/26/2020, 11/10/2020, 12/10/2020, 9/21/2020, 9/14/2020, 10/29/2020, 10/22/2020, 10/31/2020, 11/2/2020, 11/5/2020, 11/21/2020, 12/1/2020, 1/19/2021, 1/26/2021)

I’m struggling to find a way to use DATETIME_DIFF correctly to do this as it seems to assume that dates are all in separate fields, which obviously mine are not.

Does anyone have any ideas on how to solve it?

Thanks so much in advance!

kuovonne · ‎Mar 10, 2021

You want to perform calculations across rows in your linked table. Airtable does not have good support for this.

You could have each record in your linked table also link to the next record in the series and calculate the durations that way. Then you can use rollup fields on the durations. However, that is a lot of linking to maintain.
You could use a script. The script could either manage the links in the above scenario so that formula fields and roll ups do the calculations. Or the script could do the calculations directly.

rhymeslikeruns · ‎Mar 10, 2021

Thanks @kuovonne yes I think you are right on the script…sadly that’s not my strong point!

Justin_Barrett · ‎Mar 10, 2021

If you need help, just ask! There are lots of us here who can help with scripting solutions.

rhymeslikeruns · ‎Mar 10, 2021

Help! Any ideas on how to approach it would be very welcome! :grinning:

kuovonne · ‎Mar 10, 2021

When it comes to scripts, do you want to invest time or money? Do you want to learn to code, or do you just want working code?

Some people want to learn scripting themselves. This takes an investment of time. If you have never written code, I recommend finding a JavaScript course you like. If you already know some JavaScript, the documentation is a good starting point. After that, there are many people on this community who will help with any snags you meet.
Some people do not want to learn scripting, but they have budgets to hire someone. If this is your situation, I (and others) are available for hire. If you are interested in hiring me, you can see my process or book an appointment for more information.
Some people are not interested in learning scripting and also do not have budgets. This is a difficult situation, and you need to decide how important the feature is or get creative coming up with a different solution (which can also take time or money).

Good luck in your decision!