Calculating Number of Days Between Start and End Dates with Specific Criteria

I have a data set that contains the following:

1. Start date for an individual registered in a program
2. End date for an individual registered in a program
3. Blank as the person is still in the program

Note the entries are as follows:


Person 1 Row 1 Column start date, column end date,
Person 1 Row 2 (for second start date) column state date, column end date
Person 1 Row 3 Column start date, column end date,
Person 2 Row 1 Column start date , column end date

Some person have one row. Some have multiple rows. Each time they joined the program. Some end dates are blank because during the data pull they were still on.


I want to create a column of data that tells me the number of days an individual was registered for a program.
However, for individuals that ended a program for less than six months you count it as they have never left the program.

In other words,
person 1, start date Jan 2021 and end date March 2021 start date again in separate row June 2021 and end date December 2021. They are counted as being on for 365 days. If the gap was more than six months then it would be counted as two separate registrations and you would count from start to end date.
Only one count should be reported in one row per person when it comes to multiple start and end dates.

there is no expected result in your example and your description isn't clear

Yes, so if its a six month gap or more than it's counted as two different registration periods.

If its less than six months you add the two periods together that they were in the program.

Originally Posted by bsalv

nice job for a sumproduct. Do you have an example in a file ?

Originally Posted by bsalv

hello, that 6-months-gap, i'm not sure that I understood it correct.

Apologies as I was not clear. More information below. I also made edits to the sample file.

Background
I'm trying to determine how long individuals stay in a program in days. Many individuals come back to the program as observations indicate a "revolving door". We are concerned that individuals that are in the program for a period of time then come out again for a couple of months only to comeback to the program reduce the average length of stay (also known as "spells") for the population. Therefore a gap of six months from end date to reapplying to the program is being used to differentiate individuals that have truly detached themselves from the program with six or over six months being deemed as a new program registration and less than six (6) months being considered as not having leaving the program. Let's define six months as 180 days.

Expected Results from the Example

1. Person 1 - Row 2 and Row 3 should be counted as separate periods a person has been in the program. 180 and 152 days separately.
2. Person 2 - Row 4, 5 and 6. For row 4 and 5 start and end dates they should be added together as there is less than 180 days with the start and end dates. 14 days + 30 days. 30 days should be included in the number of days in the program (column created through when executing the formula). The 30 days should be included in row 4 column d which is created by the formula in the first empty column. In this case column d Average days in Program, row five should remain blank as I don't want to double count these dates.
3. Person 3 - Row 7 and Row 8 should be counted as one program registration with 151+396 = 547 days. the 547 should be in made in a new column created by the formula in this case column d Average Days in Program, and row 8 should remain blank as I don't want it double counted.
4. Person 4 - number of days should be populated in column D Average Days in Program or the first empty column and should be 16 days.

Originally Posted by bsalv

nice job for a sumproduct. Do you have an example in a file ?

Originally Posted by sandy666

there is no expected result in your example and your description isn't clear

Originally Posted by bsalv

nice job for a sumproduct. Do you have an example in a file ?

Originally Posted by bsalv

nice job for a sumproduct. Do you have an example in a file ?

Originally Posted by sandy666

there is no expected result in your example and your description isn't clear

Almost and apologies for not being more clear.

Person three for the first two start and end dates has less than 180 days (6 months) between the end date of the first program and the start date of the second program. As such the number of days attended for the first and second program should be added together. Ideally the formula should then create a new column that adds up all the dates in this example let's say column F in this case. Column F row 7 will indicate 247 days (91+252). Column F row 8 will be blank as I don't want to double count the dates as there was a break of less than 6 months. Row 9 will indicate 1 as there has been a break of over 180 days (or six months).

I did upload a new file as row 6 should not be overlapping 4 and 5 as an FYI.

Originally Posted by bsalv

nice job for a sumproduct. Do you have an example in a file ?

Originally Posted by bsalv

hello, that 6-months-gap, i'm not sure that I understood it correct.

great, now there is neither new nor old file!

Originally Posted by bsalv

nice job for a sumproduct. Do you have an example in a file ?

Not quite.

When an individual ends and then starts a program in less than 180 days then the two program periods are added together in a new column. For this example it should look like as follows:

person 1 row 2 column f 365 days less than 180 days / 6 month break
person 1 row 3 column f blank I don't want the dates double counted

person 3 Row 7 column f 243 less than 180 days / 6 month break
person 3 Row 8 column f blank
person 3 row 9 column f 1 more than 180 day / 6 month break so not merged with other program start end dates on its own

Originally Posted by AliGW

Unsure - this?

=LET(a,Table1[Person Number],d,BYROW(Table1[[Person Number]:[end date]],LAMBDA(x,SUMPRODUCT((Table1[Person Number]=x)*(Table1[end date]-Table1[start date])))),UNIQUE(CHOOSE({1,2},a,d+1)))

i've old but i need new with proper source and expected result with description in the file
i don't want to jump from post to post

but i see OP is a declared proponent of formulas, so i am out

Thank you you've all been helpful want to close this thread!