Nested IF AND with IS_BEFORE & IS_AFTER for School Term Dates

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.

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.

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.

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.

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.

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.

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")

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")

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:

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:

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.

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!