#INVALID DATA TYPE error when IFs are combined

Options

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 ✓
    Options

    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 ✭✭✭✭✭✭
    Options

    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 ✭✭✭✭✭✭
    Options

    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 ✭✭✭✭✭✭
    Options

    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 ✭✭✭✭✭✭
    Options

    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 ✭✭✭✭✭✭
    Options

    @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 ✓
    Options

    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
    Options

    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
    Michaela Kamenska ✭✭✭✭✭✭
    Options

    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 ✭✭✭✭✭✭
    Options

    @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 ✭✭✭✭✭✭
    Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Michaela Kamenska Happy to help. 👍️


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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!