Fórmulas de tempo do Excel: Calcular, formatar e manipular o tempo no Excel

O Excel facilita o trabalho com números, mas lidar com o tempo (na minha opinião) é uma história diferente. A parte confusa (que abordarei a seguir) é que o Excel armazena e processa valores de tempo como dias fracionários, o que pode levar a resultados inesperados se você não estiver familiarizado.

Neste tutorial, orientarei você em tudo, desde a criação e a formatação de valores de tempo até a solução de problemas comuns, como durações negativas e estouros de tempo. Ao final, você saberá como criar planilhas mais inteligentes e confiáveis para planejamento de projetos, folha de pagamento ou qualquer outra coisa em que esteja trabalhando.

Entendendo o sistema de datas do Excel

Temos que começar com o sistema de datas do Excel. O Excel armazena datas como números de série sequenciais, a partir de 1º de janeiro de 1900, que é representado pelo valor 1. Cada dia subsequente aumenta o número de série em 1. Por exemplo:

• 1º de janeiro de 1900 =. 1

• 2 de janeiro de 1900 = 2 de janeiro de 1900 2

• 31 de dezembro de 2023 =. 45205

Os valores de tempo são armazenados como frações de um dia. Por exemplo:

• 6:00 AM = 0.25 (6 horas é 25% de um dia de 24 horas)

• 12:00 PM = 0.5

• 6:00 PM = 0.75

A combinação de datas e horas é tão simples quanto adicionar os dois valores. Por exemplo, a fórmula abaixo combina uma data e uma hora:

=A2 + TIME(10, 30, 0)

Se A2 contiver 45205 (31 de dezembro de 2023), o resultado será 45205.4375, representando 10:30 AM em 31 de dezembro de 2023.

Como criar valores de tempo no Excel

Vamos dar uma olhada nas duas maneiras mais comuns de criar valores de tempo válidos no Excel: usando a função TIME() e convertendo texto com TIMEVALUE().

Use a função TIME()

Mencionei isso anteriormente. A função TIME() permite que você crie um valor de tempo a partir de componentes separados: hora, minuto e segundo. Aqui está a sintaxe:

=TIME(hour, minute, second)

Por exemplo, se quiser representar 10:30 AM, você pode escrever:

=TIME(10, 30, 0)

Isso cria um valor de hora adequado no Excel para 10:30 AM, que é armazenado internamente como 0,4375 (já que 10 horas e 30 minutos são 43,75% de um dia de 24 horas).

O Excel é flexível com os valores que você insere. Se você ultrapassar os limites normais de tempo, o Excel reverterá o tempo extra. Por exemplo:

=TIME(27, 0, 0)

Isso retorna 3:00 AM porque 27 horas é o mesmo que um dia inteiro (24 horas) mais 3 horas. Da mesma forma:

=TIME(10, 75, 0)

Isso é interpretado como 11:15 AM, já que 75 minutos se tornam 1 hora e 15 minutos.

É importante entender esses casos extremos, especialmente se você estiver gerando valores de tempo dinamicamente usando fórmulas.

Use a função TIMEVALUE()

Você pode converter texto em hora com TIMEVALUE(). Portanto, se a sua planilha incluir a hora escrita como texto, como "2:30 PM" ou "14:45", o Excel poderá não tratá-la como um valor de hora automaticamente. É aí que entra o site TIMEVALUE().

=TIMEVALUE("2:30 PM")

Isso converte a cadeia de texto em um valor de hora do Excel. Agora você pode usá-lo em cálculos como em qualquer outro momento.

Vale a pena observar a diferença entre TIME() e TIMEVALUE():

• TIME() constrói um horário a partir de números separados (hora, minuto, segundo)

• TIMEVALUE() converte uma string de texto em uma hora

Ambos retornam valores que o Excel reconhece como horas válidas, que é o que você precisa para matemática e formatação precisas.

Como calcular as diferenças de horário

Quando você tem valores de tempo válidos no Excel, uma das tarefas mais comuns é calcular quanto tempo se passou entre dois pontos. Quer você esteja medindo a duração de um turno ou calculando o tempo necessário para concluir uma tarefa, o Excel oferece várias maneiras de obter a resposta.

Vamos explorar as abordagens mais úteis, desde a subtração simples até truques mais avançados.

Subtrair duas vezes diretamente

A maneira mais direta de calcular uma diferença de horário é subtrair a hora de início da hora de término. 

Por exemplo, se a célula A1 contiver 10:00 AM e B1 contiver 1:30 PM, a fórmula será semelhante a esta:

=B1 - A1

O resultado será 0,145833. Isso pode parecer estranho à primeira vista, mas lembre-se de que o Excel representa o tempo como uma fração do dia. Nesse caso, 0,145833 equivale a 3 horas e 30 minutos.

Para tornar o resultado mais útil, você geralmente deseja convertê-lo em um formato legível por humanos.

Converter diferenças de tempo em horas, minutos ou segundos

Você pode multiplicar o resultado de uma subtração de tempo para expressar a diferença em horas, minutos ou segundos. Veja como você pode converter uma diferença de horário em horas:

