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
-
=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
-
=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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!