Hello, I am working right now with an Access Point Report. They ask me to take out how many Hours a day a person works having this table:
| Day |
Time |
Access Point |
Name |
| 3-Jan-2017 |
7:21:05 |
Office |
Francisco |
| 3-Jan-2017 |
7:36:21 |
Office |
Marielos |
| 3-Jan-2017 |
7:41:28 |
Office |
Flor |
| 3-Jan-2017 |
13:01:45 |
Office |
Flor |
| 3-Jan-2017 |
13:02:21 |
Office |
Francisco |
| 3-Jan-2017 |
14.08.23 |
Office |
Flor |
| 3-Jan-2017 |
13:08:09 |
Office |
Francisco |
| 3-Jan-2017 |
13:34:55 |
Office |
Francisco |
| 3-Jan-2017 |
15:48:05 |
Office |
Francisco |
| 3-Jan-2017 |
17:46:00 |
Office |
Flor |
| 3-Jan-2017 |
17:49:55 |
Office |
Francisco |
| 3-Jan-2017 |
17:51:14 |
Office |
Marielos |
The problem is that there is only ONE sensor and they have to press it every time they enter or they get out, but there is no way of differentiating what is an ENTER and what is a OUT unless you look at the time adn you analyze that the first hour is entering the office and the second our is when they get out, and so on.
I am trying to find a way to calculate this working time automatically because there are 15 employees and more than 300 days and more to come. I found a way but it is hard to apply as you have to filter day by day.
=IF($D$2="Francisco",IF=D3="Francisco",IF(C4>$B$2,SUM(B3-$B$2),""),""),"")
And the you apply a sum to the Resulting values to calculate the working time... but it is a long process day by day, employee by employee.
Is there another way to do it? Thanks very much!
