How do I subtract days from a date

I'm trying to build a production schedule spreadsheet. In one column I have the date the production item needs to be completed. In the next column I have the number of days (just a whole integer number) needed to complete. I would like to use a formula to determine the date production needs to begin. Simply (Date to be complete) - (Days to complete).

In excel I can subtract an integer from a date and it will treat the integer as days. So, for example, [3/9/2023 - 12] would result in 2/25/2023. But when I try to subtract an integer from a date in SmartSheet it gives an error #INVALID COLUMN VALUE

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Make sure the column you are putting the formula in is set as a date type column.

steph_aw

Did you ever figure this out? I'm trying to do the exact same thing.

steph_aw

Oh wait I figured it out! Make sure the field you're putting the formula in is a Date field. then it's just:

=[FIELD_NAME]@row - [FIELD_NAME_2]@row

Attached an image of mine. Everything in the brackets [] is a field name. First field is a Date field, second field is an integer (26, -103, etc.).

Paul Newcome

@steph_aw Yes. The column containing the date and the column containing the formula (that outputs a date) must be set as date type columns and how data that is being stored as a date (not a text string that just looks like a date).

The particular error mentioned in the original post typically comes from trying to output a date value in a non-date column.

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.