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


Tags:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    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

  • Jeff Reisman
    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", "")

    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!

  • Kelly Moore
    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
    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
    Jeff Reisman ✭✭✭✭✭✭

    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!

  • Michaela Kamenska
    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
    Jeff Reisman ✭✭✭✭✭✭
    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!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 09/13/22

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Michaela Kamenska
    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
    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.

    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!

  • Michaela Kamenska
    Michaela Kamenska ✭✭✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Michaela Kamenska Happy to help. 👍️


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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!