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!

Find more posts tagged with

Accepted answers

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.

All comments

Royce.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.