Need to find out hours worked from door reports

Hi,

Been looking at this for hours now but I'm getting nowhere. I've been asked to come up with a formula in the attached excel sheet to see how many hours each person has been in the building. They have a swipe card which they swipe in and out and the time is logged as you can see. The card will not let you "out" if you have not been "in" that day, so people are usually pretty good at swiping in and out. However, the cards are reset at night so it is possible to get into the building in the morning without having got "out" the day before.

I've been trying IF statements to do my evil work in excel but they're getting me nowhere. I already delimited column A in the one I was working on so I had the raw times in column b. I then had an if statement which checked =if(a2=a3,1,0). The idea of that was to see if the in and out entry are on the same day. I wanted it to also check if the "User" column was the same, but I couldn't get the AND statement to work when building the expression, so I had to do another IF statement in another cell. Then I looked at the "Where" Column and realised this might be be my breaking point.

So, in short, I need to know how long person A, Person B and Person C (and a lot more actually but I think this is enough sample data) have been INSIDE the building. The report is generated weekly, so I need to know how many hours they've been in the building per week.

I'll get major major kudos if I achieve this, and would be so thankful if someone could help me with the formula I need to create. There just seem so many steps to get this going...

Originally Posted by DonkeyOte

So I think you're saying someone might be let out c/o someone else swiping out and forget to swipe out themselves, correct ?
(else they can't get out - unless it's gone midnight at which point the first record for the following day is presumably "out")

If that happens how do you intend to calculate their "in-building" time for that day given it is otherwise open-ended ?

Currently, all in-hours are calculated manually. As such, some manual intervention will not be a problem. If someone forgets to swipe out, in an ideal scenario, the sheet would flag up that they've not swiped out on however many days they've forgotten and show the hours that have been calculated. However, if it just generated an error for that user/member of staff, that would be fine too, as the sheet will still be looked at every week no matter what.

I guess users would then be reminded to swipe in and out, and it's in their interest anyway so wages are not docked, so how regularly it happens should decrease over time.

Thanks for looking at this!

Hi All,

Seems nobody is sure on how to do this so I've re-looked at my approach. I'm currently using this formula to work out the hours spent "in building" per session:

Please Login or Register  to view this content.

This gives me a value of 0 if the cell it is applied to is not the "out" row. It also gives me a 0 if the user has not swiped in and out correctly. It gives me the time spent in the building if everything works correctly, and this seems to be doing the trick. However, I need to seperate the users, if I don't, once the user changes, the results become corrupt as we have negative times. I think the best thing I could do here is to run a Macro which finds all the different values in Column B (which was the user's name) and creates new worksheets with that name. Then I can apply the formula in each sheet and collate the data at the end, presenting a nice report which has just the user's name and the hours worked.
Actually, as I type that out, that'll be my next problem won't it? Haha.

I've also had to change the format of the cells where the calcs are running to be

Please Login or Register  to view this content.

as to not have problems with any values returned looking like times of day rather than durations.

Any help would (still) be appreciated.