Counting the number of days not the total number of days

[Chrysta]

Hello,

I need help creating a calculation field. I have a database with articles in it. I track the on-hold and off-hold dates, reasons, and number of days on hold. I track them in a portal. This portal is linked to another table within the database. So if the article has gone on/off hold multiple times there is a record for each occurrence.

We have a field that sums the total number of days on hold for each occurrence. What we need is a field that will calculate total days on hold. This way, if we have something that was on hold from 12/1 until 12/2 (1 day) and also on hold from 12/1 to 12/5 (4 days) we want the total number of days to be 4 and not 5. I have attached a spreadsheet of what we are trying to accomplish. Is there anyone that can help me with the logic of this? It maybe something simple, but we have been trying different logic and the calculation isn't coming out right.

Thanks!

hold_dates.zip

[comment]

I don't think this is simple. IIUC, you have multiple periods which MAY overlap (fully or partially) - and you want the total duration of all periods, with any overlapping parts counted only once. IOW, you are looking for the count of UNIQUE on-hold dates.

I think I would approach it this way:

1. In the child table, define a calculation that enumerates the on-hold dates. This can be done using a custom function, or by a repeating calculation field;

2. Define a value list using values from the calculation field, including only related values starting from the parent table - this will combine all the on-hold dates of the child records AND remove any duplicates;

3. In the parent table, define a calculation field that counts the value list items - this should be the number you're after.