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
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,431
Office Version
  1. 2016
Platform
  1. Windows
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
MNOPQR
2Last RowAsset #DescriptionUnbroken Use PeriodCount of Continuous Days UseTotal No. of Hours based on continued use
36113Van Helsing1/1/2022 - 5/1/2022520
416113Van Helsing7/1/2022 - 16/1/20221040
521101Van Halen2/2/2022 - 6/2/2022520
641101Van Halen9/2/2022 - 26/2/202218#REF!
753101Van Halen3/3/2022 - 10/3/20228#REF!
4th
Cell Formulas
RangeFormula
M3:M7M3=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:O7N3=IF($M3="","",INDEX(A$2:A$9998,$M3))
P3:P7P3=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:Q7Q3=IF($M3="","",INDEX($E$2:$E$9998,$M3))
R3:R7R3=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)))
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

ABC1

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

1641872078840.png
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,431
Office Version
  1. 2016
Platform
  1. Windows
OK, I've split the range into two columns and the calculations become easier with real date fields.

ABC1.xlsx
ABCDEFGHIJKLMNOPQRSTU
1AssetEquipmentDateSum of SomethingTotal $No of Consecutive DaysStatus
2884Car Ladelavigne1-Jan-20205.541EndActual Last RowLast RowAsset #DescriptionUnbroken Use Period StartUnbroken Use Period EndCount of Continuous Days UseTotal No. of Hours based on continued useCharges
3113Van Helsing1-Jan-202012101In used76113Van Helsing1-Jan-20205-Jan-202056050
4113Van Helsing2-Jan-202012102In used1716113Van Helsing7-Jan-202016-Jan-202010120100
5113Van Helsing3-Jan-202012103In used2221101Van Halen2-Feb-20206-Feb-202056050
6113Van Helsing4-Jan-202012104In used4241101Van Halen9-Feb-202026-Feb-202018216180
7113Van Helsing5-Jan-202012105End5453884Car Ladelavigne3-Mar-202010-Mar-202089680
8113Van Helsing7-Jan-202012101In used         
9113Van Helsing8-Jan-202012102In used         
10113Van Helsing9-Jan-202012103In used         
11113Van Helsing10-Jan-202012104In used         
12113Van Helsing11-Jan-202012105In used         
5th
Cell Formulas
RangeFormula
F2:F12F2=IF(A2="","",IF(AND(A2=A1,IFERROR(VALUE(C2)=VALUE(C1)+1,0)),SUM(F1)+1,1))
G2:G12G2=IF(A2="","",IF(F2+1=F3,"In used","End"))
M3:M12M3=IF($N3="","",N3+ROW($A$1))
N3:N12N3=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:P12O3=IF($N3="","",INDEX(A$2:A$99999,$N3))
Q3:Q12Q3=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:R12R3=IF($N3="","",INDEX($C$2:$C$99999,$N3))
S3:S12S3=IF($N3="","",INDEX($F$2:$F$99999,$N3))
T3:T12T3=IF($N3="","",SUMIFS($D$2:$D$99999,$A$2:$A$99999,O3,$C$2:$C$99999,">="&Q3,$C$2:$C$99999,"<="&R3))
U3:U12U3=IF($N3="","",SUMIFS($E$2:$E$99999,$A$2:$A$99999,O3,$C$2:$C$99999,">="&Q3,$C$2:$C$99999,"<="&R3))
 
Solution

ABC1

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

ADVERTISEMENT

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

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