Monthly report for yard activity - ISO best method

[MB110]

Hi,

I'm new to the forum but I've been using Excel for several years. I consider myself at least an intermediate user, however I don't write a lot of code. Better at copy paste, edit.

I inherited the task of adding some KPI's to an already existing workbook. It was full of missing and inaccurate data. I decided to start over but with limited time so my data structure may not be the greatest for what I've been asked to do.

The spreadsheet tracks in and out movement of vehicles in a yard. The time is not tracked but the dates are. I can identify the vehicle and it's owner. I know arrival dates and departure dates. I currently have things set up to manage current activity rather than view history.

I need a method to look at records several months old and say if that car was on site that month. If the same vehicle was still on site in the following month or months it needs to show up on those reports as well. I have a cell stating active on site but it changes to departed after the departed date is entered.

So lets say vehicle A arrived on 01/01/2019 and departed on 03/16/2019, then it was on site for 2.5 months. I can use DATEDIF easy enough to get time spans. I need an eloquent method of saying January had x number of vehicles on site, what those vehicles were and their owners, etc.

I'm at a loss on where to start. I'm guessing an advanced pivot table?

Thanks for any assistance in pointing me in the right direction.
MB110

 

[oldbrewer]

vehicle	date in	date out
V1	02/01/2019	27/01/2019
V3	10/01/2019	06/02/2019				01/01/2019	01/01/2019	01/03/2019
V4	14/01/2019	11/02/2019				31/01/2019	28/02/2019	31/03/2019
V5	18/01/2019				V1	YES	YES	YES
V1	22/01/2019	21/02/2019			V2	NO	YES	YES
V3	30/01/2019	03/03/2019			V3	YES	YES	YES
V4	03/02/2019	08/03/2019			V4	YES	YES	YES
V1	11/02/2019	18/03/2019			V5	YES	YES	NO
V2	15/02/2019	23/03/2019
V3	19/02/2019	28/03/2019
V4	23/02/2019	02/04/2019
V1	03/03/2019	12/04/2019
V2	07/03/2019	17/04/2019
V3	11/03/2019	22/04/2019
V4	15/03/2019	27/04/2019
		is this near to what you want ?

<colgroup><col><col span="2"><col span="3"><col span="3"><col span="2"></colgroup><tbody>
</tbody>

 

[MB110]

Yes, I think this would work but I was hoping to not make a yes/no calendar. This data range is years long. What formula is used to return the yes or no?

Thanks!

MB110

 

[oldbrewer]

vehicle

date in

date out

V1

02/01/2019

27/01/2019

V3

10/01/2019

06/02/2019

01/01/2019

01/01/2019

01/03/2019

V4

14/01/2019

11/02/2019

31/01/2019

28/02/2019

31/03/2019

V5

18/01/2019

V1

YES

YES

YES

V1

22/01/2019

21/02/2019

V2

NO

YES

YES

V3

30/01/2019

03/03/2019

V3

YES

YES

YES

V4

03/02/2019

08/03/2019

V4

YES

YES

YES

V1

11/02/2019

18/03/2019

V5

YES

YES

NO

V2

15/02/2019

23/03/2019

V3

19/02/2019

28/03/2019

V4

23/02/2019

02/04/2019

V1

03/03/2019

12/04/2019

V2

07/03/2019

17/04/2019

V3

11/03/2019

22/04/2019

V4

15/03/2019

27/04/2019

is this near to what you want ?

the yes next to V1

=IF(SUMPRODUCT(($A$2:$A$16=$F5)*($B$2:$B$16>=G$3)*($B$2:$B$16<=G$4))>0,"YES","NO")

so if you rmove the if it will count how many times a vehicle entered the yard in any given date period

<colgroup><col><col span="2"><col span="3"><col span="3"><col span="7"></colgroup><tbody>
</tbody>

 

[MB110]

Thank you oldbrewer!

This will do the trick! SUMPRODUCT is not a common function for me and I wasn't even considering it.

After so many years of not actively posting on forums I thing I made a good choice with MrExcel!

Cheers!
MB110

 

[oldbrewer]

come back to us if you need to refine it - and you could calculate the time in yard for each vehicle, etc