How to: Reservation status dynamically change based on dates; act like Single Select Option

[Emmett_Klopp]

Hi All,

Apologies as I’m not entirely sure how to articulate what I’m trying to do.

I’d like to use this as a Vacation Rental CRM. I’d like the Base to automatically classify a reservation as “future,” “current,” or “past.” I’d like it to look like it does when I have this field set up as Single Section Option, but I’m not finding a way to combine a formula (If/Then) and that method of displaying /managing data.

I want the logic and automation of my Status Dynamic field (using formula), but the functionality/appearance of the Status Manual field (set up as a Single Select option). Something you might do with Conditional Formatting in Excel…

Thank you!

[Justin_Barrett]

Welcome to the community, Emmett! :grinning_face_with_big_eyes: This should be fairly easy to do using a formula that checks one or more date fields in your table. You won’t get the colored background of a single select field, but the contents will be driven dynamically by the date(s) in each record. If you describe your setup in more detail, we can provide more specific guidance on how to create the formula.

[Emmett_Klopp]

Hi Justin,

I believe I’ve already implemented what you’re describing. I have created a dynamic “Reservation Status” field that uses a nested If statement to check if today’s date is:

*Greater than Check Out, it returns “past”
*Less than Check In, it returns “Future”
*If neither, then it must be “Current.”

IF(TODAY()>{Check Out},“Past”, IF(TODAY()<{Check In},“Future”, “Current”))

Is that the extent of what I can do in AirTable?

[Justin_Barrett]

Not necessarily. There are other functions that can be used to examine and process dates as well. If your current formula doesn’t quite give you what you want, how exactly would you like the setup to behave?

[W_Vann_Hall]

I think the OP is looking to do something like change the field color based on status — which, as you know, can’t currently be done.

@Emmett_Klopp – at the moment, your choices are limited to record coloring, which tags the entire record, not field, with a colored flag, and the hack/workaround of using emoji coloring. Admittedly, neither are as satisfying or intuitive as conditional formatting on a cell level — but I have to assume its something that will eventually appear in Airtable…