• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to calculate actual time spent at desk

ThrottleWorks

Excel Ninja
Hi,

Please refer attached file for more details. Can anyone please help me in this.
I am not trying to write how to calculate interval, get their sum for each user.
 

Attachments

  • Book1.xls
    33.5 KB · Views: 13
Hey ThrottleWorks, see the attached , it's probably a different format than you were hoping but you can definitely structure it somehow more presentable. I only added the formulae in U:Y and results table starting in AC
 

Attachments

  • Copy of Book1.xls
    46 KB · Views: 7
Hi @pecoflyer thanks for the help. As mentioned by you, it is working for one player. I guess, it does not work for multiple players in same column. Have a nice day ahead. :)
 
Hi @John Jairo V , @Nightlytic , @pecoflyer my apologies for uploading wrong sample data. I did one mistake while creating dummy data.

Usually there will 'x' number of in and 'x+1' number of out in real data.
And this is the most important condition. Due to this omission by me, solutions provided are not able to work.

Once again really sorry for goof-up. I won't say please give me updated solution as you have already invested your time on my issue. But I thought I should reply with my feedback.
 

Attachments

  • Book1.xls
    36 KB · Views: 6
Hi,

When I posted this query originally there were lot of mistakes in dummy data from my side. Can anyone please help me based on the updated dummy data.

I am aware it is my mistake, please help if you get time.

Original data will be from A to F column.
Rest all the columns are created by me for reference.
Ideal result will be in new worksheet of new file. Column J to P.
Column G and H are for forum reference. Just to explain logic behind numbers in result.

I am trying to calcuate number of hours spent in the office for a particular day.
One work day will consist 9 hours (540 minutes). We need to exclude breaks taken between the day to calculate productivity.

The original data will have 1000s of users, might exceed 15,000 to 20,000.
Name will have duplicate values. For example 'Sachin Tendulkar' can be present 500 times in column B.

It is possible that person is working in night shift, so one working day can have two different dates.
Break (hours) column L in attached sheet is sum of breaks taken in entire range for the user.
If I have 5 working days in range and taking 1 hour break each day then L = 5.
Total time is first in - last out for each day.
For example I logged at 09:00 and logged out at 18:00 for 5 days then M = 45
Total In column O is M - N so in this case it will be 40
Days will be working days for the user in the range, in my example it would be 5.

Column P, 9 hours minues break time for each day
If I take 1 hour break each day for 5 working days, then P = 5
 

Attachments

  • Book1.xlsx
    12.7 KB · Views: 9
Last edited:
Back
Top