Invalid Data Type

I am having trouble with a formula that is predicting scheduling dates coming back with an #invalid data type when a date isnt entered in the first column. I would like the subsequent cells to remain blank but cant seem to make the formula work.

My formula is =IF(((MONTH([Visit 1 (Baseline Visit) Date]@row) + 3) > 12, DATE(YEAR([Visit 1 (Baseline Visit) Date]@row) + 1, (MONTH([Visit 1 (Baseline Visit) Date]@row) + 3) - 12, DAY([Visit 1 (Baseline Visit) Date]@row)), DATE(YEAR([Visit 1 (Baseline Visit) Date]@row), MONTH([Visit 1 (Baseline Visit) Date]@row) + 3, DAY([Visit 1 (Baseline Visit) Date]@row)))

I have tried this =IFERROR(((MONTH([Visit 1 (Baseline Visit) Date]@row) + 3) > 12, DATE(YEAR([Visit 1 (Baseline Visit) Date]@row) + 1, (MONTH([Visit 1 (Baseline Visit) Date]@row) + 3) - 12, DAY([Visit 1 (Baseline Visit) Date]@row)), DATE(YEAR([Visit 1 (Baseline Visit) Date]@row), MONTH([Visit 1 (Baseline Visit) Date]@row) + 3, DAY([Visit 1 (Baseline Visit) Date]@row))), "")

but it comes back with #unparseable everytime. I cant detect where i have gone wrong. Please help!

Best Answer

  • Royce.
    Royce. ✭✭
    Answer ✓

    =IF(ISBLANK([Visit 1 (Baseline Visit) Date]@row), "", IF((MONTH([Visit 1 (Baseline Visit) Date]@row) + 3) > 12, DATE(YEAR([Visit 1 (Baseline Visit) Date]@row) + 1, MONTH([Visit 1 (Baseline Visit) Date]@row) - 9, DAY([Visit 1 (Baseline Visit) Date]@row)), DATE(YEAR([Visit 1 (Baseline Visit) Date]@row), MONTH([Visit 1 (Baseline Visit) Date]@row) + 3, DAY([Visit 1 (Baseline Visit) Date]@row))))

    Make sure the columns are set as dates types.

Answers

  • Royce.
    Royce. ✭✭
    Answer ✓

    =IF(ISBLANK([Visit 1 (Baseline Visit) Date]@row), "", IF((MONTH([Visit 1 (Baseline Visit) Date]@row) + 3) > 12, DATE(YEAR([Visit 1 (Baseline Visit) Date]@row) + 1, MONTH([Visit 1 (Baseline Visit) Date]@row) - 9, DAY([Visit 1 (Baseline Visit) Date]@row)), DATE(YEAR([Visit 1 (Baseline Visit) Date]@row), MONTH([Visit 1 (Baseline Visit) Date]@row) + 3, DAY([Visit 1 (Baseline Visit) Date]@row))))

    Make sure the columns are set as dates types.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Rozamund,

    You have a few brackets extra there that you don't need. This should do what you're after, I think:

    =IF(MONTH([Visit 1 (Baseline Visit) Date]@row) + 3 > 12, DATE(YEAR([Visit 1 (Baseline Visit) Date]@row) + 1, (MONTH([Visit 1 (Baseline Visit) Date]@row) + 3) - 12, DAY([Visit 1 (Baseline Visit) Date]@row)), DATE(YEAR([Visit 1 (Baseline Visit) Date]@row), MONTH([Visit 1 (Baseline Visit) Date]@row) + 3, DAY([Visit 1 (Baseline Visit) Date]@row)))

    Sample output:

    Hope this helps, but if I've misunderstood something or you have any problems/questions then just post! 🙂

  • Many thanks to you both for answering :) adding the ISBLANK option in worked a treat

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!