Como calcular para “Engaged Time” no excel [closed]

Question

Eu tenho um conjunto de dados como este:

       |      A         |          B       | 
       -------------------------------------
    1  | Start Time     |   End  Time      |
    2  |  6:43 AM       |     7:00 AM      |
    3  |  8:01 AM       |     9:43 AM      |
    4  |  8:02 AM       |     8:20 AM      |
    5  |  8:22 AM       |     8:57 AM      |
    6  |  8:58 AM       |     9:55 AM      |
    7  |  1:20 PM       |     2:41 PM      |
    8  |  1:27 PM       |     2:11 PM      |

Cada linha representa instâncias em que um funcionário está trabalhando em uma tarefa, neste caso, entre 6h43 e 14h11. Portanto, usando o bom senso, você deduziria o 'Horário de término' ao 'Horário de início' para obter o tempo de 'Engajado'. Mas esse não é o tempo dedicado. Esse é o 'Comprimento da Tarefa'. O tempo que ele levou para concluir uma tarefa.

A questão aqui é que 1 funcionário pode trabalhar em várias tarefas ao mesmo tempo. Se você observar a linha 3 (das 8h01 às 9h43), o funcionário demorou mais de uma hora para concluir uma tarefa. Ao fazer essa tarefa específica, ele começou a fazer outras tarefas nas linhas 4 5 e 6.

então pensando em 'Engaged Time' ele estava noivo de 8h01 às 9h55. Não consigo pensar em uma fórmula dinâmica que faça isso automaticamente para toda a tabela. Outra questão é que, este é um conjunto de dados para apenas 1 funcionário por 1 dia. Eu tenho 10 funcionários que trabalham 5 dias por semana. Eles produzem aproximadamente 3.000 linhas de dados em um mês. Você pode sugerir uma fórmula, técnica de tabela dinâmica ou código VBA para eu analisar os números dinamicamente?

Obrigado!

    

Answer 1

Veja se isso ajuda, faça duas colunas auxiliares, a primeira é o número do bloco. Isso mostra um bloco de tempo em que há uma ou mais tarefas em andamento.

As fórmulas são e3 = 1, f3 = c3 para fornecer um ponto de partida inicial.

O restante da coluna e começando com e4 usa

• = + SE (B4 > F3,1,0) + E3

onde está verificando a nova hora de início para ver se ela está dentro da hora final atual. se for, o bloco é estendido, senão um novo número de bloco é iniciado

A coluna f começando com f4 usa

• = + SE (E4 > E3, C4, MÁXIMO (C4, F3))

Onde está verificando se o horário de término do bloco foi estendido. Para cada bloco, o tempo mínimo em beo máximo em c irá gerar o tempo contratado.

Espero ter entendido seu problema o suficiente.