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"?
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
Best Answer

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 onhold duration else use just the cycle time..
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
Answers

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 onhold duration else use just the cycle time..
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com

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!
Help Article Resources
Categories
Check out the Formula Handbook template!