I’m sure there are other ways to do this, but I think the quickest way to accomplish this without breaking your existing structure would simply be to split up the bookings that straddle different months. Then all of your lookups and rollups in the Months table would calculate correctly
So, if someone booked from June 23 to July 5, it might look something like this:
Hmmm… yeah, this is a tricky one because of the limitations of Airtable. I like @Brennan_Ward’s idea of splitting up the bookings into different months.
If you wanted to use an external tool like Make.com, you would need to setup a loop to cycle through all the dates in between the start date & the end date, and then apply the daily rate for each day within that loop.
This would probably take me a few hours to set it up, so I wouldn’t be able to guide you through this here without being hired as a consultant on your job.