Skip to main content

Hi everyone,

I desperately need help as this formula is driving me crazy! Basically the formula works except for Jan - Feb and March 25 and shows them as Q2 24 instead of Q$ 24.

 

This is the fiscal breakdown

  • Q1: april24,may24, juen24 ->  Fiscal year (e.g., for May 24, Q1 FY’24)
  • Q2: July24, August24, September24 → Fiscal Year (e.g., for July 2024, Q2 FY'24)
  • Q3: October24, November24, December24 → Fiscal Year (e.g., for October 2024, Q3 FY'24)
  • Q4: January25, February25, March25 → Fiscal Year (e.g., for January 2025, Q4 FY'24)

 Here is the formula:

"Q" & ROUNDUP(
  (MOD(MONTH({Month}) - 4, 12) + 1) / 3,
  0
) & " FY'" & IF(
  MONTH({Month}) >= 4,
  DATETIME_FORMAT({Month}, 'YY'),
  DATETIME_FORMAT(DATEADD({Month}, -1, 'year'), 'YY')
)

Thank you in advance!

Does this look right?

SWITCH(
MONTH(Date),
4, 'Q' & 1 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
5, 'Q' & 1 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
6, 'Q' & 1 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
7, 'Q' & 2 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
8, 'Q' & 2 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
9, 'Q' & 2 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
10, 'Q' & 3 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
11, 'Q' & 3 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
12, 'Q' & 3 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
1, 'Q' & 4 & ' FY' & DATETIME_FORMAT(DATEADD(Date, -1, 'years'), 'YY'),
2, 'Q' & 4 & ' FY' & DATETIME_FORMAT(DATEADD(Date, -1, 'years'), 'YY'),
3, 'Q' & 4 & ' FY' & DATETIME_FORMAT(DATEADD(Date, -1, 'years'), 'YY')
)

 

Does this look right?

SWITCH(
MONTH(Date),
4, 'Q' & 1 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
5, 'Q' & 1 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
6, 'Q' & 1 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
7, 'Q' & 2 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
8, 'Q' & 2 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
9, 'Q' & 2 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
10, 'Q' & 3 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
11, 'Q' & 3 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
12, 'Q' & 3 & ' FY' & DATETIME_FORMAT(Date, 'YY'),
1, 'Q' & 4 & ' FY' & DATETIME_FORMAT(DATEADD(Date, -1, 'years'), 'YY'),
2, 'Q' & 4 & ' FY' & DATETIME_FORMAT(DATEADD(Date, -1, 'years'), 'YY'),
3, 'Q' & 4 & ' FY' & DATETIME_FORMAT(DATEADD(Date, -1, 'years'), 'YY')
)

 


Thank you so much!!!! BLESS YOU.

Reply


Terms & Conditions