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. 👍️