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