Identify equipment used -No of consecutive days

[ABC1]

Hello,
I appreciate your time and effort.

I would like to have formula for column O and P.
Column A : lots of Asset
Column C: date
If one Asset is used more than 5 consecutive days than that date range (Start date and end date) should go to column 0 and no of consecutive days go to Column P.
I try no of different formula
Column E : =IF(C4-C3=1,C4-C3+E3,1)
Column F: =IF(E4+1=E5, "In used","End")
Column G: =IF(A4=A5,IF(C4-C3=1,C4-C3+G3,1),C4-C3+G3)
Column H: =IF(A4<>A3,"START OUTTER",IF(A4=A5,"Used","Outer End"))
How can I copy Column C and (E or G) to column O and P base on my condition.
Also would like to update status as "consider as day use- if no of days used less than 5 days -and this total no need to copy Column Q.
Thanks,

 

[Toadstool]

Hi ABC1,

You can't use the AGGREGATE SUM option 9 when specifying ROW numbers for AGGREGATE. i.e. the option 9 requires the array specified such as =AGGREGATE(9,6,$D$2:$D$5) which would return 0+4+4+4=12

I suspect you are trying to sum the column D hours for the Asset specified in N3 for the dates specified in P3.

If you can fix that date in column C (please!) and let me split column P into two columns, start date and end date, then it becomes a simple SUMIFS function. As it is we can use that column P formula to find the start and end rows for a SUM.
NOTE: The #REF errors are because those rows are >25.

ABC1.xlsx
	M	N	O	P	Q	R
2	Last Row	Asset #	Description	Unbroken Use Period	Count of Continuous Days Use	Total No. of Hours based on continued use
3	6	113	Van Helsing	1/1/2022 - 5/1/2022	5	20
4	16	113	Van Helsing	7/1/2022 - 16/1/2022	10	40
5	21	101	Van Halen	2/2/2022 - 6/2/2022	5	20
6	41	101	Van Halen	9/2/2022 - 26/2/2022	18	#REF!
7	53	101	Van Halen	3/3/2022 - 10/3/2022	8	#REF!
4th

Cell Formulas
Range	Formula
M3:M7	M3	=IFERROR(AGGREGATE(15,6,ROW($G$2:$G$9998)-ROW($G$1)/(($F$2:$F$9998="End")*($E$2:$E$9998>=5)),ROW()-ROW($M$2)),"")
N3:O7	N3	=IF($M3="","",INDEX(A$2:A$9998,$M3))
P3:P7	P3	=IF($M3="","",TEXT(INDEX($C$2:$C$9998,AGGREGATE(14,6,ROW($C$2:$C$9998)-ROW($C$1)/(($A$2:$A$9998=$N3)*($E$2:$E$9998=1)*(ROW($C$2:$C$9998)<$M3)),1)),"d/m/yyyy")&" - "&TEXT(INDEX($C$2:$C$9998,$M3),"d/m/yyyy"))
Q3:Q7	Q3	=IF($M3="","",INDEX($E$2:$E$9998,$M3))
R3:R7	R3	=IF($M3="","",SUM(INDEX($D$2:$D$25,AGGREGATE(14,6,ROW($C$2:$C$9998)-ROW($C$1)/(($A$2:$A$9998=$N3)*($E$2:$E$9998=1)*(ROW($C$2:$C$9998)<$M3)),1)):INDEX($D$2:$D$25,M3)))

 

[ABC1]

Hello Toadstool,
I did start from fresh with Asset as number and date is in date format. there is one more column E (total $) is for Total.
Now we can split p column into two and then use sumif function
Total no of hrs based on continue used - mine is not coming correct way

=IF($M3="","",SUM(INDEX($D$2:$D$6000,AGGREGATE(14,6,ROW($C$2:$C$6000)-ROW($C$1)/(($A$2:$A$6000=$O3)*($F$2:$F$6000=1)*(ROW($C$2:$C$6000)<$M3)),1)),INDEX($D$2:$D$6000,M3)))

Thanks

 

[Toadstool]

OK, I've split the range into two columns and the calculations become easier with real date fields.

