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

lrsjohn

New Member
Joined
Jun 3, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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:

ABCDE
1Error1st Date on ReportDays on ReportReport DateNew Max Date Column
210056435565/10/20210
5/10/2021​
5/10/2021​
310056435565/10/20211
5/11/2021​
5/10/2021​
410056435565/10/20212
5/12/2021​
5/10/2021​
510056435565/10/20213
5/13/2021​
5/10/2021​
610056435565/10/20214
5/14/2021​
5/10/2021​
810056435565/10/20210
6/2/2021​
6/2/2021​
910056435565/10/20211
6/3/2021​
6/2/2021​
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

johnny51981

New Member
Joined
Jun 8, 2015
Messages
12
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
Joined
Jun 12, 2014
Messages
60,180
Office Version
  1. 365
Platform
  1. Windows
How about
+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
Joined
Jun 3, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 3, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
60,180
Office Version
  1. 365
Platform
  1. Windows
Did you try the formula I suggested?
 

lrsjohn

New Member
Joined
Jun 3, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
60,180
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,136,845
Messages
5,678,097
Members
419,742
Latest member
Dropzyl88

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top