Nested IF Formula Issue - Returning UNPARSEABLE
I have a formula that is returning the month name for a specific date column (Next Audit Period) that is returning an UNPARSEABLE error and I can't figure out why. The formula is below.
= IF(MONTH([Next Audit Period]@row) = 1, "Jan”, IF(MONTH([Next Audit Period]@row) = 2, "Feb”, IF(MONTH([Next Audit Period]@row) = 3, "Mar”, IF(MONTH([Next Audit Period]@row) = 4, "Apr”, IF(MONTH([Next Audit Period]@row) = 5, "May”, IF(MONTH([Next Audit Period]@row) = 6, "Jun”, IF(MONTH([Next Audit Period]@row) = 7, "Jul”, IF(MONTH([Next Audit Period]@row) = 8, "Aug”, IF(MONTH([Next Audit Period]@row) = 9, "Sep”, IF(MONTH([Next Audit Period]@row) = 10, "Oct”, IF(MONTH([Next Audit Period]@row) = 11, "Nov”, IF(MONTH([Next Audit Period]@row) = 12, "Dec”, “”))))))))))))
Best Answer
-
It is your quotes. The slanted quotes come from programs such as Word. They are called "smart quotes" and (ironically enough) are not recognized by Smartsheet. Try retyping the formula either here in the community, in the sheet itself, or in a text editor such as Notepad. You should notice that the quotes are now straight up and down. These are the quotes you need for Smartsheet.
Answers
-
It is your quotes. The slanted quotes come from programs such as Word. They are called "smart quotes" and (ironically enough) are not recognized by Smartsheet. Try retyping the formula either here in the community, in the sheet itself, or in a text editor such as Notepad. You should notice that the quotes are now straight up and down. These are the quotes you need for Smartsheet.
-
Hi Bethany!
You can take away MONTH and just add "" around the numbers (text value you are evaluating). (i.e. - = "1", ...) 😊
Another method to accomplish this would be to create 2 month helper columns on this sheet (that you can hide) or some arbitrary helper sheet with two columns, one being the number of the month and the next having the month abbrv. you are trying to use.
Number Month
1 Jan
2 Feb
You can then easily use the VLOOKUP to evaluate the cell in your Audit Period column against the month helper column, and return the value of the column next to it with the month abbreviation.
It is sometimes nice to have a whole other sheet with these columns so you can use it as a reference for multiple sheets.
-
@Paul Newcome that worked, thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!