1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by ThrottleWorks, Dec 6, 2017.

  1. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,816
    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.

    Attached Files:

  2. Nightlytic

    Nightlytic Member

    Messages:
    107
    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

    Attached Files:

    ThrottleWorks likes this.
  3. pecoflyer

    pecoflyer Active Member

    Messages:
    158
    This seems to work for Virenadr =MOD(-SUMPRODUCT((J3:J12="in")*(K3:K12))+SUMPRODUCT((J3:J12="out")*(K3:K12)),1)*24
    Format as number
    ThrottleWorks likes this.
  4. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,816
    Hi @Nightlytic thanks for the help. Am checking this. Please allow me some to revert. Have a nice day ahead. :)
  5. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,816
    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. :)
  6. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    452

    Attached Files:

  7. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,816
    Hi @John Jairo V thanks a lot for the help. Have a nice day ahead. :)
  8. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,816
    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.

    Attached Files:

  9. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    452
    Hi, again!

    Check file with new instructions. Blessings!

    Attached Files:

  10. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,816
    Hi @John Jairo V thanks a lot for the help. I am checking this and will revert with details. Have a nice day ahead. :)
  11. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,816
    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

    Attached Files:

    Last edited: Jan 12, 2018

Share This Page