Calculating total hours based on logs

[mgirou]

Hello,

I have a set of data for a entry system where we swipe in and out of the office. What i am trying to get is the total time that people are at the office for on each day. The issue i come into is when someone comes into work then leaves for lunch then comes back in then leaves for the day. (Yellow Highlighting).

Could anyone help me at getting the Total hours that people where at the office for?

Thankss

[parry2k]

@mgirou can you provide the sample data in excel sheet to work on the solution?

[mgirou]

How do i attach files to this?I can't seem to figure it out...

[parry2k]

@mgirou you cannot attach file here with the post, you need to share it using google drive or dropbox etc.

[mgirou]

Thanks,

Here you go i think this should work

https://drive.google.com/file/d/1HlS4cL5H7NeKYUT60f7pcm8vWAdfeYL0/view?usp=sharing

[mgirou]

Try it now.

[parry2k]

Does EXT is in and INT is out?

[mgirou]

yes

[parry2k · Accepted solution]

lets'c if this do the job, there is paramter in the file , change value in paramter to point to you excel sheet.

https://drive.google.com/file/d/15huP3OEpBv-Fkmv9liqd8sh9caAz1oYq/view?usp=sharing

[mgirou]

I am having one issue though, when you just want to see the total hours  for each member on each date the time in office hours uses the last time they swiped out and subracts it from the first time they swiped in for the date. Instead of just grab each individual time they swiped in and out and adding those times.

[parry2k]

Will check, didn't tested all the use cases. it was more of a concept for you to make changes as you see fit.

Anyhow, will look at it.

[parry2k]

can you give me example employee and date, and expected result, it will help me to debug

[mgirou]

@parry2k, I hope i am not bugging you but i have really hit a road block trying to get this done. did you get a chance to look at it?

[mgirou]

@parry2k. I was wondering if you had a chance to see if there was a solution for seeing the total hours in office per date.

[mgirou]

Member ID 495. October 31

I want to be able to have a table just with Member Id, Date, and time in office in hours

Now i get 15.47 hours (because its taking the min date which is date they entered and max date which is last date the left. but because they left and came back during the day its wrong) which is wrong. it should be 7.6

Thanks!

[mgirou]

This is amazing.. thank you very much!! I was wondering if you could help me understand how you did this??

In Time for This out time Measure - I know your filtering the table the way you want it but i dont completely understand whats going on there.

Whats the point for Rank Calc Column?

Why use Var x? do you need to declare variable like that or is that best practice??

Thank you very much again.

[parry2k]

your excel file has all the data in one column, is that how you get the data or it is just an error in file you shared.