# Identify equipment used -No of consecutive days

#### ABC1

##### New Member
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,

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

##### Well-known Member
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
ABCDEFGLMNOP
1AssetEquipmentDateNo HrsConsecutive Days UsedStatus
2884Car Ladelavigne02-Feb-2101EndLast RowAsset #DescriptionUnbroken Use PeriodCount of Continuous Days Use
3113Van Helsing01-Jan-2141In used6113Van Helsing1/1/2021 - 5/1/20215
4113Van Helsing02-Jan-2142In used16113Van Helsing7/1/2021 - 16/1/202110
5113Van Helsing03-Jan-2143In used
6113Van Helsing04-Jan-2144In used
7113Van Helsing05-Jan-2145End
8113Van Helsing07-Jan-2141In used
9113Van Helsing08-Jan-2142In used
10113Van Helsing09-Jan-2143In used
11113Van Helsing10-Jan-2144In used
12113Van Helsing11-Jan-2145In used
13113Van Helsing12-Jan-2146In used
14113Van Helsing13-Jan-2147In used
15113Van Helsing14-Jan-2148In used
16113Van Helsing15-Jan-2149In used
17113Van Helsing16-Jan-21410End
18101Van Halen02-Feb-2141End
19
Sheet1
Cell Formulas
RangeFormula
E2:E19E2=IF(A2="","",IF(AND(A2=A1,C2=SUM(C1)+1),SUM(E1)+1,1))
F2:F19F2=IF(A2="","",IF(E2+1=E3,"In used","End"))
L3:L19L3=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:N19M3=IF(\$L3="","",INDEX(A\$2:A\$9998,\$L3))
O3:O19O3=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:P19P3=IF(\$L3="","",INDEX(\$E\$2:\$E\$9998,\$L3))

#### ABC1

##### New Member
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

##### Well-known Member
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
Excel 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

##### New Member
C2+1 is working. but not formula. also I did press F9. what else I should try
Thanks

##### Well-known Member
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
ABCDEFGLMNOP
1AssetEquipmentDateNo HrsNo of Consecutive DaysStatus
2884Car Ladelavigne2-Feb01EndLast RowAsset #DescriptionUnbroken Use PeriodCount of Continuous Days Use
3113Van Helsing1-Jan41In used6113Van Helsing1/1/2022 - 5/1/20225
4113Van Helsing2-Jan42In used16113Van Helsing7/1/2022 - 16/1/202210
5113Van Helsing3-Jan43In used
6113Van Helsing4-Jan44In used
7113Van Helsing5-Jan45End
8113Van Helsing7-Jan41In used
9113Van Helsing8-Jan42In used
10113Van Helsing9-Jan43In used
11113Van Helsing10-Jan44In used
12113Van Helsing11-Jan45In used
13113Van Helsing12-Jan46In used
14113Van Helsing13-Jan47In used
15113Van Helsing14-Jan48In used
16113Van Helsing15-Jan49In used
17113Van Helsing16-Jan410End
18101Van Halen2-Feb41End
19
2nd
Cell Formulas
RangeFormula
E2:E19E2=IF(A2="","",IF(AND(A2=A1,IFERROR(VALUE(C2)=VALUE(C1)+1,0)),SUM(E1)+1,1))
F2:F19F2=IF(A2="","",IF(E2+1=E3,"In used","End"))
L3:L19L3=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:N19M3=IF(\$L3="","",INDEX(A\$2:A\$9998,\$L3))
O3:O19O3=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:P19P3=IF(\$L3="","",INDEX(\$E\$2:\$E\$9998,\$L3))

#### ABC1

##### New Member
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

##### Well-known Member
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

##### New Member
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,

##### Well-known Member
Here's that breakdown:

ABC1.xlsx
ABCDEFGLMNOPQRSTU
1AssetEquipmentDateNo HrsNo of Consecutive DaysStatus\$ 42.50\$ 53.00
2884Car Ladelavigne2-Feb01EndLast RowAsset #DescriptionUnbroken Use PeriodCount of Continuous Days UseAssetWeekly DaysWeeklyDaily DaysDaily
3113Van Helsing1-Jan41In used6113Van Helsing1/1/2022 - 5/1/2022510131\$ 1,317.500\$ -
4113Van Helsing2-Jan42In used16113Van Helsing7/1/2022 - 16/1/20221011315\$ 637.502\$ 106.00
5113Van Helsing3-Jan43In used21101Van Halen2/2/2022 - 6/2/202258840\$ -5\$ 265.00
6113Van Helsing4-Jan44In used41101Van Halen9/2/2022 - 26/2/202218
7113Van Helsing5-Jan45End53101Van Halen3/3/2022 - 10/3/20228
8113Van Helsing7-Jan41In used
9113Van Helsing8-Jan42In used
10113Van Helsing9-Jan43In used
3rd
Cell Formulas
RangeFormula
E2:E10E2=IF(A2="","",IF(AND(A2=A1,IFERROR(VALUE(C2)=VALUE(C1)+1,0)),SUM(E1)+1,1))
F2:F10F2=IF(A2="","",IF(E2+1=E3,"In used","End"))
L3:L10L3=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:N10M3=IF(\$L3="","",INDEX(A\$2:A\$9998,\$L3))
O3:O10O3=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:P10P3=IF(\$L3="","",INDEX(\$E\$2:\$E\$9998,\$L3))
Q3:Q10Q3=IF(Q2="","",IFERROR(AGGREGATE(15,6,\$A\$2:\$A\$9999/(\$A\$2:\$A\$9999>SUM(Q2)),1),""))
R3:R10R3=IF(\$Q3="","",SUMIFS(\$P\$3:\$P\$9999,\$M\$3:\$M\$9999,Q3))
S3:S10,U3:U10S3=IF(\$Q3="","",R3*S\$1)
T3:T10T3=IF(\$Q3="","",COUNTIFS(\$A\$2:\$A\$9999,Q3,\$A\$2:\$A\$9999,"<>")-SUMIFS(\$P\$3:\$P\$9999,\$M\$3:\$M\$9999,Q3))

Replies
3
Views
70
Replies
1
Views
94
Replies
3
Views
72
Replies
9
Views
208
Replies
7
Views
314

1,186,808
Messages
5,959,932
Members
438,454
Latest member
leopedrini

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back