#INVALID DATA TYPE error when IFs are combined
Hi all,
I've got this formula here that basically enters name of the month in the cell based on date:
=IF(MONTH([Date of Defense]@row) = 1, "a. JANUARY", IF(MONTH([Date of Defense]@row) = 2, "b. FEBRUARY", IF(MONTH([Date of Defense]@row) = 3, "c. MARCH", IF(MONTH([Date of Defense]@row) = 4, "d. APRIL", IF(MONTH([Date of Defense]@row) = 5, "e. MAY", IF(MONTH([Date of Defense]@row) = 6, "f. JUNE", IF(MONTH([Date of Defense]@row) = 7, "g. JULY", IF(MONTH([Date of Defense]@row) = 8, "h. AUGUST", IF(MONTH([Date of Defense]@row) = 9, "i. SEPTEMBER", IF(MONTH([Date of Defense]@row) = 10, "j. OCTOBER", IF(MONTH([Date of Defense]@row) = 11, "k. NOVEMBER", IF(MONTH([Date of Defense]@row) = 12, "l. DECEMBER", IF(ISBLANK([Date of Defense]@row), "m. UNKNOWN", "")))))))))))))
The formula works fine all the way down to the last argument - everytime the date is blank, it gives me #invalid data type error. Interestingly, if I isolate that bit of logic, it works fine.
Does anybody have any clue what I'm doing wrong? The date column is formatted as "Date", the month column is "Text/Number". As I said, it works fine if I isolate the last argument but doesn't want to work within.
Thank you.
Best Answer
-
Ok, one more thing to try:
Move the check for blank cell to the front of the formula, and wrap the whole thing in IFERROR:
=IFERROR(IF([Date of Defense]@row = "", "m. UNKNOWN", IF(MONTH([Date of Defense]@row) = 1, "a. JANUARY", IF(MONTH([Date of Defense]@row) = 2, "b. FEBRUARY", IF(MONTH([Date of Defense]@row) = 3, "c. MARCH", IF(MONTH([Date of Defense]@row) = 4, "d. APRIL", IF(MONTH([Date of Defense]@row) = 5, "e. MAY", IF(MONTH([Date of Defense]@row) = 6, "f. JUNE", IF(MONTH([Date of Defense]@row) = 7, "g. JULY", IF(MONTH([Date of Defense]@row) = 8, "h. AUGUST", IF(MONTH([Date of Defense]@row) = 9, "i. SEPTEMBER", IF(MONTH([Date of Defense]@row) = 10, "j. OCTOBER", IF(MONTH([Date of Defense]@row) = 11, "k. NOVEMBER", IF(MONTH([Date of Defense]@row) = 12, "l. DECEMBER", ""))))))))))))), "")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
That's very strange. The #INVALID DATA TYPE error comes from trying to use a function on an incompatible data type, like trying to get the lowercase of numeric value.
Try using this instead and see if you get a different result:
IF(NOT(ISDATE([Date of Defense]@row)), "m. UNKNOWN", "")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
As an alternative, you could wrap your formula in an IFERROR() and use "m. UNKNOWN" as the Error response
=IFERROR(IF(MONTH([Date of Defense]@row) = 1, "a. JANUARY", IF(MONTH([Date of Defense]@row) = 2, "b. FEBRUARY", IF(MONTH([Date of Defense]@row) = 3, "c. MARCH", IF(MONTH([Date of Defense]@row) = 4, "d. APRIL", IF(MONTH([Date of Defense]@row) = 5, "e. MAY", IF(MONTH([Date of Defense]@row) = 6, "f. JUNE", IF(MONTH([Date of Defense]@row) = 7, "g. JULY", IF(MONTH([Date of Defense]@row) = 8, "h. AUGUST", IF(MONTH([Date of Defense]@row) = 9, "i. SEPTEMBER", IF(MONTH([Date of Defense]@row) = 10, "j. OCTOBER", IF(MONTH([Date of Defense]@row) = 11, "k. NOVEMBER", IF(MONTH([Date of Defense]@row) = 12, "l. DECEMBER", IF(ISBLANK([Date of Defense]@row), "m. UNKNOWN", ""))))))))))))),"m. UNKNOWN")
-
Hello!
@Jeff Reisman I know it's weird. It's always the last one that causes the trouble and I feel like I've tried everything. I've tried your solution but the error remains there. I was always under the intention that if the isolated arguments all work fine, they should do so when combined. I am lost.
@Kelly Moore I was thinking about that solution but I have a tiny legend for the status underneath (as it was requested) so basically if I do that, It will show "m. UNKNOWN" when I want it to be blank.
-
There's your problem! The Legend's text values in your date range that's being evaluated by the formula. Move the legend so that there is no value in the green column and your original formula will work.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman That does not solve the problem I'm afraid! If I was getting that error only for those cells with the legend, it would be fine with me. But I'm getting the error also for cells where the date is blank but another entry is made in the same line, e.g. "STUDY #". As captured previously, if I isolated that last bit of formula, I was getting what I wanted, just not when I combined them.
-
Ok, one more thing to try:
Move the check for blank cell to the front of the formula, and wrap the whole thing in IFERROR:
=IFERROR(IF([Date of Defense]@row = "", "m. UNKNOWN", IF(MONTH([Date of Defense]@row) = 1, "a. JANUARY", IF(MONTH([Date of Defense]@row) = 2, "b. FEBRUARY", IF(MONTH([Date of Defense]@row) = 3, "c. MARCH", IF(MONTH([Date of Defense]@row) = 4, "d. APRIL", IF(MONTH([Date of Defense]@row) = 5, "e. MAY", IF(MONTH([Date of Defense]@row) = 6, "f. JUNE", IF(MONTH([Date of Defense]@row) = 7, "g. JULY", IF(MONTH([Date of Defense]@row) = 8, "h. AUGUST", IF(MONTH([Date of Defense]@row) = 9, "i. SEPTEMBER", IF(MONTH([Date of Defense]@row) = 10, "j. OCTOBER", IF(MONTH([Date of Defense]@row) = 11, "k. NOVEMBER", IF(MONTH([Date of Defense]@row) = 12, "l. DECEMBER", ""))))))))))))), "")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Moving the blank check to the first argument (as @Jeff Reisman suggested) should do the trick. The error is because the very first MONTH function has no date to pull from which throws the error. Setting the blank check as the first argument will grab that and stop there before it gets on to the MONTH functions.
Edit to add:
There should be no need for an IFERROR then either.
-
Thank you @Jeff Davies and @Paul Newcome , this works!
Always a great help as usual and thank you Paul for the explanation :)
-
@Paul Newcome Good explanation!
I think the IFERROR might still be needed though, in case there are values other than blanks or dates, as in the first iteration of the Legend, which had text in the date field.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman , thank you, I've already accounted for that and implemented IFERROR. It works magic now!
-
@Michaela Kamenska Happy to help. 👍️
@Jeff Reisman Very good point about the IFERROR for text values.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
