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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Toadstool

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

1641584715366.png
 

Toadstool

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

ADVERTISEMENT

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

Toadstool

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

ADVERTISEMENT

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

1641669357446.png
 

Toadstool

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

Well-known Member
Joined
Mar 5, 2018
Messages
2,429
Office Version
  1. 2016
Platform
  1. Windows
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))
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,467
Messages
5,764,498
Members
425,219
Latest member
datdanigg

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