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