IF ERROR function - Help request.
Hi guys, I'm working with the following formula:
=IF(MONTH([Start Date]@row) = 2, "February", IF(MONTH([Start Date]@row) = 1, "January", IF(MONTH([Start Date]@row) = 3, "March", IF(MONTH([Start Date]@row) = 4, "April", IF(MONTH([Start Date]@row) = 5, "May", IF(MONTH([Start Date]@row) = 6, "June", IF(MONTH([Start Date]@row) = 7, "July", IF(MONTH([Start Date]@row) = 8, "August", IF(MONTH([Start Date]@row) = 9, "September", IF(MONTH([Start Date]@row) = 10, "October", IF(MONTH([Start Date]@row) = 11, "November", "December")
So I can populate the month since "text" formula does not work in smartsheet.
I have a problem when trying to combine it with the "IF ERROR" fuction.
After entering the IF ERROR, does not populate information, it says #INVALID DATA TYPE only.
How should I enter the formula?
Thanks so munch in advance!
Answers
-
Hi
You can put your formula in the position to be returned by the IFERROR if there is no error. The syntax for IFERROR is
=IFERROR("do this if no error", "do this if error")
So in your case, assuming you want to return nothing if there is an error, it looks like:
=IFERROR(IF(MONTH([Start Date]@row) = 2, "February", IF(MONTH([Start Date]@row) = 1, "January", IF(MONTH([Start Date]@row) = 3, "March", IF(MONTH([Start Date]@row) = 4, "April", IF(MONTH([Start Date]@row) = 5, "May", IF(MONTH([Start Date]@row) = 6, "June", IF(MONTH([Start Date]@row) = 7, "July", IF(MONTH([Start Date]@row) = 8, "August", IF(MONTH([Start Date]@row) = 9, "September", IF(MONTH([Start Date]@row) = 10, "October", IF(MONTH([Start Date]@row) = 11, "November", "December"))))))))))), "")
Your formula just slots into the "do this if no error" position.
The "" at the end returns nothing if there is an error.
As an aside, if you don't mind have 3 character month names instead of the full name, there is a very quick way to do it, which will save you 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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!