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

Hemi_Phillips · ‎Dec 01, 2020

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.

e.g.
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!!

Julian_E_Post · ‎Dec 02, 2020

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:

IF( 
	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: