calculate counts of days for consecutive five dates based on time for item

[Hasson]

hi
I would calculate days for consecutive five dates based on time for item . if I have five or more consecutive five dates contain time is in column C 00.00 then should calculate days for consecutive five dates for item in column A as is highlighted in column B the result should show in columns I,J

a.xlsx
	A	B	C	D	E	F	G	H	I	J
1	CODE	DATE	TIME						CODE	DAYS
2	AA-S	01/10/2021	13:00:00 ص						AA-S	10 DAYS
3	AA-S	02/10/2021	00:00						BB-DD	15 DAYS
4	AA-S	05/10/2021	00:00						MM-NN	11DAYS
5	AA-S	10/10/2021	00:00
6	AA-S	11/10/2021	00:00
7	AA-S	12/10/2021	00:00
8	AA-S	13/10/2021	00:00
9	AA-S	14/10/2021	00:00
10	AA-S	15/10/2021	00:00
11	BB-DD	01/10/2021	14:00:00 ص
12	BB-DD	02/10/2021	00:00
13	BB-DD	15/10/2021	00:00
14	BB-DD	20/10/2021	00:00
15	BB-DD	26/10/2021	00:00
16	BB-DD	27/10/2021	00:00
17	BB-DD	28/10/2021	00:00
18	BB-DD	29/10/2021	00:00
19	BB-DD	30/10/2021	00:00
20	SS-NN	01/10/2021	00:00
21	SS-NN	02/10/2021	00:00
22	SS-NN	15/10/2021	00:00
23	SS-NN	20/10/2021	00:00
24	SS-NN	24/10/2021	00:00
25	SS-NN	25/10/2021	00:00
26	SS-NN	26/10/2021	00:00
27	SS-NN	27/10/2021	17:00:00 ص
28	SS-NN	28/10/2021	00:00
29	MM-NN	15/10/2021	00:00
30	MM-NN	20/10/2021	00:00
31	MM-NN	24/10/2021	00:00
32	MM-NN	25/10/2021	00:00
33	MM-NN	26/10/2021	00:00
Sheet1

 

[Eric W]

What do you consider a "five date"? From your example, it seems to be a date where the day of the month is a multiple of 5. This holds true, except for the last date you have marked 26/10/2021. Assuming that is the case, then I think this is too complicated for a native Excel function. I wrote up a User Defined Function though. If I understand your requirements correctly, and you're OK with a User Function, then here's the function:

Public Function Consec5(MyTab As Range, MyCode As String)
Dim MyDat As Variant, loc1 As Long, loc2 As Long, i As Long, w As Long, mr As Long

    MyDat = MyTab.Value
    
    loc1 = 0
    loc2 = 0
    mr = 0
    
    For i = 1 To UBound(MyDat)
        If MyDat(i, 1) = MyCode Then
            If MyDat(i, 3) = 0 Then
                If Day(MyDat(i, 2)) Mod 5 = 0 Then
                    If loc1 = 0 Then
                        loc1 = i
                    Else
                        loc2 = i
                    End If
                End If
            Else
                If loc1 > 0 And loc2 > 0 Then
                    w = MyDat(loc2, 2) - MyDat(loc1, 2)
                    If w > mr Then mr = w
                End If
                loc1 = 0
                loc2 = 0
            End If
        Else
            If loc1 > 0 And loc2 > 0 Then
                w = MyDat(loc2, 2) - MyDat(loc1, 2)
                If w > mr Then mr = w
            End If
            loc1 = 0
            loc2 = 0
        End If
    Next i
    
    If loc1 > 0 And loc2 > 0 Then
        w = MyDat(loc2, 2) - MyDat(loc1, 2)
        If w > mr Then mr = w
    End If

    If mr >= 5 Then
        Consec5 = mr
    End If
    
End Function

