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,

Assuming Date is sorted ascending with Asset sorted either ascending or descending then this should work, but I need helper column L to keep track of where I am through the list, or the formulae get really long.

ABC1.xlsx
	A	B	C	D	E	F	G					L	M	N	O	P
1	Asset	Equipment	Date	No Hrs	Consecutive Days Used	Status
2	884	Car Ladelavigne	02-Feb-21	0	1	End						Last Row	Asset #	Description	Unbroken Use Period	Count of Continuous Days Use
3	113	Van Helsing	01-Jan-21	4	1	In used						6	113	Van Helsing	1/1/2021 - 5/1/2021	5
4	113	Van Helsing	02-Jan-21	4	2	In used						16	113	Van Helsing	7/1/2021 - 16/1/2021	10
5	113	Van Helsing	03-Jan-21	4	3	In used
6	113	Van Helsing	04-Jan-21	4	4	In used
7	113	Van Helsing	05-Jan-21	4	5	End
8	113	Van Helsing	07-Jan-21	4	1	In used
9	113	Van Helsing	08-Jan-21	4	2	In used
10	113	Van Helsing	09-Jan-21	4	3	In used
11	113	Van Helsing	10-Jan-21	4	4	In used
12	113	Van Helsing	11-Jan-21	4	5	In used
13	113	Van Helsing	12-Jan-21	4	6	In used
14	113	Van Helsing	13-Jan-21	4	7	In used
15	113	Van Helsing	14-Jan-21	4	8	In used
16	113	Van Helsing	15-Jan-21	4	9	In used
17	113	Van Helsing	16-Jan-21	4	10	End
18	101	Van Halen	02-Feb-21	4	1	End
19
Sheet1

Cell Formulas
Range	Formula
E2:E19	E2	=IF(A2="","",IF(AND(A2=A1,C2=SUM(C1)+1),SUM(E1)+1,1))
F2:F19	F2	=IF(A2="","",IF(E2+1=E3,"In used","End"))
L3:L19	L3	=IFERROR(AGGREGATE(15,6,ROW($G$2:$G$9998)-ROW($G$1)/(($F$2:$F$9998="End")*($E$2:$E$9998>=5)),ROW()-ROW($L$2)),"")
M3:N19	M3	=IF($L3="","",INDEX(A$2:A$9998,$L3))
O3:O19	O3	=IF($L3="","",TEXT(INDEX($C$2:$C$9998,AGGREGATE(14,6,ROW($C$2:$C$9998)-ROW($C$1)/(($A$2:$A$9998=$M3)*($E$2:$E$9998=1)*(ROW($C$2:$C$9998)<$L3)),1)),"d/m/yyyy")&" - "&TEXT(INDEX($C$2:$C$9998,$L3),"d/m/yyyy"))
P3:P19	P3	=IF($L3="","",INDEX($E$2:$E$9998,$L3))

 

[ABC1]

Hello Toadstool,
First of all I would like to thank you for your time and effort.
Would you please, take a look formula is not working for me. when I debug formula look like C1 column is not integer that may be the reason return 0 for C1.
Regards,
ABC1

 

[Toadstool]

But E2 is not returning zero, it returns a 1 because that 18 Jan entry is the first day of a rental. C1 is alphabetic but that's why I use SUM, so I get a zero returned for alphabetics instead of a #VALUE error.

I am assuming column C is an actual date from row 2 down. Prove this by putting in an unused cell
=C2+1
...and is should display 19 Jan.

E2 and E3 should both be a 1 but E4 should display a 2 an should contain the formula

=IF(A4="","",IF(AND(A4=A3,C4=SUM(C3)+1),SUM(E3)+1,1))

Make sure you have automatic calculation or press F9 before checking.

 

[ABC1]

Hello Toadstool,
C2+1 is working. but not formula. also I did press F9. what else I should try
Please
Thanks

 

[Toadstool]

OK, that test in column F won't actually prove the date is text but the result showing a year of 2022 suggests your date is text and so it defaults the missing year to the current year.

Here's a formula which should work except that with the year missing the range will always show current year values. You really need to replace the column C dates with the full date.

ABC1.xlsx
	A	B	C	D	E	F	G					L	M	N	O	P
1	Asset	Equipment	Date	No Hrs	No of Consecutive Days	Status
2	884	Car Ladelavigne	2-Feb	0	1	End						Last Row	Asset #	Description	Unbroken Use Period	Count of Continuous Days Use
3	113	Van Helsing	1-Jan	4	1	In used						6	113	Van Helsing	1/1/2022 - 5/1/2022	5
4	113	Van Helsing	2-Jan	4	2	In used						16	113	Van Helsing	7/1/2022 - 16/1/2022	10
5	113	Van Helsing	3-Jan	4	3	In used
6	113	Van Helsing	4-Jan	4	4	In used
7	113	Van Helsing	5-Jan	4	5	End
8	113	Van Helsing	7-Jan	4	1	In used
9	113	Van Helsing	8-Jan	4	2	In used
10	113	Van Helsing	9-Jan	4	3	In used
11	113	Van Helsing	10-Jan	4	4	In used
12	113	Van Helsing	11-Jan	4	5	In used
13	113	Van Helsing	12-Jan	4	6	In used
14	113	Van Helsing	13-Jan	4	7	In used
15	113	Van Helsing	14-Jan	4	8	In used
16	113	Van Helsing	15-Jan	4	9	In used
17	113	Van Helsing	16-Jan	4	10	End
18	101	Van Halen	2-Feb	4	1	End
19
2nd

