Nested IF AND with IS_BEFORE & IS_AFTER for School Term Dates

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”, " ")
)
)
)
)
)
)
)
)`

Do the breaks for which terms start change every year?

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.

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?

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.

2 Likes

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?

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?

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.

2 Likes

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

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!

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.

It only shows the calendar selection option to choose a date to the right, it doesn’t have a blue icon to select a field.

I’ve searched and searched and can’t find a way around the issue, it seems it’s a problem for many and the ability to select a dynamic date field like that in Automations is a much requested feature, hopefully it might be an option in the future.

In the meantime, I’m wondering if I could use WEEKNUM to translate the dates to numbers and then match the week number between the 2 tables. I’ve tested it with the beginning of Term start date using “Find Records > based on Condition > 'Where WEEKNUM = (select dynamic) {WEEKNUM}” and then using your suggestion to Update Record from that, which works perfectly, but where I’m stuck is how to do this or something similar with the date range for each term and holidays.

Because each term has a start and end date, I’m wondering if there’s a formula I can use to list all the dates within each range, and then list all the week numbers. However, I’m not sure how to set that up in the automation, because the Term Dates table WEEKNUM field would have multiple week numbers (unless I list out every single week date, which I don’t want to do!), but the Events table WEEKNUM field would only have a single week number, so I assume the = match up wouldn’t work??

There surely must be a way around this, but I’m stuck! Hopefully a fresh set of eyes on this will think of something I haven’t. Would really appreciate your help! Thank you!

ETA: And another issue with this that I didn’t think of is the fact that it won’t differentiate between different years, so I’ve adjusted the WEEKNUM formula to: DATETIME_FORMAT({Start Date},"YYYY, W")

@W_Vann_Hall - apologies for the random tag, but I’ve been trawling through anything related to this that I can find and I came across some of your responses to different scenarios. You appear to enjoy a challenge and give really well-explained suggestions, so I’m wondering if you have any thoughts/suggestions on how to make this work, if you have a moment to take a look? Thank you!

Using week numbers is actually pretty clever, and will in fact solve your problem. You don’t need to list out every week for each term. You have a start date and and end date for each Term in the Terms table. Simply add two formula fields that convert those dates into the week numbers including the year, but adjust your formula so the output is a number not a string:

VALUE(DATETIME_FORMAT({Start Date},"YYYYWW"))

^ That will output a number, meaning you can use “greater than or less than” filters. I forgot that Date fields can’t have dynamic variables in Find Records Automation Steps, but number fields can.

So follow the original suggestion above using the Week fields instead of the date fields.

“Find records in the Terms table there {Week of Date & Time} is greater than or equal to {Week of Start Date} and less than or equal to {Week of End Date}.”

As a note: W is a legend in the forums but hasn’t been been here for years. They may not ever respond.

Thank you so much @Kamille_Parks !!

So a couple of questions…

  1. In the original Events table I have a CONCATENATE formula in the first field that combines the event name and the date, as it’s used in an RSVP form. I’d like to do the same for the Term Dates table, as then when I group by that linked field in Events table, it will display “Term (start date - end date)”. However, the Events CONCATENATE only references the one Date, so it’s working as intended, but when I try to add a second date for the Term Dates table, I just can’t get it to work correctly. The issue is that I’m also adding a SET_TIMEZONE so that the dates are correct for Australia/Brisbane (not sure if that’s necessary), so I’m struggling with the right formula.

  2. If you add a SET_TIMEZONE formula field to a table, does it only set the timezone for the specific date field referenced in the formula, or does it set the timezone for the whole table? If I have SET_TIMEZONE included in both of the CONCATENATE formulas in Term Dates, does that set the timezone for the table, or do I need to continually add a SET_TIMEZONE formula to all formulas relating to the date in order to keep them all consistent? I’ve been adding a separate SET_TIMEZONE formula field for each date field, as I assume it needs to be per date field, but do I also need to add SET_TIMEZONE to other date formulas, or what bits are unnecessary? I can’t find a definitive answer to this question after a lot of searching.

  3. Because of the above, I modified the week number formula for both tables to: VALUE(DATETIME_FORMAT(SET_TIMEZONE({Date & Time}, 'Australia/Brisbane'), "YYYYWW"))
    It appears to be working correctly, but is this ok to use? Is it necessary?

  4. I think I’ve gotten myself confused between ‘on/before or on/after’ with the date fields and the greater/less than option for the new week numbers, as they are the opposite way around to your wording above. I think it should be "Find records in the Terms table, where {Start Date WEEKNUM} is equal to/less than {Event WEEKNUM} and, where {End Date WEEKNUM} is equal to/greater than {Event WEEKNUM}. Is this right?

However, this returns 2 records instead of 1, and I’m wondering if it’s due to the time? Like perhaps I should include a time with the Term Dates? Because there seems to be an overlap. Screenshot below… The top record with star is the one that should be returned, but the 2nd one shouldn’t be there, and I’m wondering if it’s something to do with the 12am time, which is pushing it into the 19th, so it should be 11.59pm instead? How would I fix that?

Thank you so much for your continued help!! :star_struck:

Hi @Kamille_Parks, just bumping this so it doesn’t close, would love your help with the above when you have a moment, particularly the automation issue with the times and returning 2 records. Thanks so much for all your help so far!

Yes

Yes

Yes, and probably

What was the date that is being compared to these two Terms? If you need more precise comparison values because the Breaks will end up having the same week numbers as the Terms, you could try “YYYYDDDD” instead of “YYYYWW” which will give you the day of the year, or even “x” which will give you the unix timestamp (basically, the date including the “time” as a number). That should resolve instances of overlap.

1 Like

Hi,

solution with weeks is more ‘correct’, but JFYI, in order to use SWITCH here you can use the factor of adjacent periods and boolean conversion (true=1, false=0), X*0=0, like this:
(I took other dates for quickness and put final line together to fit into screenshot)

Thank you so much! the YYYDDDD option worked perfectly! That’s pretty much everything working except the CONCATENATE with the 2 dates, but I can live without that if you don’t have any suggestions on that one. I really, really appreciate all your help, thank you so much!

Thank you so much for providing a different approach, I love learning as much as possible about different ways to do things, so this is great. Thank you :slight_smile: