#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.

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

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", ""))))))))))))), "")

All comments

Jeff Reisman

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", "")

Kelly Moore

Hey @Michaela Kamenska

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")

Michaela Kamenska

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.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/343317#Comment_343317

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.

Michaela Kamenska

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

Jeff Reisman

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:

Paul Newcome

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.

Michaela Kamenska

Thank you @Jeff Davies and @Paul Newcome , this works!

Always a great help as usual and thank you Paul for the explanation :)

Jeff Reisman

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

Michaela Kamenska

@Jeff Reisman , thank you, I've already accounted for that and implemented IFERROR. It works magic now!

Paul Newcome

@Michaela Kamenska Happy to help. 👍️

@Jeff Reisman Very good point about the IFERROR for text values.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.