Formula to derive fiscal quarter from a date

Guaca Mohle

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.

Screen Shot 2020-12-01 at 10.20.05 AM.png

Paul Newcome

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

Paul Newcome

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

Guaca Mohle

Looks promising, but I get a #UNPARSEABLE error.

Paul Newcome

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

Guaca Mohle

That is a thing a beauty!! And you make it look so simple. THANK YOU!

Paul Newcome

Happy to help. 👍️

brittney.krahn

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!

Paul Newcome

@brittney.krahn So would July 15th of 2025 be FY25 or FY26?

brittney.krahn

July 2025 dates would be FY 26

Paul Newcome

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

brittney.krahn

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

Paul Newcome

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