Returning a date

Good Morning!

I am having issues returning a date when I subtract a date (from a date column) from a number (text/number column). The column i am using the formula in is a date column.

My target finish column is generated by a formula based on target finish (Q). Would this affect the formula I am trying to use in Target Start?

The formula I am using is:

=[Target Finish (Date)]@row-[Project Start Days]@row

Any help is greatly appreciated as I am not sure why this formula is not correct.

Find more posts tagged with

Accepted answers

All comments

Mike TV

@TanFie

Make sure the column you're putting this formula into is formatted as a Date type column. When I do that on a test sheet I get this:

When the column is set as text/number it just returns an #INVALID COLUMN VALUE error.

TanFie

Thanks so much for your reply Mike.

I double checked my columns and this is how they are formatted:

Project Start Days: text/number

Target Finish (Date): Date

Target Start (Date): Date

I get the error #invalid operation

Is it because my project start days and target finish columns are generated via a formula?

Mike TV

@TanFie

You can always check to see how SmartSheet is considering that by doing

=IF(ISDATE([Start Date]1), "Is Date", "Not Date")

Just instead of [Start Date]1, specify the cell to check. It's probably not.

TanFie

@Mike TV Yep sure enough it is not recognized as a date because it is a result of a formula. I'll have to get clever now lol. Thank you again for all your assistance!

Mike TV

@TanFie

There are threads discussing ways to convert a text date to an actual recognized date. You could try reviewing them such as this one:

https://community.smartsheet.com/discussion/86372/text-to-date-field

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.