I've got a list of events generated by a door pass security system. I need to find the earliest event for each day, and then the latest for that same day, and hence the onsite duration.
The list of events just runs in time order through the whole month, and there could be any number of events during the day.
Ultimately, I need to build monthly totals, but just want to figure out a good way of getting the daily values for now.
I've written some formulae to check when the date changes (ie a first or last event), but I can't figure how to subtract that from the previous event when there's an unknown number of rows in between.
Sorry, I have no VBA skills to speak of, and am a bit ignorant about PivotTables, both of which may help. Happy to learn, though!
The attached file is a summary, but there will be more data - approx. 600 rows/person for a month is not uncommon.