Help

Formula to pull a list of individual nights out of a date range?

Topic Labels: Formulas
Solved
Jump to Solution
93 3
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi all!

I’m using part of my AirTable Workspace to track hotel lodging needs for my organization’s yearly event. I have the data built where I assign an attendee to a property and room type, and include their check-in and check-out dates. Is there a formula to pull each night of stay for the date range without me having to manually build out a report listing each night of stay for every individual? Or is there a way this can be built in automations or interfaces? For example, John Smith is checking in on March 22 and checking out on March 24, so his individual nights of stay would be March 22 and March 23.

I’m looking to pull this data so I can see what the nightly pick-up is for each property by room type. I started using the day calendar as a sort of “list” of nightly pick up of rooms, but since it shows up on the check out date, it’s not quite accurate.

Thanks for any suggestions you may have! I’m happy to schedule a GoogleMeet meeting to visualize things together if you’ve built something similar!

Abbey

1 Solution

Accepted Solutions

Hi @Abbey_Matye,

Perhaps my ridiculous formula in THIS POST will be of use :man_shrugging:t2: .

See Solution in Thread

3 Replies 3

Hi @Abbey_Matye,

Perhaps my ridiculous formula in THIS POST will be of use :man_shrugging:t2: .

Hi! Based on the use case description, I feel like this could work! I’m having issues getting the formula to be valid in my base though. I only copied up to 30 days, because we won’t have folks have more nights of stay than that, and I filled in the field areas for {Start Date}, {End Date}, and {Length in Days}, but I’m having issues finding what would correlate to “{Cancelled On},{Cncltn Recorded By}”. Are you able to clarify?

If it helps, this is how I have the table set up:
Screen Shot 2022-10-27 at 3.52.59 PM

I used Check In as {Start Date}, Check Out as {End Date} and Number of Room Nights as {Length in Days}.

I would just delete the surrounding IF() and the top two lines of the formula I posted.