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 Answers

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

You have a syntax problem. Each MONTH function within the OR needs to be closed off, and then each OR statement needs to be closed off, before you get to the value if true portion. This is because you're telling the system to determine the numeric MONTH for [Final Deadline]@row and evaluate if that value is equal to 9, then do the same for 10 and 11  and then if any of those is true, set the cell value to "Initiation Phase".
For instance, in the first clause, you need a green close parentheses before the = 9, an orange one before the = 10, and a light blue one before the = 11. Once you add these, the close parentheses in pink after the =11 will turn pink because it is closing off the OR function.
=IF(OR(MONTH([Final Deadline]@row) = 9, MONTH([Final Deadline]@row) = 10, MONTH([Final Deadline]@row) = 11), "Initiation Phase", …
Do the same for the remaining clauses. At the very end, I think you should have only 3 close parentheses, with the last one matching the blue color of the very first open parentheses.
You could also shorten this formula up a good bit if you want to by using "greater than or equal to" and "lesser than or equal to" operators:
=IF(MONTH([Final Deadline]@row) >= 9, "Initiation Phase", IF(MONTH([Final Deadline]@row) <= 2, "Planning Phase", IF(AND(MONTH([Final Deadline]@row) >= 3, MONTH([Final Deadline]@row) <= 8), "Execution Phase")))
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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

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.

Hi @Jeff Reisman I have tried following your suggestions to build out an IF formula that populates the project phase that each task falls within, based on the deadline that it is due. There may be an easier way to do this but here is what I have so far and I am getting an Incorrect Argument error.

You have a syntax problem. Each MONTH function within the OR needs to be closed off, and then each OR statement needs to be closed off, before you get to the value if true portion. This is because you're telling the system to determine the numeric MONTH for [Final Deadline]@row and evaluate if that value is equal to 9, then do the same for 10 and 11  and then if any of those is true, set the cell value to "Initiation Phase".
For instance, in the first clause, you need a green close parentheses before the = 9, an orange one before the = 10, and a light blue one before the = 11. Once you add these, the close parentheses in pink after the =11 will turn pink because it is closing off the OR function.
=IF(OR(MONTH([Final Deadline]@row) = 9, MONTH([Final Deadline]@row) = 10, MONTH([Final Deadline]@row) = 11), "Initiation Phase", …
Do the same for the remaining clauses. At the very end, I think you should have only 3 close parentheses, with the last one matching the blue color of the very first open parentheses.
You could also shorten this formula up a good bit if you want to by using "greater than or equal to" and "lesser than or equal to" operators:
=IF(MONTH([Final Deadline]@row) >= 9, "Initiation Phase", IF(MONTH([Final Deadline]@row) <= 2, "Planning Phase", IF(AND(MONTH([Final Deadline]@row) >= 3, MONTH([Final Deadline]@row) <= 8), "Execution Phase")))
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!

The shorter formula you gave worked perfectly, thank you very much @Jeff Reisman !

@Jeff Reisman last question  is there a function that allows the formula to take the year into account? During the month of September I usually close out 1 project, but also track tasks to initiate the next project. I have September 2024 tasks and September 2025 tasks in my Smartsheet. Is there a formula that has the capability to distinguish between the month AND year, or only month?

@DoyleMegan You can add more criteria into the formulas using the YEAR function. It works just like MONTH. See the links in my signature for the Formula and Error Message help pages.
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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 218 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!