ABC1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Asset	Equipment	Date	Sum of Something	Total $	No of Consecutive Days	Status
2	884	Car Ladelavigne	1-Jan-2020	5.5	4	1	End						Actual Last Row	Last Row	Asset #	Description	Unbroken Use Period Start	Unbroken Use Period End	Count of Continuous Days Use	Total No. of Hours based on continued use	Charges
3	113	Van Helsing	1-Jan-2020	12	10	1	In used						7	6	113	Van Helsing	1-Jan-2020	5-Jan-2020	5	60	50
4	113	Van Helsing	2-Jan-2020	12	10	2	In used						17	16	113	Van Helsing	7-Jan-2020	16-Jan-2020	10	120	100
5	113	Van Helsing	3-Jan-2020	12	10	3	In used						22	21	101	Van Halen	2-Feb-2020	6-Feb-2020	5	60	50
6	113	Van Helsing	4-Jan-2020	12	10	4	In used						42	41	101	Van Halen	9-Feb-2020	26-Feb-2020	18	216	180
7	113	Van Helsing	5-Jan-2020	12	10	5	End						54	53	884	Car Ladelavigne	3-Mar-2020	10-Mar-2020	8	96	80
8	113	Van Helsing	7-Jan-2020	12	10	1	In used
9	113	Van Helsing	8-Jan-2020	12	10	2	In used
10	113	Van Helsing	9-Jan-2020	12	10	3	In used
11	113	Van Helsing	10-Jan-2020	12	10	4	In used
12	113	Van Helsing	11-Jan-2020	12	10	5	In used
5th

Cell Formulas
Range	Formula
F2:F12	F2	=IF(A2="","",IF(AND(A2=A1,IFERROR(VALUE(C2)=VALUE(C1)+1,0)),SUM(F1)+1,1))
G2:G12	G2	=IF(A2="","",IF(F2+1=F3,"In used","End"))
M3:M12	M3	=IF($N3="","",N3+ROW($A$1))
N3:N12	N3	=IFERROR(AGGREGATE(15,6,ROW($H$2:$H$99999)-ROW($H$1)/(($G$2:$G$99999="End")*($F$2:$F$99999>=5)),ROW()-ROW($N$2)),"")
O3:P12	O3	=IF($N3="","",INDEX(A$2:A$99999,$N3))
Q3:Q12	Q3	=IF($N3="","",INDEX($C$2:$C$99999,AGGREGATE(14,6,ROW($C$2:$C$99999)-ROW($C$1)/(($A$2:$A$99999=$O3)*($F$2:$F$99999=1)*(ROW($C$2:$C$99999)<$N3)),1)))
R3:R12	R3	=IF($N3="","",INDEX($C$2:$C$99999,$N3))
S3:S12	S3	=IF($N3="","",INDEX($F$2:$F$99999,$N3))
T3:T12	T3	=IF($N3="","",SUMIFS($D$2:$D$99999,$A$2:$A$99999,O3,$C$2:$C$99999,">="&Q3,$C$2:$C$99999,"<="&R3))
U3:U12	U3	=IF($N3="","",SUMIFS($E$2:$E$99999,$A$2:$A$99999,O3,$C$2:$C$99999,">="&Q3,$C$2:$C$99999,"<="&R3))

 

[ABC1]

Ok Thanks lots. I will work on it

 

[ABC1]

Thanks lots. Its worked. Again thanks

May I ask you your help for one more different puzzle.
Formula is ready but I need to update that formula to apply other cell.

 

[Toadstool]

Thanks lots. Its worked. Again thanks

May I ask you your help for one more different puzzle.
Formula is ready but I need to update that formula to apply other cell.

You're welcome.
If it's a new question then you should start a new thread so that others can also contribute.

 

[ABC1]

I will start new thread.
Thanks for your help

 

[ABC1]

I will start new thread.
Thanks for your help

Hello Toadstool,
I have little different scenario - want to count no of quantity(quantity is not same) for on of consecutive days. We can ignore Column D9 (Sum of equiphrs). below data coming from pivot table.
Second screen short data we calculated below. Would you please help me. I appreciate your time