Nested Formula using BLANK and MONTH lookup

I want my query to look up the Finish column to determine what month it should display, 1 = January, 2 = February, etc... but if its blank, I want a blank to appear. Right now it says "Invalid Data Type". Can you help fix this query:

=IF(Finish@row) = " ", " ", IF(MONTH(Finish@row) = 1, "January", IF(MONTH(Finish@row) = 2, "February", IF(MONTH(Finish@row) = 3, "March", IF(MONTH(Finish@row) = 4, "April", IF(MONTH(Finish@row) = 5, "May", IF(MONTH(Finish@row) = 6, "June", IF(MONTH(Finish@row) = 7, "July", IF(MONTH(Finish@row) = 8, "August", IF(MONTH(Finish@row) = 9, "September", IF(MONTH(Finish@row) = 10, "October", IF(MONTH(Finish@row) = 11, "November", IF(MONTH(Finish@row) = 12, "December", " "))))))))))))

Best Answer

  • KevAnalyst
    KevAnalyst ✭✭
    edited 01/27/22 Answer ✓

    Jennifer,

    I copy/pasted into a test sheet and got the error #UNPARSEABLE because you have a misplaced closing parenthesis at the beginning of your formula. Once I moved that first closing parenthesis to the end of the formula, it worked.

    Correction:

    =IF(Finish@row = " ", " ", IF(MONTH(Finish@row) = 1, "January", IF(MONTH(Finish@row) = 2, "February", IF(MONTH(Finish@row) = 3, "March", IF(MONTH(Finish@row) = 4, "April", IF(MONTH(Finish@row) = 5, "May", IF(MONTH(Finish@row) = 6, "June", IF(MONTH(Finish@row) = 7, "July", IF(MONTH(Finish@row) = 8, "August", IF(MONTH(Finish@row) = 9, "September", IF(MONTH(Finish@row) = 10, "October", IF(MONTH(Finish@row) = 11, "November", IF(MONTH(Finish@row) = 12, "December", " ")))))))))))))
    

    Hope this helps!

    Kev

Answers

  • KevAnalyst
    KevAnalyst ✭✭
    edited 01/27/22 Answer ✓

    Jennifer,

    I copy/pasted into a test sheet and got the error #UNPARSEABLE because you have a misplaced closing parenthesis at the beginning of your formula. Once I moved that first closing parenthesis to the end of the formula, it worked.

    Correction:

    =IF(Finish@row = " ", " ", IF(MONTH(Finish@row) = 1, "January", IF(MONTH(Finish@row) = 2, "February", IF(MONTH(Finish@row) = 3, "March", IF(MONTH(Finish@row) = 4, "April", IF(MONTH(Finish@row) = 5, "May", IF(MONTH(Finish@row) = 6, "June", IF(MONTH(Finish@row) = 7, "July", IF(MONTH(Finish@row) = 8, "August", IF(MONTH(Finish@row) = 9, "September", IF(MONTH(Finish@row) = 10, "October", IF(MONTH(Finish@row) = 11, "November", IF(MONTH(Finish@row) = 12, "December", " ")))))))))))))
    

    Hope this helps!

    Kev

  • Thank you Kev. I actually figured it out using ISBLANK function but your way works to and silly me missing a parenthesis!

  • Ok another question.

    Is there a way I can have it look up Month AND Year? Right now my formula only has month but I need to add year to it as well.

    Thanks for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!