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

Welcome to the community, @Homestead_Band! :grinning_face_with_big_eyes:

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.

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.