Cell Formulas
Range	Formula
E2:E19	E2	=IF(A2="","",IF(AND(A2=A1,IFERROR(VALUE(C2)=VALUE(C1)+1,0)),SUM(E1)+1,1))
F2:F19	F2	=IF(A2="","",IF(E2+1=E3,"In used","End"))
L3:L19	L3	=IFERROR(AGGREGATE(15,6,ROW($G$2:$G$9998)-ROW($G$1)/(($F$2:$F$9998="End")*($E$2:$E$9998>=5)),ROW()-ROW($L$2)),"")
M3:N19	M3	=IF($L3="","",INDEX(A$2:A$9998,$L3))
O3:O19	O3	=IF($L3="","",TEXT(INDEX($C$2:$C$9998,AGGREGATE(14,6,ROW($C$2:$C$9998)-ROW($C$1)/(($A$2:$A$9998=$M3)*($E$2:$E$9998=1)*(ROW($C$2:$C$9998)<$L3)),1)),"d/m/yyyy")&" - "&TEXT(INDEX($C$2:$C$9998,$L3),"d/m/yyyy"))
P3:P19	P3	=IF($L3="","",INDEX($E$2:$E$9998,$L3))

 

[ABC1]

Hello Toadstool,
Thanks - its worked.
L3 value is one row off(not returning correct row.)
Also In F2 column function we need to add one more condition for day used(if equipment used less that 5 days consider as Day Used" and that also need to be count and copy in column Q .
I do not have a word to appreciate you enough for your time and effort .
Thanks again

 

[Toadstool]

You're welcome.

"L3 value is one row off(not returning correct row.)"
It's returning the row number relative to the first row of data. Because your headings are in row 1 then a Row Number 3 would actually mean Excel row 4. This is done so it will work even if your headings were on row 5.


"Also In F2 column function we need to add one more condition for day used(if equipment used less that 5 days consider as Day Used" and that also need to be count and copy in column Q ."
I'm not sure what you're trying to calculate. If it's days in use of an Asset which is not accounted for in the >=5 days then I don't understand how that would work as you may have multiple entries for that Asset, or none if no contract was 5+ consecutive days.

 

[ABC1]

Thanks Toadstool,
For F2
If that equipment is "Used less than 5 consecutive days" then those day would be calculated as daily rate. and if that equipment used more than 5 consecutive days than that would be count as weekly rate.
This is the condition

Regards,

 

[Toadstool]

Here's that breakdown:

ABC1.xlsx
	A	B	C	D	E	F	G					L	M	N	O	P	Q	R	S	T	U
1	Asset	Equipment	Date	No Hrs	No of Consecutive Days	Status													$ 42.50		$ 53.00
2	884	Car Ladelavigne	2-Feb	0	1	End						Last Row	Asset #	Description	Unbroken Use Period	Count of Continuous Days Use	Asset	Weekly Days	Weekly	Daily Days	Daily
3	113	Van Helsing	1-Jan	4	1	In used						6	113	Van Helsing	1/1/2022 - 5/1/2022	5	101	31	$ 1,317.50	0	$ -
4	113	Van Helsing	2-Jan	4	2	In used						16	113	Van Helsing	7/1/2022 - 16/1/2022	10	113	15	$ 637.50	2	$ 106.00
5	113	Van Helsing	3-Jan	4	3	In used						21	101	Van Halen	2/2/2022 - 6/2/2022	5	884	0	$ -	5	$ 265.00
6	113	Van Helsing	4-Jan	4	4	In used						41	101	Van Halen	9/2/2022 - 26/2/2022	18
7	113	Van Helsing	5-Jan	4	5	End						53	101	Van Halen	3/3/2022 - 10/3/2022	8
8	113	Van Helsing	7-Jan	4	1	In used
9	113	Van Helsing	8-Jan	4	2	In used
10	113	Van Helsing	9-Jan	4	3	In used
3rd

Cell Formulas
Range	Formula
E2:E10	E2	=IF(A2="","",IF(AND(A2=A1,IFERROR(VALUE(C2)=VALUE(C1)+1,0)),SUM(E1)+1,1))
F2:F10	F2	=IF(A2="","",IF(E2+1=E3,"In used","End"))
L3:L10	L3	=IFERROR(AGGREGATE(15,6,ROW($G$2:$G$9998)-ROW($G$1)/(($F$2:$F$9998="End")*($E$2:$E$9998>=5)),ROW()-ROW($L$2)),"")
M3:N10	M3	=IF($L3="","",INDEX(A$2:A$9998,$L3))
O3:O10	O3	=IF($L3="","",TEXT(INDEX($C$2:$C$9998,AGGREGATE(14,6,ROW($C$2:$C$9998)-ROW($C$1)/(($A$2:$A$9998=$M3)*($E$2:$E$9998=1)*(ROW($C$2:$C$9998)<$L3)),1)),"d/m/yyyy")&" - "&TEXT(INDEX($C$2:$C$9998,$L3),"d/m/yyyy"))
P3:P10	P3	=IF($L3="","",INDEX($E$2:$E$9998,$L3))
Q3:Q10	Q3	=IF(Q2="","",IFERROR(AGGREGATE(15,6,$A$2:$A$9999/($A$2:$A$9999>SUM(Q2)),1),""))
R3:R10	R3	=IF($Q3="","",SUMIFS($P$3:$P$9999,$M$3:$M$9999,Q3))
S3:S10,U3:U10	S3	=IF($Q3="","",R3*S$1)
T3:T10	T3	=IF($Q3="","",COUNTIFS($A$2:$A$9999,Q3,$A$2:$A$9999,"<>")-SUMIFS($P$3:$P$9999,$M$3:$M$9999,Q3))