+ Reply to Thread
Results 1 to 9 of 9

Calculate working hours-several in/exit in same day

  1. #1
    Registered User
    Join Date
    12-04-2012
    Location
    PG
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Calculate working hours-several in/exit in same day

    Hello everybody!

    I have searched the forum, and Google also, but I was unable to find a solution for what I need (probably because I’m not so good searching or not).
    I have a control access table, were all entries and exits are stated. In some days, it can happen that a person leaves the facility more than 1 time (typically is one time for lunch hour, but not always true).

    I want to calculate the working hours per day, using the following premises:
    On column A, it is the time stamp (date+hour)
    On column B, it is the location were the person placed the card
    On column C, it is the indication if the person is entering or leaving the facility

    As an example, day 20-09
    20-09-2012 07:59 POR_T1 IN -> person enter the building
    20-09-2012 09:39 POR_BOU1 OUT - > person leaves the building
    20-09-2012 10:56 POR_BIN1 IN -> person enter the building
    20-09-2012 13:03 POR_T1 OUT-> person leaves the building
    20-09-2012 14:08 POR_T1 IN ->person enter the building
    20-09-2012 17:48 POR_T1 OUT ->person leaves the building
    Now, I want to calculate how many hours was the person inside the building

    The attached time sheet, is in “raw format”, meaning that data wasn’t treated yet.

    I have tried formulas, but I’m afraid that this will only work with VBA

    Thanks in advanced!

    Cheers
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Calculate working hours-several in/exit in same day

    Hi,

    U need duration hours of In & out of Particular Location right.

  3. #3
    Registered User
    Join Date
    12-04-2012
    Location
    PG
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Calculate working hours-several in/exit in same day

    Quote Originally Posted by Naveed Raza View Post
    Hi,

    U need duration hours of In & out of Particular Location right.
    Not exactly, but that would help very much.

    Per day, I need working hours/hours inside building.
    Although not explicit in the first post(my mistake, sorry), the person can enter the building via POR_T1 and leave it via POR_T2

  4. #4
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Calculate working hours-several in/exit in same day

    so if u enter date in assigned cells u should get all the information related to that particular date in addition to that need In time hours and out time hours right.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Calculate working hours-several in/exit in same day

    In your example row2 and 3 are "IN" at 7:57 and 7:59 - Are they 2 diff persons and if so how are they distinguished?

    EDIT no need for VBA here, formula will do
    Last edited by Pepe Le Mokko; 12-07-2012 at 07:59 AM.

  6. #6
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Calculate working hours-several in/exit in same day

    Hi,

    I have updated the formulaes have a look.

    Please find the attached workbook and please have review and let me know if u have question.

    Thanks - Naveed.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-04-2012
    Location
    PG
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Calculate working hours-several in/exit in same day

    Quote Originally Posted by Naveed Raza View Post
    so if u enter date in assigned cells u should get all the information related to that particular date in addition to that need In time hours and out time hours right.
    I’m a little confused, sorry. Probably the example I give, wasn’t the best one.

    With the information in the excel sheet, I need to calculate the hours inside the building.

    Using the example
    20-09-2012 07:59 POR_T1 IN -> person enter the building
    20-09-2012 09:39 POR_BOU1 OUT - > person leaves the building
    20-09-2012 10:56 POR_BIN1 IN -> person enter the building
    20-09-2012 13:03 POR_T1 OUT-> person leaves the building
    20-09-2012 14:08 POR_T1 IN ->person enter the building
    20-09-2012 17:48 POR_T1 OUT ->person leaves the building


    I will need to do the following “calculation”
    |07:59-09:39|+|10:56-13:03|+|14:08-17:48| = Total working hours or total hours inside building during 20-09-2012. Now do this for the rest of the days (21, 22, etc…)

    In this example I only have the information regarding the valid locations for determine the working hour. Inside the excel sheet, there are more locations (column B) but not all of them matter. Of course I can filter up the spreadsheet, but I wish I could do it automatically

    EDIT: Sorry Naveed Raza, didn't see your last post when replying.

    Quote Originally Posted by Naveed Raza View Post
    Hi,

    I have updated the formulaes have a look.

    Please find the attached workbook and please have review and let me know if u have question.

    Thanks - Naveed.
    Not quite what I wanted. The data that I give in the excel sheet/example are basically like a "data base/data logger" of events for a single person (most like a access control data base). The intention is to see if the "IN" is true (Only in POR_T1/POR_T2/POR_BIN1 is the "IN" true) and see if the "OUT" is true (Only in POR_T1/POR_T2/POR_BOU1 is the "OUT" true) and calculate the differences, to see the time inside building/working hours

    Quote Originally Posted by Pepe Le Mokko View Post
    In your example row2 and 3 are "IN" at 7:57 and 7:59 - Are they 2 diff persons and if so how are they distinguished?

    EDIT no need for VBA here, formula will do
    Each person is on a different excel sheet, so all the information that is on the example/attachment is referred to only one person
    Last edited by rds2012; 12-07-2012 at 08:32 AM. Reason: Avoid double posting

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Calculate working hours-several in/exit in same day

    Does the attached help? Time_in.xlsx

  9. #9
    Registered User
    Join Date
    12-04-2012
    Location
    PG
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Calculate working hours-several in/exit in same day

    Quote Originally Posted by Pepe Le Mokko View Post
    Does the attached help? Attachment 198742
    95% of the job done. the other 5% are cosmetic, and I think I can manage it!

    Thanks Pepe Le Mokko

    and also thanks to Naveed Raza

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1