Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

How do I find the date range between different transactions?

Topic Labels: Formulas
328 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello! I’m trying to build a CRM tracker that allows me to calculate the number of days between each order placed by a unique customer.

Screenshot 2021-12-15 at 5.14.32 PM

So far, this format I have is great and want to build on it, but what I want is to find the day range between each order number (so for eg: days between order #100090 and #100092, and then #100092 and #100132). Have been trying for awhile and still can’t figure this out

Any help is greatly appreciated!

2 Replies 2

Hi @Luong_Do,

If you have a separate table for your customers (where they are listed), then you could use a rollup to get min and max dates, which would give you the data range.

If you don’t use a separate table for customers, you could use the summary blocks in apps, to get min a max dates. If you want to parse it out by different customers, you may want to consider setting up a filtered view. This would be a good option if you just need the ranges occasionally but not for regular reporting.

The third option would be using a script to get min and max dates.

Thanks,
Chris

Welcome to the Airtable community!

Using rollups as suggested by Williams_Innovations will only work if there are exactly two orders. If there are multiple orders, you will not be able to see the interval between the orders.

The screenshot is not showing up in the forums, but I’m guessing that you have an [Orders] table where each order is a record.

You can have a same-table linked record field to link to the previous record. Then use a rollup field to get the date of the previous order. Finally, have a formula field calculate the date range using DATETIME_DIFF. (It is possible to do this all in a rollup field, but the formula gets complex because of how dates are handled in rollup fields.)

If you have a [Customers] table, it is possible to automate the creation of the link to the previous record. The automation would require either access to the beta for conditional logic in automations, or a automation script.