IF Formula for Date Ranges

Options
ClaireWallace
ClaireWallace ✭✭✭✭
edited 03/10/23 in Formulas and Functions

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:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    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

    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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @ClaireWallace

    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

  • ClaireWallace
    ClaireWallace ✭✭✭✭
    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!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    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

    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!

  • ClaireWallace
    ClaireWallace ✭✭✭✭
    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"))))


    Thanks for your help!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    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

    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!

  • ClaireWallace
    ClaireWallace ✭✭✭✭
    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!