How do I bring back a blank cell on date column cells when there isn't a date

Options

Can anyone help with this? I keep getting the #Data Invalid Type message. I am referencing one column to bring back the Month name in another. It works fine where there is a date but when the cell in the Test Start Date column is blank i get the #Data Invalid Type message.

Here is the formula I am trying to use. Like I said works good when there is a date in the column. Both columns are Date columns. I am using parent/children rows, but those also work fine when I put in a fictitious date.

Any help that is good help is appreciated.

Brings back the error with or without the first IF statement.

My formula

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

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 01/14/21 Answer ✓
    Options

    Hi @Jacob Harness

    Hope you are fine, please use the following formula and convert it to a column formula:

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



    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • JeremiahHorstick
    JeremiahHorstick ✭✭✭✭✭✭
    Options

    You might be looking for the IfError() function.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 01/14/21 Answer ✓
    Options

    Hi @Jacob Harness

    Hope you are fine, please use the following formula and convert it to a column formula:

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



    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Jacob Harness
    Options

    Thank you Bassam! I was looking for an IFERROR in my formula, but couldn't get the position of it correct. Now it makes a lot more sense.

    This will also help out on a couple of other formulas I was planning on using. Namely the day of the week formula.

    Thanks Again

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    You are more than welcome @Jacob Harness

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!