# Calculate Times

1. ## Calculate Times

Hi everyone,

I've got a series of data of (dates, times, doors and people) - I need to calculate how long each person has been in office per day - the complexity is that people use different accesses to enter/leave the building, and they also "come & go" so you have multiple logs. Has anyone experienced similar that they have managed to build a formula to do?

2. ## Re: Calculate Times

maybe worth attaching a sample as mentioned in the yellow banner

There have been various solutions for timekeeping here
HOW TO ATTACH YOUR SAMPLE WORKBOOK:
etaf Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

3. ## Re: Calculate Times

Document Attached

4. ## Re: Calculate Times

A Time2 column has been added to the data to show the time between the current "Out" and the previous "In".
The formula for the Time2 column is: =IFERROR(IF(RIGHT(C2,3)="Out",B2-INDEX(B\$1:B1,AGGREGATE(14,6,ROW(B\$1:B1)/(D\$1:D1=D2),1)),""),"")
As you can see some people (i.e. A.Person2 at 12:55:32 PM) are recorded as "Out" before being recorded as "In" which causes an error (trapped by IFERROR).
A pivot table displays the cumulative amount of time between the in's and out's for each person.
Let us know if you have any questions.

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

#### 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