Filtering Card Reader Data to Total After Hours Use

[coneill89]

I'm trying to create a spreadsheet that accepts data from a card reader and gives me information about after hours usage. This is for an office building where tenants can insert a card into a reader to keep the lights and A/C on after business hours.

The data the card reader provides is pretty messy. The entries in Column B that start with "CMO" are the tenants card usage. There is also janitorial usage, which I don't want to include. Out of the Column B entries that start with CMO, the ones that say ON and OFF in Column F are the time periods at which the card was inserted and removed, respectively.

Sometimes the card is left in during business hours, which I do not want to count. If the office opens at 8AM and the card goes in from 7-9AM, I only want to record one hour.

I'm not looking for someone to do this for me, I've been struggling with the appropriate approach and am looking for advice. I am trying to create a solution that is very simple and doesn't require much or any Excel knowledge to use. I was able to develop a rather ugly model that worked for everything except weekends (when there are no business hours) and times when the card was left in for >24 hours. I have a feeling there is a better way to deal with this. Any advice on an approach would be greatly appreciated.

[dflak]

I am assuming that the information in Column C is a badge number and that swipes or insertions are to be processed by badge number.

If this is so, then there are instances of two consecutive ONs without an OFF in between. What does this mean and how do you want to handle it?

[coneill89]

Sorry for my delayed response, I had to ask around to get an answer to your question. There are two different card slots that activate the same zone. So there isn't any reason for them to both be in at the same time, but it looks like they put them both in anyway.

In this case we would want to start counting when the first card went in and stop counting when whichever card stayed in longer came out. We still need to ignore the janitorial card, which makes this tricky.

[dflak]

I just got back from vacation myself. I've partially implemented something which takes into account whether the in and out are both before the business day or after it or wholly within it or partially overlapping it. There's 5 separate cases and I haven't addressed the possibility that it overlaps the end of one day and the beginning of the next. That is as far as I've made it so far. It does not account for weekends and holidays yet nor any of the other exceptions except for janitorial. I simply ignore those entries.

Also I will have to go back to the drawing board. I am ignoring the first ON if there are two in a row. I'll have to reverse my logic there and figure out how to get the earliest ON after the last OFF if there is a last OFF.

This is a big project to do and I can only get to it when I have a reasonably large amount of time to think about it. I may get a solution, but it might be a while getting there.