Call it like this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	CODE	DATE	TIME						CODE	DAYS
2	AA-S	10/1/2021	13:00:00 ص						AA-S	10
3	AA-S	10/2/2021	0						BB-DD	15
4	AA-S	10/5/2021	0						MM-NN	10
5	AA-S	10/10/2021	0
6	AA-S	10/11/2021	0
7	AA-S	10/12/2021	0
8	AA-S	10/13/2021	0
9	AA-S	10/14/2021	0
10	AA-S	10/15/2021	0
11	BB-DD	10/1/2021	14:00:00 ص
12	BB-DD	10/2/2021	0
13	BB-DD	10/15/2021	0
14	BB-DD	10/20/2021	0
15	BB-DD	10/26/2021	0
16	BB-DD	10/27/2021	0
17	BB-DD	10/28/2021	0
18	BB-DD	10/29/2021	0
19	BB-DD	10/30/2021	0
20	SS-NN	10/1/2021	0
21	SS-NN	10/2/2021	0
22	SS-NN	10/15/2021	0
23	SS-NN	10/20/2021	0
24	SS-NN	10/24/2021	0
25	SS-NN	10/25/2021	0
26	SS-NN	10/26/2021	0
27	SS-NN	10/27/2021	17:00:00 ص
28	SS-NN	10/28/2021	0
29	MM-NN	10/15/2021	0
30	MM-NN	10/20/2021	0
31	MM-NN	10/24/2021	0
32	MM-NN	10/25/2021	0
33	MM-NN	10/26/2021	0
Sheet17

Cell Formulas
Range	Formula
J2:J4	J2	=consec5($A$2:$C$33,I2)

If that's not what you want, let me know and I'll take another look.

 

[Hasson]

except for the last date you have marked 26/10/2021.

I find difficulty to understand this sentence what do you mean? why MM-NN gives wrong value it should give 11 not 10 when calculate last fives dates?
when show the result should also show the item in column I (CODE)

 

[Eric W]

How do you choose which dates to include? Your first range has 7, the next has 7, the last has 5. My next guess is your start date is the first date in the range with a time of 00:00, and the last date is the last date in the range.

 

[Hasson]

My next guess is your start date is the first date in the range with a time of 00:00, and the last date is the last date in the range.

yes this is correct. so why gives wrong value?

 

[Eric W]

See if this does what you want:

Book1
	A	B	C	D	E	F	G	H	I	J
1	CODE	DATE	TIME						CODE	DAYS
2	AA-S	10/1/2021	13:00:00 ص						AA-S	10
3	AA-S	10/2/2021	0						BB-DD	15
4	AA-S	10/5/2021	0						MM-NN	11
5	AA-S	10/10/2021	0
6	AA-S	10/11/2021	0
7	AA-S	10/12/2021	0
8	AA-S	10/13/2021	0
9	AA-S	10/14/2021	0
10	AA-S	10/15/2021	0
11	BB-DD	10/1/2021	14:00:00 ص
12	BB-DD	10/2/2021	0
13	BB-DD	10/15/2021	0
14	BB-DD	10/20/2021	0
15	BB-DD	10/26/2021	0
16	BB-DD	10/27/2021	0
17	BB-DD	10/28/2021	0
18	BB-DD	10/29/2021	0
19	BB-DD	10/30/2021	0
20	SS-NN	10/1/2021	0
21	SS-NN	10/2/2021	0
22	SS-NN	10/15/2021	0
23	SS-NN	10/20/2021	0
24	SS-NN	10/24/2021	0
25	SS-NN	10/25/2021	0
26	SS-NN	10/26/2021	0
27	SS-NN	10/27/2021	17:00:00 ص
28	SS-NN	10/28/2021	0
29	MM-NN	10/15/2021	0
30	MM-NN	10/20/2021	0
31	MM-NN	10/24/2021	0
32	MM-NN	10/25/2021	0
33	MM-NN	10/26/2021	0
Sheet17

Cell Formulas
Range	Formula
J2:J4	J2	=LOOKUP(2,1/($A$2:$A$33=I2),$B$2:$B$33)-AGGREGATE(15,6,$B$2:$B$33/($A$2:$A$33=I2)/(MOD(DAY($B$2:$B$33),5)=0)/($C$2:$C$33=0),1)

 

[Hasson]

great ! waht about CODE in column I ? shouldn't writing