Tutorial do Excel: Cálculo de Data e Hora (calcular diferença, idade, adicionar/subtrair)
No Excel, o cálculo de data e hora é utilizado frequentemente, como calcular a diferença entre duas datas/horários, adicionar ou subtrair datas e horários, obter a idade com base na data de nascimento fornecida, entre outros. Neste tutorial, listamos quase todos os cenários de cálculo de data e hora e apresentamos os métodos relacionados para você.
Neste tutorial, criei alguns exemplos para explicar os métodos. Você pode alterar as referências conforme necessário ao usar o código VBA ou fórmulas abaixo.
1. Calcular diferença entre duas datas/horários
Calcular a diferença entre duas datas ou dois horários pode ser o problema mais comum de cálculo de data e hora que você encontra no trabalho diário com Excel. Siga os exemplos abaixo para aumentar sua eficiência ao lidar com essas situações.
1.11 Calcular a diferença entre duas datas em dias/meses/anos/semanas
A função DATEDIF do Excel pode ser usada para calcular rapidamente a diferença entre duas datas em dias, meses, anos e semanas.
Clique para mais detalhes sobre a função DATEDIF
Diferença em dias entre duas datas
Para obter a diferença em dias entre duas datas nas células A2 e B2, utilize a seguinte fórmula:
=DATEDIF(A2,B2,"d")
Pressione Enter para obter o resultado.
Diferença em meses entre duas datas
Para obter a diferença em meses entre duas datas nas células A5 e B5, utilize a seguinte fórmula:
=DATEDIF(A5,B5,"m")
Pressione Enter para obter o resultado.
Diferença em anos entre duas datas
Para obter a diferença em anos entre duas datas nas células A8 e B8, utilize a seguinte fórmula:
=DATEDIF(A8,B8,"y")
Pressione Enter para obter o resultado.
Diferença em semanas entre duas datas
Para obter a diferença em semanas entre duas datas nas células A11 e B11, utilize a seguinte fórmula:
=DATEDIF(A11,B11,"d")/7
Pressione Enter para obter o resultado.
Observação:
1) Ao usar a fórmula acima para obter a diferença em semanas, o resultado pode ser exibido no formato de data. Você pode formatar o resultado como geral ou número, conforme necessário.
2) Ao usar a fórmula acima para obter a diferença em semanas, pode retornar um número decimal. Se quiser obter o número inteiro de semanas, adicione a função ROUNDDOWN conforme mostrado abaixo para obter a diferença inteira de semanas:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Calcular meses ignorando anos e dias entre duas datas
Se você deseja apenas calcular a diferença em meses ignorando anos e dias entre duas datas, como mostra a imagem abaixo, existe uma fórmula que pode ajudar.
=DATEDIF(A2,B2,"ym")
Pressione Enter para obter o resultado.
A2 é a data inicial e B2 é a data final.
1.13 Calcular dias ignorando anos e meses entre duas datas
Se você deseja apenas calcular a diferença em dias ignorando anos e meses entre duas datas, como mostra a imagem abaixo, existe uma fórmula que pode ajudar.
=DATEDIF(A5,B5,"md")
Pressione Enter para obter o resultado.
A5 é a data inicial e B5 é a data final.
1.14 Calcular a diferença entre duas datas e retornar anos, meses e dias
Se você deseja obter a diferença entre duas datas e retornar xx anos, xx meses e xx dias, como mostra a imagem abaixo, também existe uma fórmula disponível.
=DATEDIF(A8, B8, "y") & " anos, " & DATEDIF(A8, B8, "ym") & " meses, " & DATEDIF(A8, B8, "md") & " dias"
Pressione Enter para obter o resultado.
A8 é a data inicial e B8 é a data final.
1.15 Calcular a diferença entre uma data e hoje
Para calcular automaticamente a diferença entre uma data e hoje, basta trocar o end_date nas fórmulas acima por TODAY(). Aqui, como exemplo, calcule a diferença em dias entre uma data passada e hoje.
=DATEDIF(A11,TODAY(),"d")
Pressione Enter para obter o resultado.
Observação: se você deseja calcular a diferença entre uma data futura e hoje, troque o start_date por hoje e use a data futura como end_date, assim:
=DATEDIF(TODAY(),A14,"d")
Observe que o start_date deve ser menor que o end_date na função DATEDIF, caso contrário, será retornado o valor de erro #NÚM!.
1.16 Calcular dias úteis com ou sem feriados entre duas datas
Às vezes, pode ser necessário contar o número de dias úteis com ou sem feriados entre duas datas fornecidas.
Nesta parte, você usará a função NETWORKDAYS.INTL:
Clique em NETWORKDAYS.INTL para saber sobre seus argumentos e uso.
Contar dias úteis com feriados
Para contar os dias úteis com feriados entre duas datas nas células A2 e B2, utilize a seguinte fórmula:
=NETWORKDAYS.INTL(A2,B2)
Pressione Enter para obter o resultado.
Contar dias úteis sem feriados
Para contar os dias úteis com feriados entre duas datas nas células A2 e B2, excluindo os feriados no intervalo D5:D9, utilize a seguinte fórmula:
=NETWORKDAYS.INTL(A5,B5,1,D5:D9)
Pressione Enter para obter o resultado.
Observação:
Nas fórmulas acima, sábado e domingo são considerados fim de semana. Se você tiver outros dias de fim de semana, altere o argumento [weekend] conforme necessário.
1.17 Calcular fins de semana entre duas datas
Se você deseja contar o número de fins de semana entre duas datas, as funções SOMARPRODUTO ou SOMA podem ajudar.
Para contar os fins de semana (sábado e domingo) entre duas datas nas células A12 e B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Ou
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Pressione Enter para obter o resultado.
1.18 Calcular dia da semana específico entre duas datas
Para contar o número de um dia da semana específico, como segunda-feira, entre duas datas, a combinação das funções INT e WEEKDAY pode ajudar.
As células A15 e B15 são as datas entre as quais você deseja contar as segundas-feiras. Utilize a seguinte fórmula:
=INT((WEEKDAY(A15-2)-A15 +B15)/7)
Pressione Enter para obter o resultado.
Altere o número do dia da semana na função WEEKDAY para contar outro dia da semana:
1 é domingo,2 é segunda-feira,3 é terça-feira,4 é quarta-feira,5 é quinta-feira,6 é sexta-feira e7 é sábado.
1.19 Calcular dias restantes no mês/ano
Às vezes, você pode querer saber os dias restantes no mês ou ano com base na data fornecida, como mostra a imagem abaixo:
Obter dias restantes no mês atual
Clique em EOMONTH para saber sobre o argumento e uso.
Para obter os dias restantes do mês atual na célula A2, utilize a seguinte fórmula:
=EOMONTH(A2,0)-A2
Pressione Enter e arraste a alça de preenchimento automático para aplicar esta fórmula a outras células, se necessário.
Dica: os resultados podem ser exibidos no formato de data. Basta alterá-los para formato geral ou número.
Obter dias restantes no ano atual
Para obter os dias restantes do ano atual na célula A2, utilize a seguinte fórmula:
=DATE(YEAR(A2),12,31)-A2
Pressione Enter e arraste a alça de preenchimento automático para aplicar esta fórmula a outras células, se necessário.
1.21 Calcular a diferença entre dois horários
Para obter a diferença entre dois horários, aqui estão duas fórmulas simples que podem ajudar.
Supondo que as células A2 e B2 contenham start_time e end_time, respectivamente, utilize as seguintes fórmulas:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Pressione Enter para obter o resultado.
Observação:
- Se você usar end_time-start_time, pode formatar o resultado para outro formato de hora conforme desejar no diálogo Formatar Células.
- Se você usar TEXT(end_time-first_time,"time_format"), insira o formato de hora desejado no resultado da fórmula, como TEXT(end_time-first_time,"h") retorna16.
- Se o end_time for menor que o start_time, ambas as fórmulas retornarão valores de erro. Para resolver esse problema, adicione ABS antes das fórmulas, como ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")), e depois formate o resultado como hora.
1.22 Calcular a diferença entre dois horários em horas/minutos/segundos
Se você deseja calcular a diferença entre dois horários em horas, minutos ou segundos, como mostra a imagem abaixo, siga esta parte.
Obter diferença em horas entre dois horários
Para obter a diferença em horas entre dois horários em A5 e B5, utilize a seguinte fórmula:
=INT((B5-A5)*24)
Pressione Enter e depois formate o resultado como geral ou número.
Se desejar obter a diferença em horas decimais, use (end_time-start_time)*24.
Obter diferença em minutos entre dois horários
Para obter a diferença em minutos entre dois horários em A8 e B8, utilize a seguinte fórmula:
=INT((B8-A8)*1440)
Pressione Enter e depois formate o resultado como geral ou número.
Se desejar obter a diferença em minutos decimais, use (end_time-start_time)*1440.
Obter diferença em segundos entre dois horários
Para obter a diferença em segundos entre dois horários em A5 e B5, utilize a seguinte fórmula:
=(B11-A11)*86400)
Pressione Enter e depois formate o resultado como geral ou número.
1.23 Calcular apenas a diferença em horas entre dois horários (não excede24 horas)
Se a diferença entre dois horários não exceder24 horas, a função HOUR pode obter rapidamente a diferença em horas entre eles.
Clique em HOUR para mais detalhes sobre esta função.
Para obter a diferença em horas entre os horários nas células A14 e B14, utilize a função HOUR assim:
=HOUR(B14-A14)
Pressione Enter para obter o resultado.
O start_time deve ser menor que o end_time, caso contrário, a fórmula retorna o valor de erro #NÚM!.
1.24 Calcular apenas a diferença em minutos entre dois horários (não excede60 minutos)
A função MINUTE pode obter rapidamente apenas a diferença em minutos entre dois horários, ignorando horas e segundos.
Clique em MINUTE para mais detalhes sobre esta função.
Para obter apenas a diferença em minutos entre os horários nas células A17 e B17, utilize a função MINUTE assim:
=MINUTE(B17-A17)
Pressione Enter para obter o resultado.
O start_time deve ser menor que o end_time, caso contrário, a fórmula retorna o valor de erro #NÚM!.
1.25 Calcular apenas a diferença em segundos entre dois horários (não excede60 segundos)
A função SECOND pode obter rapidamente apenas a diferença em segundos entre dois horários, ignorando horas e minutos.
Clique em SECOND para mais detalhes sobre esta função.
Para obter apenas a diferença em segundos entre os horários nas células A20 e B20, utilize a função SECOND assim:
=SECOND(B20-A20)
Pressione Enter para obter o resultado.
O start_time deve ser menor que o end_time, caso contrário, a fórmula retorna o valor de erro #NÚM!.
1.26 Calcular a diferença entre dois horários e retornar horas, minutos, segundos
Se você deseja mostrar a diferença entre dois horários como xx horas xx minutos xx segundos, utilize a função TEXT conforme mostrado abaixo:
Clique em TEXT para entender os argumentos e o uso desta função.
Para calcular a diferença entre os horários nas células A23 e B23, utilize a fórmula:
=TEXT(B23-A23,"h"" horas ""m"" minutos ""s"" segundos"").
Pressione Enter para obter o resultado.
Observação:
Esta fórmula também calcula apenas a diferença em horas não excedendo24 horas, e o end_time deve ser maior que o start_time, caso contrário, retorna o valor de erro #VALOR!.
1.27 Calcular a diferença entre duas datas e horários
Se houver dois horários no formato mm/dd/aaaa hh:mm:ss, para calcular a diferença entre eles, você pode usar uma das fórmulas abaixo conforme necessário.
Obter diferença de tempo entre duas datas e horários e retornar o resultado no formato hh:mm:ss
Como exemplo, considere duas datas e horários nas células A2 e B2. Utilize a seguinte fórmula:
=B2-A2
Pressione Enter para obter um resultado no formato de data e hora. Em seguida, formate este resultado como [h]:mm:ss na categoria personalizada em Número aba em Formatar Células diálogo.
Obter diferença entre duas datas e horários e retornar dias, horas, minutos, segundos
Como exemplo, considere duas datas e horários nas células A5 e B5. Utilize a seguinte fórmula:
=INT(B5-A5) & " Dias, " & HOUR(B5-A5) & " Horas, " & MINUTE(B5-A5) & " Minutos, " & SECOND(B5-A5) & " Segundos "
Pressione Enter para obter o resultado.
Observação: em ambas as fórmulas, end_datetime deve ser maior que start_datetime, caso contrário, as fórmulas retornam valores de erro.
1.28 Calcular diferença de tempo com milissegundos
Primeiro, você precisa saber como formatar a célula para exibir os milissegundos:
Selecione as células que deseja exibir milissegundos e clique com o botão direito para selecionar Formatar Células para habilitar o Formatar Células diálogo, selecione Personalizado na Categoria lista sob a guia Número e digite hh:mm:ss.000 na caixa de texto.
Use a fórmula:
Aqui, para calcular a diferença entre dois horários nas células A8 e B8, utilize a fórmula:
=ABS(B8-A8)
Pressione Enter para obter o resultado.
1.29 Calcular horas de trabalho entre duas datas excluindo fins de semana
Às vezes, pode ser necessário contar as horas de trabalho entre duas datas, excluindo fins de semana (sábado e domingo).
Aqui, as horas de trabalho são fixas em8 horas por dia. Para calcular as horas de trabalho entre duas datas nas células A16 e B16, utilize a fórmula:
=NETWORKDAYS(A16,B16) *8
Pressione Enter e depois formate o resultado como geral ou número.
Para mais exemplos sobre como calcular horas de trabalho entre duas datas, visite Obter Horas de Trabalho Entre Duas Datas no Excel
Se você tem o Kutools para Excel instalado no Excel,90% dos cálculos de diferença de data e hora podem ser resolvidos rapidamente sem precisar memorizar fórmulas.
1.31 Calcular diferença entre duas datas e horários pelo Assistente de Data e Hora
Para calcular a diferença entre duas datas e horários no Excel, apenas o Assistente de Data e Hora já é suficiente.
1. Selecione uma célula onde deseja colocar o resultado calculado e clique em Kutools > Assistente de Fórmulas > Assistente de Data e Hora.
2. Na janela Assistente de Data e Hora que aparece, siga as configurações abaixo:
- Marque a opção Diferença;
- Selecione a data e hora inicial e final na seção Entrada de Argumento. Você também pode digitar manualmente ou clicar no ícone de calendário para selecionar a data;
- Selecione o tipo de resultado de saída na lista suspensa;
- Visualize o resultado na seção Resultado.
3. Clique em Ok. O resultado calculado será exibido e arraste a alça de preenchimento automático sobre as células que também deseja calcular.
Dica:
Se você deseja obter a diferença entre duas datas e horários e mostrar o resultado como dias, horas e minutos com Kutools para Excel, faça o seguinte:
Selecione uma célula onde deseja colocar o resultado e clique em Kutools > Assistente de Fórmulas > Data e Hora > Contar dias, horas e minutos entre duas datas.
Em seguida, na Assistente de Fórmulas janela, especifique a data inicial e final, depois clique em Ok.
E a diferença será exibida como dias, horas e minutos.
Clique em Assistente de Data e Hora para saber mais sobre o uso deste recurso.
Clique em Kutools para Excel para conhecer todos os recursos deste suplemento.
Clique em Download Grátis para obter 30 dias de teste gratuito do Kutools para Excel
Se você deseja contar rapidamente o fim de semana, dias úteis ou um dia da semana específico entre duas datas e horários, o Assistente de Fórmulas do Kutools para Excel pode ajudar.
1. Selecione a célula onde será exibido o resultado calculado, clique em Kutools > Contagem > Número de dias não úteis entre duas datas/Número de dias úteis entre duas datas/Número de dias da semana entre duas datas.
2. Na janela Assistente de Fórmulas que aparece, especifique a data inicial e final. Se aplicar Número de dias da semana entre duas datas, também será necessário especificar o dia da semana.
Para contar o dia da semana específico, consulte a observação para usar1-7 para indicar domingo a sábado.
3. Clique em Ok e depois arraste a alça de preenchimento automático sobre as células que precisam contar o número de fim de semana/dia útil/dia da semana específico, se necessário.
Clique em Kutools para Excel para conhecer todos os recursos deste suplemento.
Clique em Download Grátis para obter 30 dias de teste gratuito do Kutools para Excel
2. Adicionar ou subtrair data e hora
Além de calcular a diferença entre duas datas e horários, adicionar ou subtrair também é um cálculo comum de data e hora no Excel. Por exemplo, você pode querer obter a data de vencimento com base na data de produção e no número de dias de preservação de um produto.
2.11 Adicionar ou subtrair dias a uma data
Para adicionar ou subtrair um número específico de dias a uma data, há dois métodos diferentes.
Supondo que você queira adicionar21 dias a uma data na célula A2, escolha um dos métodos abaixo para resolver:
Método1: data+dias
Selecione uma célula e digite a fórmula:
=A+21
Pressione Enter para obter o resultado.
Se quiser subtrair21 dias, basta trocar o sinal de mais (+) pelo de menos (-).
Método2: Colar Especial
1. Digite o número de dias que deseja adicionar em uma célula, por exemplo, na célula C2, e pressione Ctrl + C para copiar.
2. Em seguida, selecione as datas às quais deseja adicionar21 dias, clique com o botão direito para exibir o menu de contexto e selecione Colar Especial....
3. Na Colar Especial janela, marque Adicionar opção (Se quiser subtrair dias, marque Subtrair opção). Clique em OK.
4. Agora as datas originais mudam para números de5 dígitos, formate-os como datas.
2.12 Adicionar ou subtrair meses a uma data
Para adicionar ou subtrair meses a uma data, pode-se usar a função EDATE.
Clique em EDATE para estudar seus argumentos e uso.
Supondo que você queira adicionar6 meses à data na célula A2, utilize a fórmula:
=EDATE(A2,6)
Pressione Enter para obter o resultado.
Se quiser subtrair6 meses da data, troque6 por -6.
2.13 Adicionar ou subtrair anos a uma data
Para adicionar ou subtrair n anos a uma data, pode-se usar uma fórmula que combina as funções DATE, YEAR, MONTH e DAY.
Supondo que você queira adicionar3 anos à data na célula A2, utilize a fórmula:
=DATE(YEAR(A2) +3, MONTH(A2),DAY(A2))
Pressione Enter para obter o resultado.
Se quiser subtrair3 anos da data, troque3 por -3.
2.14 Adicionar ou subtrair semanas a uma data
Para adicionar ou subtrair semanas a uma data, a fórmula geral é
Supondo que você queira adicionar4 semanas à data na célula A2, utilize a fórmula:
=A2+4*7
Pressione Enter para obter o resultado.
Se quiser subtrair4 semanas da data, troque o sinal de mais (+) pelo de menos (-).
2.15 Adicionar ou subtrair dias úteis incluindo ou excluindo feriados
Nesta seção, é apresentado como usar a função WORKDAY para adicionar ou subtrair dias úteis a uma data fornecida, excluindo ou incluindo feriados.
Visite WORKDAY para saber mais detalhes sobre seus argumentos e uso.
Adicionar dias úteis incluindo feriados
Na célula A2 está a data que você usará, na célula B2 está o número de dias que deseja adicionar. Utilize a fórmula:
=WORKDAY(A2,B2)
Pressione Enter para obter o resultado.
Adicionar dias úteis excluindo feriados
Na célula A5 está a data que você usará, na célula B5 está o número de dias que deseja adicionar, e no intervalo D5:D8 estão os feriados. Utilize a fórmula:
=WORKDAY(A5,B5,D5:D8)
Pressione Enter para obter o resultado.
Observação:
A função WORKDAY considera sábado e domingo como fins de semana. Se seus fins de semana forem diferentes, utilize a função WORKDAY.INTL, que permite especificar os fins de semana.
Visite WORKDAY.INTL para mais detalhes.
Se quiser subtrair dias úteis de uma data, basta usar um número negativo de dias na fórmula.
2.16 Adicionar ou subtrair ano, mês, dias específicos a uma data
Se você deseja adicionar um ano, mês e dias específicos a uma data, a fórmula que combina as funções DATE, YEAR, MONTH e DAYS pode ajudar.
Para adicionar1 ano,2 meses e30 dias a uma data em A11, utilize a fórmula:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Pressione Enter para obter o resultado.
Se quiser subtrair, troque todos os sinais de mais (+) por menos (-).
2.21 Adicionar ou subtrair horas/minutos/segundos a uma data e horário
Aqui estão algumas fórmulas para adicionar ou subtrair horas, minutos ou segundos a uma data e horário.
Adicionar ou subtrair horas a uma data e horário
Supondo que você queira adicionar3 horas a uma data e horário (ou a um horário) na célula A2, utilize a fórmula:
=A2+3/24
Pressione Enter para obter o resultado.
Adicionar ou subtrair minutos a uma data e horário
Supondo que você queira adicionar15 minutos a uma data e horário (ou a um horário) na célula A5, utilize a fórmula:
=A2+15/1440
Pressione Enter para obter o resultado.
Adicionar ou subtrair segundos a uma data e horário
Supondo que você queira adicionar20 segundos a uma data e horário (ou a um horário) na célula A8, utilize a fórmula:
=A2+20/86400
Pressione Enter para obter o resultado.
2.22 Somar horários acima de24 horas
Supondo que haja uma tabela no Excel registrando o tempo de trabalho de todos os funcionários em uma semana, para somar o tempo total de trabalho e calcular os pagamentos, você pode usar SOMA(intervalo) para obter o resultado. Mas, em geral, o resultado somado será exibido como um horário que não excede24 horas, como mostra a imagem abaixo. Como obter o resultado correto?
Na verdade, basta formatar o resultado como [hh]:mm:ss.
Clique com o botão direito na célula do resultado, escolha Formatar Células no menu de contexto e, na janela Formatar Células escolha Personalizado na lista e digite [hh]:mm:ss na caixa de texto à direita, clique em OK.
O resultado somado será exibido corretamente.
2.23 Adicionar horas de trabalho a uma data excluindo fim de semana e feriado
Aqui está uma fórmula longa para obter a data final ao adicionar um número específico de horas de trabalho a uma data inicial, excluindo fins de semana (sábado e domingo) e feriados.
Em uma tabela do Excel, A11 contém a data e hora inicial, B11 contém as horas de trabalho, E11 e E13 são os horários de início e fim do expediente, e E15 contém o feriado a ser excluído.
Utilize a fórmula:
=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))
Pressione Enter para obter o resultado.
Se você tem o Kutools para Excel instalado, apenas uma ferramenta – Assistente de Data e Horapode resolver a maioria dos cálculos de adição e subtração de data e hora.
1. Clique em uma célula onde deseja exibir o resultado e aplique esta ferramenta clicando em Kutools > Assistente de Fórmulas > Assistente de Data e Hora.
2. Na janela Assistente de Data e Hora, marque a opção Adicionar ou Subtrair conforme necessário, depois escolha a célula ou digite diretamente a data e hora desejada na seção Entrada de Argumento, depois especifique os anos, meses, semanas, dias, horas, minutos e segundos que deseja adicionar ou subtrair, depois clique em Ok. Veja a imagem:
Você pode visualizar o resultado calculado na Resultado seção.
Agora o resultado é exibido, arraste a alça de preenchimento automático para outras células para obter os resultados.
Clique em Assistente de Data e Hora para saber mais sobre o uso deste recurso.
Clique em Kutools para Excel para conhecer todos os recursos deste suplemento.
Clique em Download Grátis para obter 30 dias de teste gratuito do Kutools para Excel
2.41 Verificar ou destacar se uma data está expirada
Se houver uma lista de datas de validade de produtos, você pode querer verificar e destacar as datas que já expiraram com base em hoje, como mostra a imagem abaixo.
Na verdade, a Formatação Condicional pode resolver rapidamente essa tarefa.
1. Selecione as datas que deseja verificar e clique em Página Inicial > Formatação Condicional > Nova Regra.
2. Na Nova Regra de Formatação janela, selecione Usar uma fórmula para determinar quais células formatar na Selecione um Tipo de Regra seção e digite =B2
2.42 Retornar o fim do mês atual/primeiro dia do próximo mês
As datas de validade de alguns produtos são no final do mês de produção ou no primeiro dia do mês seguinte. Para listar rapidamente as datas de validade com base na data de produção, siga esta parte.
Obter o fim do mês atual
Aqui está uma data de produção na célula B13. Utilize a fórmula:
=EOMONTH(B13,0)
Pressione Enter para obter o resultado.
Obter o1º dia do próximo mês
Aqui está uma data de produção na célula B18. Utilize a fórmula:
=EOMONTH(B18,0)+1
Pressione Enter para obter o resultado.
3. Calcular idade
Nesta seção, listamos métodos para resolver como calcular a idade com base em uma data fornecida ou um número de série.
3.11 Calcular idade com base na data de nascimento fornecida
Obter idade em número decimal com base na data de nascimento
Clique em YEARFRAC para detalhes sobre seus argumentos e uso.
Por exemplo, para obter as idades com base na lista de datas de nascimento na coluna B2:B9, utilize a fórmula:
=YEARFRAC(B2,TODAY())
Pressione Enter e arraste a alça de preenchimento automático para baixo até que todas as idades sejam calculadas.
Dica:
1) Você pode especificar as casas decimais conforme necessário na janela Formatar Células janela.
2) Se quiser calcular a idade em uma data específica com base na data de nascimento, troque TODAY() pela data específica entre aspas duplas, como =YEARFRAC(B2,"1/1/2021")
3) Se quiser obter a idade do próximo ano com base na data de nascimento, basta adicionar1 na fórmula, como =YEARFRAC(B2,TODAY())+1.
Obter idade em número inteiro com base na data de nascimento
Clique em DATEDIF para detalhes sobre seus argumentos e uso.
Usando o exemplo acima, para obter a idade com base nas datas de nascimento na lista B2:B9, utilize a fórmula:
=DATEDIF(B2,TODAY(),"y")
Pressione Enter e arraste a alça de preenchimento automático para baixo até que todas as idades sejam calculadas.
Dica:
1) Se quiser calcular a idade em uma data específica com base na data de nascimento, troque TODAY() pela data específica entre aspas duplas, como =DATEDIF(B2,"1/1/2021","y").
2) Se quiser obter a idade do próximo ano com base na data de nascimento, basta adicionar1 na fórmula, como =DATEDIF(B2,TODAY(),"y")+1.
3.12 Calcular idade em formato de anos, meses e dias pela data de nascimento fornecida
Se você deseja calcular a idade com base em uma data de nascimento e mostrar o resultado como xx anos, xx meses, xx dias, como mostra a imagem abaixo, existe uma fórmula longa que pode ajudar.
Para obter a idade em anos, meses e dias com base na data de nascimento na célula B12, utilize a fórmula:
=DATEDIF(B12,TODAY(),"Y")&" Anos, "&DATEDIF(B12,TODAY(),"YM")&" Meses, "&DATEDIF(B12,TODAY(),"MD")&" Dias"
Pressione Enter para obter a idade e arraste a alça de preenchimento automático para baixo para outras células.
Dica:
Se quiser calcular a idade em uma data específica com base na data de nascimento, troque TODAY() pela data específica entre aspas duplas, como =DATEDIF(B12,"1/1/2021","Y")&" Anos, "&DATEDIF(B12,"1/1/2021","YM")&" Meses, "&DATEDIF(B12,"1/1/2021","MD")&" Dias".
3.13 Calcular idade por nascimento anterior a1/1/1900
No Excel, datas anteriores a1/1/1900 não podem ser inseridas como data e hora nem calculadas corretamente. Mas, se você quiser calcular a idade de uma pessoa famosa com base na data de nascimento (anterior a1/1/1900) e data de falecimento, apenas um código VBA pode ajudar.
1. Pressione Alt + F11 para abrir o Microsoft Visual Basic for Applications, clique na guia Inserir e escolha Módulo para criar um novo módulo.
2. Em seguida, copie e cole o código abaixo no novo módulo.
VBA: Calcular idade antes de1/1/1900
Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
Dim xSMonth As Integer
Dim xSDay As Integer
Dim xSYear As Integer
Dim xEMonth As Integer
Dim xEDay As Integer
Dim xEYear As Integer
Dim xAge As Integer
If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
xAge = xEYear - xSYear
If xSMonth > xEMonth Then
xAge = xAge - 1
ElseIf xSMonth = xEMonth Then
If xSDay > xEDay Then xAge = xAge - 1
End If
If xAge < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = xAge
End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
Dim I As Long
Dim K As Long
Y = 0
M = 0
D = 0
GetDate = True
On Error Resume Next
I = InStr(1, DateStr, "/")
M = CLng(Left(DateStr, I - 1))
D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
GetDate = False
End If
End Function
3. Salve o código, volte para a planilha, selecione uma célula para exibir a idade calculada, digite =AgeFunc(birthdate,deathdate), neste caso, =AgeFunc(B22,C22), pressione Enter para obter a idade. Use a alça de preenchimento automático para aplicar a fórmula a outras células, se necessário.
Se você tem Kutools para Excel instalado no Excel, pode usar o Assistente de Data e Hora para calcular a idade.
1. Selecione uma célula onde deseja exibir a idade calculada e clique em Kutools > Assistente de Fórmulas > Assistente de Data e Hora.
2. Na janela Assistente de Data e Hora,
- 1) Marque a opção Idade;
- 2) Escolha a célula da data de nascimento ou digite diretamente a data de nascimento ou clique no ícone de calendário para selecionar a data;
- 3) Escolha a opção Hoje se quiser calcular a idade atual, escolha a opção Data específica e digite a data se quiser calcular a idade no passado ou futuro;
- 4) Especifique o tipo de saída na lista suspensa;
- 5) Visualize o resultado de saída. Clique em Ok.
Clique em Assistente de Data e Hora para saber mais sobre o uso deste recurso.
Clique em Kutools para Excel para conhecer todos os recursos deste suplemento.
Clique em Download Grátis para obter 30 dias de teste gratuito do Kutools para Excel
3.31 Obter data de nascimento a partir do número de identificação
Se houver uma lista de números de identificação que usam os6 primeiros dígitos para registrar a data de nascimento, como920315330 significa que a data de nascimento é15/03/1992, como obter rapidamente a data de nascimento em outra coluna?
Vamos usar a lista de números de identificação iniciando na célula C2 como exemplo e utilizar a fórmula:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Pressione Enter tecla. Em seguida, arraste a alça de preenchimento automático para baixo para obter outros resultados.
Observação:
Na fórmula, você pode alterar a referência conforme necessário. Por exemplo, se o número de identificação for13219920420392, a data de nascimento é20/04/1992. Você pode alterar a fórmula para =MID(C2,8,2)&"/"&MID(C2,10,2)&"/"&MID(C2,4,4) para obter o resultado correto.
3.32 Calcular idade a partir do número de identificação
Se houver uma lista de números de identificação que usam os6 primeiros dígitos para registrar a data de nascimento, como920315330 significa que a data de nascimento é15/03/1992, como calcular rapidamente a idade com base em cada número de identificação no Excel?
Vamos usar a lista de números de identificação iniciando na célula C2 como exemplo e utilizar a fórmula:
=DATEDIF(DATE(IF(LEFT(C2,2)>TEXT(TODAY(),"YY"),"19"&LEFT(C2,2),"20"&LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),"y")
Pressione Enter tecla. Em seguida, arraste a alça de preenchimento automático para baixo para obter outros resultados.
Observação:
Nesta fórmula, se o ano for menor que o ano atual, será considerado como começando com20, como200203943 será considerado ano2020; se o ano for maior que o ano atual, será considerado como começando com19, como920420392 será considerado ano1992.
Mais Tutoriais de Excel:
Consolidar Múltiplas Pastas de Trabalho/Planilhas em Uma
Este tutorial lista quase todos os cenários de consolidação que você pode enfrentar e fornece soluções profissionais relativas para você.
Dividir Células de Texto, Número e Data (Separar em Múltiplas Colunas)
Este tutorial é dividido em três partes: dividir células de texto, dividir células numéricas e dividir células de data. Cada parte fornece exemplos diferentes para ajudá-lo a lidar com a tarefa de divisão ao encontrar o mesmo problema.
Consolidar Conteúdo de Múltiplas Células Sem Perder Dados no Excel
Este tutorial restringe a extração a uma posição específica em uma célula e reúne diferentes métodos para ajudar a extrair texto ou números de uma célula por posição específica no Excel.
Comparar Duas Colunas para Correspondências e Diferenças no Excel
Este artigo cobre a maioria dos cenários possíveis de comparação entre duas colunas que você pode encontrar. Esperamos que possa ajudar.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%
- Barra de Fórmulas Super (edite facilmente várias linhas de texto e fórmulas); Layout de Leitura (leia e edite facilmente um grande número de células); Cole em Intervalo Filtrado...
- Mesclar Células/Linhas/Colunas mantendo os dados; Dividir conteúdo de células; Combinar Linhas Duplicadas e Somar/Média... Prevenir Células Duplicadas; Comparar Intervalos...
- Selecionar Linhas Duplicadas ou Únicas; Selecionar Linhas em Branco (todas as células estão vazias); Super Localizar e Localização Difusa em Muitas Pastas de Trabalho; Seleção Aleatória...
- Cópia Exata de Múltiplas Células sem alterar a referência da fórmula; Criar automaticamente referências para várias planilhas; Inserir Marcadores, Caixas de Seleção e mais...
- Favoritar e Inserir rapidamente Fórmulas, Intervalos, Gráficos e Imagens; Criptografar Células com senha; Criar Lista de Correspondência e enviar emails...
- Extrair Texto, Adicionar Texto, Remover por Posição, Remover Espaço; Criar e Imprimir Estatísticas da Página de Dados; Converter entre o Conteúdo das Células e Comentários...
- Super Filtro (salvar e aplicar esquemas de filtro em outras planilhas); Classificação Avançada por mês/semana/dia, frequência e mais; Filtro Especial por negrito, itálico...
- Consolidar Pastas de Trabalho e Planilhas; Mesclar Tabelas com base em colunas-chave; Dividir Dados em Múltiplas Planilhas; Conversão em Lote de xls, xlsx e PDF...
- Agrupamento de Tabela Dinâmica por número da semana, dia da semana e mais... Mostrar Células Desbloqueadas, Bloqueadas por cores diferentes; Destacar Células Que Contêm Fórmula/Nome...
- Habilite a edição e leitura com guias no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas abas da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques de mouse para você todos os dias!
