Tutorial Excel: Cálculo DateTime (calcular diferença, idade, adicionar/subtrair)
No Excel, o cálculo de data e hora é usado com frequência, como calcular a diferença entre duas datas/horas, adicionar ou subtrair data e hora, obter a idade com base na data de nascimento e assim por diante. Aqui neste tutorial, ele lista quase cenários sobre cálculo de data e hora e fornece os métodos relacionados para você.
Neste tutorial, crio alguns exemplos para explicar os métodos, você pode alterar as referências necessárias quando usar o código ou fórmulas VBA abaixo
1. Calcular a diferença entre duas datas/horas
Calcular a diferença entre duas datas ou duas vezes pode ser o problema mais normal de cálculo de data e hora que você encontra no trabalho diário do Excel. O seguidor abaixo dos exemplos fornecidos pode ajudá-lo a aumentar a eficiência quando você encontrar os mesmos problemas.
1.11 Calcular a diferença entre duas datas em dias/meses/anos/semanas
A função DATADIF 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 DATA SE função
Diferença de dias entre duas datas
Para obter a diferença de dias entre duas datas na célula A2 e B2, use a fórmula como esta
=DATEDIF(A2,B2,"d")
Press Entrar chave para obter o resultado.
Meses de diferença entre duas datas
Para obter a diferença de meses entre duas datas na célula A5 e B5, use a fórmula como esta
=DATEDIF(A5,B5,"m")
Press Entrar chave para obter o resultado.
Diferença de anos entre duas datas
Para obter a diferença de anos entre duas datas na célula A8 e B8, use a fórmula como esta
=DATEDIF(A8,B8,"y")
Press Entrar chave para obter o resultado.
Semanas de diferença entre duas datas
Para obter a diferença de semanas entre duas datas na célula A11 e B11, use a fórmula como esta
=DATEDIF(A11,B11,"d")/7
Press Entrar chave para obter o resultado.
Observação:
1) Quando você usa a fórmula acima para obter a diferença de semanas, ela pode retornar um resultado no formato de data, você precisa formatar o resultado para geral ou número conforme necessário.
2) Quando você usa a fórmula acima para obter a diferença de semanas, ela pode retornar a um número decimal, se você deseja obter o número inteiro da semana, pode adicionar a função ROUNDDOWN antes, conforme mostrado abaixo, para obter a diferença de semanas inteiras:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Calcular meses ignora anos e dias entre duas datas
Se você deseja apenas calcular a diferença de meses ignorando anos e dias entre duas datas, como mostra a captura de tela abaixo, aqui está uma fórmula que pode ajudá-lo.
=DATEDIF(A2,B2,"ym")
Press Entrar chave para obter o resultado.
A2 é a data de início e B2 é a data de término.
1.13 Calcular dias ignora anos e meses entre duas datas
Se você deseja apenas calcular a diferença de dias ignorando anos e meses entre duas datas, conforme a captura de tela abaixo, aqui está uma fórmula que pode ajudá-lo.
=DATEDIF(A5,B5,"md")
Press Entrar chave para obter o resultado.
A5 é a data de início e B5 é a data de término.
1.14 Calcular 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 captura de tela abaixo, aqui também está uma fórmula fornecida.
=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"
Press Entrar chave para obter o resultado.
A8 é a data de início e B8 é a data de término.
1.15 Calcular a diferença entre uma data e hoje
Para calcular automaticamente a diferença entre uma data e hoje, basta alterar o end_date nas fórmulas acima para TODAY(). Aqui, tome para calcular a diferença de dias entre uma data passada e hoje como uma instância.
=DATEDIF(A11,TODAY(),"d")
Press Entrar chave para obter o resultado.
Note: se você quiser calcular a diferença entre uma data futura e hoje, altere a data_inicial para hoje e tome a data futura como data_final assim:
=DATEDIF(TODAY(),A14,"d")
Observe que start_date deve ser menor que end_date na função DATEDIF, caso contrário, retornará para #NUM! valor de erro.
1.16 Calcular dias úteis com ou sem feriado entre duas datas
Às vezes, você pode precisar contar o número de dias úteis com ou sem feriados entre duas datas.
Nesta parte, você usará a função NETWORKDAYS.INTL:
Clique NETWORKDAYS.INTL conhecer seus argumentos e uso.
Contar dias úteis com feriados
Para contar os dias úteis com feriados entre duas datas na célula A2 e B2, use a fórmula da seguinte forma:
=NETWORKDAYS.INTL(A2,B2)
Press Entrar chave para obter o resultado.
Contar dias úteis sem feriados
Para contar os dias úteis com feriados entre duas datas na célula A2 e B2 e excluindo os feriados no intervalo D5:D9, use a fórmula como esta:
=DIASTRABALHO.INTL(A5,B5,1,D5:D9)
Press Entrar chave para obter o resultado.
Observação:
Nas fórmulas acima, eles consideram sábado e domingo como fim de semana, se você tiver dias de fim de semana diferentes, altere o argumento [fim de semana] conforme necessário.
1.17 Calcular fins de semana entre duas datas
Se você deseja contar o número de finais de semana entre duas datas, as funções SUMPRODUCT ou SUM podem fazer um favor.
Para contar os finais de semana (sábado e domingo) entre duas datas na célula A12 e B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Press Entrar chave 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 ajudá-lo.
As células A15 e B15 são as duas datas que você deseja contar na segunda-feira, use a fórmula como esta:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Press Entrar chave para obter o resultado.
Altere o número do dia da semana na função WEEKDAY para contar um dia da semana diferente:
1 é domingo, 2 é segunda, 3 é terça, 4 é quarta, 5 é quinta, 6 é sexta e 7 é 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 captura de tela abaixo:
Obter dias restantes no mês atual
Clique EOMÊS conhecer o argumento e o uso.
Para obter os dias restantes do mês atual na célula A2, use a fórmula como esta:
=EOMONTH(A2,0)-A2
Press Entrar e arraste a alça de preenchimento automático para aplicar essa fórmula a outras células, se necessário.
Dica: os resultados podem ser mostrados como formato de data, basta alterá-los como formato geral ou numérico.
Obter dias restantes no ano atual
Para obter os dias restantes do ano atual na célula A2, use a fórmula como esta:
=DATE(YEAR(A2),12,31)-A2
Press Entrar e arraste a alça de preenchimento automático para aplicar essa fórmula a outras células, se necessário.
1.21 Calcular a diferença entre dois tempos
Para obter a diferença entre dois tempos, aqui estão duas fórmulas simples que podem ajudá-lo.
Supondo que na célula A2 e B2 contenham hora_inicial e hora_final separadamente, usando as fórmulas como estas:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Press Entrar chave para obter o resultado.
Observação:
- Se você usar end_time-start_time, poderá formatar o resultado para outro formato de hora conforme necessário na caixa de diálogo Formatar células.
- Se você usar TEXT(end_time-first_time,"time_format"), insira o formato de hora em que deseja que o resultado seja mostrado na fórmula, como TEXT(end_time-first_time,"h") retorna 16.
- Se end_time for menor que start_time, ambas as fórmulas retornarão valores de erro. Para resolver esse problema, você pode adicionar ABS na frente dessas fórmulas, como ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")) e formatar o resultado como hora.
1.22 Calcular a diferença entre dois tempos em horas/minutos/segundos
Se você quiser calcular a diferença entre duas vezes em horas, minutos ou segundos, como mostra a captura de tela abaixo, siga esta parte.
Obter diferença de horas entre dois horários
Para obter a diferença de horas entre dois horários em A5 e B5, use a fórmula como esta:
=INT((B5-A5)*24)
Press Entrar key e formate o resultado do formato de hora como geral ou número.
Se você deseja obter a diferença de horas decimais, use (end_time-start_time)*24.
Obter minutos de diferença entre duas vezes
Para obter a diferença de minutos entre duas vezes em A8 e B8, use a fórmula como esta:
=INT((B8-A8)*1440)
Press Entrar key e formate o resultado do formato de hora como geral ou número.
Se você deseja obter a diferença de minutos decimais, use (end_time-start_time)*1440.
Obter segundos de diferença entre duas vezes
Para obter a diferença de segundos entre duas vezes em A5 e B5, use a fórmula como esta:
=(B11-A11)*86400)
Press Entrar key e formate o resultado do formato de hora como geral ou número.
1.23 Calcular a diferença de horas apenas entre duas vezes (não exceder 24 horas)
Se a diferença entre dois horários não exceder 24 horas, a função HOUR pode obter rapidamente a diferença de horas entre esses dois horários.
Clique HORA para mais detalhes sobre esta função.
Para obter a diferença de horas entre os horários na célula A14 e B14, use a função HOUR assim:
=HOUR(B14-A14)
Press Entrar chave para obter o resultado.
O start_time deve ser menor que o end_time, caso contrário, a fórmula retornará #NUM! valor de erro.
1.24 Calcular a diferença de minutos apenas entre duas vezes (não exceder 60 minutos)
A função MINUTE pode obter rapidamente a diferença de apenas alguns minutos entre esses dois horários e ignorar horas e segundos.
Clique MINUTO para mais detalhes sobre esta função.
Para obter apenas a diferença de minutos entre os tempos na célula A17 e B17, use a função MINUTE assim:
=MINUTE(B17-A17)
Press Entrar chave para obter o resultado.
O start_time deve ser menor que o end_time, caso contrário, a fórmula retornará #NUM! valor de erro.
1.25 Calcular a diferença de segundos apenas entre duas vezes (não exceder 60 segundos)
A função SECOND pode obter rapidamente a diferença de apenas alguns segundos entre esses dois horários e ignorar horas e minutos.
Clique SEGUNDA para mais detalhes sobre esta função.
Para obter apenas a diferença de segundos entre os tempos na célula A20 e B20, use a função SEGUNDA como esta:
=SECOND(B20-A20)
Press Entrar chave para obter o resultado.
O start_time deve ser menor que o end_time, caso contrário, a fórmula retornará #NUM! valor de erro.
1.26 Calcular a diferença entre dois tempos e retornar horas, minutos, segundos
Se você quiser mostrar a diferença entre duas vezes como xx horas xx minutos xx segundos, use a função TEXT como mostrado abaixo:
Clique TEXTO para perceber os argumentos e uso desta função.
Para calcular a diferença entre os tempos na célula A23 e B23, use a fórmula como esta:
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
Press Entrar chave para obter o resultado.
Observação:
Essa fórmula também calcula apenas a diferença de horas não superior a 24 horas, e o end_time deve ser maior que o start_time, caso contrário, retorna #VALUE! valor de erro.
1.27 Calcular a diferença entre dois datetimes
Se houver duas vezes no formato mm/dd/aaaa hh:mm:ss, para calcular a diferença entre elas, você pode usar uma das fórmulas abaixo conforme necessário.
Obtenha a diferença de horário entre dois datetimes e retorne o resultado no formato hh:mm:ss
Tome dois datetimes na célula A2 e B2 como uma instância, use a fórmula como esta:
=B2-A2
Press Entrar key, retornando um resultado no formato datetime e formate esse resultado como [h]: mm: ss na categoria personalizada sob o Sessão guia em formatar células diálogo.
Obtenha a diferença entre dois datetimes e retorne dias, horas, minutos, segundos
Tome dois datetimes na célula A5 e B5 como uma instância, use a fórmula como esta:
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
Press Entrar chave 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 retornarão valores de erro.
1.28 Calcular diferença de tempo com milissegundos
Primeiramente, você precisa saber como formatar a célula para mostrar os milissegundos:
Selecione as células que você deseja mostrar milissegundos e direito de selecionar formatar células para habilitar o formatar células diálogo, selecione Personalizadas no Categoria lista na guia Número e digite isso hh: mm: ss.000 na caixa de texto.
Use a fórmula:
Aqui para calcular a diferença entre duas vezes na célula A8 e B8, use a fórmula como:
=ABS(B8-A8)
Press Entrar chave 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 fixadas em 8 horas todos os dias e, para calcular as horas de trabalho entre duas datas fornecidas nas células A16 e B16, use a fórmula como esta:
=NETWORKDAYS(A16,B16) * 8
Press Entrar key e formate o resultado como geral ou número.
Para obter mais exemplos sobre como calcular horas de trabalho entre duas datas, visite Obtenha horas de trabalho entre duas datas no Excel
Se você tem Kutools for Excel instalado no Excel, 90 por cento dos cálculos de diferença de data e hora podem ser resolvidos rapidamente sem lembrar de nenhuma fórmula.
1.31 Calcular a diferença entre dois datetimes pelo Data & Time Helper
Para calcular a diferença entre dois datetimes no Excel, basta o Auxiliar de data e hora basta.
1. Selecione uma célula onde você coloca o resultado calculado e clique em Kutools > Fórmula Helper > Ajudante de data e hora.
2. No popping Auxiliar de data e hora diálogo, siga as configurações abaixo:
- Verifique Diferença opção;
- Selecione a data e hora de início e a data e hora de término em Entrada de argumentos seção, você também pode inserir a data e hora manualmente na caixa de entrada 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 em Resultado seção.
3. Clique Ok. O resultado calculado é gerado e arraste a alça de preenchimento automático sobre as células que você também precisa calcular.
Dica:
Se você deseja obter a diferença entre duas datas e horas e mostrar o resultado como dias, horas e minutos com o Kutools para Excel, faça o seguinte:
Selecione uma célula onde você deseja colocar o resultado e clique em Kutools > Fórmula Helper > Data e Hora > Contar dias, horas e minutos entre duas datas.
Então, no Auxiliar de Fórmulas caixa de diálogo, especifique a data de início e a data de término e clique em Ok.
E o resultado da diferença será mostrado como dias, horas e minutos.
Clique Auxiliar de data e hora para saber mais uso deste recurso.
Clique Kutools for Excel para conhecer todos os recursos deste add-in.
Clique Download grátis para obter uma avaliação gratuita de 30 dias do Kutools para Excel
Se você quiser contar rapidamente o fim de semana, os dias úteis ou um dia da semana específico entre duas datas, Kutools para Excel Fórmula Helper grupo pode ajudá-lo.
1. Selecione a célula que colocará o resultado calculado, clique em Kutools > Estatístico > Número de dias não úteis entre duas datas/Número de dias úteis entre duas datas/Contar o número de dias específicos da semana.
2. No popping out Auxiliar de Fórmulas caixa de diálogo, especifique a data de início e a data de término, se você aplicar Contar o número de dias específicos da semana, você precisa especificar o dia da semana também.
Para contar o dia da semana específico, você pode consultar a nota para usar 1-7 para indicar domingo-sábado.
3. Clique Oke, em seguida, 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 Kutools for Excel para conhecer todos os recursos deste add-in.
Clique Download grátis para obter uma avaliação gratuita de 30 dias do Kutools para Excel
2. Adicione ou subtraia data e hora
Exceto para calcular a diferença entre dois datetimes, adicionar ou subtrair também é o cálculo de datetime normal 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, aqui estão dois métodos diferentes.
Supondo adicionar 21 dias a uma data na célula A2, escolha um dos métodos abaixo para resolvê-lo,
Método 1 data+dias
Selecione uma célula e digite a fórmula:
=A+21
Press Entrar chave para obter o resultado.
Se você quiser subtrair 21 dias, basta mudar o sinal de mais (+) para o sinal de menos (-).
Método 2 Colar Especial
1. Digite o número de dias que deseja adicionar em uma célula suposta na célula C2 e pressione Ctrl + C para copiá-lo.
2. Em seguida, selecione as datas que deseja adicionar 21 dias, clique com o botão direito do mouse para mostrar o menu de contexto e selecione Colar especial....
3. No colar especial diálogo, verificar Adicionar opção (Se você quiser subtrair dias, marque Subtrair opção). Clique OK.
4. Agora as datas originais mudam para números de 5 dígitos, formate-os como datas.
2.12 Adicionar ou subtrair meses a uma data
Para adicionar ou subtrair meses a uma data, a função EDATE pode ser usada.
Clique DATA estudar seus argumentos e uso.
Supondo adicionar 6 meses à data na célula A2, use a fórmula como esta:
=EDATE(A2,6)
Press Entrar chave para obter o resultado.
Se você quiser subtrair 6 meses da data, altere 6 para -6.
2.13 Adicionar ou subtrair anos a uma data
Para adicionar ou subtrair n anos a uma data, pode ser usada uma fórmula que combina as funções DATA, ANO, MÊS e DIA.
Supondo adicionar 3 anos à data na célula A2, use a fórmula como esta:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Press Entrar chave para obter o resultado.
Se você quiser subtrair 3 anos da data, altere 3 para -3.
2.14 Adicionar ou subtrair semanas a uma data
Para adicionar ou subtrair semanas a uma data, a fórmula geral é
Supondo adicionar 4 semanas à data na célula A2, use a fórmula como esta:
=A2+4*7
Press Entrar chave para obter o resultado.
Se você quiser subtrair 4 semanas da data, mude o sinal de mais (+) para o sinal de menos (-).
2.15 Adicionar ou subtrair dias úteis, incluindo ou excluindo feriados
Nesta seção, ele apresenta como usar a função WORKDAY para adicionar ou subtrair dias úteis a uma determinada data, excluindo feriados ou incluindo feriados.
Visite a DIA DE TRABALHO para saber mais detalhes sobre seus argumentos e uso.
Adicionar dias úteis, incluindo feriados
Na célula A2 está a data que você usa, na célula B2 contém o número de dias que você deseja adicionar, por favor use a fórmula assim:
=WORKDAY(A2,B2)
Press Entrar chave para obter o resultado.
Adicionar dias úteis excluindo feriados
Na célula A5 está a data que você usa, na célula B5 contém o número de dias que você deseja adicionar, no intervalo D5:D8 lista os feriados, por favor use a fórmula assim:
=WORKDAY(A5,B5,D5:D8)
Press Entrar chave para obter o resultado.
Observação:
A função WORKDAY considera sábado e domingo como fins de semana, se seus fins de semana são sábado e domingo, você pode aplicar a função WOKRDAY.INTL, que suporta a especificação de fins de semana.
Visite a DIATRABALHO.INTL para mais detalhes.
Se você quiser subtrair dias úteis para uma data, basta alterar o número de dias para negativo na fórmula.
2.16 Adicionar ou subtrair ano, mês, dias específicos a uma data
Se você deseja adicionar um ano específico, dias do mês a uma data, a fórmula que combina a função DATA, ANO, MÊS e DIAS pode fazer um favor.
Para adicionar 1 ano 2 meses e 30 dias a uma data em A11, use a fórmula como esta:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Press Entrar chave para obter o resultado.
Se você quiser subtrair, altere todos os sinais de mais (+) para sinais de menos (-).
2.21 Adicionar ou subtrair horas/minutos/segundos a um datetime
Aqui fornece algumas fórmulas para adicionar ou subtrair horas, minutos ou segundos a um datetime.
Adicionar ou subtrair horas a um datetime
Supondo adicionar 3 horas a um datetime (também pode ser um time) na célula A2, use a fórmula como esta:
=A2+3/24
Press Entrar chave para obter o resultado.
Adicionar ou subtrair horas a um datetime
Supondo que você adicione 15 minutos a uma data e hora (também pode ser uma hora) na célula A5, use a fórmula como esta:
=A2+15/1440
Press Entrar chave para obter o resultado.
Adicionar ou subtrair horas a um datetime
Supondo que você adicione 20 segundos a uma data e hora (também pode ser uma hora) na célula A8, use a fórmula como esta:
=A2+20/86400
Press Entrar chave para obter o resultado.
2.22 Soma vezes mais de 24 horas
Supondo que haja uma tabela do Excel registrando o tempo de trabalho de todos os funcionários em uma semana, para somar o tempo total de trabalho para cálculo dos pagamentos, você pode usar SOMA(intervalo) para obter o resultado. Mas, em geral, o resultado somado será mostrado como um tempo não superior a 24 horas, como mostra a captura de tela abaixo, como você pode obter o resultado correto?
Na verdade, você só precisa formatar o resultado como [hh]:mm:ss.
Clique com o botão direito do mouse na célula de resultado, escolha formatar células no menu de contexto e no popping formatar células diálogo, escolha Personalizadas da bolha e digite [hh]:mm:ss na caixa de texto na seção direita, clique OK.
O resultado somado será mostrado corretamente.
2.23 Adicionar horas de trabalho a uma data excluindo fim de semana e feriado
Aqui fornece uma fórmula longa para obter a data de término com base na adição de um número específico de horas de trabalho a uma data de início e exclui fins de semana (sábado e domingo) e feriados.
Em uma tabela do Excel, A11 contém a data e hora de início e B11 contém as horas de trabalho, na célula E11 e E13 estão as horas de início e término de trabalho e a célula E15 contém o feriado que será excluído.
Use a fórmula desta forma:
=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))
Press Entrar chave para obter o resultado.
Se você tiver o Kutools for Excel instalado, apenas uma ferramenta – Ajuda de Data e Horar pode resolver a maioria dos cálculos sobre adição e subtração de data e hora.
1. Clique em uma célula para a qual você deseja gerar o resultado e aplique esta ferramenta clicando Kutools > Fórmula Helper > Auxiliar de data e hora.
2. No Auxiliar de data e hora diálogo, verificar Adicionar opção ou Subtrair opção que você precisa, então escolha a célula ou digite diretamente a data e hora que você deseja usar Entrada de argumentos seção, especifique os anos, meses, semanas, dias, horas, minutos e segundos que deseja adicionar ou subtrair e clique em Ok. Veja a imagem:
Você pode visualizar o resultado calculado no Resultado seção.
Agora o resultado é gerado, arraste a alça automática sobre outras células para obter os resultados.
Clique Auxiliar de data e hora para saber mais uso deste recurso.
Clique Kutools for Excel para conhecer todos os recursos deste add-in.
Clique Download de taxas para obter uma avaliação gratuita de 30 dias do Kutools para Excel
2.41 Verifique ou destaque se uma data expirou
Se houver uma lista de datas expiradas de produtos, você pode querer verificar e destacar as datas que expiraram com base em hoje, como mostra a captura de tela abaixo.
Na verdade, o Formatação condicional pode lidar rapidamente com este trabalho.
1. Selecione as datas que deseja verificar e clique em INÍCIO
> Formatação condicional > Nova regra.
2. No Nova regra de formatação diálogo, selecione Use uma fórmula para determinar quais células formatar no Selecione um tipo de regra seção e tipo =B2 na caixa de entrada (B2 é a primeira data que você deseja verificar) e clique em Formato para aparecer formatar células caixa de diálogo e, em seguida, escolha uma formatação diferente para destacar as datas de expiração conforme necessário. Clique OK > OK.
2.42 Retornar no final do mês atual/primeiro dia do próximo mês/a>
As datas vencidas de alguns produtos estão no final do mês de produção ou no primeiro dia do próximo mês de produção, para listar rapidamente as datas vencidas com base na data de produção, siga esta parte.
Obter final do mês atual
Aqui está uma data de produção na célula B13, use a fórmula como esta:
=EOMONTH(B13,0)
Press Entrar chave para obter o resultado.
Obter 1º dia do próximo mês
Aqui está uma data de produção na célula B18, use a fórmula como esta:
=EOMONTH(B18,0)+1
Press Entrar chave para obter o resultado.
3. Calcular a idade
Nesta seção, ele lista os métodos para resolver como calcular a idade com base em uma determinada data ou número de série.
3.11 Calcular a idade com base na data de nascimento
Obter idade em número decimal com base na data de nascimento
Clique FRAC ANO para detalhes sobre seus argumentos e o uso.
Por exemplo, para obter as idades com base na lista de datas de nascimento na coluna B2:B9, use a fórmula como esta:
=YEARFRAC(B2,TODAY())
Press Entrar e arraste a alça de preenchimento automático para baixo até que todas as idades sejam calculadas.
Dica:
1) Você pode especificar a casa decimal conforme necessário no formatar células diálogo.
2) Se você deseja calcular a idade em uma data específica com base em uma determinada data de nascimento, altere TODAY() para a data específica entre aspas duplas, como =YEARFRAC(B2,"1/1/2021")
3) Se você deseja obter a idade do próximo ano com base na data de nascimento, basta adicionar 1 na fórmula como =YEARFRAC(B2,HODAY())+1.
Obter idade em número inteiro com base na data de nascimento
Clique DATA SE para detalhes sobre seus argumentos e o uso.
Usando o exemplo acima, para obter a idade com base nas datas de nascimento na lista em B2:B9, use a fórmula como esta:
=DATEDIF(B2,TODAY(),"y")
Press Entrar e arraste a alça de preenchimento automático para baixo até que todas as idades sejam calculadas.
Dica:
1) Se você deseja calcular a idade em uma data específica com base em uma determinada data de nascimento, altere TODAY() para a data específica entre aspas duplas, como =DATEDIF(B2,"1/1/2021","y") .
2) Se você quiser obter a idade do próximo ano com base na data de nascimento, basta adicionar 1 na fórmula como =DATASE(B2,HOJE(),"y")+1.
3.12 Calcular a idade em anos, mês e formato de dias por determinado aniversário
Se você deseja calcular a idade com base em uma determinada data de nascimento e mostrar o resultado como xx anos, xx meses, xx dias, como mostra a captura de tela abaixo, aqui está uma fórmula longa que pode ajudá-lo.
Para obter a idade em anos, meses e dias com base na data de nascimento na célula B12, use a fórmula como esta:
=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"
Press Entrar key para obter a idade e, em seguida, arraste a alça de preenchimento automático para outras células.
Dica:
Se você deseja calcular a idade em uma data específica com base em uma determinada data de nascimento, altere TODAY() para a 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 a idade por data de nascimento antes de 1/1/1900
No Excel, a data anterior a 1/1/1900 não pode ser inserida como data e hora ou calculada corretamente. Mas se você quiser calcular a idade de uma pessoa famosa com base na data de nascimento (antes de 1/11900) e data de morte, apenas um código VBA pode ajudá-lo.
1. Pressione outro + F11 chaves para habilitar Microsoft Visual Basic para Aplicações janela e clique inserção aba 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 a idade antes de 1/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 e selecione uma célula para colocar a idade calculada, digite =AgeFunc(birthdate,deathdate), neste caso, = IdadeFunc(B22,C22), pressione a tecla Enter para obter a idade. E use a alça de preenchimento automático para aplicar essa fórmula a outras células, se necessário.
Se você tem Kutools for Excel instalado no Excel, você pode aplicar o Auxiliar de data e hora ferramenta para calcular a idade.
1. Selecione uma célula na qual deseja colocar a idade calculada e clique em Kutools > Fórmula Helper > Ajudante de data e hora.
2. No Auxiliar de data e hora diálogo,
- 1) Verifique Idade opção;
- 2) Escolha a célula da data de nascimento ou digite diretamente a data de nascimento ou clique no ícone do calendário para selecionar a data de nascimento;
- 3) Escolha Agora opção se você quiser calcular a idade atual, escolha Data especificada opção e insira a data se quiser calcular a idade no passado ou no futuro;
- 4) Especifique o tipo de saída na lista suspensa;
- 5) Visualize o resultado de saída. Clique Ok.
Clique Auxiliar de data e hora para saber mais uso deste recurso.
Clique Kutools for Excel para conhecer todos os recursos deste add-in.
Clique Download grátis para obter uma avaliação gratuita de 30 dias do Kutools para Excel
3.31 Obter aniversário do número de identificação
Se houver uma lista de números de identificação que usam os primeiros 6 dígitos para registrar a data de nascimento, como 920315330 significa que a data de nascimento é 03/15/1992, como você pode obter rapidamente a data de nascimento em outra coluna?
Agora vamos pegar a lista de números de ID começando na célula C2 como uma instância e usar a fórmula como esta:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Press Entrar chave. 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 para sua necessidade. Por exemplo, se o número de ID mostrado como 13219920420392, o aniversário for 04/20/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 a idade a partir do número de identificação
Se houver uma lista de números de identificação que usam os primeiros 6 dígitos para registrar a data de nascimento, como 920315330 significa que a data de nascimento é 03/15/1992, como você pode calcular rapidamente a idade com base em cada número de identificação no Excel?
Agora vamos pegar a lista de números de ID começando na célula C2 como uma instância e usar a fórmula como esta:
=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")
Press Entrar chave. 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, o ano será considerado como começando com 20, como 200203943 será considerado como o ano 2020; se o ano for maior que o ano atual, o ano será considerado como começando com 19, como 920420392 será considerado como o ano de 1992.
Mais tutoriais de Excel:
Combine várias pastas de trabalho/planilhas em uma
Este tutorial, listando quase todos os cenários de combinação que você pode enfrentar e fornecendo soluções profissionais relativas para você.
Células de texto, número e data divididas (separadas em várias colunas)
Este tutorial está dividido em três partes: células de texto divididas, células de números divididos e células de datas divididas. Cada parte fornece exemplos diferentes para ajudá-lo a saber como lidar com o trabalho de divisão ao encontrar o mesmo problema.
Combine o conteúdo de várias células sem perder dados no Excel
Este tutorial restringe a extração a uma posição específica em uma célula e coleta diferentes métodos para ajudar a extrair texto ou números de uma célula por posição específica no Excel.
Compare duas colunas para correspondências e diferenças no Excel
Aqui, este artigo aborda a maioria dos cenários possíveis da comparação de duas colunas que você pode encontrar e espero que possa ajudá-lo.
As melhores ferramentas de produtividade para escritório
O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%
- Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
- Mesclar células / linhas / colunas e manutenção de dados; Dividir o conteúdo das células; Combine Linhas Duplicadas e Soma / Média... Evite células duplicadas; Comparar intervalos...
- Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
- Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
- Fórmulas favoritas e de inserção rápida, Intervalos, gráficos e imagens; Criptografar células com senha; Criar lista de discussão e enviar emails ...
- Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
- Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
- Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; 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 possuem fórmula / nome...
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
