Skip to main content

Hello 👋 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!

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.



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.




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

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



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


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


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

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



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!

Reply


Terms & Conditions