Invalid Data Type

Rozamund

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!

Royce.

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

Royce.

=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

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! 🙂

Rozamund

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