Locate the max date in list of consecutive dates that have breaks

[lrsjohn]

Problem - I have an error log that lists errors by 1st Date (occurrence) on report and compares to number of days from current report date. Currently the formula uses the Report Date less max date from the 1st Date field. I would like a new column that would return the max date from the last break in consecutive days (1st Date) so I could calculate the difference from report Date column vs. this new column - is this possible? Example below:

	A	B	C	D	E
1	Error	1st Date on Report	Days on Report	Report Date	New Max Date Column
2	1005643556	5/10/2021	0	5/10/2021​	5/10/2021​
3	1005643556	5/10/2021	1	5/11/2021​	5/10/2021​
4	1005643556	5/10/2021	2	5/12/2021​	5/10/2021​
5	1005643556	5/10/2021	3	5/13/2021​	5/10/2021​
6	1005643556	5/10/2021	4	5/14/2021​	5/10/2021​
8	1005643556	5/10/2021	0	6/2/2021​	6/2/2021​
9	1005643556	5/10/2021	1	6/3/2021​	6/2/2021​

 

[johnny51981]

From the way you have it listed, the New Max Date Column seems to equal the last time the Days on Report equaled 0, so would it not just be:

=MAXIFS(C:C,0,D:D)

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	Error	1st Date on Report	Days on Report	Report Date	New Max Date Column
2	1005643556	10/05/2021	0	10/05/2021	10/05/2021
3	1005643556	10/05/2021	1	11/05/2021	10/05/2021
4	1005643556	10/05/2021	2	12/05/2021	10/05/2021
5	1005643556	10/05/2021	3	13/05/2021	10/05/2021
6	1005643556	10/05/2021	4	14/05/2021	10/05/2021
7	1005643556	10/05/2021	0	02/06/2021	02/06/2021
8	1005643556	10/05/2021	1	03/06/2021	02/06/2021
Lists

Cell Formulas
Range	Formula
E2:E8	E2	=INDEX(D$1:D2,LOOKUP(2,1/(C$2:C2=0),ROW(C$2:C2)))

 

[lrsjohn]

From the way you have it listed, the New Max Date Column seems to equal the last time the Days on Report equaled 0, so would it not just be:

=MAXIFS(C:C,0,D:D)

I listed how I would like it to be - currently column C in the example is based on the Report Date vs. the 1st Date 0 I want to be able to re-calculate the days when there is a break in the consecutive dates

 

[lrsjohn]

I listed how I would like it to be - currently column C in the example is based on the Report Date vs. the 1st Date 0 I want to be able to re-calculate the days when there is a break in the consecutive dates

I have tried creating formula looking at the max frequency but could not get it to work correctly

 

[Fluff]

Did you try the formula I suggested?

 

[lrsjohn]

Did you try the formula I suggested?

I have tried you solution but my "New Max Date" is not calculating like yours did - it remains 5/10/2021 for all rows. I have attached a copy - do I need to change the date format to dd/mm/yyyy (I tried this but still no change)

SUMMARY - CHANGE CALCULATED DAYS 06 02 2021.xlsx
	A	B	C	D	E
1	Error	1st Date on Report	Days on Report	Report Date	New Max Date
2	1005643556	5/10/2021	0	5/10/2021	5/10/2021
3	1005643556	5/10/2021	1	5/11/2021	5/10/2021
4	1005643556	5/10/2021	2	5/12/2021	5/10/2021
5	1005643556	5/10/2021	3	5/13/2021	5/10/2021
6	1005643556	5/10/2021	4	5/14/2021	5/10/2021
7	1005643556	5/10/2021	18	6/2/2021	5/10/2021
8	1005643556	5/10/2021	22	6/3/2021	5/10/2021
Sheet2

Cell Formulas
Range	Formula
E2:E8	E2	=INDEX(D$1:D2,LOOKUP(2,1/(C$2:C2=0),ROW(C$2:C2)))

 

[Fluff]

You're original data showed a 0 in col C for each change of date. Are you now saying that that isn't always the case?