If date in one field is either not empty or within a certain range, then choose an option in a single select field

[Homestead_Band]

I have a base where I am tracking instruments loaned to students in a music department. Three of my data points are date checked out, date checked in, and a single select field for which two of the options are ‘unavailable for loan’ and ‘available for loan.’

At this point, I change the single select field by hand to indicate whether an instrument is available for loan or not. I’d like to create a formula that looks at the ‘date checked in’ field and if the field is empty, it changes the single select field to ‘available for loan.’ If the ‘date checked in’ is not empty, then the single select field would be changed to 'available for loan." Another layer would be if the ‘date checked in’ field is not empty, to look at the date and if it falls within the current school year (or another range of dates I choose), then the single select field would change to ‘available for loan.’

I think this can be done, I’m just not sure how to do it

[Justin_Barrett]

Welcome to the community, @Homestead_Band! :grinning_face_with_big_eyes:

Homestead_Band:

I’d like to create a formula that looks at the ‘date checked in’ field and if the field is empty, it changes the single select field to ‘available for loan.’
…
I think this can be done, I’m just not sure how to do it

Unfortunately this isn’t possible. Formula fields can only drive their own output; they can’t control the output of other fields.
With that in mind, you could create a formula that effectively replaces the single select field and outputs the desired text, either “Available for loan” or “Unavailable for loan”. You could even include emojis in the output for a better visual cue:

:white_check_mark: Available for loan
:x: Unavailable for loan

The basic version of the formula could look like this:

IF(
  {Date Checked In},
  "✅ Available for loan",
  "❌ Unavailable for loan"
)

A variant of this to compare the check-in date against a date range is also doable, but I’m afraid my time to experiment is short. Perhaps someone else can fill in that gap.

[Katerie]

Hi, this will do exactly what you asked, although it won’t be able to manipulate a single-select field directly. If you want it to do that, you will need to use an automation to copy the result of this formula field into the single-select field that you wish to use.

(Alternatively, you can just use this formula’s output in place of the single-select field as your status indicator.)

To use this formula, first create two additional date fields: school year start and school year end, and input the starting and ending dates accordingly.

IF({date checked in}, IF(AND(IS_AFTER({date checked in}, DATEADD({school year start}, -1, "day")), IS_BEFORE({date checked in}, DATEADD({school year end}, 1, "day"))), "Available for Loan", "Unavailable for Loan"), "Unavailable for Loan")

If you do not need to use this formula in connection with a single-select field, I would recommend adding the emoji that @Justin_Barrett suggested.