IF Formula for Date Ranges
Hi,
I am looking to create a formula that would automatically assign a quarter (Q1, Q2, Q3 or Q4) based on the start date entered in another column. I would need to have the formula evaluate the date to see if it falls within a certain range (example, Feb 1  Apr 30 would populate Q1 etc).
How would I write out the date range portion of this formula? Thank you for your help!
Best Answer

@ClaireWallace e Your syntax is off in both. This is the syntax for IF with OR:
=IF(OR(logical expression1, logical expression2, logical epression3...), value_if_true, value_if_false)
The logical expressions must be contained within OR, as I show above:
=IF(OR([Month Formula]@row >= 2, [Month Formula]@row < 5), "Q1")
Now, when nesting IFs as the "value_if_false" of the IF before them, at the very end of the formula you need to be sure you are closing off each IF; So when you have an IF with three additional nested IFs after it, you need 4 parentheses at the very end:
=IF(OR([Month Formula]@row >= 2, [Month Formula]@row < 5), "Q1", IF(OR([Month Formula]@row >= 5, [Month Formula]@row < 7), "Q2", IF(OR([Month Formula]@row >= 7, [Month Formula]@row < 10), "Q3", IF(OR([Month Formula]@row >= 10, [Month Formula]@row = 1), "Q4"))))
Now, if you want to avoid using the [Month Formula] column, we can do that too:
=IF(OR(MONTH([Start Date]@row) >= 2, MONTH([Start Date]@row) < 5), "Q1", IF(OR(MONTH([Start Date]@row) >= 5, MONTH([Start Date]@row) < 7), "Q2", IF(OR(MONTH([Start Date]@row) >= 7, MONTH([Start Date]@row) < 10), "Q3", IF(OR(MONTH([Start Date]@row) >= 10, MONTH([Start Date]@row) = 1), "Q4"))))
Lastly, always make sure the system shows your first and last parentheses as the same color.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

Hey @ClaireWallace
Check out some of these other threads:
 Fiscal Year and Quarter Designation
 Formula to assign fiscal quarter not working
 IF/OR Quarters Formula
 Quarter Formula
If none of these have helped, it would be useful to know what the column name is for your start date column and when exactly your quarters are.
Thanks!
Genevieve

Hi Genevieve,
Thanks for your response. I am getting closer but I'm encountering an Unparseable response. Here is what my formula is:
=IF(OR([Month Formula]@row = "2"), [Month Formula]@row >"2", [Month Formula]@row <"5"), "Q1", IF(OR([Month Formula]@row = "5", [Month Formula]@row <"5", [Month Formula]@row <"7"), "Q2" , IF(OR([Month Formula]@row="7",[Month Formula]@row > "7",[Month Formula]@row < "10"),"Q3", IF(OR([Month Formula]@row = "10", [Month Formula]@row >"10", [Month Formula]@row ="1"), "Q4")
Q1: Feb  April 30
Q2: May  July 30
Q3: AugOct 31
Q4: Nov Jan 31
Thank you!

@ClaireWallace  Remove the extraneous end parentheses in the red circle. Add three end parentheses' to the end of the formula to close off all the nested IFs. And you probably want to remove the quotes from around your number criteria ( [Month Formula]@row < 2 instead of <"2" )
You can also combine some of your criteria: [Month Formula]@row = 2, [Month Formula]@row > 2 can be replaced by [Month Formula]@row >= 2
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Hi Jeff,
Thanks for the advice. I have made these changes & I am attempting to just enter the conditions for Q1 into the formula for the time now.
I have alternatively been referencing a cell called "Start Date" where there is a date entered, and I have alternatively referenced a cell where the Month Formula is already in use and the cell contains numbers 112.
This types is returning an incorrect argument.
Version where I am referencing the cell already containing the Month Formula
=IF(OR([Month Formula]@row >= 2, [Month Formula]@row < 5, "Q1"))
The other version where I am directly referencing the Month Column is returning an unparseable response, regardless of the little tweaks I am making to the syntax. This is currently what I have.
=IF(OR(MONTH([Start Date]@row>=2, (MONTH([Start Date]@row <5), "Q1"))))
Thanks for your help!

@ClaireWallace e Your syntax is off in both. This is the syntax for IF with OR:
=IF(OR(logical expression1, logical expression2, logical epression3...), value_if_true, value_if_false)
The logical expressions must be contained within OR, as I show above:
=IF(OR([Month Formula]@row >= 2, [Month Formula]@row < 5), "Q1")
Now, when nesting IFs as the "value_if_false" of the IF before them, at the very end of the formula you need to be sure you are closing off each IF; So when you have an IF with three additional nested IFs after it, you need 4 parentheses at the very end:
=IF(OR([Month Formula]@row >= 2, [Month Formula]@row < 5), "Q1", IF(OR([Month Formula]@row >= 5, [Month Formula]@row < 7), "Q2", IF(OR([Month Formula]@row >= 7, [Month Formula]@row < 10), "Q3", IF(OR([Month Formula]@row >= 10, [Month Formula]@row = 1), "Q4"))))
Now, if you want to avoid using the [Month Formula] column, we can do that too:
=IF(OR(MONTH([Start Date]@row) >= 2, MONTH([Start Date]@row) < 5), "Q1", IF(OR(MONTH([Start Date]@row) >= 5, MONTH([Start Date]@row) < 7), "Q2", IF(OR(MONTH([Start Date]@row) >= 7, MONTH([Start Date]@row) < 10), "Q3", IF(OR(MONTH([Start Date]@row) >= 10, MONTH([Start Date]@row) = 1), "Q4"))))
Lastly, always make sure the system shows your first and last parentheses as the same color.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Thank you so much, Jeff! This helped immensely.
Help Article Resources
Categories
Check out the Formula Handbook template!