Consecutive Inspection Date

F

fahad_ibnfurjan

New Member
Joined
Sep 22, 2024
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
Greetings, I want to get a formula that summarizes my report when the equipment were conducted a consective inspection for at least two months.

also I need light formula version-365 insteaed of existing Formula I used in column E and F ver 2019

best regards

consecutive .xlsm
ABCDEFGH
1EquipmentInspection DateInspection due DateDaysConsecutiveSummary
2 Inspection DateDays
3Welding Machine01/01/2531/01/253101/01/2528/02/2559I need a formula to summarize as bellow: Air Compressure: Consecutive Inspection Date 05/02/25-30/04/25 for 120 days Backhoe Loaders: Consecutive Inspection Date N/A Elc Generator: Consecutive Inspection Date 02/03/25-30/04/25 for 85 days Welding Machine: Consecutive Inspection Date 1/1/25-28/2/25 for 59 days 02/03/25-30/04/25 for 60 days
4Welding Machine01/02/2528/02/252801/01/2528/02/2559
5Air Compressure01/01/2531/01/253101/01/2530/04/25120
6Elc Generator01/01/2531/01/253101/01/2531/01/2531
7Backhoe Loaders01/01/2531/01/253101/01/2531/01/2531
8Elc Generator05/02/2528/02/252405/02/2530/04/2585
9Air Compressure01/02/2528/02/252801/01/2530/04/25120
10Welding Machine02/03/2531/03/253002/03/2530/04/2560
11Air Compressure01/03/2531/03/253101/01/2530/04/25120
12Air Compressure01/04/2530/04/253001/01/2530/04/25120
13Backhoe Loaders10/02/2528/02/251910/02/2528/02/2519
14Welding Machine01/04/2530/04/253002/03/2530/04/2560
15Backhoe Loaders08/03/2531/03/252408/03/2531/03/2524
16Backhoe Loaders04/04/2530/04/252704/04/2530/04/2527
17Elc Generator01/03/2531/03/253105/02/2530/04/2585
18Elc Generator01/04/2530/04/253005/02/2530/04/2585
Sheet1
Cell Formulas
RangeFormula
D3:D18D3=$C3-$B3+1
E3:E18E3=IFERROR(MAX(IFERROR(SMALL(IFERROR($B$3:$B$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(1&":"&COUNTIF($A$3:$A3,$A3))))/(SMALL(IFERROR($B$3:$B$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(1&":"&COUNTIF($A$3:$A3,$A3))))-IFERROR(SMALL(IFERROR($C$3:$C$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(1&":"&COUNTIF($A$3:$A3,$A3)))-1),0)<>1),"")),"")
F3:F18F3=IFERROR(MIN(IFERROR(SMALL(IFERROR($C$3:$C$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(COUNTIF($A$3:$A3,$A3)&":"&COUNTIF($A$3:$A$20,$A3))))/(IFERROR(SMALL(IFERROR($B$3:$B$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(COUNTIF($A$3:$A3,$A3)&":"&COUNTIF($A$3:$A$20,$A3)))+1),0)-SMALL(IFERROR($C$3:$C$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(COUNTIF($A$3:$A3,$A3)&":"&COUNTIF($A$3:$A$20,$A3))))<>1),"")),"")
G3:G18G3=IFERROR(MIN(IFERROR(SMALL(IFERROR($C$3:$C$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(COUNTIF($A$3:$A3,$A3)&":"&COUNTIF($A$3:$A$20,$A3))))/(IFERROR(SMALL(IFERROR($B$3:$B$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(COUNTIF($A$3:$A3,$A3)&":"&COUNTIF($A$3:$A$20,$A3)))+1),0)-SMALL(IFERROR($C$3:$C$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(COUNTIF($A$3:$A3,$A3)&":"&COUNTIF($A$3:$A$20,$A3))))<>1),""))-MAX(IFERROR(SMALL(IFERROR($B$3:$B$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(1&":"&COUNTIF($A$3:$A3,$A3))))/(SMALL(IFERROR($B$3:$B$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(1&":"&COUNTIF($A$3:$A3,$A3))))-IFERROR(SMALL(IFERROR($C$3:$C$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(1&":"&COUNTIF($A$3:$A3,$A3)))-1),0)<>1),""))+1,"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:G20Expression=$A3=IFERROR( INDEX($A$3:$A$20,MATCH(SMALL(IFERROR(SMALL(COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),ROW(INDIRECT("1:"&ROWS($A$3:$A$20))))/(SMALL(COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),ROW(INDIRECT("1:"&ROWS($A$3:$A$20))))<>SMALL(IFERROR(SMALL(COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),ROW(INDIRECT("1:"&1+ROWS($A$3:$A$20)))),0),ROW(INDIRECT("1:"&ROWS($A$3:$A$20))))),""),4),COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),0)),"")textNO
A3:G20Expression=$A3=IFERROR( INDEX($A$3:$A$20,MATCH(SMALL(IFERROR(SMALL(COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),ROW(INDIRECT("1:"&ROWS($A$3:$A$20))))/(SMALL(COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),ROW(INDIRECT("1:"&ROWS($A$3:$A$20))))<>SMALL(IFERROR(SMALL(COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),ROW(INDIRECT("1:"&1+ROWS($A$3:$A$20)))),0),ROW(INDIRECT("1:"&ROWS($A$3:$A$20))))),""),3),COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),0)),"")textNO
A3:G20Expression=$A3=IFERROR( INDEX($A$3:$A$20,MATCH(SMALL(IFERROR(SMALL(COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),ROW(INDIRECT("1:"&ROWS($A$3:$A$20))))/(SMALL(COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),ROW(INDIRECT("1:"&ROWS($A$3:$A$20))))<>SMALL(IFERROR(SMALL(COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),ROW(INDIRECT("1:"&1+ROWS($A$3:$A$20)))),0),ROW(INDIRECT("1:"&ROWS($A$3:$A$20))))),""),2),COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),0)),"")textNO
A3:G20Expression=$A3=IFERROR( INDEX($A$3:$A$20,MATCH(SMALL(IFERROR(SMALL(COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),ROW(INDIRECT("1:"&ROWS($A$3:$A$20))))/(SMALL(COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),ROW(INDIRECT("1:"&ROWS($A$3:$A$20))))<>SMALL(IFERROR(SMALL(COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),ROW(INDIRECT("1:"&1+ROWS($A$3:$A$20)))),0),ROW(INDIRECT("1:"&ROWS($A$3:$A$20))))),""),1),COUNTIF($A$3:$A$20,"<="&$A$3:$A$20),0)),"")textNO



