IF Formula Unparseable

FormulaPain

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.

Screenshot 2024-02-11 at 12.15.56.png

KPH

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.

KPH

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.

KPH

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 3-character 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.

FormulaPain

@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!

KPH

Great to hear! Hopefully, the time you save with the MID function makes up for the time you wasted on the quotation marks.