Find Records Automation With Date Condition

[David_Dixon]

I am using airtable to track reservations at a vacation home. Each reservation is its own record with an “Arrival Date” and “Departure Date”. I would like to be able to add a field called “Prior Guest Departure Date” which can allow me to schedule tasks for the next guest at the end of the previous reservation. I can’t figure out how to lookup, or use a formula that can find the previous date relative to the date in the record. Any suggestions? I am happy to share more if you need it!!!
In case it helps, I am using the dates along with filtered views to setup Zapier for messaging.

[Per_Carlen]

What exactly would you want “Prior Guest Departure Date” to do/output?

[David_Dixon]

I would like it to output the prior departure date on the record, that way I can filter for it to setup actions on that date.
ie: name: John | Arrival Date: 9/25/21 | Departure date: 9/28/21 | prior departure date: 9/15/21

This way I know when the previous guest has left to send information about this upcoming guest on the day of the prior departure. In this example, I could setup a note: Leave balloons and write Happy Birthday John (which would be sent on 9/15).
In my reservation software, I could set that up when they schedule their reservation, and if someone later books from 9/17-9/20, it would update the prior departure date and send the message on 9/20.

[David_Dixon]

My thought is some type of a formula that would find the maximum date before the current one, this way always finding the one prior. But I know I think in excel terms… not airtable terms! ha