Calculating longest time between 'Green' and 'Orange' result groups...

[jacketpotato]

Hi experts

Please could you help me to configure this spreadsheet to auto calculate the time between ending and start dates for result groups?

The result groups are on the left, column A. Start dates C, End dates D. Sheet auto generates with most recent dates at the top.

As you can see, I've already calculated overall time spent in green groups which shows at the top right.
But what I need now - is to calculate the times between ending then starting again in a group.

For example - D10 - green result group has finished on 14 Mar, then picks back up again in C6 on 15-Jun. I need the sheet to auto calculate these gaps, for the purpose of determining what has been the longest tine spent 'outside' of a group.

 

[jasonb75]

Something like this maybe? Note that the formula in column G extends to the first blank row below the data. Column G shows the number of days between current and previous for the same group, you could then use maxifs to get the desired results for the individual groups in the same way that you have presumably already used sumifs for the green total.

For future posts it would be helpful to you if you post your examples using XL2BB instead of screen captures so that we can copy and paste them to excel for formula testing instead of having to retype them.

Book1
	A	B	C	D	E	F	G
2	Group		Start	End
3	Orange Result		15-Aug-21	Current	#VALUE!	#VALUE!	1.00
4	Orange Result		11-Aug-21	14-Aug-21	3	0.4	58.00
5	Green Result		15-Jul-21	10-Aug-21	26	3.7	1.00
6	Green Result		15-Jun-21	14-Jul-21	29	4.1	93.00
7	Orange Result		15-May-21	14-Jun-21	30	4.3	1.00
8	Orange Result		15-Apr-21	14-May-21	29	4.1	1.00
9	Orange Result		15-Mar-21	14-Apr-21	30	4.3	29.00
10	Green Result		15-Feb-21	14-Mar-21	27	3.9	32.00
11	Orange Result		15-Jan-21	14-Feb-21	30	4.3	0.00
12	Green Result		15-Dec-20	14-Jan-21	30	4.3	0.00
13
Sheet2

Cell Formulas
Range	Formula
E3:E12	E3	=D3-C3
F3:F12	F3	=E3/7
G3:G12	G3	=C3-XLOOKUP(A3,$A4:$A$13,$D4:$D$13,C3)