Fórmula para calcular a duração de períodos 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 para a coluna A)
Coluna C: Duração, formatada como [mm], usando a fórmula=B1-A1

Como calculo a duração se os dados da coluna Bincluem horários que ocorrem no dia seguinte?

---

Planilha real:

Answer 1

Possivelmentea solução mais elegante e simples é aquela emesta respostamencionado por fixer1234 emeste comentário:

=MOD(B1-A1,1)

Observe que ele assume que ambos os valores são apenas valores de tempo (ou que a parte da data deve ser truncada), ou seja, 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 serial, 1 corresponde a 24 horas e um valor somente de hora é um valor de data e hora menor que 1. Assim:

• Se B1 >= A1então MOD(B1-A1,1)→ B1-A1como esperado
  ou
• If B1 < A1then MOD(B1-A1,1)é equivalente a 1-(A1-B1)(since MOD(-n,m)=m-MOD(n,m)), o que fornece a duração correta do "complemento"

---

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

=(B1<A1)+B1-A1

Explicação:

Isso funciona porque TRUEos valores são implicitamente coagidos a a 1e FALSEos valores a 0, quando usados ​​em operações aritméticas. Por isso:

• Se B1 >= A1então (B1<A1)→ 0e a fórmula se torna B1-A1
  ou
• Se B1 < A1então (B1<A1)→ 1e a fórmula é equivalente a1-(A1-B1)

Observe que esta fórmula funcionará corretamentemesmo que ambos os valores sejam data e horaeestão separados por mais de 24 horas!Isso énãoé o caso da primeira fórmula, portanto esta segunda fórmula é mais útil/geral.

Answer 2

Ctrl++ Shiftapenas ;coloca a hora atual na célula sem carimbo de data e, como @Akina aponta em sua resposta, não bloqueia nada.

NOW()adiciona um carimbo de data à célula, bem como a hora. Para ver isso, clique em umcélula vazia não formatada ou qualquer célula no Generalformatoe digite =NOW(). No momento de digitar isso eu recebo 21/08/2018 11:25.

Se você colocar =NOW()na célula A1e =NOW()+TIME(23,0,0)na célula B1, se você colocar =B1-A1na célula C1e formatar a célula, Timevocê obterá 23:00:00.

Se você estiver inserindo horários sem carimbos de data, precisará usar uma IF()fórmula para determinar se o segundo horário é menor que o primeiro (o período passa da meia-noite). A seguir, calcularemos a diferença de horário entre o horário na célula B1e na célula, A1independentemente de o horário de término ser depois da meia-noite ou não. Também funcionará com ou sem carimbos de data, pois os carimbos de data são ignorados.

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

Explicação da fórmula

=IF(B1<A1,...)

a hora na cela B1passa da meia-noite? Se sim, a próxima parte é executada

MOD(B1-A1,1)

O MOD(number,divisor)retorna o resto depois que um número é dividido por um divisor. Célula B1menos célula A1é um valor negativo, pois o horário na célula B1já passa da meia-noite e o Excel não gosta de valores de tempo negativos na maioria dos casos, portanto, fornecerá #####sem o MOD(). Isso MOD()resolve isso.

Uma alternativa à MOD()fórmula 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 é ,B1-A1)a última parte do IF()tempo onde se na célula B1énãoDepois da meia-noite.

Answer 3

Obrigado @Akina, esta é a única fórmula que funcionou para mim ... Eu tentei várias das opções acima - com contagens de votos positivas, lembre-se - e obtive o temido ###### ou algum número negativo com várias casas decimais. Contabilizados por tempos que abrangem dois dias SEM carimbos de data e toda aquela bobagem...

Answer 4

Para obter a diferença Minutesentre dois, 24Hrs DateTime Stampsvocê 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 GeneralFormato.