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