Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Quarters in fiscal years formula
Hey all,
I'm trying to write a formula that will return the quarter and fiscal year (FY) of a given date.
Our fiscal year is October - September. So for the first quarter, the FY doesn't match the calendar year (CY).
So far I've been able to execute this for one CY, but if I try and add a second CY, I get back a blank cell.
The formula I've been using is below:
=IF(YEAR([Date]1) = 2017, IF(MONTH([Date]1) = 1, "Q2 2017", IF(MONTH([Date]1) = 2, "Q2 2017", IF(MONTH([Date]1) = 3, "Q2 2017", IF(MONTH([Date]1) = 4, "Q3 2017", IF(MONTH([Date]1) = 5, "Q3 2017", IF(MONTH([Date]1) = 6, "Q3 2017", IF(MONTH([Date]1) = 7, "Q4 2017", IF(MONTH([Date]1) = 8, "Q4 2017", IF(MONTH([Date]1) = 9, "Q4 2017", IF(MONTH([Date]1) = 10, "Q1 2018", IF(MONTH([Date]1) = 11, "Q1 2018", IF(MONTH([Date]1) = 12, "Q1 2018", "TBD")))))))))))))
That part works great.
But if, instead of TBD, I insert the formula again but with "IF(YEAR([Date]1)=2018", I just get back a blank cell.
Any suggestions? Is there a simpler way to do this?
Comments
-
Hi Kathleen,
Are you opposed to seeing the fiscal year/quarter in the Gantt chart instead? You can change the Gantt timeline display with the steps in the help article: https://help.smartsheet.com/articles/765755-modifying-project-settings#timelinedisplay
Otherwise, your formula's going to get quite large.
IF statement logic, from a broad perspective, is IF a condition is met return something, if it isn't met, return something else. If you haven't specified a "something else" then the formula returns nothing.
It's likely that since you replaced your "TBD" with another copy of your formula, then your formula may not have a "return this if the condition's aren't met" value.
From what I'm seeing, your formula logic is set up as: IF the YEAR of Date1 is 2017, run this nested IF function—your series of nested IF(MONTH(Date1) = x, "Qx 201x") statements—if this condition isn't met, return TBD.
One way to consolidate this type of formula is to stick your related conditions in OR functions, since it appears that you want to return one text value if one of a few conditions are met:
=IF(OR(MONTH(Date1) = 1, MONTH(Date1) = 2, MONTH(Date1) = 3), "Q2 2017", IF(OR(..., ..., ...), "Q3 2017", IF(OR(...etc...), "Q4 2017", "TBD")))
This will help your formula run faster and will make it a little shorter.
More on our functions can be found in the help center: https://help.smartsheet.com/functions
-
You can further simplify the formula to capture the year automatically. This will also take the last 3 months of the year and make them Q1 of the following year (12/1/17 = Q1 2018)
=IF(OR(MONTH([Date]@row) = 1, MONTH([Date]@row) = 2, MONTH([Date]@row) = 3), "Q2 " + YEAR([Date]@row), IF(OR(MONTH([Date]@row) = 4, MONTH([Date]@row) = 5, MONTH([Date]@row) = 6), "Q3 " + YEAR([Date]@row), IF(OR(MONTH([Date]@row) = 7, MONTH([Date]@row) = 8, MONTH([Date]@row) = 9), "Q4 " + YEAR([Date]@row), IF(OR(MONTH([Date]@row) = 10, MONTH([Date]@row) = 11, MONTH([Date]@row) = 12), "Q1 " + (VALUE(YEAR([Date]@row) + 1))))))
