IF Formula Unparseable
I’ve read through many posts to include the formula guide, but I cannot figure out what I’m doing on a very simple IF formula. Let’s start with the first challenge.
If a number is equal to a defined number, I want to display text. To do so, I’m using the IF function. More specifically, I want a column to display the month based on a numeric value, 1 = JAN, 2 = FEB etc.
All formulas return the result of #UNPARSEABLE. The attached screenshow shows the result of using those formulas, the Formula Guidance column shows the Formulas attempted but I’ve omitted the equal sign to display the full text of the formula. I’m sure I’m missing something simple but I cannot figure it out. Please help.
Best Answer

Hi @FormulaPain
You aren't very far off. Formulas 2 and 3 look good. The only problem I can see is that it looks like you have "smart" quotation marks  they are curved, not straight. Are you copying them? Can you try entering a quote mark directly on the keyboard, or pasting your formula into a simple text editor, such as notepad, and then into smartsheet?
Formula 1 is great but you can't extract the month number from the column that holds the month number, you need to use the date column. So instead of
IF(MONTH(MONTH@row)=2
you need
IF(MONTH(Date@row)=2
That's it. Those are the only problems I can see.
Here I have mimicked what you did. The formula is in the MONTH TEST column and the Formula Guidance column shows the formula without the = sign.
The only changes are to the column name in formula 1 and the quotation marks.
I hope this helps get you unstuck.
Answers

Hi @FormulaPain
You aren't very far off. Formulas 2 and 3 look good. The only problem I can see is that it looks like you have "smart" quotation marks  they are curved, not straight. Are you copying them? Can you try entering a quote mark directly on the keyboard, or pasting your formula into a simple text editor, such as notepad, and then into smartsheet?
Formula 1 is great but you can't extract the month number from the column that holds the month number, you need to use the date column. So instead of
IF(MONTH(MONTH@row)=2
you need
IF(MONTH(Date@row)=2
That's it. Those are the only problems I can see.
Here I have mimicked what you did. The formula is in the MONTH TEST column and the Formula Guidance column shows the formula without the = sign.
The only changes are to the column name in formula 1 and the quotation marks.
I hope this helps get you unstuck.

As an extra credit followup to the above...
It looks like you are going to be creating a series of IF functions to display the month name from the Date in your date column. That is a great approach but there is a little trick you can do if you only want the 3character month name. This will save you from having to nest IF functions.
This formula here...
=MID("JanFebMarAprMayJunJulAugSepOctNovDec", (MONTH(Date@row) * 3)  2, 3)
takes the text string JanFebMarAprMayJunJulAugSepOctNovDec
And extracts a middle part of it using a little math based on the month number
This part here defines the start position within the chunk of text to extract:
(MONTH(Date@row) * 3)  2
It says take the month number from the date and multiply it by 3, then subtract 2.
 So January is (1x3)2 = 1
 February is (2x3)2 = 4
 March is (3x3)2 = 7
And if you look at the text string "Jan" starts at position 1, "Feb" starts at position 4, "Mar" starts at position 7...
The
,3
at the end of the function says to extract 2 characters.
 So for a date in January, it starts at position 1 and extracts 3 characters  Jan
 For a date in February, it starts at position 4 and extracts 3 characters  Feb
 For a date in March, it starts at position 7 and extracts 3 characters  Mar
I know you didn't ask about this, but thought it was a cool and helpful tip.

@KPH That was somehow it. I tried a few different things but ultimately decided to switch from writing the formula on my Mac to my PC. The quotation marks were making it unparseable and once typed using straight quotation marks, the formula instantly worked.
Regarding the extra credit, my plan was to create a series of IF functions, but I had to solve one problem at a time. Therefore, what you've shared with me here is very relevant and helpful. Thank you for your time on both!

Great to hear! Hopefully, the time you save with the MID function makes up for the time you wasted on the quotation marks.
Help Article Resources
Categories
Check out the Formula Handbook template!