Duration with stop points

 Katie,

Here is one approach

It seems you want to be able to do something with the duration(days) when the service goes ON HOLD to when they come off ON HOLD.  Must be that you do NOT want to include those days in the ultimate calculation of how long it took to complete Stage 4?  If so, then try this...

Create a new Child Relationship to this table. Call it [Stage 4 Pause Records]

ADD these fields to your table:

• Date Field – [ON HOLD DATE]
• Date Field – [OFF HOLD DATE]
• Formula Date Field - Value is Today()
• Formula Duration Field – [Running Count] in [Days]
  • (Today() - [ON HOLD DATE])
• Formula Duration Field – [ON_OFF HOLD] in [Days]

([OFF HOLD DATE] - [ON HOLD DATE])

• Formula Duration Field – [ACTUAL DAYS] in [Days]
  • If IsNull([ON_OFF HOLD], [Running Count], [ON_OFF HOLD]
  • This is the field that you will summarize back to the Parent Record

Leave your current Parent table Duration Formula in place.  I assume it just goes from Stage 4 create trigger to Stage 4 complete trigger.  All the days.

• ADD these fields to your table:
  • Formula Date Field - Value is Today()
  • Date Field – [ON HOLD DATE]
  • Date Field – [OFF HOLD DATE]
  • Report Link Field – [Record ID#] to [Record ID#] to the same table. (For use in ACTIONS)
  • [Summary Field - # of Actual Days] from new Relationship
  • Formula Duration Field – [STAGE 4 DURATION DAYS] in [Days]
    • [Your Current Stage Duration field] - [Summary Field - # of Actual Days]
  • This gives you the number of Stage 4 days excluding those days ON HOLD.

You will need Parent Record triggers to automatically update the [ON HOLD DATE] and [OFF HOLD DATE]. The update to these fields will be to copy the value in the [Today()] field into these values based upon the triggers you set or currently have in place. 

• Stage 4 goes "ON HOLD" copy Today() to [ON HOLD DATE]
• Stage 4 goes "OFF HOLD" copy Today() to [OFF HOLD DATE]

These will trigger the Actions / Automations below

Create Actions or Automations to do the following  with triggers from Parent Record)

1. When [ON HOLD DATE] changes ADD a record to the [Stage 4 Pause Records]
   1. Import [Record ID#] to [Related Parent]
   2. Import [ON HOLD DATE]

• When [OFF HOLD DATE] changes EDIT the Related [Stage 4 Pause Records] record.
  • Change the value in [Stage 4 Pause Records] [OFF HOLD DATE] field to the value from your [Parent Table] [OFF HOLD DATE] field

Updating the [OFF HOLD DATE] essentially closes this record.  If the Stage 4 process needs to go ON HOLD again, you change the [ON HOLD Date] and new Child Record is created, and the process starts all over again.  The [Summary Field - # of Actual Days] will keep track of all the starts and stops and keep the Formula Duration Field – [STAGE 4 DURATION DAYS] in [Days] always accurate.