Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Pause "Days in Progress" counter

Within our SS solution we have a formula that starts a day counter from project status of "assigned" and stops counting when the project status is marked "complete". So if a project was assigned on the 5th day of a month and the project is marked complete on the 10th, it captures that the project was worked on for 5 days.

Our current formula for the Days in Progress counter is: =IF([Designer Project Status]@row = "Complete", [Date Approved]@row - [Date Assigned]@row, IF([Designer Project Status]@row = "Sent to Print", [Date at Printer]@row - [Date Assigned]@row, TODAY() - [Date Assigned]@row))

Is there a formula that would tell the day counter to pause if the status changes to "hold", and then resume counting from where it left off once the status changes back to "in progress"?

Answers

  • ✭✭✭✭

    Since the formula is just calculating the number of days in a range based on different date columns (or today's date), I think you would need something in the sheet to record when the status changed to on hold.

    One way to do it would be to create two helper columns to record the date the status changed to on hold and then one to record the date the status changed back to not on hold. To set that up, you could create an automation that records a date when with a Rows changed (Designer Project Status changes to On Hold) trigger. Then another automation that records a date when rows changed (Designer Project Status changes to any value) trigger, with a condition where On Hold date (recorded in other automation) is not blank. You can alternatively just manually mark dates when a project is placed on hold, but the automation would be more efficient and you wouldn't have to think about it.

    Then for your formula, you can use an IF statement that subtracts out the days that the project was on hold. I might add a helper column that takes out days on hold from the original day counter… that way you can see and compare both counts if you want. That formula would be:

    =IF(AND(NOT(ISBLANK([On Hold Date]@row)), NOT(ISBLANK([Re-open Date]@row))), [Days in Progress]@row - ([Re-open Date]@row - [On Hold Date]@row), IF(AND(NOT(ISBLANK([On Hold Date]@row)), ISBLANK([Re-open Date]@row)), [Days in Progress]@row - (TODAY() - [On Hold Date]@row), [Days in Progress]@row))

    You could also just add this logic to your existing formula for Days in Progress, but I think it would be a bit cumbersome. Anywhere in the above formula that references [Days in Progress]@row you would have to put the whole original formula in.

    There might be a better way to do it but this is one way I think would work. Hope this helps or at least gives you an idea to start with!

  • Thanks Janae, I will certainly look into this!

  • Community Champion

    Building off of the two date columns for on hold then off of hold, you could just do a basic subtraction of the two dates to get the duration of on hold and then subtract that from the final result of your existing formula.

    =IF([Designer Project Status]@row = "Complete", [Date Approved]@row - [Date Assigned]@row, IF([Designer Project Status]@row = "Sent to Print", [Date at Printer]@row - [Date Assigned]@row, TODAY() - [Date Assigned]@row)) - IF([On Hold Date]@row <> "", IF([Off Hold Date]@row <> "", [Off Hold Date]@row, TODAY()) - [On Hold Date]@row, 0)

  • Great, thanks Paul. I will look into this option as well.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6