fahad_ibnfurjan
New Member
- Joined
- Sep 22, 2024
- Messages
- 1
- Office Version
- 2019
- Platform
- 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
code below I used it for long data
E
F
F-E
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 | 31 | 01/01/25 | 28/02/25 | 59 | 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 | 28 | 01/01/25 | 28/02/25 | 59 | |||
5 | Air Compressure | 01/01/25 | 31/01/25 | 31 | 01/01/25 | 30/04/25 | 120 | |||
6 | Elc Generator | 01/01/25 | 31/01/25 | 31 | 01/01/25 | 31/01/25 | 31 | |||
7 | Backhoe Loaders | 01/01/25 | 31/01/25 | 31 | 01/01/25 | 31/01/25 | 31 | |||
8 | Elc Generator | 05/02/25 | 28/02/25 | 24 | 05/02/25 | 30/04/25 | 85 | |||
9 | Air Compressure | 01/02/25 | 28/02/25 | 28 | 01/01/25 | 30/04/25 | 120 | |||
10 | Welding Machine | 02/03/25 | 31/03/25 | 30 | 02/03/25 | 30/04/25 | 60 | |||
11 | Air Compressure | 01/03/25 | 31/03/25 | 31 | 01/01/25 | 30/04/25 | 120 | |||
12 | Air Compressure | 01/04/25 | 30/04/25 | 30 | 01/01/25 | 30/04/25 | 120 | |||
13 | Backhoe Loaders | 10/02/25 | 28/02/25 | 19 | 10/02/25 | 28/02/25 | 19 | |||
14 | Welding Machine | 01/04/25 | 30/04/25 | 30 | 02/03/25 | 30/04/25 | 60 | |||
15 | Backhoe Loaders | 08/03/25 | 31/03/25 | 24 | 08/03/25 | 31/03/25 | 24 | |||
16 | Backhoe Loaders | 04/04/25 | 30/04/25 | 27 | 04/04/25 | 30/04/25 | 27 | |||
17 | Elc Generator | 01/03/25 | 31/03/25 | 31 | 05/02/25 | 30/04/25 | 85 | |||
18 | Elc Generator | 01/04/25 | 30/04/25 | 30 | 05/02/25 | 30/04/25 | 85 | |||
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
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),"")),"")
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),"")),"")
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,"")