- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 13, 2021 10:37 AM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 13, 2021 02:09 PM
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.