IF Formula for Date Ranges

ClaireWallace

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!

Jeff Reisman

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

Jeff Reisman

@DoyleMegan

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")))

Genevieve P.

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

ClaireWallace

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: Aug-Oct 31

Q4: Nov- Jan 31

Thank you!

Jeff Reisman

@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

ClaireWallace

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 1-12.

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!

Jeff Reisman

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

ClaireWallace

Thank you so much, Jeff! This helped immensely.

DoyleMegan

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.

Jeff Reisman

@DoyleMegan

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")))

DoyleMegan

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

DoyleMegan

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

Jeff Reisman

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