code below I used it for long data

VBA Code: 
  Public Sub Add__Consecutive_Date()
    Const StrtRw As Long = 3, StrCl As Long = 1, RwCnts As Long = 18
    Dim DtrmnItm As String
    Dim Clr As Variant
   
         With Worksheets(1)
      
                Items = .Cells(StrtRw, StrCl).Address(0, 1)
                Itemss1 = .Cells(StrtRw, StrCl).Resize(1, 1).Address(1, 1) & ":" & Items
                Itemss2 = .Cells(StrtRw, StrCl).Resize(RwCnts, 1).Address(1, 1)
               
                STRTDate = .Cells(StrtRw, StrCl + 1).Resize(RwCnts, 1).Address(1, 1)
                EndDate = .Cells(StrtRw, StrCl + 2).Resize(RwCnts, 1).Address(1, 1)
               
                ItmCnt1 = "COUNTIF(" & Itemss1 & "," & Items & ")"
                ItmCnt2 = "COUNTIF(" & Itemss2 & "," & Items & ")"
               
                ItmCnt3 = "ROW(INDIRECT(1&"":""&" & ItmCnt1 & "))"
               
                ItmCnt4 = "ROW(INDIRECT(" & ItmCnt1 & "&"":""&" & ItmCnt2 & "))"
               
                DtrmnItms = "(" & Items & "=" & Itemss2 & ")"
               
                STRTDateSeq = "SMALL(IFERROR(" & STRTDate & "/" & DtrmnItms & ","""")," & ItmCnt3 & ")"
                STRTDateSeqOffsetUp = "IFERROR(SMALL(IFERROR(" & STRTDate & "/" & DtrmnItms & ","""")," & ItmCnt4 & "+1),0)"
               
                EndDateSeq = "SMALL(IFERROR(" & EndDate & "/" & DtrmnItms & ","""")," & ItmCnt4 & ")"
                EndDateSeqOffsetDwn = "IFERROR(SMALL(IFERROR(" & EndDate & "/" & DtrmnItms & ","""")," & ItmCnt3 & "-1),0)"
               
                DIFFSTRTDateSeq = "MAX(IFERROR(STRTDateSeq/(STRTDateSeq-EndDateSeqOffsetDwn<>1),""""))"
                DIFFEndDateSeq = "MIN(IFERROR(EndDateSeq/(STRTDateSeqOffsetUp-EndDateSeq<>1),""""))"
               
                Different = "MIN(IFERROR(EndDateSeq/(STRTDateSeqOffsetUp-EndDateSeq<>1),""""))-MAX(IFERROR(STRTDateSeq/(STRTDateSeq-EndDateSeqOffsetDwn<>1),""""))+1"
           
            With .Cells(StrtRw, 5)
                    .FormulaArray = "=" & "IFERROR(" & DIFFSTRTDateSeq & ","""")"
                    .Replace "STRTDateSeq", STRTDateSeq
                    .Replace "EndDateSeqOffsetDwn", EndDateSeqOffsetDwn
                    .AutoFill .Resize(RwCnts, 1)
           
            End With
           
           
            With .Cells(StrtRw, 6)
                    .FormulaArray = "=" & "IFERROR(" & DIFFEndDateSeq & ","""")"
                    .Replace "STRTDateSeqOffsetUp", STRTDateSeqOffsetUp
                    .Replace "EndDateSeq", EndDateSeq
                    .AutoFill .Resize(RwCnts, 1)
            End With
            With .Cells(StrtRw, 7)
                    .FormulaArray = "=" & "IFERROR(" & Different & ","""")"
                   
                    .Replace "EndDateSeqOffsetDwn", EndDateSeqOffsetDwn
                    .Replace "EndDateSeqOffsetDwn", EndDateSeqOffsetDwn
                    .Replace "STRTDateSeqOffsetUp", STRTDateSeqOffsetUp
                    .Replace "EndDateSeq", EndDateSeq
                    .Replace "STRTDateSeq", STRTDateSeq
                    .AutoFill .Resize(RwCnts, 1)
            End With
           
            .Cells.FormatConditions.Delete
            Clr = Array(RGB(192, 230, 345), RGB(218, 242, 208), RGB(251, 226, 213), RGB(217, 217, 217))
            For I = LBound(Clr) To UBound(Clr)
                With .Cells(StrtRw, 1).Resize(RwCnts, 7)
                        .FormatConditions.Add Type:=xlExpression, Formula1:= _
                        "=" & .Cells(1, 1).Address(0, 1) & "=" & "IFERROR( INDEX(" & Itemss2 & ",MATCH(SMALL(IFERROR(SMALL(COUNTIF(" & Itemss2 & ",""<=""&" & Itemss2 & "),ROW(INDIRECT(""1:""&ROWS(" & Itemss2 & "))))/(SMALL(COUNTIF(" & Itemss2 & ",""<=""&" & Itemss2 & "),ROW(INDIRECT(""1:""&ROWS(" & Itemss2 & "))))<>SMALL(IFERROR(SMALL(COUNTIF(" & Itemss2 & ",""<=""&" & Itemss2 & "),ROW(INDIRECT(""1:""&1+ROWS(" & Itemss2 & ")))),0),ROW(INDIRECT(""1:""&ROWS(" & Itemss2 & "))))),"""")," & I + 1 & "),COUNTIF(" & Itemss2 & ",""<=""&" & Itemss2 & "),0)),"""")"
                        .FormatConditions(.FormatConditions.Count).SetFirstPriority
                        With .FormatConditions(1).Interior
                            .Color = Clr(I)
                        End With
                        .FormatConditions(1).StopIfTrue = False
               
            End With
            Next
           
        End With
   
    End Sub
E
Excel Formula: 
IFERROR(MAX(IFERROR(SMALL(IFERROR($B$3:$B$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(1&":"&COUNTIF($A$3:$A3,$A3))))/(SMALL(IFERROR($B$3:$B$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(1&":"&COUNTIF($A$3:$A3,$A3))))-IFERROR(SMALL(IFERROR($C$3:$C$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(1&":"&COUNTIF($A$3:$A3,$A3)))-1),0)<>1),"")),"")
F
VBA Code: 
IFERROR(MIN(IFERROR(SMALL(IFERROR($C$3:$C$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(COUNTIF($A$3:$A3,$A3)&":"&COUNTIF($A$3:$A$20,$A3))))/(IFERROR(SMALL(IFERROR($B$3:$B$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(COUNTIF($A$3:$A3,$A3)&":"&COUNTIF($A$3:$A$20,$A3)))+1),0)-SMALL(IFERROR($C$3:$C$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(COUNTIF($A$3:$A3,$A3)&":"&COUNTIF($A$3:$A$20,$A3))))<>1),"")),"")
F-E
VBA Code: 
IFERROR(MIN(IFERROR(SMALL(IFERROR($C$3:$C$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(COUNTIF($A$3:$A3,$A3)&":"&COUNTIF($A$3:$A$20,$A3))))/(IFERROR(SMALL(IFERROR($B$3:$B$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(COUNTIF($A$3:$A3,$A3)&":"&COUNTIF($A$3:$A$20,$A3)))+1),0)-SMALL(IFERROR($C$3:$C$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(COUNTIF($A$3:$A3,$A3)&":"&COUNTIF($A$3:$A$20,$A3))))<>1),""))-MAX(IFERROR(SMALL(IFERROR($B$3:$B$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(1&":"&COUNTIF($A$3:$A3,$A3))))/(SMALL(IFERROR($B$3:$B$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(1&":"&COUNTIF($A$3:$A3,$A3))))-IFERROR(SMALL(IFERROR($C$3:$C$20/($A3=$A$3:$A$20),""),ROW(INDIRECT(1&":"&COUNTIF($A$3:$A3,$A3)))-1),0)<>1),""))+1,"")
 
You must log in or register to reply here.

Similar threads

C
  • Question Question
How to calculate a weekend as a working day
Replies
4
Views
86
kvsrinivasamurthy
K
B
  • Solved
Highlight expired dates
Replies
3
Views
54
SunnyAlv
SunnyAlv
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
164
kivikatz
K
JTL9161
  • Question Question
Same formula's but one puts ties in Alpha order and the other does not
Replies
1
Views
41
jasonb75
J
Sumeluar
  • Question Question
Match a task if it is within a date range
Replies
0
Views
163
Sumeluar
Sumeluar

Share this page

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
Back
Top