=(B2 - A2) * 24

Veja como você pode converter uma diferença de horário em minutos:

=(B2 - A2) * 1440

Veja como você pode converter uma diferença de horário em segundos:

=(B2 - A2) * 86400

Essas fórmulas funcionam porque há 24 horas, 1.440 minutos e 86.400 segundos em um dia. Se a diferença de horário for de três horas e meia, você obterá 3,5 da fórmula das horas, 210 da fórmula dos minutos e 12.600 da fórmula dos segundos.

Extrair unidades de tempo específicas

Se quiser obter apenas uma parte da hora, por exemplo, apenas os minutos ou as horas, você pode usar HOUR(). HOUR() retorna a parte da hora de uma hora.

=HOUR(B2 - A2)

MINUTE() retorna a parte do minuto.

=MINUTE(B2 - A2)

SECOND() retorna os segundos.

=SECOND(B2 - A2)

Essas funções são úteis, mas são redefinidas após 23 horas, 59 minutos ou 59 segundos. Portanto, se a duração for maior que um dia inteiro, eles não refletirão o tempo total decorrido. Nesses casos, é melhor multiplicar a diferença ou usar formatos personalizados, sobre os quais falaremos mais adiante.

Adicionar valores de tempo acima de 24 horas

Se você precisar adicionar horas que excedam 24 horas, o uso direto do site TIME() não funcionará como esperado. Em vez disso, você pode realizar a aritmética usando frações de um dia.

Aqui está como você adiciona horas acima de 24.

=A2 + (28/24)  ' Adds 28 hours

Aqui está como você adiciona minutos acima de 60.

=A2 + (90/1440)  ' Adds 90 minutes

Aqui está como você adiciona segundos acima de 60.

=A2 + (3600/86400)  ' Adds 3600 seconds

O Excel armazena o tempo como decimal, em que 1 dia é igual a 1, portanto, ao dividir por 24, 1440 ou 86400, você ajusta o valor para representar uma parte de um dia.

Lidar com valores de tempo negativos

Por padrão, se você subtrair um tempo maior de um tempo menor (por exemplo, 10:00 AM - 2:00 PM), o Excel retornará um erro ou exibirá uma sequência de ###.

Para lidar com segurança com valores de tempo negativos, use a função MOD(). Essa função evita efetivamente os resultados negativos, envolvendo-os em um horário válido.

Por exemplo, para subtrair 3 horas com segurança:

=MOD(A2 - TIME(3, 0, 0), 1)

Essa fórmula garante que o resultado seja sempre um valor de tempo positivo, evitando erros indesejados ou problemas de exibição.

Como formatar corretamente os resultados de tempo

Os cálculos de tempo no Excel geralmente produzem valores decimais confusos ou resultados inesperados, especialmente quando você lida com durações superiores a 24 horas ou valores de tempo negativos. A formatação adequada pode transformar os resultados brutos em formatos legíveis e fáceis de interpretar e apresentar.

Usar a formatação básica de hora

Se você quiser exibir uma diferença de horário como "3:30" em vez de um decimal, use a função TEXT().

=TEXT(B2 - A2, "h:mm")

Isso formata o resultado como horas e minutos. Lembre-se de que o resultado é um texto com boa aparência, mas que você não poderá usá-lo em cálculos matemáticos posteriores, a menos que o converta novamente.

Aplicação de formatos de hora personalizados

Os formatos de hora incorporados no Excel lidam com valores de hora padrão de forma eficaz, mas os formatos personalizados oferecem maior flexibilidade para a exibição de durações, horas estendidas ou componentes de hora específicos.

Para aplicar um formato de hora personalizado, siga estas etapas:

1. Selecione a(s) célula(s) que contém os valores de tempo.
2. Clique com o botão direito do mouse e selecione Formatar células.
3. No Número selecione Personalizar.
4. Digite o código de formato desejado.

Aqui estão alguns formatos de hora personalizados comuns:

• [h]:mm: Exibe o total de horas e minutos, mesmo após 24 horas. Exemplo: 36:45 (36 horas e 45 minutos)

• d "days" h:mm:ss: Exibe dias, horas, minutos e segundos. Exemplo: 1 days 12:30:00

• h:mm AM/PM: Exibe a hora com notação AM/PM. Exemplo: 10:30 PM

• mm:ss: Exibe apenas minutos e segundos. Aqui está um exemplo: 45:15

Os formatos personalizados permitem que você apresente os dados de tempo de forma mais intuitiva para casos de uso específicos, como planilhas de horas ou registros de trabalho.

Exibição de diferenças de horário amigáveis

Se quiser exibir uma diferença de horário como uma cadeia de caracteres legível por humanos, como "2 dias, 4 horas e 15 minutos", você poderá combinar várias funções em uma única fórmula.

Aqui está um exemplo prático:

=INT(B2 - A2) & " days, " & HOUR(B2 - A2) & " hours, " & MINUTE(B2 - A2) & " minutes"

Nessa fórmula, a função INT() extrai o número de dias completos, enquanto HOUR() e MINUTE() extraem as horas e os minutos restantes.

