#Invalid Operation Error subtracting days from another Date field

I have a sheet that has a date field that is link in from another sheet (actually 2 other sheets) that is not working as expected and I cannot seem to root cause the issue.

I have a column 75%/SD Date that I want to get 14 days before it in another column. Below are some things I have put in for the formula and the result I have gotten.

The date I see in the 75%/SD Date column is 5/30/20

=[75%/SD Date]54 - 14 (result = #INVALID OERATION)

=[75%/SD Date]54 (result = 5/30/20)

=[75%/SD Date]54 + 14 (result = 5/30/2014)

=WORKDAY([75%/SD Date]@row, -14) (result = #INVALID DATA TYPE)

The original source of the Date is a Finish date from a row in a project plan. I think I have seen this could cause an issue before but I cannot find anything on it now.

Thanks

Find more posts tagged with

Formulas

Cell linking

Accepted answers

All comments

Mark Cronk

Interesting. =[75%/SD Date]54 + 14 should work. Smartsheets is supposed to treat the number (14) as days unless a function is used. Appears to be treating it as years for you.

You could try: =dateonly([75%/SD Date]54) + 14

Confirm all columns are date columns and that the date format is set the same.

Mark

guy.rooker83086

Mark I get the #INVALID DATE TYPE error when I try that.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.