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.