Para evitar que valores zero apareçam na saída, você pode aninhar esses cálculos nas instruções IF(). Por exemplo:

=IF(INT(B2 - A2) > 0, INT(B2 - A2) & " days, ", "") &
 IF(HOUR(B2 - A2) > 0, HOUR(B2 - A2) & " hours, ", "") &
 MINUTE(B2 - A2) & " minutes"

Essa fórmula exibe apenas as unidades relevantes, tornando o resultado mais limpo e legível.

Principais funções de tempo do Excel que você deve conhecer

Como você viu até agora, o Excel inclui um conjunto poderoso de funções de tempo. Para que você tenha uma contabilidade mais completa, aquiestá um rápido resumo das funções de tempo mais importantes do Excel.

Usando a função TIME() para criar valores de tempo

TIME() constrói um valor de hora válido a partir de três componentes: horas, minutos e segundos. Essa função é ideal quando você precisa criar um valor de tempo de forma programática.

Aqui está um bom exemplo:

=TIME(14, 30, 0)  

O comando acima retornará 2:30 PM, que é o resultado dessa sintaxe.

=TIME(hour, minute, second)

Conversão de texto em tempo com TIMEVALUE()

Se você tiver uma hora escrita como uma cadeia de texto (por exemplo, "2:45 PM"), use TIMEVALUE() para convertê-la em um valor de hora adequado no Excel que possa ser usado em cálculos.

=TIMEVALUE("2:45 PM")

Por exemplo, isso retornará 0.614583, que é uma fração de um dia:

=TIMEVALUE("14:45")

Obtendo a data e a hora atuais com NOW()

NOW() retorna a data e a hora atuais como um único valor. Ele é atualizado automaticamente sempre que a planilha é recalculada.

=NOW()

Aqui está um bom exemplo. Se a hora atual for 3:15 PM, NOW() poderá retornar 44710.63542, em que 44710 é a data e .63542 representa a hora.

Recuperando a data atual com TODAY()

Se você precisar apenas da data atual sem a hora, use TODAY(). Essa função é particularmente útil para calcular prazos ou dados de envelhecimento.

=TODAY()

Extração de componentes de tempo com HOUR(), MINUTE() e SECOND()

Essas funções permitem que você isole partes específicas de um valor de tempo, tornando-as úteis para relatórios e lógica condicional.

HOUR() retorna a parte da hora de um valor de tempo (0-23).

=HOUR(A2)

MINUTE() retorna a parte do minuto (0-59).

=MINUTE(A2)

SECOND() retorna a porção de segundos (0-59).

=SECOND(A2)

Formatação de valores de tempo com TEXT()

TEXT() converte um valor de tempo em um formato específico, o que o torna ideal para exibir durações ou criar cadeias de tempo personalizadas. Syntax:

=TEXT(A2, "h:mm:ss AM/PM")

Manipulação de valores de tempo negativos com TEXT()

Ao subtrair valores de tempo, o site MOD() garante que o resultado seja sempre positivo, evitando erros causados por valores de tempo negativos.

=MOD(A2 - B2, 1)

Criando valores de data com DATE()

DATE() permite que você crie uma data a partir de componentes separados: ano, mês e dia. Isso é útil para combinar datas e horas em um único valor.

=DATE(year, month, day)

Aqui está um bom exemplo:

=DATE(2023, 12, 31) + TIME(14, 30, 0)

Que retorna em 31/12/2023 às 2:30 PM.

Criando dias úteis com NETWORKDAYS()

NETWORKDAYS() calcula o número de dias úteis entre duas datas, excluindo fins de semana e feriados especificados opcionalmente. Essa função é ideal para calcular a duração de projetos ou períodos de folha de pagamento.

=NETWORKDAYS(start_date, end_date, [holidays])

Por exemplo:

=NETWORKDAYS(A2, B2, C2:C5)

Se A2 contiver 12/1/2023 e B2 contiver 12/31/2023, o resultado será o número de dias úteis em dezembro, excluindo os feriados listados no intervalo C2:C5.

Criando dias úteis e finais de semana com WORKDAY.INTL()

WORKDAY.INTL() é uma versão avançada do WORKDAY() que permite que você personalize quais dias são considerados fins de semana.

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

Por exemplo:

=WORKDAY.INTL(A2, 10, "0110000")

Aqui, 0110000 especifica que terça-feira (0) e quarta-feira (1) são fins de semana. A fórmula calcula a data 10 dias úteis após A2, excluindo terça e quarta-feira.

Conclusão

O manuseio do tempo no Excel pode parecer complicado, mas com as funções certas, ele se torna muito mais gerenciável.

Se você deseja aprimorar ainda mais suas habilidades em Excel, confira nosso curso Introdução ao Excel para obter habilidades básicas ou o curso Análise de dados no Excel para praticar o trabalho com dados do mundo real.

Para aplicativos mais avançados, o programa de habilidades Excel Fundamentals oferece projetos e exercícios guiados para ajudar você a dominar os cálculos de tempo e muito mais.