Simpler Fiscal Year and Quarter Formula
Hi,
Admittedly, I'm very new when it comes to spreadsheet formulas. I was given this formula for fiscal year and quarter calculations but I was wondering if there was a simpler way to do it. Also, it's in a calendar year format and our fiscal year is July 1 to June 30. I appreciate your help.
=IF(ISDATE([Completion Target]@row), "FY" + IF(MONTH([Completion Target]@row) = 1, RIGHT(YEAR([Completion Target]@row), 2), RIGHT(YEAR([Completion Target]@row) + 1, 2)) + "-Q" + IF(OR(MONTH([Completion Target]@row) = 1, MONTH([Completion Target]@row) >= 11), 4, IF(MONTH([Completion Target]@row) >= 8, 3, IF(MONTH([Completion Target]@row) >= 5, 2, 1))))
Thanks!
Ashley Ferguson, PMP
IS Project Manager | St. Joseph’s/Candler
Answers
-
Hi Ashley, we use this for the fiscal year.
="FY" + RIGHT(IF(MONTH(TODAY()) > 6, YEAR(TODAY()) + 1, YEAR(TODAY())), 2)
Which shows (for this fiscal year) FY22.
So
=IF(ISDATE([Completion Target]@row), "FY" +RIGHT(IF(MONTH(TODAY()) > 6, YEAR(TODAY()) + 1, YEAR(TODAY())), 2) + "-Q" + IF(OR(MONTH([Completion Target]@row) = 1, MONTH([Completion Target]@row) >= 11), 4, IF(MONTH([Completion Target]@row) >= 8, 3, IF(MONTH([Completion Target]@row) >= 5, 2, 1))))
-
Thank you so much. Any thoughts on what I need to adjust so it reflects our quarters properly?
Q1: July/August/September
Q2: October/November/December
Q3: January/February/March
Q4: April/May/June
Ashley Ferguson, PMP
IS Project Manager | St. Joseph’s/Candler
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 75 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!