Trying to Analyze Complex ID Card Data to Total Overtime

[coneill89]

I'm trying to create a spreadsheet that I can dump data into from our building's ID card software. The goal is to get a sum total of the amount of time that specific ID cards were inserted in a slot. However, there are a number of challenges that make this more complex than I initially thought.


The System:
An employee puts an ID card into a slot on the wall and it turns on the lights and air conditioning on their floor. This is only necessary to do after business hours have ended (however sometimes the cards are accidentally left in during business hours). I am only interested in the total time that doesn't overlap with business hours (overtime).

The Data:
The ID software creates a row each time a card is inserted "ON" or removed "OFF" and shows the badge/ID number as well the exact time of the event.


Challenges:
Janitors: There are ID cards I need to filter out and not count (like those associated with janitors turning the lights on to clean at night).
Setting Business hours: Business hours change throughout the year, so I need to be able to dynamically set business hours
Weekends: There are no business hours on weekends so all card usage should count as overtime.
Double Counting: If you look at row 137 and row 138 they are both turned "ON" before either card is removed. I don't want to double count that time because they turn on the same sets of lights. All of the data in this spreadsheet concerns the same area.
>24 Hours: My approach failed when someone accidentally leaves the card in for >24 hours. I would end up counting 25 hours of use as 1 hour.

Does anyone have advice on how to approach this data with these issues? I've been banging my head against the wall as every attempt I've made has failed to account for at least one of the "Challenges" above. Any advice is greatly, greatly appreciated!!

[scottiex]

I'd create a series of tabs with the janitor numbers, business hours, public holidays

then create some columns with formula that give 0 if the data is invalid or 1 if it is valid (either on this sheet or another one).
check janitor number, check business hours, check weekend/public holiday, check if double,maybe check if over 24hrs (although this is probably OK).

then one more column to calculate the times it is on based on the criteria (ie the time between valid "ON" s and the corresponding previous off i suppose)

Then a table to sum and display the result.

Exactly how this would look depends on the format of the data you get for business hours etc and how you want the result displayed.