Filling a field when a date is entered into another field

I have a table that I’m using to track instrument checkouts for a high school marching band. It pulls from two other tables (one with the student information and the other with the instrument information), but I also enter data directly into the form, namely the date the instrument was checked out. I want to automate the step of filling in the school year field when I enter the date that the instrument was checked out (see screen shot for what I want it to look like).


The formula I used is this

IF(IS_AFTER({Date Checked Out},6/3/2021), “21-22”)

but it only gave me an error.

How would I change the formula to get the result I’m looking for?

Alternatively, because the school year will only be “21-22”, I could just have that field pre-populate with that value when I create a new record, but I haven’t been able to figure out how to do that either.

I appreciate any help you can give me!

Thanks

The IS_AFTER function expects a date object which is different form typing in a date. Currently Airtable thinks that you are dividing numbers.

You need to use DATETIME_PARSE to convert your date text string to a date object.

Try this …

IF(
  AND(
    {Date Checked Out},
    IS_AFTER(
      {Date Checked Out}, 
      DATETIME_PARSE("6/3/2021", "M/D/YYYY")
    )
  ), 
  "21-22"
)

However, this formula will not work well when you want school year 22-23.

As for pre-populating the school year, you could run an automation that will fill in the text field. You could use the “when record created” trigger.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.