Formula Text field populating based on date range

Question

I am working on a task scheduler for my team that would automate the task assignments. Tasks are based on season. How can I automate the Season field using date ranges that exclude a year. As of now this is the formula I use:

If([Date Assigned] > Date(2024,10,15) and [Date Assigned] < Date(2024,12,31) ,"Aep",
If ([Date Assigned] > Date(2025,1,1) and [Date Assigned] < Date(2025,2,28) ,"Aep","Non-AEP"))

This gets what I need - this year. But if we use this going forward I will need to go in and change the dates to adapt for next year. How would Month(ToDate(Date Assigned)) or something similar be used to accomplish this goal regardless of year?

Top Step · Answer

You can drop the year completely by comparing just the month and day. Turn the date into a number like 1015 for October 15 and compare against that:

var Number md = Month([Date Assigned]) * 100 + Day([Date Assigned]);
If(md >= 1015 or md <= 228, "AEP", "Non-AEP")

Because the season wraps past New Year, use “or” instead of “and”. Swap in your exact cutoff days, and if your real window has a gap in it, just add another range with another “or”.

It works the same every year, so there’s nothing to update in January.

Sign up

Login to the community