Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

HELP! formula for showing Quarter and Fiscal Year

Topic Labels: Formulas
Solved
Jump to Solution
219 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Chi_S
4 - Data Explorer
4 - Data Explorer

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!
1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Does this look right?

Screenshot 2025-01-23 at 8.34.38 AM.png

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')
)

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

Does this look right?

Screenshot 2025-01-23 at 8.34.38 AM.png

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.