Creating dynamic quarterly formulas in Airtable without massive nested IF statements

Other Questions Airtable
1

Hey everyone! I’ve been working with Airtable for a few weeks now and I’m trying to build a formula that assigns quarters based on months. Our company uses a weird fiscal year where quarters start in September and end in August.

Right now I have a column called “Project Date” that contains values like “January-21” or “March-22”. I want to create another column that automatically shows which quarter and year this falls into. For example, if the date is “January-21”, it should display “Q2-Y2”.

The issue is that I’m building this massive nested IF formula like:

IF({Project Date}='January-21',"Q2-Y2",
IF({Project Date}='February-21',"Q2-Y2",
IF({Project Date}='March-21',"Q2-Y2", etc...

This approach means I’ll need to manually add every single month and update the years constantly. There has to be a smarter way to handle this right? Maybe using date functions or some other approach?

Any suggestions would be really helpful!

2

Laura’s right about the approach, but here’s the actual formula since I dealt with this exact thing at my last company.

First, parse that text format with DATEVALUE(“1-” & {Project Date}) to convert “January-21” into a proper date.

For fiscal quarters starting in September, use this:

"Q" & 
IF(MONTH(DATEVALUE("1-" & {Project Date})) >= 9, 
    ROUNDUP((MONTH(DATEVALUE("1-" & {Project Date})) - 8) / 3, 0), 
    ROUNDUP((MONTH(DATEVALUE("1-" & {Project Date})) + 4) / 3, 0)
) & 
"-Y" & 
IF(MONTH(DATEVALUE("1-" & {Project Date})) >= 9, 
    YEAR(DATEVALUE("1-" & {Project Date})) - 2019, 
    YEAR(DATEVALUE("1-" & {Project Date})) - 2020
)

It handles the fiscal year logic automatically. September-November = Q1, December-February = Q2, etc.

This video helped me tons when I was figuring out similar formulas:

Once it’s set up, new dates automatically calculate the right quarter without manual updates.

3

u’re overcomplicating this. first, convert the text to actual dates using datevalue(). then use month() to get the month number. after that it’s simple math - sept/oct/nov = q1, dec/jan/feb = q2, and so on. much cleaner than hardcoding every possible combination.