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

#### lrsjohn

##### New Member
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​

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### johnny51981

##### New Member
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

##### MrExcel MVP, Moderator
+Fluff 1.xlsm
ABCDE
1Error1st Date on ReportDays on ReportReport DateNew Max Date Column
2100564355610/05/2021010/05/202110/05/2021
3100564355610/05/2021111/05/202110/05/2021
4100564355610/05/2021212/05/202110/05/2021
5100564355610/05/2021313/05/202110/05/2021
6100564355610/05/2021414/05/202110/05/2021
7100564355610/05/2021002/06/202102/06/2021
8100564355610/05/2021103/06/202102/06/2021
Lists
Cell Formulas
RangeFormula
E2:E8E2=INDEX(D\$1:D2,LOOKUP(2,1/(C\$2:C2=0),ROW(C\$2:C2)))

#### lrsjohn

##### New Member
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

##### New Member

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

##### MrExcel MVP, Moderator
Did you try the formula I suggested?

#### lrsjohn

##### New Member
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
ABCDE
1Error1st Date on ReportDays on ReportReport DateNew Max Date
210056435565/10/202105/10/20215/10/2021
310056435565/10/202115/11/20215/10/2021
410056435565/10/202125/12/20215/10/2021
510056435565/10/202135/13/20215/10/2021
610056435565/10/202145/14/20215/10/2021
710056435565/10/2021186/2/20215/10/2021
810056435565/10/2021226/3/20215/10/2021
Sheet2
Cell Formulas
RangeFormula
E2:E8E2=INDEX(D\$1:D2,LOOKUP(2,1/(C\$2:C2=0),ROW(C\$2:C2)))

#### Fluff

##### MrExcel MVP, Moderator
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?

Replies
7
Views
72
Replies
3
Views
103
Replies
5
Views
85
Replies
7
Views
312
Replies
4
Views
99

1,141,203
Messages
5,704,934
Members
421,372
Latest member
Jamie11

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back