Nested IF AND with IS_BEFORE & IS_AFTER for School Term Dates

[Meagan_Caesar]

Hi Community, I’m really hoping someone can help me with this, as I’ve tried so many different ways to get it to work and read so many different scenarios that don’t quite fit mine, that I’m now totally muddled and have no idea why it’s not working.

I’m trying to have a formula field that looks at the date in the {Date & Time} field of an event and determines which school term or holidays (Australia) the event falls into. The end goal is to then be able to group the events by this formula column.

Originally I tried a SWITCH formula, but I couldn’t figure out how to add in the DATETIME_PARSE, so I’ve changed to nested IFs, so it is quite long, but I would really, really appreciate any suggestions as to why it might not be working. Also, if the date doesn’t fall into any of the date ranges, I want the cell to be blank.

Thanks so much in advance!

`IF(
{Date & Time},

IF(
AND(
IS_AFTER({Date & Time}, DATETIME_PARSE(‘06/02/22’, ‘DD/MM/YY’)),
IS_BEFORE({Date & Time}, DATETIME_PARSE(‘02/04/22’, ‘DD/MM/YY’))),
"TERM 1”,

IF(
AND(
IS_AFTER({Date & Time}, DATETIME_PARSE(‘01/04/22’, ‘DD/MM/YY’)),
IS_BEFORE({Date & Time}, DATETIME_PARSE(‘19/04/22’, ‘DD/MM/YY’))),
"TERM 1 HOLS”,

IF(
AND(
IS_AFTER({Date & Time}, DATETIME_PARSE(‘18/04/22’, ‘DD/MM/YY’)),
IS_BEFORE({Date & Time}, DATETIME_PARSE(‘25/06/22’, ‘DD/MM/YY’))),
“TERM 2”,

IF(
AND(
IS_AFTER({Date & Time}, DATETIME_PARSE(‘24/06/22’, ‘DD/MM/YY’)),
IS_BEFORE({Date & Time}, DATETIME_PARSE(‘11/07/22’, ‘DD/MM/YY’))),
"TERM 2 HOLS”,

IF(
AND(
IS_AFTER({Date & Time}, DATETIME_PARSE(‘10/07/22’, ‘DD/MM/YY’)),
IS_BEFORE({Date & Time}, DATETIME_PARSE(‘17/09/22’, ‘DD/MM/YY’))),
"TERM 3”,

IF(
AND(
IS_AFTER({Date & Time}, DATETIME_PARSE(‘16/09/22’, ‘DD/MM/YY’)),
IS_BEFORE({Date & Time}, DATETIME_PARSE(‘04/10/22’, ‘DD/MM/YY’))),
"TERM 3 HOLS”,

IF(
AND(
IS_AFTER({Date & Time}, DATETIME_PARSE(‘03/10/22’, ‘DD/MM/YY’)),
IS_BEFORE({Date & Time}, DATETIME_PARSE(‘10/12/22’, ‘DD/MM/YY’))),
"TERM 4”,

IF(
AND(
IS_AFTER({Date & Time}, DATETIME_PARSE(‘09/12/22’, ‘DD/MM/YY’)),
IS_BEFORE({Date & Time}, DATETIME_PARSE(‘21/01/23’, ‘DD/MM/YY’))),
“CHRISTMAS HOLS”, " ")
)
)
)
)
)
)
)
)`

[Kamille_Parks]

Do the breaks for which terms start change every year?

[Meagan_Caesar]

Yes. They are usually around similar dates, but they’re not always the same dates. My plan was to duplicate the field for each subsequent year and adjust the dates as the new year approaches.

[Kamille_Parks]

How similar? Is it: “Term 1 always ends on the first Saturday of April” similar or “Term 1 always ends on some day early in April” similar?

Meagan_Caesar:

My plan was to duplicate the field for each subsequent year and adjust the dates as the new year approaches.

I’m not sure I would recommend that approach. I would consider having a table for Terms that has 3 fields: {Start Date}, {End Date}, and {Link to Table 1}.

That will make finding the appropriate term easier to automate, and easier to report on.

[Meagan_Caesar]

Although, each term is usually 10 weeks, with 2 weeks break in between, except for the Christmas holidays, so perhaps a formula that uses the number of weeks from the school return date for the year (January) might work better?

[Meagan_Caesar]

Ah yes, that would be easier! Thank you! Would there be a way to automate the selected linked term based on the date of the event, or is that asking too much?

[Kamille_Parks]

The exact automation setup will depend on your workflow. I would imagine you create your terms once at the start of the (school) year with their dates filled in.

Then you’d decide if you want an Automation to trigger when a new class(?) record is created, or when the {Date and Time} field is modified. If you create records via forms or external integrations, choose “created”, otherwise choose “modified”.

Then you’d add a Find Records step to search the Terms table for records whose {Start Date} is on or before {Date and Time} and whose {End Date} is on or after {Date and Time}.

Finally an Update Records step can take the record IDs for the records found in the earlier step and paste it into the {Link to Terms} field. Only one term should be found/linked if your dates are entered with no overlaps.

[Meagan_Caesar]

Thank you so much, that was such an easy to follow explanation and suits my needs perfectly, I really appreciate your time!

[Meagan_Caesar]

I’ve been testing out your suggestions and it’s all worked really well up until the automation where you suggest to select On/Before {Date & Time}. There doesn’t appear to be a way for me to select a specific field, such as the {Date & Time} field, there’s only a drop down menu of “today, tomorrow, exact date, etc.” Have I missed something somewhere or do you have any suggestions on ways to work around this? Thanks so much!

[Kamille_Parks]

Chose Exact Date, then in the box to the left where you’d ordinarily type in a date, click the blue icon to select the Date and Time field from the trigger record.