Date formula calculation array in airtable or Zapier


#1

Hi,
I am using Airtable in combination with Zapier

Zapier generates a record in my master table with a start and end date. Those dates can be 1) within the same month, 2) across two to many months, 3) over a new year.

I would like to log for each record, how many dates are within each month within the given date range.

E.g. 1) 3/5/19 - 3/10/19 should return 5 days in month 3
2) 3/5/19 - 5/2/19 should return 26 says in month 3, 30 days in month 4, 2 days in month 5
3) 12/29/18 - 1/3/19 should return 2 days in month 12 2018, 3 days in month 1 2019

The reason is that I want to calculate occupancy per year/month across multiple master table line items.

Any thoughts?


#2

This type of problem is challenging because Airtable’s formulas don’t allow for iterating through an arbitrary collection. A single-formula solution would first calculate how many months/years are spanned by your dates, then go through each month to calculate the specific day total using something like a FOR loop, and Airtable doesn’t have that power yet.

This may be possible to achieve in the same table as your main records, though the solution percolating in my head involves a lot of extra fields, most of which would ultimately end up being hidden. I don’t have time to test this idea right now, but here’s the gist of it:

You’d add twelve extra formula fields, which would be hidden once they’re set up. Each field would check your date span to see if it covers a specific month, like January. If so, it would calculate how many days of that month are covered by the span, outputting something like “9 days in month 1 2019” if there’s a match, otherwise remaining blank. Another formula field (the only one you’d leave visible) would concatenate all of the results from these month fields.

One of the main problems I see, though, is that it would be really difficult to put the results in chronological order in situations like your third example where the date span crosses different years. Assuming that the concatenation process collects the results in month order, you could end up with a result like:

9 days in month 1 2019
5 days in month 11 2018
31 days in month 12 2018

I can’t think of a way to sort the resulting collection so that the years are kept in chronological order.