devido aDatas e horas são armazenadas como números no backend do Excel, para que você possa usar facilmente operações aritméticas simples e fórmulas em valores de data e hora.
Por exemplo, você pode adicionar e subtrair dois valores de tempo ou valores de data diferentes, ou pode calcular a diferença de tempo entre duas datas/horas fornecidas.
Neste tutorial vou mostrar como Execute cálculos usando o tempo no Excelvários métodos (como calcular a diferença horária, adicionar e subtrair tempo, exibir a hora em diferentes formatos e calcular a soma dos valores de tempo).
Conteúdo
- 1 Como o Excel lida com datas e horas?
- 2 A fórmula para calcular a diferença de tempo entre dois tempos
- 2.1 Subtração simples para calcular a diferença de tempo no Excel
- 2.2 Calcular a diferença horária em horas, minutos ou segundos
- 2.3 Calcule a diferença de tempo usando a função TEXT
- 2.4 Obtenha a diferença horária em uma unidade (horas/minutos), ignorando outras
- 2.5 Calcular o tempo decorrido agora (a partir da hora de início)
- 3 Calcular o tempo quando a data muda (calcular e exibir o tempo negativo no Excel)
- 4 Adicione e subtraia tempo no Excel
- 5 Como somar o tempo no Excel
- 6 Os resultados mostram hash (###) em vez de data/hora (causa + correção)
- 7 Perguntas frequentes sobre como calcular adição e subtração de tempo no Excel
- 8 Oh, olá, prazer em conhecê-lo.
Como o Excel lida com datas e horas?
Como mencionei, datas e horas são armazenadas como números nas células do Excel.Número inteiro representa o dia inteiro, parte fracionária do número representa a parte do dia (pode ser convertido em valores de hora, minuto e segundo)
Por exemplo, um valor de 1 representa 1900º de janeiro de 1 no Excel, que é o ponto de partida para o Excel começar a considerar as datas.
Portanto, 2 significa 1990 de janeiro de 1, 2 significa 3 de janeiro de 1900 e assim por diante, 1 significa 3 de janeiro de 44197.
Observação: o Excel para Windows e o Excel para Mac seguem datas de início diferentes.1 no Excel para Windows significa 1900 de janeiro de 1, 1 no Excel para Mac significa 1 de janeiro de 1904
Se esses números tiverem algum dígito após o ponto decimal, o Excel tratará esses números como parte de um dia e poderá convertê-los em horas, minutos e segundos.
Por exemplo, 44197.5 significa 2021º de janeiro de 1 às 1:12:00.
Então, se você está trabalhando com valores de tempo no Excel, basicamente está trabalhando com a parte fracionária do número.
O Excel oferece a flexibilidade de converter partes fracionárias em formatos diferentes, como apenas horas, apenas minutos, apenas segundos ou uma combinação de horas, minutos e segundos
Agora que você entende como o tempo é armazenado no Excel, vejamos alguns exemplos de como calcular a diferença de tempo entre duas datas ou horas diferentes no Excel
A fórmula para calcular a diferença de tempo entre dois tempos
Em muitos casos, tudo o que você precisa fazer é encontrar o tempo total decorrido entre dois valores de tempo (por exemplo, no caso de um horário com In-time e Out-time).
O método escolhido depende de como a hora é mencionada na célula e de como você deseja que o resultado seja formatado.
Vejamos alguns exemplos
Subtração simples para calcular a diferença de tempo no Excel
Como as horas são armazenadas como números no Excel, para encontrar a diferença entre 2 valores de hora, você pode subtrair facilmente a hora de início da hora de término.
hora final - hora inicial
O resultado da subtração também será um valor decimal que representa o tempo decorrido entre os dois valores de tempo.
Abaixo está um exemplo em que tenho hora de início e hora de término e calculei a diferença de tempo por subtração simples.
É possível que seus resultados sejam exibidos no formato de hora (em vez de valores decimais ou de hora/minuto).No exemplo acima, o resultado na célula C2 mostra 09:30 AM em vez de 9.5.
Isso é bom porque o Excel tentará copiar a formatação das colunas adjacentes.
Para convertê-lo em decimal, altere o formato da célula para "Geral" (esta opção está na guia "Início" do grupo "Números")
Depois de obter o resultado, você pode formatá-lo de diferentes maneiras.Por exemplo, você pode exibir valores apenas em horas, apenas minutos ou uma combinação de horas, minutos e segundos.
Aqui estão os diferentes formatos que você pode usar:
| formato | o que pode fazer |
| hh | Mostrar apenas o número de horas decorridas entre duas datas |
| mm | Exibir horas como dois dígitos (por exemplo, 04 ou 12) |
| h: mm | Exibe horas e minutos decorridos entre duas datas, por exemplo, 10:20 |
| hh: mm: ss | Exibe horas, minutos e segundos decorridos entre duas datas, por exemplo, 10:20:36 |
Se você está se perguntando onde e como aplicar esses formatos de data personalizados, siga estas etapas:
- Selecione as células às quais aplicar o formato de data
- Segure Control e pressione 1 (Command + 1 se estiver usando um Mac)
- Na caixa de diálogo Formatar células que é aberta, clique na guia Números (se ainda não estiver selecionada)
- No painel esquerdo, clique em Personalizar
- Digite qualquer código de formato desejado no campo Tipo (estou usando hh:mm:ss neste exemplo)
- Clique OK
As etapas acima alterarão o formato e mostrarão o valor de acordo com o formato.
Observe que a formatação de número personalizado não altera o valor na célula.Apenas altera a forma como o valor é exibido.Assim, posso optar por exibir apenas o valor da hora na célula e ela ainda tem o valor original.
DICA PRO: Se o total de horas exceder 24 horas, use o seguinte formato de número personalizado: [hh]:mm:ss
Calcular a diferença horária em horas, minutos ou segundos
Quando você subtrai valores de tempo, o Excel retorna um número decimal que representa a diferença de tempo resultante.
Como cada número inteiro representa um dia, a parte fracionária do número representará a maior parte do dia e pode ser facilmente convertida em horas, minutos ou segundos.
Calcular diferença horária em horas
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e queira calcular o número de horas entre dois valores de tempo
A fórmula abaixo lhe dará a diferença de tempo em horas:
= (B2-A2) * 24
A fórmula acima lhe dará o número total de horas decorridas entre dois valores de tempo.
Às vezes, o Excel tentará ajudar e também fornecerá os resultados em um formato de hora (mostrado abaixo).
Você pode convertê-lo facilmente para o formato numérico clicando na guia Início e selecionando Número como formato no grupo Número.
Se você quiser apenas retornar o número total de horas decorridas entre duas vezes (sem nenhuma parte fracionária), use esta fórmula:
=INT((B2-A2)*24)
Nota: Esta fórmula só funciona se os dois valores de hora forem no mesmo dia.Esta fórmula dará resultados errados se as datas mudarem (um dos valores de hora é outra data e o segundo é outra data).Quando a data mudar posteriormente neste tutorial, confira a seção onde apresento a fórmula para calcular a diferença de horário.
Calcular diferença de tempo em minutos
Para calcular a diferença horária em minutos, você precisa multiplicar o valor resultante pelo número total de minutos em um dia (ou seja, 1440 ou 24*60).
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e queira calcular o número total de minutos decorridos entre a data de início e a data de término.
Aqui está a fórmula que pode fazer isso:
=(B2-A2)*24*60
Calcular diferença de tempo em segundos
Para calcular a diferença de tempo em segundos, você precisa multiplicar o valor resultante pelo número total de segundos em um dia (ou seja, 24*60*60 ou 86400).
Suponha que você tenha um conjunto de dados como abaixo e queira contar o número total de segundos decorridos entre a data de início e a data de término.
Aqui está a fórmula que pode fazer isso:
=(B2-A2)*24*60*60
Calcule a diferença de tempo usando a função TEXT
Outra maneira fácil de obter rapidamente a diferença de horário sem se preocupar em alterar o formato é usar a função TEXT.
A função TEXT permite especificar a formatação nas fórmulas.
=TEXT(data_final-data_inicial, formato)
O primeiro parâmetro é o cálculo que você deseja realizar e o segundo parâmetro é o formato no qual você deseja exibir os resultados do cálculo.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e queira calcular a diferença de tempo entre duas vezes.
Aqui estão algumas fórmulas que lhe darão os resultados em diferentes formatos
Mostrar apenas horários:
=TEXT(B2-A2,"hh")
A fórmula acima só lhe dará o resultado do número de horas decorridas entre dois valores de tempo.Se o seu resultado for 9 horas e 30 minutos, ele ainda mostrará apenas 9.
Mostrar o total de minutos
=TEXT(B2-A2,"[mm]")
Mostrar o total de segundos
=TEXT(B2-A2,"[ss]")
Exibir horas e minutos
=TEXT(B2-A2,"[hh]:mm")
Exibir horas, minutos e segundos
=TEXT(B2-A2,"hh:mm:ss")
Se você está se perguntando qual é a diferença entre hh e [hh] no formato (ou mm e [mm]), quando você usa colchetes, ele fornece o número total de horas entre as duas datas, mesmo que a hora Values superior a 24.Portanto, se você estiver subtraindo dois valores de data que diferem em mais de 24 horas, usar [hh] fornecerá o número total de horas, enquanto hh fornecerá apenas o número de horas decorridas na data final.
Obtenha a diferença horária em uma unidade (horas/minutos), ignorando outras
Se você deseja apenas calcular a diferença de tempo entre dois valores de tempo em horas ou minutos ou segundos, você pode usar as funções dedicadas HORA, MINUTO ou SEGUNDO.
Cada uma dessas funções recebe um parâmetro, o valor de tempo, e retorna a unidade de tempo especificada.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e queira calcular o número total de horas e segundos decorridos entre esses dois tempos.
Aqui está a fórmula para fazer isso:
Calcular o número de horas decorridas entre duas vezes
=HORA(B2-A2)
Calcular minutos com base no resultado do valor do tempo (excluindo horas concluídas)
=MINUTO(B2-A2)
Calcular segundos a partir do resultado do valor do tempo (excluindo horas e minutos concluídos)
=SEGUNDO(B2-A2)
Algumas coisas para saber ao usar essas fórmulas de HORAS, MINUTOS e SEGUNDOS:
- A diferença entre a hora de término e a hora de início não pode ser negativa (geralmente é o caso quando a data muda).Nesse caso, essas fórmulas retornarão #NUM!Erro
- Essas fórmulas usam apenas a parte de tempo (e ignoram a parte de dias) do valor de tempo resultante.Portanto, se a diferença entre a hora de término e a hora de início for 2 dias, 10 horas, 32 minutos e 44 segundos, a fórmula HORA dará 10, a fórmula MINUTE dará 32 e a fórmula SEGUNDO dará 44
Calcular o tempo decorrido agora (a partir da hora de início)
Se quiser calcular o tempo total decorrido entre a hora de início e a hora atual, você pode usar a fórmula AGORA em vez da hora de término.
A função NOW retorna a data e hora atuais na célula em que é usada.É uma das funções que não aceita nenhum argumento de entrada.
Portanto, se você deseja calcular o tempo total decorrido entre a hora de início e a hora atual, pode usar a seguinte fórmula:
=AGORA() - hora de início
Abaixo está um exemplo onde tenho a hora de início na coluna A e a hora até agora na coluna B.
Se a diferença de horário entre a data e a hora de início e a hora atual for superior a 24 horas, você poderá formatar o resultado para exibir a parte de data e hora.
Você pode fazer isso com a seguinte fórmula TEXT:
=TEXT(AGORA()-A2,"dd hh:ss:mm")
Você também pode conseguir a mesma coisa alterando o formato personalizado da célula (conforme descrito anteriormente neste tutorial) para que ela exiba a parte de data e hora.
Se a hora de início tiver apenas a parte do tempo, o Excel a tratará como 1990º de janeiro de 1.
Nesse caso, se você usar a função NOW para calcular o tempo decorrido até agora, ela fornecerá o resultado errado (porque o valor resultante também conterá o número total de dias decorridos desde 1990º de janeiro de 1).
Neste caso, você pode usar a seguinte fórmula:
=AGORA()-INT(AGORA())-A2
A fórmula acima usa a função INT para remover a parte dos dias do valor retornado pela função now, que é então usada para calcular a diferença de horário.
Observe que NOW é uma função volátil que atualiza quando a planilha é alterada, mas não em tempo real
Calcular o tempo quando a data muda (calcular e exibir o tempo negativo no Excel)
Os métodos descritos até agora funcionam bem se a hora de término for posterior à hora de início.
No entanto, o problema ocorre quando a hora de término é menor que a hora de início.Isso geralmente acontece quando você preenche uma planilha de horas e insere apenas a hora e não a data e hora inteiras.
Nesse caso, se você trabalha no turno da noite e a data muda, o horário de término pode ser anterior ao horário de início.
Por exemplo, se você começar a trabalhar às 6h e terminar o trabalho e pausar às 00h.
Se você estivesse usando apenas valores de hora, subtrair a hora de início da hora de término resultaria em um valor negativo de 9 horas (9 – 18).
O Excel não pode lidar com valores de tempo negativos (e, nesse caso, humanos, a menos que você possa viajar no tempo)
Nesse caso, você precisa de uma maneira de determinar que a data foi alterada e deve calcular de acordo.
Felizmente, existe uma solução muito simples.
Suponha que você tenha um conjunto de dados como abaixo, onde tenho hora de início e hora de término.
Como você notará, às vezes o horário de início é à noite e o horário de término é de manhã (indicando que era um turno noturno e o dia havia mudado).
Se eu usar a fórmula abaixo para calcular a diferença horária, ela exibe um sinal de libra (destacado em amarelo na imagem abaixo) nas células onde o resultado é um valor negativo.
= B2-A2
Esta é uma fórmula SE, se o valor da diferença de tempo for negativo, se for negativo retorna o resultado correto
=IF((B2-A2)<0,1-(A2-B2),(B2-A2))
Embora isso funcione bem na maioria dos casos, ainda não é suficiente se os horários de início e término tiverem mais de 24 horas de intervalo.Por exemplo, alguém faz login às 1h no dia 9 e sai às 00h no dia 2.
Como são mais de 24 horas, não há como saber se a pessoa desiste após 2 horas ou 26 horas.
Embora a melhor maneira de corrigir isso seja garantir que a entrada inclua a data e a hora, se for apenas a hora com a qual você está trabalhando, a fórmula acima deve resolver a maior parte do problema (considerando que é improvável que alguém esteja trabalhando mais do que 24 horas)
Adicione e subtraia tempo no Excel
Até agora vimos exemplos de horários de início e término, precisamos encontrar a diferença de horário.
O Excel também permite adicionar ou subtrair facilmente valores de tempo fixos de valores de data e hora existentes.
Por exemplo, suponha que você tenha uma lista de tarefas enfileiradas, em que cada tarefa leva um tempo especificado e deseja saber quando cada tarefa termina.
Nesse caso, você pode adicionar facilmente o tempo que cada tarefa levará à hora de início para ver quando a tarefa deve ser concluída.
Como o Excel armazena valores de data e hora como números, você deve certificar-se de que a hora que está tentando adicionar está em um formato que o Excel já segue.
Por exemplo, se você adicionar 1 a uma data no Excel, ele fornecerá a próxima data.Isso ocorre porque 1 representa um dia inteiro (equivalente a 24 horas) no Excel.
Portanto, se você deseja adicionar 1 hora a um valor de tempo existente, não pode ir em frente e simplesmente adicionar 1 a ele.Você deve certificar-se de converter o valor da hora em uma parte fracionária que representa uma hora.O mesmo vale para adicionar minutos e segundos.
Use a função de tempo
As funções de hora no Excel pegam valores de hora, minuto e segundo e os convertem em um número decimal que representa a hora.
Por exemplo, se eu quisesse adicionar 4 horas ao horário existente, eu poderia usar a seguinte fórmula:
= hora de início + HORA(4,0,0)
Isso é útil se você souber as horas, minutos e segundos para adicionar a uma hora existente e puder usar a função TIME sem se preocupar em converter a hora em um valor decimal corretamente.
Além disso, observe que a função TIME considera apenas a parte inteira dos valores de horas, minutos e segundos inseridos.Por exemplo, se eu usar 5.5 horas na função TIME, ela apenas adiciona 5 horas e ignora a parte fracionária.
Observe também que a função TIME só pode adicionar valores com menos de 24 horas.Isso lhe dará um resultado incorreto se o valor da hora exceder 24.
O mesmo vale para as partes de minutos e segundos, a função só irá considerar valores inferiores a 60 minutos e 60 segundos
Assim como usei a função TIME para adicionar tempo, você também pode subtrair o tempo.Basta alterar o sinal + na fórmula acima para um sinal de menos
Usar aritmética básica
Embora a função de tempo seja fácil de usar, ela tem algumas limitações (como mencionado acima).
Se você quiser mais controle, pode usar o método aritmético que abordarei aqui.
O conceito é simples - converta um valor de hora em um valor decimal representando a parte do dia, que você pode adicionar a qualquer valor de hora no Excel.
Por exemplo, se você quiser adicionar 24 horas a um valor de tempo existente, poderá usar a seguinte fórmula:
= hora de início + 24/24
Significa apenas que estou adicionando um dia ao valor de tempo existente.
Agora tomando o mesmo conceito, digamos que você queira adicionar 30 horas ao valor do tempo, você pode usar a seguinte fórmula:
= hora de início + 30/24
A fórmula acima faz a mesma coisa, onde a parte inteira de (30/24) representa o número total de dias do tempo que você deseja adicionar e a parte fracionária representa horas/minutos/segundos
Da mesma forma, se você quiser adicionar um número específico de minutos ao valor do tempo, poderá usar a seguinte fórmula:
=Hora de início +(Adicionar minutos)/24*60
Se você tiver o número de segundos para adicionar, poderá usar a seguinte fórmula:
= hora de início + (minutos adicionais)/24*60*60
Embora esse método não seja tão simples quanto usar a função time, acho muito melhor porque funciona em todos os casos e segue os mesmos conceitos.Ao contrário da função de tempo, você não precisa se preocupar em adicionar tempo inferior ou superior a 24 horas
Você também pode seguir o mesmo conceito ao subtrair o tempo.Apenas mude + para sinal negativo na fórmula acima
Como somar o tempo no Excel
Às vezes você pode querer adicionar rapidamente todos os valores de tempo no Excel.Adicionar vários valores de tempo no Excel é fácil (apenas uma fórmula SOMA simples)
No entanto, ao adicionar tempo no Excel, você precisa saber algumas coisas, principalmente o formato da célula que mostrará os resultados.
Vejamos um exemplo.
Abaixo, tenho uma lista de tarefas na coluna B e o tempo que cada tarefa levará, e quero adicionar rapidamente esses tempos e saber o tempo total que todas essas tarefas levarão.
Na célula B9, usei uma fórmula SOMA simples para calcular o tempo total que todas essas tarefas levarão e isso me dá um valor de 18:30 (o que significa que leva 18 horas e 20 minutos para concluir todas essas tarefas)
Até agora tudo bem!
Como somar mais de 24 horas no Excel
Agora veja o que acontece quando altero o tempo de conclusão da tarefa 2 de 1 hora para 10 horas.
O resultado agora mostra 03:20, o que significa que levará 3 horas e 20 minutos para concluir todas essas tarefas.
Isso está incorreto (obviamente)
O problema aqui não é que o Excel está estragado.O problema aqui é que as células são formatadas para exibir apenas a parte de tempo do valor resultante.
E como o valor resultante aqui excede 24 horas, o Excel decide converter a parte de 24 horas em um dia, removê-lo do valor exibido ao usuário e mostrar apenas as horas, minutos e segundos restantes.
Felizmente, isso é fácil de corrigir.
Tudo o que você precisa fazer é alterar o formato da célula para forçá-la a exibir as horas, mesmo que sejam mais de 24 horas.
Aqui estão alguns formatos que você pode usar:
| formato | resultados esperados |
| [Hmm | 28:30 |
| [m]: ss | 1710:00 |
| d "D" hh:mm | Dia 04 30:XNUMX |
| d "D" hh "Min" ss "Seg" | 1 dia 04 minutos 00 segundos |
| d “Dia” hh “Minuto” ss “Segundos” | 1 dia 04 minutos 00 segundos |
Você pode alterar o formato acessando a caixa de diálogo Formatar Células e aplicando um formato personalizado, ou use a função TEXTO e use qualquer um dos formatos acima na própria fórmula
Você pode usar a seguinte fórmula de TEXTO para exibir a hora mesmo que exceda 24 horas:
=TEXT(SOMA(B2:B7),"[h]:mm:ss")
Ou se você quiser converter horas acima de 24 horas em dias, use a seguinte fórmula:
=TEXT(SOMA(B2:B7),"dd hh:mm:ss")
Os resultados mostram hash (###) em vez de data/hora (causa + correção)
Em alguns casos, você pode descobrir que, em vez de exibir valores de tempo, o Excel exibe sinais de sustenido nas células.
Aqui estão algumas causas possíveis e como corrigi-las:
As colunas não são largas o suficiente
Quando uma célula não tem espaço suficiente para exibir a data completa, ela pode exibir um sinal de sustenido.
Tem uma solução fácil - altere a largura da coluna e torne-a mais larga.
valor de data negativo
Os valores de data ou hora no Excel não podem ser negativos.Se você estiver calculando a diferença horária e o resultado for negativo, o Excel exibirá o sinal de hash.
Fix método para alterar a fórmula para dar o resultado correto.Por exemplo, se você estiver calculando a diferença de tempo entre duas vezes e a data mudar, será necessário ajustar a fórmula para levar isso em consideração.
Em outros casos, você pode usar a função ABS para converter valores de tempo negativos em números positivos para exibição adequada.Como alternativa, você pode usar uma fórmula SE para verificar se o resultado é negativo e retornar um valor mais significativo.
Neste tutorial, abordei tópicos sobre cálculo de tempo no Excel (onde você pode calcular diferenças de tempo, adicionar e subtrair tempo, exibir tempo em diferentes formatos e somar valores de tempo)
Espero que você tenha achado este tutorial útil.
Perguntas frequentes sobre como calcular adição e subtração de tempo no Excel
No Excel, você pode usar a função de tempo para adicionar tempo.Tomando minutos como exemplo, se houver um horário '2:2' na célula B30 e, em seguida, inserir '=B2+tempo(2)' em C0,30,0, o resultado será '2:3' exibido em C00.
Para reduzir o tempo no Excel, você pode usar a função de tempo.Por exemplo, se você tiver um horário '3:30' em B2 e, em seguida, inserir '=B2-time(2)' em C0,30,0, o resultado será '2:3' em C00.
Para calcular um intervalo de tempo no Excel, basta subtrair o horário de início do horário de término.Por exemplo, se houver um horário de início de '2:1' em B30 e um horário de término de '2:3' em C00, insira '=C2-B2' em D2.
O sistema padrão de data e hora do Excel é baseado no relógio de 24 horas. Se o resultado do cálculo da hora exceder 24 horas, o Excel irá convertê-lo para o formato de data.Para resolver este problema, você pode definir o formato das células incluídas no cálculo para "[h]:mm".
Certifique-se de que após adicionar ou subtrair tempos, o formato numérico do campo de resultado permaneça no formato de hora.Normalmente, você pode selecionar a célula, selecionar “Formato de número” na barra de ferramentas e, em seguida, selecionar “Hora” para configurá-la para o formato de hora.
A seguir está um exemplo de formulário:
| primeira vez | segunda vez | tempo de diferença |
|---|---|---|
| 2:30 | 3:00 | 0:30 |
| 10:00 | 8:00 | 2:00 |
