Como as fórmulas de tempo funcionam no Excel

Tipos de Tempo

Existem 2 maneiras principais de interpretar o tempo. A primeira é a hora conforme aparece no relógio – 13h, meio-dia, 17h, etc. Essa geralmente é a resposta para perguntas como ‘Que horas são agora?’ ou ‘Quando o jantar será servido hoje?’

O segundo tipo de interpretação é o tempo gasto – 1 hora, 3 dias, um mês, etc. Essa geralmente é a resposta para perguntas como ‘Quanto tempo dura o filme?’ e ‘Por quanto tempo devo assar?’. Uma palavra mais apropriada para isso é duração.

Veremos como usar essas duas interpretações no Excel.

Primeiro, em um encontro

Todas as datas no Excel são armazenadas como números de série. Para ver isso, coloque a data de hoje em uma célula e, em seguida, altere seu formato de número para Geral (na guia Home, grupo de formatos de número). Você verá que a data muda para um número de série. Esse número de série começa em 1 de janeiro de 1900 – que tem o número de série 1. 2 de janeiro de 1900 terá um número de série 2; e continua assim. O número de série para a data de hoje é 43200. Isso significa simplesmente que se passaram 43.200 dias desde 1º de janeiro de 1900. Se você digitar 43200 no Excel e pressionar Ctrl + Shift + 3; você verá a data em que este artigo foi escrito pela primeira vez.

Por causa disso, você pode fazer cálculos matemáticos em datas. Você pode tentar adicionar ou subtrair dias de alguma data facilmente. Uma lógica semelhante é usada para o Tempo.

Como o tempo funciona no Excel

Se você entende como ele é armazenado no Excel, não terá mais problemas com ele! Deixe-me explicar.

O tempo é armazenado como um valor decimal. Para entender melhor, digite 6h em uma célula e altere o formato do número da célula para geral. Você verá que o valor armazenado pelo Excel é 0,25.

Isso porque em um único dia de 24 horas; 6h00 seria um quarto do dia. Como já vimos, um dia é armazenado como 1 no Excel. Portanto, um quarto de 1 é 0,25! Isso prova que a hora é armazenada em um valor entre 0 e 1. Seguindo essa lógica, 12 horas é 0,5 – digite-o em uma célula e altere o formato do número para Hora.

É aqui que a confusão geralmente acontece – 9h00 é 0,375 e 15h30 é 0,645833333. Em outras palavras, o tempo é uma fração de 1. Para ver o valor decimal de uma hora, divida 1 por 24.

Como funciona a duração no Excel

Aqui estão algumas boas notícias – não existe um sistema separado para a duração. Os valores decimais armazenados no Excel permanecem os mesmos. Tudo o que difere é o formato do número. Isso significa que para o Excel 6 da manhã e 6 horas é o mesmo. Isso porque se você tentar escrever 6 da manhã em uma célula, isso significa 6 horas desde a meia-noite. Em decimais, a duração ainda permanecerá 0,25 – um quarto de dia. Se você disser 30 horas, significa um dia inteiro mais 6 horas. É exatamente assim que o Excel também vê a duração – como 1,25.

Se você digitar 6:00 em uma célula e for para o formato de número personalizado, o formato de número será definido como hh:mm. Mas tente digitar 30 horas e verificar o formato do número – ele será definido como [h]:mm:ss.

No que diz respeito à duração, apenas esses dois precisam ser compreendidos. O fato é que existem apenas 24 horas em um dia. Agora, o formato hh:mm segue as limitações de tempo do relógio. Portanto, não pode mostrar nenhuma duração superior a 23 horas e 59 minutos. A 24ª hora é meia-noite – que é armazenada como zero pelo Excel.

Portanto, para mostrar qualquer duração superior a 23 horas e 59 minutos, o formato de número [h]:mm:ss é usado. Os colchetes informam ao Excel que esse número não deve ser exibido como duração do relógio, mas como duração – em geral. Observe atentamente a data na barra de fórmulas:

Duração Negativa

Você não pode ter uma unidade de tempo negativa como -6:00 AM, mas a duração pode ser negativa ou positiva. Por exemplo, em um exame de três horas, você levou apenas 2,5 horas para concluir todas as suas respostas. Isso também pode ser expresso como -0,5 horas, se você estiver observando quando terminou (tempo gasto menos o tempo total). Mas tente fazer isso no Excel com um formato de duração aplicado. A célula vai apenas mostrar ### – que é a maneira do Excel de indicar um erro de exibição. Existem 2 maneiras de corrigir isso – alterando as configurações de data para a pasta de trabalho [A] ou usando uma função [B].

