Fórmula do Excel para calcular o tempo total gasto em várias tarefas [fechado]

Question

Eu preciso de uma fórmula para calcular o tempo total gasto em várias tarefas, mas quero dividir todo o intervalo relevante todos os dias.

Por exemplo, a coluna A tem a hora de início e a coluna B tem a hora de término.

Colunas C & D apenas os intervalos por 30 minutos variando das 07: 00-23: 00.

Na coluna E, preciso de uma fórmula que calcule o tempo gasto de uma tarefa de 09: 07-10: 56, mas para o intervalo de 30 minutos relevante, ou seja, 09: 00-09: 30 seria 23 minutos, 09 : 30-10: 00 seria 30 minutos, etc.

Eu tenho milhares de tarefas, então quando eu adicionar uma nova, eu gostaria de um total acumulado à medida que eu prossiga.

    

Answer 1

Eu realmente gostaria de poder anexar arquivos
Se alguém tem uma solução para isso, avise - me. Eu tenho uma solução para você. Primeiro, alguma configuração:

Você pode configurar a mesma coisa com intervalos nomeados dinâmicos e usá-los em suas fórmulas, mas eu gosto de tabelas, então usei essas. Eu criei uma tabela nas colunas A: B chamado tblTimeLog
Tem dois campos: Iniciar, Parar
É aqui que você insere todos os seus horários de início e término conforme necessário

Eu criei uma segunda tabela nas colunas D: G chamada tblTimeSummary Review, Tem 4 campos: Intervalo, Intervalo, Intervalo, Tempo Total
Início do intervalo & Parar são conveniências
Você poderia fazer tudo com um único campo de texto mostrando o intervalo de tempo, mas as fórmulas são muito mais simples se você as dividir | Estes dois terão intervalos de tempo como 07:00 e 07:30, 07:30 e 08:00, etc.
Eu digitei estes em mão e autofill
Interval simplesmente combina os dois por uma questão de apresentação para que você obtenha coisas como "07:00 - 07:30", "07:30 - 08:00", etc.
Eu usei uma fórmula aqui: =TEXT([@[Interval Start]],"HH:mm") & " - " & TEXT([@[Interval Stop]],"HH:mm")
O tempo total é onde ele calculará quanto trabalho foi feito nesse intervalo.

Aqui está a grande fórmula do Tempo Total:

=SUMPRODUCT((tblTimeLog[Start]<=[@[Interval Stop]])*(tblTimeLog[Stop]>[@[Interval Start]])*(IF([@[Interval Stop]]<=tblTimeLog[Stop],[@[Interval Stop]],tblTimeLog[Stop])-IF([@[Interval Start]]>tblTimeLog[Start],[@[Interval Start]],tblTimeLog[Start])))*24*60

Vamos dividir isso% SUMPRODUCT criará matrizes, multiplicará cada linha e somará essas linhas
É importante saber que isso tratará TRUE como 1 e FALSE como 0

O primeiro array é (tblTimeLog[Start]<=[@[Interval Stop]])
Isso localiza todas as entradas de log iniciadas antes do final desse intervalo

O segundo array é a ideia oposta (tblTimeLog[Stop]>[@[Interval Start]])
Localiza todas as entradas de log que terminaram após o início desse intervalo
Juntos, eles localizam todas as entradas de log que possuem algum tempo dentro do intervalo

(IF([@[Interval Stop]]<=tblTimeLog[Stop],[@[Interval Stop]],tblTimeLog[Stop])
Esta matriz seleciona o final do intervalo ou o final da atividade, o que ocorrer primeiro

IF([@[Interval Start]]>tblTimeLog[Start],[@[Interval Start]],tblTimeLog[Start])
Esta matriz seleciona o início do intervalo ou o início da atividade, o que tiver passado

A diferença entre os últimos dois arrays dá a você quanto tempo de cada entrada de log foi gasto nesse intervalo específico.

*24*60 este bit no final apenas transforma o valor de tempo em dias em um valor em minutos

Vamos resumir com algum pseudo-código:
=Sum((If the log entry is in this interval)*(How much time was spent in this interval))
O primeiro array será 1 ou 0 e o segundo será algum valor de tempo. Multiplicando-os juntos dá uma matriz de valores de 0 e tempo
Somando isso, você tem o tempo total registrado naquele intervalo -