Formula to derive fiscal quarter from a date
I've been working on a formula to try to derive a fiscal quarter from a date value.
Depending upon the data value, I want the output to be in the follow format:
Working previously with a Smartsheet help agent, I've added a couple of helper columns but I'm not yet getting it to work. The columns I'm work with are:
Target Delivery: MM/DD/YY
Helper (Fiscal) Year: FY21- <---- Here is my problem!
Helper (Fiscal) Quarter: Q1 (or Q2....) =IF(MONTH([Target Delivery]@row) = 1, "Q4", IF(MONTH([Target Delivery]@row) > 10, "Q4", IF(MONTH([Target Delivery]@row) > 7, "Q3", IF(MONTH([Target Delivery]@row) > 4, "Q2", "Q1")))). <---- This works fine.
Fiscal Quarter: FY21-Q1 <---- Here I will just concatenate the two helper column outputs
I cannot seem to get the Helper Year column to work. I'm starting with the following formula but need more help:
=IF(ISDATE([Target Delivery]@row), IF(MONTH([Target Delivery]@row) = 1, YEAR([Target Delivery]@row), IF(MONTH([Target Delivery]@row) > 1, YEAR([Target Delivery]@row) + 1), IF(MONTH([Target Delivery]@row) > 13, "")))
Any ideas? I've attached a visual of my spreadsheet if that helps.
Best Answer
-
My apologies. I had an extra closing parenthesis tucked in there. Try this one...
=IF(ISDATE([Target Delivery]@row), "FY" + IF(MONTH([Target Delivery]@row) = 1, RIGHT(YEAR([Target Delivery]@row), 2), RIGHT(YEAR([Target Delivery]@row) + 1, 2)) + "-Q" + IF(OR(MONTH([Target Delivery]@row) = 1, MONTH([Target Delivery]@row) >= 11), 4, IF(MONTH([Target Delivery]@row) >= 8, 3, IF(MONTH([Target Delivery]@row) >= 5, 2, 1))))
Answers
-
Try this in a single column...
=IF(ISDATE([Target Delivery]@row), "FY" + IF(MONTH([Target Delivery]@row) = 1, RIGHT(YEAR([Target Delivery]@row), 2), RIGHT(YEAR([Target Delivery]@row) + 1), 2)) + "-Q" + IF(OR(MONTH([Target Delivery]@row) = 1, MONTH([Target Delivery]@row)>= 11), 4, IF(MONTH([Target Delivery]@row)>= 8, 3, IF(MONTH([Target Delivery]@row)>= 5, 2, 1))))
-
Looks promising, but I get a #UNPARSEABLE error.
-
My apologies. I had an extra closing parenthesis tucked in there. Try this one...
=IF(ISDATE([Target Delivery]@row), "FY" + IF(MONTH([Target Delivery]@row) = 1, RIGHT(YEAR([Target Delivery]@row), 2), RIGHT(YEAR([Target Delivery]@row) + 1, 2)) + "-Q" + IF(OR(MONTH([Target Delivery]@row) = 1, MONTH([Target Delivery]@row) >= 11), 4, IF(MONTH([Target Delivery]@row) >= 8, 3, IF(MONTH([Target Delivery]@row) >= 5, 2, 1))))
-
That is a thing a beauty!! And you make it look so simple. THANK YOU!
-
Many years late, but hoping you might be able to help. Our fiscal year is July 1-June 30 (financial fiscal) and I'm definitely struggling to make this adjust the year and quarters appropriately.
Q1= July, Aug, Sept (7, 8, 9) and FY+1
Q2= Oct, Nov, Dec (10, 11, 12) and FY+1
Q3= Jan, Feb, Mar (1, 2, 3)
Q4= Apr, May, June (4,5,6)
Thanks!
-
-
July 2025 dates would be FY 26
-
@brittney.krahn Give this a go:
=IF([Date Column]@row <> "", "FY" + RIGHT(YEAR([Date Column]@row) + IF(MONTH([Date Column]@row) >= 7, 1, 0), 2) + " Q" + IF(MONTH([Date Column]@row) >= 10, 2, IF(MONTH([Date Column]@row >= 7, 1, IF(MONTH([Date Column]@row >= 4, 4, 3)))
-
It is coming back as #Invalid Operation. The earlier formula works, just the quarters and fiscal year break are incorrect. Not sure if that is helpful
-
I missed a closing parenthesis. Sorry about that. Give this a try:
=IF([Date Column]@row <> "", "FY" + RIGHT(YEAR([Date Column]@row) + IF(MONTH([Date Column]@row) >= 7, 1, 0), 2) + " Q" + IF(MONTH([Date Column]@row) >= 10, 2, IF(MONTH([Date Column]@row >= 7, 1, IF(MONTH([Date Column]@row) >= 4, 4, 3)))
Help Article Resources
Categories
Check out the Formula Handbook template!