CALCULATE THE TIME BETWEEN EACH EMPLOYEE MANAGEMENT

[Syndicate_Admin]

GOOD DAY COMMUNITY!

I need your help.
From the table below, I must find the time between events segmented by Day and Employee. Here I present a sample of the data, as I receive it, in no established order.

My goal is the management time that each employee has, understood as time between each event. It is also necessary to segment it by Employee and Day. The difficulty I face is that there is no finished event time data, only the Day and Start Time data.

Assuming that the start time is at 08hs and the end of the day at 14hs, I look for something like the following:

That way, I'll be able to see work time intervals and get daily averages per employee, shift, or sector. It is useful for me to expand the analysis of management and continue learning from this great community.

Here I leave a link to the database, reduced and cleaner than the one used, to serve as an example.
https://docs.google.com/spreadsheets/d/1OwRX-8wjLCg_BmV9Rr3oW4qYSb-XHaRv/edit#gid=1573902123

Best regards. Thanks a lot!

[v-zhangti · Accepted solution]

Hi, @Syndicate_Admin 

You can try the following methods.

Column:

Column =
RANKX (
    FILTER (
        'Table',
        [OPERADOR] = EARLIER ( 'Table'[OPERADOR] )
            && [FECHA] = EARLIER ( 'Table'[FECHA] )
    ),
    'Table'[HORA],
    ,ASC)

Column2 =
CALCULATE (
    MAX ( 'Table'[HORA] ),
    FILTER (
        'Table',
        [Column]
            = EARLIER ( 'Table'[Column] ) + 1
            && [OPERADOR] = EARLIER ( 'Table'[OPERADOR] )
            && [FECHA] = EARLIER ( 'Table'[FECHA] ) ) )

TIEMPO = IF([Column2]=BLANK(),BLANK(), [Column2]-[HORA])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Thanks a lot!!
It was a great help to me.