Formula to derive fiscal quarter from a date

Options

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!