2 Replies Latest reply on Sep 1, 2016 8:11 AM by David Li

# Calculate the total time for which employee was inside office, by using Intime-Outtime entries.

Hello,

I am stuck with following usecase.

Any suggestions are welcome

Usecase:

I am maintaining attendance sheet of a company.

The Comapny's Intime is 9:30 and Outtime is 18:30

When any employee uses his card to enter office, one date-time entry gets added in column "In time 1"

Now, when he goes out for some reason,one date-time entry gets added in column "Out time 1"

Again when he comes back, date-time entry gets added in column "In time 2" and so on..

Each row has data of one employee for one day.

The number of entries in each row will depend on how many times that person goes out and comes back in. This may differ for each employee.

I want to calculate: For how many minutes that employee was actually present inside office, per day?

I have attached sample dataset. Please refer Attendence.xlsx

• ###### 1. Re: Calculate the total time for which employee was inside office, by using Intime-Outtime entries.

Hi, Swapnil,

It's not pretty, but you could use the calc below to find the total minutes.  You'd just need to make sure you had the max number of in/out times included in the calc.

Hope this helps.

Thanks,

Ryan

• ###### 2. Re: Calculate the total time for which employee was inside office, by using Intime-Outtime entries.

You should try to normalize (unpivot) the data if at all possible. If you're working with Excel, this is pretty easy: Pivot Data from Columns to Rows

The data structure you want is for each row to include an in-time and out-time for one employee. There may be multiple rows per employee per day.