Comment Post Options
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 22, 2025 05:46 AM
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!
Solved! Go to Solution.
1 Solution
Accepted Solutions
Solved
See Solution in Thread
Comment Post Options
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 22, 2025 04:35 PM
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')
)
Reply
2 Replies 2
Solved
See Solution in Thread
Comment Post Options
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 22, 2025 04:35 PM
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')
)
Reply
Comment Post Options
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 23, 2025 01:32 AM
Thank you so much!!!! BLESS YOU.
Reply
