
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 01, 2020 10:34 PM
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!!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 02, 2020 05:15 PM
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:
