Calculate total days with a condition...

Question

I'll try my best to explain...I have a dropdown field [Status] with 2 options.&nbsp;&nbsp;- For now we'll label them "Option 1", and "Option 2".I have a date field [O1_DATE] that puts in the "current date" when Option 1 is selected via dynamic rule.When Option 1 is selected, a numeric formula field [FORMULA1] calculates [today] - [O1_DATE] (using ToDays and ToDate), and this gives me a total number of days Option 1 has been selected.so far no problem...Now lets say I change [Status] to Option 2 and 10 days later I switch back [Status] to "Option 1" for 20 more days. How do I get it to only calculate the days that "Option 1" was selected?In a nutshell, I just need to know the total time a record had the status of "Option 1".Thank you and I look forward to your suggestions.&nbsp;LJM&nbsp;

miketamoush · Answer

I think I would use a child table and a pipeline. The child table would have two fields: [Date Option 1 Was Selected] and [Date Option 1 Was De-selected].

Pipeline: When the record is changed and Option 1 is Selected, create a new child (related to the parent) and set [Date Option 1 Was Selected] to Today().

When [Status] is changed and prev.Status = Option 1, then search for the child that where Date Option 1 was selected is NOT blank, and in that record set [Date Option 1 Was De-selected] to Today().

Now you have a child table of a list of records of every date Option 1 was selected, then deselected. On each child record, you calculate the duration (Date Option 1 was DeSelected - Date Option 1 was selected). If the deselected date is blank, then you use Today() for your duration calculation.

Then use a summary field of the duration to get your total time Option 1 has been selected.

leomatute1 · Answer

That's a good idea. Creating a child table opens a lot more possibilities also.I was hoping for a formula to keep it all in the existing table, but your suggestion is definitely an option.

miketamoush · Answer

The trick is somehow you need to keep track of each date that it is changed. If you can guarantee it is only switched a max number of times (say 2 or 3 times) you could keep it in the table by simply making extra fields. [First Time Option 1 Selected Date], [Second Time Option 1 Selected Date], [First Time Option 1 Deselected Date], etc).

Then you can fill these dates in with dynamic form rules, webhooks, or pipelines. Then get your durations that way.

The child table allows for unlimited switching. But somehow, you need to keep logging dates.

It might be possible to log the date switches in logged multiple choice field. So say you had a logged multiple choice field with the choices 'Option 1 Selected' or 'Option 1 Deselected'. Use dynamic form rules to keep selecting an option which will log the date. Then you would need some creative formula to extract all the dates from the logged field and do all the math. It should be possible, but I like the child table better.

Forum Discussion

Calculate total days with a condition...