Does any one know how I can insert rows into my data that calculates the DATEDIFF between each Student's last Activity date and the End date in a new column? In the example below I would like to generate a row after each of the rows with red text. It would like it to duplicate the first 4 fields, have a null in the Activity field and 11 in the new column for Student 101 (DATEDIFF 31/01/16 and 20/01/16). Is that possible? CourseStartEndStudentActivity11/01/201631/01/20161011/01/201611/01/201631/01/201610110/01/201611/01/201631/01/201610111/01/201611/01/201631/01/201610112/01/201611/01/201631/01/201610120/01/201611/01/201631/01/20162022/01/201611/01/201631/01/20162026/01/201611/01/201631/01/201620211/01/201611/01/201631/01/201620220/01/201611/01/201631/01/201620231/01/201611/01/201631/01/20163031/01/201611/01/201631/01/201630320/01/201611/01/201631/01/201630321/01/201611/01/201631/01/201630322/01/201611/01/201631/01/201630330/01/201621/01/201631/01/201610110/01/201621/01/201631/01/201610113/01/201621/01/201631/01/201610119/01/201621/01/201631/01/201610122/01/201621/01/201631/01/201610123/01/201621/01/201631/01/20164045/01/201621/01/201631/01/20164046/01/201621/01/201631/01/201640418/01/201621/01/201631/01/201640424/01/201621/01/201631/01/201640431/01/201621/01/201631/01/20165059/01/201621/01/201631/01/201650511/01/201621/01/201631/01/201650521/01/201621/01/201631/01/201650522/01/201621/01/201631/01/201650530/01/2016 Thank tou in advance for your help. DHB.

Generate Row to incorporate DATEDIFF

Does any one know how I can insert rows into my data that calculates the DATEDIFF between each Student's last Activity date and the End date in a new column? In the example below I would like to generate a row after each of the rows with red text. It would like it to duplicate the first 4 fields, have a null in the Activity field and 11 in the new column for Student 101 (DATEDIFF 31/01/16 and 20/01/16). Is that possible?

Course	Start	End	Student	Activity
1	1/01/2016	31/01/2016	101	1/01/2016
1	1/01/2016	31/01/2016	101	10/01/2016
1	1/01/2016	31/01/2016	101	11/01/2016
1	1/01/2016	31/01/2016	101	12/01/2016
1	1/01/2016	31/01/2016	101	20/01/2016
1	1/01/2016	31/01/2016	202	2/01/2016
1	1/01/2016	31/01/2016	202	6/01/2016
1	1/01/2016	31/01/2016	202	11/01/2016
1	1/01/2016	31/01/2016	202	20/01/2016
1	1/01/2016	31/01/2016	202	31/01/2016
1	1/01/2016	31/01/2016	303	1/01/2016
1	1/01/2016	31/01/2016	303	20/01/2016
1	1/01/2016	31/01/2016	303	21/01/2016
1	1/01/2016	31/01/2016	303	22/01/2016
1	1/01/2016	31/01/2016	303	30/01/2016
2	1/01/2016	31/01/2016	101	10/01/2016
2	1/01/2016	31/01/2016	101	13/01/2016
2	1/01/2016	31/01/2016	101	19/01/2016
2	1/01/2016	31/01/2016	101	22/01/2016
2	1/01/2016	31/01/2016	101	23/01/2016
2	1/01/2016	31/01/2016	404	5/01/2016
2	1/01/2016	31/01/2016	404	6/01/2016
2	1/01/2016	31/01/2016	404	18/01/2016
2	1/01/2016	31/01/2016	404	24/01/2016
2	1/01/2016	31/01/2016	404	31/01/2016
2	1/01/2016	31/01/2016	505	9/01/2016
2	1/01/2016	31/01/2016	505	11/01/2016
2	1/01/2016	31/01/2016	505	21/01/2016
2	1/01/2016	31/01/2016	505	22/01/2016
2	1/01/2016	31/01/2016	505	30/01/2016

Thank tou in advance for your help.

DHB.

Date Time

Preparation

Accepted answers

Joe_Mako

How about the attached?

Sort - ensure correct sorting
Multi-Row Formula - Add an ActivityID field, for use sorting later
Sample - Last record per Course-Student combination
Formula - Calculate Difference in days, increment ActivityID, Null for Activity
Union - Append with data from before Sample
Sort - get final requested sort order

activity.yxmd

RodL

So I like to look at some of these questions and come up with an answer, but when I find that someone has already answered with a working solution like one of brilliant our ACEs, @Joe_Mako, I usually let it go.

But in this case, while the basic principle of creating a separate record and then unioning back with the original data set was also in my solution, I had approached the creation of the record a bit differently. And in the spirit of showing people that there are multiple ways to approach a problem, I figured I'd post my solution as well. Not that mine is better...just different...but I think we get the same result. Smiley Wink