Fórmula para calcular a duração de tempos que podem abranger dois dias

Question

Coluna A : Hora de início no formato de 24 horas (usando a fórmula =NOW() e bloqueada com Ctrl + SHIFT + ; )
Coluna B : hora de término no formato de 24 horas (inserida da mesma forma que na coluna A )
Coluna C : duração, formatada como [mm] , usando a fórmula =B1-A1

Como faço para calcular a duração se os dados na coluna B incluem os horários que ocorrem no dia seguinte?

Planilha real:

    

Answer 1

Ctrl + Desloc + ; apenas coloca a hora atual na célula sem um datestamp, e como @Akina aponta em sua resposta , não bloqueia nada.

NOW() adiciona um datestamp à célula, bem como a hora. Para ver isso, clique em uma célula vazia não formatada ou em qualquer célula em General format e digite =NOW() . No momento da digitação, recebo 21/08/2018 11:25 .

Se você colocar =NOW() na célula A1 e =NOW()+TIME(23,0,0) na célula B1 , se você colocar =B1-A1 na célula C1 e formatar a célula como Time , você receberá 23:00:00 .

Se você estiver inserindo tempos sem datestamps, será necessário usar uma fórmula IF() para determinar se a segunda vez é menor que a primeira (o período passa da meia-noite). A seguir, é preciso calcular a diferença de horário entre o tempo em que a célula B1 e a célula A1 se a hora final está além da meia-noite ou não. Ele também funcionará com ou sem datestamps, pois os dados são ignorados.

=IF(B1<A1,MOD(B1-A1,1),B1-A1)

Explicação da fórmula

=IF(B1<A1,...)

o tempo na célula B1 depois da meia-noite? Se assim for, a próxima parte é executada

MOD(B1-A1,1)

O MOD(number,divisor) retorna o restante depois que um número é dividido por um divisor. A célula B1 minus cell A1 é um valor negativo, pois o tempo na célula B1 ultrapassa a meia-noite e o Excel não gosta de valores de tempo negativos na maioria dos casos, portanto, ele fornecerá ##### sem MOD() . O MOD() classifica isso.

Uma alternativa à fórmula MOD() seria usar

TIME(23,59,59)-TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(0,0,1)+B1

Qual é 23:59:59 minus time in cell A1 plus 1 second plus time in cell B1 .

O último bit que é ,B1-A1) é a última parte do IF() , em que se o tempo na célula B1 for não depois da meia-noite.

    

Answer 2

Possivelmente a solução mais elegante e simples é a que existe em esta resposta mencionada por fixer1234 em este comentário :

=MOD(B1-A1,1)

Observe que os dois valores são apenas valores de tempo (ou que a parte da data deve ser truncada), isto é, cada um é menor que 1 e que a duração é menor que 24 horas.

Explicação:

Isso funciona porque, para um valor de data e hora de série, um 1 corresponde a 24 horas e um valor de tempo somente é um valor de data e hora menor que 1. Assim:

• Se B1 >= A1 , em seguida, MOD(B1-A1,1) → B1-A1 como esperado
  ou
• Se B1 < A1 , em seguida, MOD(B1-A1,1) for equivalente a 1-(A1-B1) (desde MOD(-n,m)=m-MOD(n,m) ), que fornece a duração correta do "complemento"

Uma solução alternativa (você julga se é mais simples / mais elegante ;-)) é:

=(B1<A1)+B1-A1

Explicação:

Isso funciona porque TRUE valores são implicitamente forçados a 1 e FALSE valores a 0 , quando usados em operações aritméticas. Assim:

• Se B1 >= A1 , em seguida, (B1<A1) → 0 e a fórmula se tornar B1-A1
  ou
• Se B1 < A1 , em seguida, (B1<A1) → 1 e a fórmula for equivalente a 1-(A1-B1)

Observe que essa fórmula funcionará corretamente mesmo que os dois valores sejam e com mais de 24 horas de diferença! Não é o caso para a primeira fórmula, então esta segunda fórmula é mais útil / geral.

    

Answer 3

How do I calculate the duration if data in Column B include the timing that fall on the next day?

=1 + B1 - A1

formatado como uma hora mostrará o resultado correto. Se B1 > A1, o resilt, claro, contém uma parte de data diferente de zero, mas não é mostrada por causa de um formato de célula.

Se você quiser usar esse valor de tempo em alguns cálculos de tempo, use uma fórmula mais complexa

=B1-A1-INT(B1-A1)

PS.

and lock with CTRL+SHIFT+;

Esta combinação não bloqueia nada. Ele substitui totalmente o conteúdo da célula pelo valor de hora atual.

    

Answer 4

Para obter a diferença em Minutes entre dois 24Hrs DateTime Stamps , você pode usar qualquer um deles:

=Int((A2-A1)*24*60)

Ou

=int((A2-A1)*1440)

Ou

=ROUNDUP((A2-A1)*1440,2)

Observação, A célula da fórmula deve ter General Format.