Pro-rating number of nights

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:

I think trickier than it may initially seem

This thread on finding all dates from start and finish dates may be useful

You could get from there to your answer by using date formulae, look up fields or automations alongside a dates table.

It may be more easily analysed in excel where there are easy to find formulae for the above.

@ScottWorld had some suggestions using make.com

@David_MacDougall1 i believe had a solution.

Best of luck

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.