☰

Agendamento do Excel - tentando calcular horas depois da meia-noite para que não sejam negativas

tag-icon • tag-icon microsoft-excel microsoft-excel-2007
Agendamento do Excel - tentando calcular horas depois da meia-noite para que não sejam negativas

Esta é uma captura de tela do problema que tenho e do código que estou usando.

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

Responder1

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

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

Mas, em B16 e C16, por exemplo, a hora é simplesmente inserida às 18h00 e 01h00 sem qualquer data. 01h00 - 18h00 é realmente um horário negativo. Aproximadamente -0,71 se exibido como um número.

A solução é alterar cada cálculo individual do horário de término-início para atender depois da meia-noite. Portanto, 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 naquele dia. Então o total só precisa somar essas células.

Editar: derrotado!

Edite novamente: você deve usar +1 como no meu exemplo, em vez de +24 como no post anterior, porque a unidade é dias, não 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 os tempos, um decimal 1 é 24 horas.

Responder2

A fórmula usual para passar dos 24 é:

 =EndTime - StartTime +(EndTime < StartTime)

Combinar tudo isso em uma fórmula, para somar todos os dias da semana, é estranho, mas factível. Requer uma fórmula inserida na matriz, pois você precisa testar cada par individualmente.

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

Observe que as duas matrizes construídas são diferentes em uma coluna. Nas versões do Excel 2007+, você pode testar diretamente ÍMPAR/PAR com a função ISODD/ISEVEN.

Esta fórmula deve serinserido na 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

Paraentrada de matrizuma fórmula, depois de inserir a fórmula na célula ou barra de fórmulas, mantenha pressionado Ctrl-Shiftenquanto batedigitar. 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

Responder3

Seu problema é quando você passa da meia-noite. Tomemos por exemplo a primeira linha na sexta, sábado e domingo. Cada dia o horário de término é 2h. Por exemplo, na sexta-feira, quando você subtrai os tempos, são 18h00 de sexta-feira menos 2h00 de sexta-feira, que é antes das 18h00.

Para evitar o negativo, mas ainda manter a bela 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 instrução IF.

(1) a condição

K3 < J3

Isso verifica se K3 (o horário de término) é menor que J3 (o horário de início). Nesse caso, ele retornaria um número negativo (o horário 2h é "menos que" 18h).

(2) se a condição for verdadeira

K3 - J3 + 24

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

(3) se a condição for falsa

K3 - J3

Se a condição for verdadeira, o Excel usa esta 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.

Responder4

Tive um problema semelhante ao calcular horários que ocasionalmente passavam da meia-noite sem nenhuma informação de dia ou data. Meu horário de início era à noite e às vezes se espalhava para o dia seguinte, então usei algumas fórmulas para fazer com que tudo funcionasse bem para mim:

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

(observe 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)

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

informação relacionada