Calculate the season start date, based on today's date

Hello, I need help to calculate the start date of the next season based on 'today’s date and a given ‘month’ for the season start.

If Today() = 2 December 2020
And Season Start = “August” (using a select field)
The formula should calculate to 1 August 2021

If Today() = 31 July 2021
And Season Start = “August”
The formula should still calculate to 1 August 2021

Thanks in advance!!

Hi Hemi,
Airtable doesn’t have a built-in way to know that “August” is a piece of a date, but you can solve that with a switch statement:

Switch( Month,

'January', 1,
'February', 2,
'March', 3,
'April', 4,
'May', 5,
'June', 6,
'July', 7,
'August', 8,
'September', 9,
'October', 10,
'November', 11,
'December', 12


Then you can use that number to do the remaining math that you need with the Datetime_Parse function. Here’s a formula for that:

	Date >= DATETIME_PARSE(YEAR(Date)&'01' & {Month #},'YYYYDDM'),
	DATETIME_PARSE((YEAR(Date)+1)&'01' & {Month #},'YYYYDDM'),
	DATETIME_PARSE(YEAR(Date)&'01' & {Month #},'YYYYDDM')

It checks whether the first day of the season in the current year is already past. If yes, it calculates the season start for the following year, and if no, it calculates the season start for the current year.

Here is a base that shows these formulas working together:

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.