# Need to create a column that translates "End Date" Month to Quarter

Options
edited 01/23/21

Trying to create a column that tells me which quarter an item will be ready in the future. I can get the Month of the Date cell easy enough but can't figure out how to do a value range that works.

This works =IF(MONTH([End Date]@row) <= 3, "Qtr 1", "Nope") and if the End Date is 1-3 it will say "Qtr 1"

Is there any way to do =IF(MONTH([End Date]@row) = 1-3, "Qtr 1", "Nope")?

Any help is appreciated.

Tags:

Options

Nevermind. Looks like i can just get around this with a nested IF statement.

=IF(MONTH([End Date]@row) <= 3, "Qtr 1", IF(MONTH([End Date]@row) <= 6, "Qtr 2", IF(MONTH([End Date]@row) <= 9, "Qtr 3", IF(MONTH([End Date]@row) <= 12, "Qtr 4"))))

Options

Nevermind. Looks like i can just get around this with a nested IF statement.

=IF(MONTH([End Date]@row) <= 3, "Qtr 1", IF(MONTH([End Date]@row) <= 6, "Qtr 2", IF(MONTH([End Date]@row) <= 9, "Qtr 3", IF(MONTH([End Date]@row) <= 12, "Qtr 4"))))

• Employee
Options

I'm glad you were able to find a solution! Thanks for posting it in the Community.

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Options

Thanks Derek,

I was trying the same formula but I am getting an #unparseable error.

The formula I entered is:

=if([Start Month]@row) <=3, "Q1", if([Start Month]@row) <=6, "Q2", if([Start Month]@row) <=9, "Q3", if([Start month]@row) <=12, "Q4"))))

• Options

This was my final formula. =IF(MONTH([End Date]@row) <= 3, "Qtr 1 " + YEAR([End Date]@row), IF(MONTH([End Date]@row) <= 6, "Qtr 2 " + YEAR([End Date]@row), IF(MONTH([End Date]@row) <= 9, "Qtr 3 " + YEAR([End Date]@row), IF(MONTH([End Date]@row) <= 12, "Qtr 4 " + YEAR([End Date]@row)))))

I noticed that you are using [Start Month] instead of the variable (MONTH[Start Month]), is Start Month a variable integer you can use in a formula like this or possibly is it a string?

• Employee
Options

To add to @Derek Lohman's advice, it looks like you are closing off each IF statement right away, instead of waiting to add the closing parentheses at the end of the formula.

=IF([Start Month]@row <=3, "Q1", IF([Start Month]@row <=6, "Q2", IF([Start Month]@row <=9, "Q3", IF([Start month]@row <=12, "Q4"))))

October 8 - 10, Seattle, WA | Register now

• edited 06/30/21
Options

Thank you Derek and Genevieve,

I wasn't clear in my original request.

I have a column titled "Start Month" where I have dates in MM/DD/YY format.

I entered this formula

=IF(MONTH([Start Month]@row) <= 3, "Q1"), if([Start Month]@row <=6, "Q2"), if([Start Month]@row <=9, "Q3"), if([Start Month]@row) <=12, "Q4"))))

and it shows me #unparseable

I know it is something silly I am missing. I will appreciate any help from the gurus here.

• Options

Prashant,

For starters, you will need the variable MONTH in front of each if statement, so:

=IF(MONTH([Start Month]@row)<=3,"Q1", IF(MONTH([Start Month]@row)<=6,"Q2", IF(MONTH([Start Month]@row)<=9,"Q3", IF(MONTH([Start Month]@row)<=12,"Q4"))))

See if that gives you a better response. Make sure the IF statements are nested correctly and not closed.

• Options

Thanks a tonne! It works!!

• Options

This will save me so much time.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!