When leveraging Airtable with Salesforce or other CRM data, you sometimes need to define the fiscal period if the year is offset.

Fiscal Period

This based on on February (Month 2: >=2), which is a Fiscal offset by one month (-1).

1
2
3
4
5
6

"FY" & IF(
  VALUE(DATETIME_FORMAT({Close Date},'M'))>=2,
  DATETIME_FORMAT(DATEADD({Close Date},1,'year'),'YY'),
  DATETIME_FORMAT({Close Date},'YY')
) &
"Q"&ROUNDUP(VALUE(DATETIME_FORMAT(DATEADD({Close Date},-1,'months'),"M"))/3,0)

Pull-In Fiscal Quarter

With the fiscal period defined, you can leverage this formula for fields that have potential to label an opportunity as an a pull-in, such as if it’s within the beginning of the next fiscal quarter (such as within the first 1 day(s) (< 1) of the next quarter). (replace >=2 based on fiscal month start, and -1 with the month offset)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

IF(
  DATETIME_DIFF({Close Date},DATETIME_PARSE(IF(
    VALUE(DATETIME_FORMAT({Close Date},'M'))>=2,
    DATETIME_FORMAT({Close Date},"YYYY"),
    DATETIME_FORMAT(DATEADD({Close Date},-1,"year"),"YYYY")
  )&"-"&(ROUNDUP(VALUE(DATETIME_FORMAT(DATEADD({Close Date},-1,"month"),"M"))/3,0)*3-1)&"-01","YYYY-M-DD"),'days')
  < 1,
  "FY"&IF(
    VALUE(DATETIME_FORMAT(DATEADD({Close Date},-1,"month"),'M'))>=2,
    DATETIME_FORMAT(DATEADD(DATEADD({Close Date},-1,"month"),1,"year"),"YY"),
    DATETIME_FORMAT(DATEADD({Close Date},-1,"month"),"YY")
  )&"Q"&ROUNDUP(VALUE(DATETIME_FORMAT(DATEADD(DATEADD({Close Date},-1,"month"),1-2,'months'),"M"))/3,0)&" (Pull-In)"
  ,
  "FY"&IF(
    VALUE(DATETIME_FORMAT({Close Date},'M'))>=2,
    DATETIME_FORMAT(DATEADD({Close Date},1,"year"),"YY"),
    DATETIME_FORMAT({Close Date},"YY")
  )&"Q"&ROUNDUP(VALUE(DATETIME_FORMAT(DATEADD({Close Date},1-2,'months'),"M"))/3,0)
)