Programação do Excel - tentando calcular horas após a meia-noite, para que não sejam negativas

Question

Se alguém puder me dar qualquer tipo de ajuda sobre o motivo pelo qual eu tenho números negativos em vez de 40, que é o que deveria ser igual, por favor ajude. Eu ficaria muito grato. Obrigado!

    

Answer 1

Você tem corretamente "Usar sistema de dados 1904" selecionado para a pasta de trabalho, em Arquivo > Avançado > "Ao calcular esta pasta de trabalho" (role até o final).

Isso é necessário para permitir que os tempos sejam calculados como negativos. (Tente desligá-lo, e você verá os negs se transformarem em todos os #s)

Mas, em B16 e C16, por exemplo, a hora é simplesmente introduzida às 18:00 e 01:00 sem qualquer data. 01:00 - 18:00 é realmente um tempo negativo. Aproximadamente -0,71, se exibido como um número.

A solução é alterar cada cálculo de tempo de início e término individual para atender a meia-noite. Então substitua "C16-B16" por "IF (C16-B16 < 0, C16-B16 + 1, C16-B16)"

Você precisaria fazer isso para cada dia da semana, o que torna a fórmula bastante longa.

Considere adicionar uma coluna extra entre os dias para exibir as horas trabalhadas nesse dia. Então, o total só precisa somar essas células.

Edit: derrotado!

Edite novamente: você deve usar +1 como no meu exemplo, em vez de +24 como na postagem anterior, porque a unidade é de dias, não de horas.

Edição final: Uma solução muito mais curta é substituir "C16-B16" por "MOD (C16-B16,1)". Isso funciona mantendo apenas a parte fracionária do tempo. Com tempos, um decimal 1 é de 24 horas.

    

Answer 2

A fórmula usual para ultrapassar 24 anos é:

 =EndTime - StartTime +(EndTime < StartTime)

Para combinar tudo isso em uma fórmula, para somar todos os dias da semana, é estranho, mas factível. Requer uma fórmula inserida em array, já que você precisa testar cada par individualmente.

A fórmula abaixo faz uso de sua configuração, pois todos os seus EndTimes estão em colunas pares; e os StartTimes estão em colunas ímpares.

Observe que os dois arrays construídos são diferentes em uma coluna. Nas versões do Excel 2007+, você pode testar diretamente de ODD / EVEN com a função ISODD / ISEVEN.

Esta fórmula deve ser introduzida por matriz :

=SUM(
MOD(COLUMN($C16:$O16),2)*$C16:$O16-
(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16+
((MOD(COLUMN($C16:$O16),2)*$C16:$O16-(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16)<0))
*24

ou, usando ISODD e ISEVEN:

=SUM(
ISODD(COLUMN($C16:$O16))*$C16:$O16-
ISEVEN(COLUMN($B16:$N16))*$B16:$N16+
((ISODD(COLUMN($C16:$O16))*$C16:$O16-ISEVEN(COLUMN($B16:$N16))*$B16:$N16)<0))
*24

Ou, ainda mais curto, mas mais difícil de entender, já que usamos a função MOD para reter apenas o componente fracionário:

=SUM(
     MOD(
         ISODD(COLUMN($C16:$O16))*$C16:$O16-
         ISEVEN(COLUMN($B16:$N16))*$B16:$N16,
     1))
*24

Para matriz-insira uma fórmula, depois de inserir a fórmula na célula ou barra de fórmulas, mantenha pressionada ctrl-shift ao pressionar digite . Se você fez isso corretamente, o Excel colocará colchetes {...} ao redor da fórmula.

Eu fiz a fórmula para a linha que você mostra na sua captura de tela. O resultado é 37,9833 usando seus números

    

Answer 3

Seu problema é quando você passa da meia-noite. Tome por exemplo a primeira linha na sexta-feira, sábado e domingo. Cada dia o horário final é 2:00. Por exemplo, na sexta-feira, quando você subtrai os horários, são necessárias as 18h00 de sexta-feira, menos as 2h da manhã de sexta-feira, antes das 18h.

Para evitar o negativo, mas ainda manter a boa aparência visual do seu gráfico, você deve usar uma instrução IF. Para calcular para sexta-feira use:

=IF(K3 < J3, K3 - J3 + 24, K3 - J3)

Existem três seções na declaração IF.

(1) a condição

K3 < J3

Isto verifica se K3 (a hora final) é menor que J3 (a hora inicial). Em caso afirmativo, retornaria um número negativo (o horário 2:00 AM é "menor que" 18:00).

(2) se a condição for verdadeira

K3 - J3 + 24

Se a condição for verdadeira, o Excel usa essa equação. Eu escrevi para adicionar 24 para neutralizar quaisquer negativos que possam ocorrer.

(3) se a condição for falsa

K3 - J3

Se a condição for verdadeira, o Excel usa essa equação.

Resumo

Use a instrução IF fornecida no lugar da equação que você estava usando para calcular as horas de cada dia.

    

Answer 4

Eu tive um problema semelhante ao calcular os horários que ocasionalmente passavam da meia-noite sem informações de dia ou data. Meus horários de início eram à noite e às vezes chegavam ao dia seguinte, então eu usei algumas fórmulas juntas para que saísse para mim:

=IF(D4< C4,TEXT(D4-C4+24,"h:mm"),TEXT(D4-C4,"h:mm"))

(note que fui forçado a adicionar um espaço após o símbolo menor que por algum motivo de formatação aqui - não estou usando um espaço na minha fórmula)

É simples se, no meu caso, D4 é a hora de término e C4 é a hora de início. Se a hora de término for menor que a hora de início, ela deve ter passado da meia-noite. A instrução do meio fornece a correção total do tempo de trabalho para ultrapassar a meia-noite (+24). O final mostra o tempo de trabalho se a subtração regular é tudo que você precisa. Descobri que sem o "TEXTO" e o formato de hora, a matemática +24 seria exibida como um valor decimal.