Skip to main content

Hey guys, probably an easy thing to do but I want to name an entry according to the right “quarter candidate” field without having to create a super long “IF” formula (I want to update this table without having to update the formula everytime I add a new quarter candidate to the list)

My goal : in an other base each new entry have the current date added to it, according to this date, I want to have a field with the right quarter candidate because the date is after the “input start date” and before the “Input end date” 


Thanks for your help ! 

 

 

Hey ​@Thibzim!

Would you please go through your need again in further detail? I don’t think I’m fully getting it.

In the meantime, I’d also suggest streamlining the naming of each of Quarter Candidate field by using the following formula:

 

"Q" & CEILING(MONTH({Input Start Date}) / 3) & " " & YEAR({Input Start Date})



Mike, Consultant @ Automatic Nation

Thanks for your reply Mike ! 
I’m failry new to Airtable so I’m not sure how to proceed but here is more detail on the topic : 

All year long, users are able to fill in a form to provide inputs. 
These inputs can be candidate for the future quarter roadmap improvements only if this is done during the first 2 months of the quarter (e.g a form is filled in January or February -→ Will be a candidate for Q2 improvements). If the form is filled during the last month of the quarter, it is too late to be a candidate for the next quarter and should be attributed to the following quarter. (e.g a form is filled in March → too late for Q2 improvements, will be a candidate for Q3). 

First, I tried creating a table with the corresponding dates (maybe there’s a way to create the quarter candidate field without going through this). 

 

Second, here is my “backlog of inputs” with a date of creation and a field I’m trying to create to automatically define which quarter candidate it belongs to through a formula : 

 


In other words, I want to create this automatic quarter candidate field where 

  • If form is created in Dec2024/Jan/Feb 2025→ Quarter Candidate is “Q2 2025”
  • If form is created in March/April/May 2025 → Quarter Candidate is “Q3 2025”
  • If form is created in June/July/August 2025 → Quarter Candidate is “Q3 2025”
  • If form is created in September/October/November 2025 → Quarter Candidate is “Q1 2026”
  • If form is created in December 2025/Jan/Feb 2026 → Quarter Candidate is “Q2 2026”
  • and so on

Ideally I would avoid managing this “automation” directly within the formula but rather by updating the first table of dates for each quarter but it might be easier to do it via the formula ? WDYT ?
 

Thanks a lot for your help

In other words, you need to add quarter to your date, to get next, and you also need to add month,  because last month  is too late. So, you nedd to add 4 months
 

DATEADD({Date_field}, 4 , 'months')

And format the output to show only quarter and year
(Note:  simple Q will be replaced by number. So for Q4 it will be ‘44’. That’s why \\ used as a sign ‘use next char AS IS, no replacing’)

DATETIME_FORMAT(
  DATEADD({Date_Field},4,'months'), 
  '\\QQ YYYY'
)

I would also add check field for ‘non-emptiness’ to avoid ERROR#s for empty dates
 

IF(Date_Field,  DATETIME_FORMAT(
    DATEADD(Date_Field,4,'months'), '\\QQ YYYY'))

 

Reply


Terms & Conditions