# IF Formula for Date Ranges

Options
✭✭✭✭
edited 03/10/23

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!

Tags:

• ✭✭✭✭✭✭
Options

@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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Options

Check out some of these other threads:

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

• ✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
edited 03/10/23
Options

@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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

@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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭
Options

Thank you so much, Jeff! This helped immensely.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!