Nested IF AND with IS_BEFORE & IS_AFTER for School Term Dates

Meagan_Caesar · ‎Jun 02, 2022

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

Meagan_Caesar · ‎Jun 02, 2022

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.

Meagan_Caesar · ‎Jun 03, 2022

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

Meagan_Caesar · ‎Jun 03, 2022

@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!

Kamille_Parks · ‎Jun 03, 2022

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.

Meagan_Caesar · ‎Jun 03, 2022

Thank you so much @Kamille_Parks !!

So a couple of questions…

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.
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.
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?
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:

Meagan_Caesar · ‎Jun 07, 2022

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!

Kamille_Parks · ‎Jun 07, 2022

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.

Alexey_Gusev · ‎Jun 08, 2022

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)

Meagan_Caesar · ‎Jun 09, 2022

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!

Meagan_Caesar · ‎Jun 09, 2022

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 :slightly_smiling_face: