Computing duration of two datetime of two records/data

[littlepixies]

Hi good day! 

I have a table with name, timestamp, and type. the type is either "in" or "out". if an employee leaves the office, the guard on duty will scan their ID and tag them as "out" with timestamp. upon the employee's return, the guard will scan them again and this time as "in".

Now I want to compute the duration they left the office. Problem is the 2 data are separate records.

how can i put where in formula? something like

[time where thisrow_name = name and type="in"] - [time where thisrow_name = name and type="out"]

in and out are virtual column as time that I pulled from a column datetime.

I know I will have issue if an employee has 2 or more "Out" status example:
employee 1 - 8:00AM - Out

employee 1 - 9:00AM - Out

employee 1 - 10:00AM - In

This should not happen but of course we are prone to human errors. the guard on duty might scan them as Out instead of In, hence the multiple Out record.

As of now I don't want to worry on that yet. I just want to assume the employee has single out and single in for the day.

[Suvrutt_Gurjar]

You may want to mention how the guard differentiates between an "In" and 'out" timestamp? Does the guard tap on an "in/ Out" enum button?

[xeonavn]

You may want to try this kind of logic, the 1st scan will be automatically tag as In and the second scan will tag as out, I just put some formula to avoid double scanning.

I Put some kind of automation to calculate the duration of 1st scan as In and the 2nd scan as Out base on their employee_id.

IF(
[type_of_log] = "OUT",
CONCATENATE(
FLOOR(TOTALHOURS(
[date_and_time_logs]
-
MIN(
SELECT(
time_log[date_and_time_logs],
AND(
[employee_id] = [_THISROW].[employee_id],
[date] = [_THISROW].[date],
[type_of_log] = "IN"
)
)
)
)), " hours ",
MOD(TOTALMINUTES(
[date_and_time_logs]
-
MIN(
SELECT(
time_log[date_and_time_logs],
AND(
[employee_id] = [_THISROW].[employee_id],
[date] = [_THISROW].[date],
[type_of_log] = "IN"
)
)
)
), 60), " minutes"
),
""
)