Identify equipment used -No of consecutive days

ABC1

New Member
Joined
Aug 30, 2021
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
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,


1641538292747.png
 

ABC1

New Member
Joined
Aug 30, 2021
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Thanks,
Not working, Sorry
Please
1641689809843.png
 

Excel Facts

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,431
Office Version
  1. 2016
Platform
  1. Windows
Thanks,
Not working, Sorry
Please
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
Joined
Aug 30, 2021
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
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

1641692628362.png
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,431
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,431
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 30, 2021
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Thanks for your explanation. I will follow and let you know.
Again Thanks,
ABD1
 

ABC1

New Member
Joined
Aug 30, 2021
Messages
25
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

WORKED!
THANKS LOTS
 

ABC1

New Member
Joined
Aug 30, 2021
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Hello Toadstool
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))))
1641845265630.png

Thanks,
 

ABC1

New Member
Joined
Aug 30, 2021
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Understand- aggregate(9)-which do not support array.
I have to look another option
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
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?

Disable AdBlock

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
Top