Consecutive Inspection Date

fahad_ibnfurjan · 2025-02-23T01:13:46-0500

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

A

B

C

D

E

F

G

H

1

Equipment

Inspection Date

Inspection due Date

Days

Consecutive

Summary

2

Inspection Date

Days

3

Welding Machine

01/01/25

31/01/25

I 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

4

Welding Machine

01/02/25

28/02/25

5

Air Compressure

01/01/25

31/01/25

6

Elc Generator

01/01/25

31/01/25

7

Backhoe Loaders

01/01/25

31/01/25

8

Elc Generator

05/02/25

28/02/25

9

Air Compressure

01/02/25

28/02/25

10

Welding Machine

02/03/25

31/03/25

11

Air Compressure

01/03/25

31/03/25

12

Air Compressure

01/04/25

30/04/25

13

Backhoe Loaders

10/02/25

28/02/25

14

Welding Machine

01/04/25

30/04/25

15

Backhoe Loaders

08/03/25

31/03/25

16

Backhoe Loaders

04/04/25

30/04/25

17

Elc Generator

01/03/25

31/03/25

18

Elc Generator

01/04/25

30/04/25

Sheet1

Cell Formulas
Range	Formula
D3:D18	D3	=$C3-$B3+1
E3:E18	E3	=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:F18	F3	=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:G18	G3	=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
Cell	Condition		Cell Format	Stop If True
A3:G20	Expression	=$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)),"")	text	NO
A3:G20	Expression	=$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)),"")	text	NO
A3:G20	Expression	=$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)),"")	text	NO
A3:G20	Expression	=$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)),"")	text	NO

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,"")

Consecutive Inspection Date

fahad_ibnfurjan

New Member

Similar threads

Forum statistics

Share this page

Consecutive Inspection Date

fahad_ibnfurjan

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock