IF formatting combined with a calculation

I am trying to include a calculation only if a certain qualitative value is met.

Trying to have the column indicate "Days Over Deadline" by subtracting the "Submission Deadline from the Submission Date," but only if the "Assessment Stage" is Issued.

=IF([Assessment Stage]@row)="Issued", =SUM([Submission Date]@row) - ([Vendor Submission Deadline]@row)

What can I do to make this formula work better?

Ideally, I would also like to freeze that number when the stage is updated and no longer "issued"--is that possible?

Find more posts tagged with

Formulas

Conditional Formatting

Accepted answers

Andrée Starå

Hi @Ryan T. Hannan

I hope you're well and safe!

Try something like this.

=IF([Assessment Stage]@row="Issued", [Submission Date]@row - [Vendor Submission Deadline]@row

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

All comments

Razetto

@Ryan T. Hannan Hi, I'm assuming that your submission date is a system generated while the vendor submission deadline changes. I don't think there's a way to freeze it as one of the dates can change but you could have helper columns as a way around it for when the stage is issued. Add a new helper column and then create a record a date workflow to populate that helper column when the stage is issued. Your formula would have to call the helper column when the stage is Issued, something like...

IF([Assessment Stage]@row)="Issued", ([Submission Date]@row) - ([helper column]@row), ([Submission Date]@row) - ([Vendor Submission Deadline]@row))

Andrée Starå

Hi @Ryan T. Hannan

I hope you're well and safe!

Try something like this.

=IF([Assessment Stage]@row="Issued", [Submission Date]@row - [Vendor Submission Deadline]@row

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

Ryan T. Hannan

@Andrée Starå Thank you! That worked great.

Andrée Starå

@Ryan T. Hannan

Excellent!

Happy to help!

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

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.