Help with IF / Then Subtraction

Hi All,

For our Project Management team, we calculate the total duration or 'Cycle Time' of our projects which is working well. However, sometimes, projects are on hold for a number of days. I would like to create a formula that subtracts the number of days a project is On Hold( "On Hold Duration" Column) from the Total Cycle time, ("Cycle Time" Column). My challenge is, how do I write a formula that will perform the subtraction only when there is a On Hold Duration Value, and when blank, just results the same number in the "Cycle Time Column"?

On Hold Cycle Time.JPG

The formula I currently have in the "Cycle Time With On Hold" Column is the following. It only works when there is a value in the "On Hold Duration". I assume I need to use an IF THEN type statement?

=IFERROR([Cycle Time]@row - [On Hold Duration]@row, "")

Any help would be greatly appreciated!

Thank you,

James

Find more posts tagged with

Accepted answers

Brent Wilson

This should work

=IF([On Hold Duration]@row > 0, IFERROR([Cycle Time]@row - [On Hold Duration]@row, ""),IFERROR([Cycle Time]@row, ""))

If On hold is greater than zero then use the cycle time minus the on-hold duration else use just the cycle time..

All comments

Brent Wilson

This should work

=IF([On Hold Duration]@row > 0, IFERROR([Cycle Time]@row - [On Hold Duration]@row, ""),IFERROR([Cycle Time]@row, ""))

If On hold is greater than zero then use the cycle time minus the on-hold duration else use just the cycle time..

Jhelbling

Thank you, Brent! It worked perfectly. I'll be studying the formula here so I can become acquainted with the syntax and logic. I appreciate your quick, timely response!

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.