Formula Issue

I am trying to create a formula in Smartsheet, which I am able to make work in Excel. However, the formula isn't working in Smartsheet. Does anyone have a solution?

What I want to do is calculate the next due date based on the current status and the date the status was updated.

=IF([Program Status]@row = “Apprenticeship Program - Phase 1”, SUM([Last Status Change]@row+120),0)

Find more posts tagged with

Accepted answers

Andrée Starå

Hi @tiernosc

I hope you're well and safe!

Try something like this.

=
IF([Program Status]@row = "Apprenticeship Program - Phase 1", 
[Last Status Change]@row + 120)

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

All comments

Christina09

Hi @tiernosc

You're very close, but you don't need the SUM formula

=IF([Program Status]@row = “Apprenticeship Program - Phase 1”, [Last Status Change]@row + 120)

Make sure the column Anticipated Date Until Next Phase is a Date column

tiernosc

I'm still getting "Unparseable" as a response...

Andrée Starå

Hi @tiernosc

I hope you're well and safe!

Try something like this.

=
IF([Program Status]@row = "Apprenticeship Program - Phase 1", 
[Last Status Change]@row + 120)

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

tiernosc

I also have 3 more status changes that I want to add to the string. Here is what I tried to do, but received an Unparseable message:

=IF([Program Status]@row = "Apprenticeship Program - Phase 1", [Last Status Change]@row + 31), IF([Program Status]@row = "Submit Preliminary Credential Application - Phase 2", [Last Status Change]@row + 31), IF([Program Status]@row = "Preliminary Credential - Phase 3", [Last Status Change]@row + 190)

So, what I want to do is when the status is manually changed to either Phase 2 or 3 I want to add to the date in the Last Status Change column and display it under the Anticipated Date Until Next Phase 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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.