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

#### ABC1

##### New Member
Thanks,
Not working, Sorry

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

##### Well-known Member
Thanks,
Not working, Sorry
The formulae in L3 to P3 need to be copied down the maximum number of times a rental may exceed 5 days.

The formulae in Q3 to U3 need to be copied down the maximum number of assets you may have.

Put the daily rate for a weekly hire in S1 and the daily rate for a daily hire in U1.

#### ABC1

##### New Member
The formulae in L3 to P3 need to be copied down the maximum number of times a rental may exceed 5 days.

The formulae in Q3 to U3 need to be copied down the maximum number of assets you may have.

Put the daily rate for a weekly hire in S1 and the daily rate for a daily hire in U1.
I did copy.
Line 13, 14 and line 29,30,31 - should be referent in our result.
Column Q: =IF(Q2="","",IFERROR(AGGREGATE(15,6,\$A\$2:\$A\$9999/(\$A\$2:\$A\$9999>SUM(Q2)),1),""))- may I know- why we are doing >Sum(Q2) in Asset column

##### Well-known Member
The Q formula is listing once, and once only, each of the Asset numbers. If you have a full list of Asset numbers elsewhere then you could replace this column.

It takes the value of the cell above (and in the case of Q3 the SUM(Q2) returns a zero) and looks for the lowest value Asst number which is greater than the cell above.

##### Well-known Member

The Q formula is listing once, and once only, each of the Asset numbers. If you have a full list of Asset numbers elsewhere then you could replace this column.

It takes the value of the cell above (and in the case of Q3 the SUM(Q2) returns a zero) and looks for the lowest value Asst number which is greater than the cell above.
Let me add some more notes:
1. The 5 consecutive days columns L to P will list in the sequence it finds them from columns A to E. Your 1179653 appears twice but the formulae in L to P seem to only have been copied down to row 29. There may be other entries of rental for 1179653 which are 5 days and over further down the data.
2. The 1179653 does not appear in Daily because the Asset list in column Q starts with the lowest Asset number (310861) and the formulae appear to have only been copied down to row 27 by which time it has only reached Asset 383008. The Daily Days are calculated by counting how many entries there are for an Asset number then subtracting any days for that Asset listed in column P. You can test this by typing 1179652 into cell Q2 and it will only list Assets from 1179653 on.
3. It appears you have thousands of entries. The formulae only look down to row 9,999 so if you have more data the formulae will need to be amended.
4. The lack of year in column C is an issue. The date range in column O will always be incorrect and I suggest you look to finding a way to provide the complete date from your source data.

#### ABC1

##### New Member
Thanks for your explanation. I will follow and let you know.
Again Thanks,
ABD1

WORKED!
THANKS LOTS

#### ABC1

##### New Member
I am trying to get total no of hrs (base on continues used) using following function. getting value# error. only took first 25 row for trial.

=IF(\$M3="","",AGGREGATE(9,6,ROW(\$D\$2:\$D\$25)-ROW(\$D\$1)/((\$A\$2:\$A\$25=\$N3)*(\$F\$2:\$F\$25="End")*(ROW(\$D\$2:\$D\$25)<\$M3))))

Thanks,

#### ABC1

##### New Member
Understand- aggregate(9)-which do not support array.
I have to look another option

Replies
2
Views
279
Replies
1
Views
61
Replies
3
Views
122
Replies
17
Views
356
Replies
0
Views
183

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,578
Messages
5,765,203
Members
425,267
Latest member
bishopc22

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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