Calculating Hours by person by projects from date ...

[Abbey_Matye]

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

[Jeremy_Oglesby · Accepted solution]

Hi @Abbey_Matye,

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

[Abbey_Matye]

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:

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

[Jeremy_Oglesby]

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

[summerdickey]

Hey @Jeremy_Oglesby - Could you reshare the link to your formula? The link in "THIS POST" is taking me to a general Airtable community support page. Thank you so much!

[Jeremy_Oglesby]

Hey @summerdickey  - Airtable changed forum services at the end of November, and apparently all the cross-referencing post links in the forum no longer work.

Here's the post I was linking to:

https://community.airtable.com/t5/product-ideas/new-formula-field-functions/idc-p/36089/highlight/tr...

[summerdickey]

Ah, thanks so much! Will give this a try 🙂