A) Na segunda data

Existe um segundo sistema de data para o qual você pode alternar – o sistema de data de 1904. Possui os 2 recursos a seguir:

1. A diferença entre os dois sistemas de data está no início do número de série. Neste sistema, o número de série 0 é 1 de janeiro de 1904. O número de série 1 é 2 de janeiro de 1904; e assim por diante.
2. Este sistema suporta durações negativas.

Para mudar para este sistema, vá para Opções do Excel> Avançado> Ao calcular esta pasta de trabalho> Usar sistema de data 1904. O único problema com isso é que todas as datas na pasta de trabalho mudam em 4 anos. Portanto, você precisa fazer isso de antemão. Para remediar isso, podemos usar uma função.

B) Usando uma função para durações de tempo negativas

O truque é fazer com que o Excel calcule sem o sinal negativo. Isso é feito facilmente usando a função ABS. Sua função é ignorar os sinais associados a um número. Se encaixarmos nosso cálculo nisso, ele funcionará bem o suficiente. O erro de exibição foi resolvido.

Mas e se você quiser ver o sinal negativo antes dessa duração? A solução seria usar algo assim:

Cuidado: Isso agora se tornará uma célula de ‘texto’. O que significa que você não pode fazer cálculos diretamente sobre isso.

Um exemplo

Estes são dados de amostra de uma corrida com nomes de jogadores, horários de início e término e duração. A duração é simplesmente o horário de término menos o horário de início; com um formato de número de duração aplicado.

Antes de calcular os leads, os dados devem ser classificados da duração mais curta para a mais longa. A vantagem para o primeiro será zero. O segundo precisará de uma fórmula que calcule quantos segundos atrás do primeiro e assim por diante.

Como você pode ver, as durações negativas não são exibidas. Para corrigir isso, use:

="-"&TEXT(ABS(D5-D4),"m:ss")

Aqui, não precisamos de horas, apenas minutos, se houver e segundos. Portanto, o formato do número é alterado para incluir apenas os minutos. Aqui, não importa se colocarmos colchetes ao redor do [m] pois não há possibilidade de ter mais de 59 minutos. A menos que seja para o Rally Dakar!

Fácil, certo? Aqui está uma pergunta – como você acha que a fórmula deveria ser para obter a liderança desde a primeira?

A função Time no Excel

Para tornar a vida mais fácil, existe uma função apenas para o tempo. Requer apenas 3 componentes para retornar uma hora:

1. Hora
2. Minuto
3. Segundo

Usar esta função torna um pouco difícil entrar no tempo; mas permite cálculos mais fáceis. Por exemplo, se você precisar adicionar algumas horas às 9:32:28 AM; a função seria semelhante a: (veja a captura de tela acima)

=TIME(D4+2,E4,F4)

Aqui, apenas para o componente de hora, estamos adicionando 2. Da mesma forma, torna-se muito fácil adicionar / subtrair minutos ou segundos também.

Funções relacionadas aos componentes do tempo

Os 3 componentes do tempo também são funções independentes. Todos os 3 requerem apenas um valor de tempo. Eles então retornarão apenas as horas, minutos ou segundos como um número inteiro em um formato de 24 horas. Por ser um número inteiro, os formatos de número de duração não podem ser aplicados diretamente a eles. Por exemplo, se você tentar usar a formatação do número de duração em 14 horas, o valor será 336:00:00. Portanto, a maneira mais confiável de calcular as durações seria como fizemos anteriormente neste artigo.

Como usar as funções de hora

Vejamos o exemplo de um ciclo de produção simples. Produto X é feito em 5 estágios, cada um levando diferentes quantidades de tempo para fabricar/processar. Se a produção começar às 10h, os horários de término de cada estágio devem ser calculados.

Aqui, estamos usando as funções de hora e minuto para primeiro extrair apenas hora e minutos do horário de início. Suas respectivas durações são então adicionadas a eles individualmente. Ambos os cálculos são usados ​​na função Time. Portanto, vemos diretamente o horário de término calculado. Observe que os segundos não são contados e são definidos como zero.

Isso é praticamente tudo que você precisará para lidar com o tempo no Excel.

Aprenda mais sobre Excel: