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

Derek Lohman

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.

Derek Lohman

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

Derek Lohman

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

Genevieve P.

Hi @Derek Lohman

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

Cheers,

Genevieve

Prashant Thankappan

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

Derek Lohman

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?

Genevieve P.

https://community.smartsheet.com/discussion/comment/296601#Comment_296601

Hi @Prashant Thankappan

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.

Try this instead:

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

Prashant Thankappan

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.

Derek Lohman

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.

Prashant Thankappan

@Derek Lohman

Thanks a tonne! It works!!

Prashant Thankappan

This will save me so much time.