Tutorial do Excel: Cálculo de datas e horas (calcular diferença, idade, adicionar/subtrair)
No Excel, o cálculo de Data e hora é frequentemente utilizado, por exemplo, para calcular a diferença entre duas datas/horas, adicionar ou subtrair datas e horas, obter a idade com base na data de nascimento fornecida, entre outros. Neste tutorial, estão listados quase todos os cenários relacionados com o cálculo de datas e horas e são fornecidos os respetivos métodos.
Neste tutorial, criei alguns exemplos para ilustrar os métodos; pode ajustar as referências de acordo com as suas necessidades ao utilizar o código VBA ou as fórmulas abaixo.
1. Calcule a diferença entre duas datas ou horários.
Calcular a diferença entre duas datas ou duas horas é, provavelmente, o desafio mais comum relacionado com cálculos de datas e horas no seu dia a dia no Excel. Seguir os exemplos abaixo ajudá-lo-á a trabalhar com maior eficiência sempre que enfrentar situações semelhantes.
1,11 Calcular a diferença entre duas datas em dias/meses/anos/semanas
A função DATADIF do Excel permite calcular rapidamente a diferença entre duas datas em dias, meses, anos ou semanas.
Clique para mais detalhes sobre a função DATADIF
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»)
Prima a tecla 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»)
Prima a tecla 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»)
Prima a tecla 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
Prima a tecla Enter para obter o resultado.
Nota:
1) Ao utilizar a fórmula acima para obter a diferença em semanas, poderá obter um resultado no formato de data; nesse caso, formate-o como “Geral” ou “Número”, conforme necessário.
2) Ao utilizar a fórmula acima para obter a diferença em semanas, poderá obter um número decimal. Se pretender o valor inteiro do número de semanas, basta adicionar a função ARRED.EXCESSO antes da fórmula, como ilustrado abaixo, para obter a diferença inteira em semanas:
=ROUNDDOWN(DATEDIF(A11,B11,«d»)/7,0)
1,12 Calcular meses ignorando anos e dias entre duas datas
Se pretender calcular apenas a diferença em meses entre duas datas, ignorando anos e dias, conforme ilustrado na imagem abaixo, eis uma fórmula que o pode ajudar.
=DATEDIF(A2,B2,«ym»)
Prima a tecla Enter para obter o resultado.
A2 é a data de início e B2 é a data de término.
1,13 Calcular dias ignorando anos e meses entre duas datas
Se pretender calcular apenas a diferença em dias, ignorando anos e meses entre duas datas, como ilustrado na imagem abaixo, aqui está uma fórmula que o pode ajudar.
=DATEDIF(A5,B5,«md»)
Prima a tecla Enter para obter o resultado.
A5 é a data de início e B5 é a data de término.
1,14 Calcular a diferença entre duas datas e devolver anos, meses e dias
Se pretender obter a diferença entre duas datas e obtê-la no formato xx anos, xx meses e xx dias, conforme ilustrado na imagem abaixo, aqui está também uma fórmula disponibilizada.
=DATEDIF(A8, B8, «y») &« years, »&DATEDIF(A8, B8, «ym») &« months, » &DATEDIF(A8, B8, «md») &« days»
Prima a tecla Enter 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 substituir a data final nas fórmulas acima pela função HOJE(). Veja, por exemplo, o cálculo da diferença em dias entre uma data passada e hoje.
=DATEDIF(A11,TODAY(),«d»)
Prima a tecla Enter para obter o resultado.
Nota: se pretender calcular a diferença entre uma data futura e hoje, substitua a data inicial por hoje e utilize a data futura como data final, tal como indicado:
=DATEDIF(TODAY(),A14,«d»)
Tenha em atenção que, na função DATADIF, a data inicial deve ser anterior à data final; caso contrário, será devolvido o erro #NÚM!.
1,16 Calcular dias úteis com ou sem feriados entre duas datas
Por vezes, poderá precisar de contar o número de dias úteis entre duas datas dadas, com ou sem feriados.
Nesta parte, irá utilizar a função DIATRABALHOTOTAL.INTL:
Clique em DIATRABALHOTOTAL.INTL para conhecer os seus argumentos e a sua utilização.
Contar dias úteis incluindo feriados
Para contar os dias úteis incluindo feriados entre duas datas nas células A2 e B2, utilize a seguinte fórmula:
=NETWORKDAYS.INTL(A2,B2)
Prima a tecla Enter para obter o resultado.
Contar dias úteis excluindo feriados
Para contar os dias úteis entre duas datas nas células A2 e B2, excluindo os feriados no intervalo D5:D9, utilize a seguinte fórmula:
=DIATRABALHOTOTAL.INTL(A5;B5;1;D5:D9)
Prima a tecla Enter para obter o resultado.
Nota:
Nas fórmulas acima, consideram-se sábado e domingo como fim de semana. Se o seu fim de semana for diferente, ajuste o argumento [fimdesemana] conforme necessário.
1,17 Calcular fins de semana entre duas datas
Se pretender contar o número de fins de semana entre duas datas, as funções SOMARPRODUTO ou SOMA podem ajudá-lo.
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))
Prima a tecla Enter para obter o resultado.
1,18 Contar um dia da semana específico entre duas datas
Para contar o número de ocorrências de um determinado dia da semana — como segunda-feira — entre duas datas, a combinação das funções INT e DIASEMANA pode ajudá-lo.
As células A15 e B15 são as duas datas entre as quais pretende contar as segundas-feiras; utilize a seguinte fórmula:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Prima a tecla Enter para obter o resultado.
Altere o número do dia da semana na função DIASEMANA para contar um dia diferente:
1 é domingo, 2 é segunda-feira, 3 é terça-feira, 4 é quarta-feira, 5 é quinta-feira, 6 é sexta-feira e 7 é sábado)
1,19 Calcular Dias restantes num mês/ano
Por vezes, poderá pretender saber o Dias restantes no mês ou ano com base numa data fornecida, conforme ilustrado na imagem abaixo:
Obter Dias restantes no mês atual
Clique em EOMONTH para conhecer os seus argumentos e como utilizá-los.
Para obter o Dias restantes do mês atual na célula A2, utilize a seguinte fórmula:
=EOMONTH(A2,0)-A2
Prima a tecla 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 apresentados como Formato de Data; basta alterá-los para o formato geral ou numérico.
Obter Dias restantes no ano atual
Para obter o Dias restantes do ano atual na célula A2, utilize a seguinte fórmula:
=DATE(YEAR(A2),12,31)-A2
Prima a tecla Enter e, se necessário, arraste a alça de preenchimento automático para aplicar esta fórmula a outras células.
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 o podem ajudar.
Supondo que as células A2 e B2 contêm, respetivamente, o horário inicial e o horário final, utilize as seguintes fórmulas:
=B2-A2
=TEXT(B2-A2,«hh:mm:ss»)
Prima a tecla Enter para obter o resultado.
Nota:
- Se utilizar fim_hora-início_hora, pode formatar o resultado conforme necessário para outro formato horário na caixa de diálogo Definir Formato de Célula.
- Se utilizar TEXTO(fim_hora‑primeira_hora;«formato_hora»), introduza o formato horário pretendido para apresentar o resultado na fórmula — por exemplo, TEXTO(fim_hora‑primeira_hora;«h») devolve 16.
- Se o fim_hora for inferior ao início_hora, ambas as fórmulas devolvem valores de erro. Para resolver este problema, basta adicionar ABS no início dessas fórmulas — por exemplo, ABS(B2-A2) ou ABS(TEXTO(B2-A2,«hh:mm:ss»)) — e, em seguida, formatar o resultado como horário.
1,22 Calcular a diferença entre dois horários em horas/minutos/segundos
Se pretender calcular a diferença entre dois horários em horas, minutos ou segundos, tal como ilustrado na imagem seguinte, siga esta secção.
Obter a diferença em horas entre dois horários
Para obter a diferença em horas entre os horários nas células A5 e B5, utilize a seguinte fórmula:
=INT((B5-A5)*24)
Prima a tecla Enter e, de seguida, formate o resultado do horário como Geral ou Numérico.
Para obter a diferença em horas decimais, utilize (horário_final – horário_inicial) × 24.
Obter a diferença em minutos entre dois horários
Para obter a diferença em minutos entre os horários nas células A8 e B8, utilize a seguinte fórmula:
=INT((B8-A8)*1440)
Prima a tecla Enter e, de seguida, formate o resultado do horário como Geral ou Numérico.
Para obter a diferença em minutos decimais, utilize (horário_final – horário_inicial) × 1440.
Obter a diferença em segundos entre dois horários
Para obter a diferença em segundos entre os horários nas células A5 e B5, utilize a seguinte fórmula:
=(B11-A11)*86400)
Prima a tecla Enter e, de seguida, formate o resultado do horário como Geral ou Numérico.
1,23 Calcular apenas a diferença em horas entre dois horários (sem exceder 24 horas)
Se a diferença entre dois horários não exceder 24 horas, a função HORA obtém rapidamente essa diferença em horas.
Clique em HORA para obter mais informações 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 HORA da seguinte forma:
=HOUR(B14-A14)
Prima a tecla Enter para obter o resultado.
O horário_inicial deve ser anterior ao horário_final; caso contrário, a fórmula devolve o erro #NÚM!.
1,24 Calcular apenas a diferença em minutos entre dois horários (sem exceder 60 minutos)
A função MINUTO extrai rapidamente apenas os minutos desses dois horários, ignorando horas e segundos.
Clique em MINUTO para obter mais informações 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 MINUTO da seguinte forma:
=MINUTE(B17-A17)
Prima a tecla Enter para obter o resultado.
O horário_inicial tem de ser anterior ao horário_final; caso contrário, a fórmula devolve o erro #NÚM!.
1,25 Calcular apenas a diferença em segundos entre dois horários (sem exceder 60 segundos)
A função SEGUNDO obtém rapidamente apenas a diferença em segundos entre estes dois horários, ignorando horas e minutos.
Clique em SEGUNDO para obter mais informações 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 SEGUNDO da seguinte forma:
=SECOND(B20-A20)
Prima a tecla Enter para obter o resultado.
O horário_inicial tem de ser anterior ao horário_final; caso contrário, a fórmula devolve o erro #NÚM!.
1,26 Calcular a diferença entre dois horários e devolver horas, minutos e segundos
Se pretender apresentar a diferença entre dois horários como xx horas xx minutos xx segundos, utilize a função TEXTO conforme indicado abaixo:
Clique em TEXTO para compreender os argumentos e a utilização desta função.
Para calcular a diferença entre os horários nas células A23 e B23, utilize a seguinte fórmula:
=TEXT(B23-A23,«h»« hours »«m»« minutes »«s»« seconds»«»).
Prima a tecla Enter para obter o resultado.
Nota:
Esta fórmula calcula apenas a diferença em horas, sem ultrapassar 24 horas, e exige que o horário_final seja posterior ao horário_inicial; caso contrário, devolve o erro #VALOR!.
1,27 Calcular a diferença entre duas datas e horas
Se tiver duas datas e horas no formato mm/dd/aaaa hh:mm:ss, pode calcular a diferença entre elas utilizando uma das seguintes fórmulas, consoante as suas necessidades.
Obter a diferença entre duas datas e horas e devolver o resultado no formato hh:mm:ss
Tome como exemplo duas datas e horas nas células A2 e B2 e utilize a seguinte fórmula:
=B2-A2
Prima a tecla Enter para obter um resultado no formato data e hora; de seguida, formate este resultado como [h]:mm:ss na categoria personalizada, no separador Número da caixa de diálogo Definir formato de célula.
Obter a diferença entre duas datas e horas e devolver dias, horas, minutos e segundos
Tome como exemplo duas datas e horas nas células A5 e B5 e utilize a seguinte fórmula:
=INT(B5-A5) & « Days, » & HOUR(B5-A5) & « Hours, » & MINUTE(B5-A5) & « Minutes, » & SECOND(B5-A5) & « Seconds »
Prima a tecla Enter para obter o resultado.
Nota: em ambas as fórmulas, a data_hora_final deve ser posterior à data_hora_inicial; caso contrário, as fórmulas devolvem valores de erro.
1,28 Calcular a diferença temporal com milissegundos
Em primeiro lugar, é necessário saber como formatar a célula para apresentar os milissegundos:
Selecione as células onde pretende apresentar milissegundos, clique com o botão direito e escolha Definir formato de célula para abrir a caixa de diálogo Definir formato de célula. Em seguida, no separador Número, selecione Personalizado na lista Categoria e introduza hh:mm:ss.000 na caixa de texto.
Utilize a fórmula:
Para calcular a diferença entre dois horários nas células A8 e B8, utilize a seguinte fórmula:
=ABS(B8-A8)
Prima a tecla Enter para obter o resultado.
1,29 Calcular as horas úteis entre duas datas excluindo fins de semana
Por vezes, poderá ser necessário contar as horas úteis entre duas datas, excluindo os fins de semana (sábado e domingo).
Considerando que as horas úteis são fixas em 8 horas por dia, para calcular as horas úteis entre duas datas fornecidas nas células A16 e B16, utilize a seguinte fórmula:
=NETWORKDAYS(A16,B16) * 8
Prima a tecla Enter e, de seguida, formate o resultado como Geral ou Numérico.
Para mais exemplos sobre o cálculo de horas úteis entre duas datas, consulte Obter Horas Úteis Entre Duas Datas no Excel
Se tiver o Kutools para Excelinstalado no Excel, 90 % dos cálculos de diferença entre datas e horas podem ser resolvidos rapidamente — sem precisar de decorar fórmulas.
1,31 Calcular a diferença entre duas datas e horas com o Assistente de Dados e Hora
Para calcular a diferença entre duas datas e horas no Excel, basta utilizar o Assistente de Data e Hora.
1. Selecione uma célula onde pretende colocar o resultado calculado e clique em Kutools > Assistente de Fórmulas > Assistente de Data e Hora.
2. Na caixa de diálogo Assistente de Data e Hora que surge, efetue as seguintes definições:
- Marcar a opção Diferença;
- Selecione a data e hora inicial e final na secção Entrada de Argumento, podendo também introduzir manualmente a data e hora na caixa de entrada ou clicar no ícone do calendário para selecionar a data;
- Selecione o Tipo de resultado de saída em Lista suspensa;
- Pré-visualize o resultado na secção Resultado.
3. Clique em OK. O resultado calculado é apresentado; arraste a alça de preenchimento automático pelas células onde pretende aplicar o mesmo cálculo.
Dica:
Se pretender obter a diferença entre duas datas e horas e apresentar o resultado em dias, horas e minutos com Kutools para Excel, proceda da seguinte forma:
Selecione uma célula onde pretende colocar o resultado e clique em Kutools > Assistente de Fórmulas > Data e Hora > Contar dias, horas e minutos entre duas datas.
Depois, na caixa de diálogo Assistente de Fórmulas, especifique as datas de início e de término e, em seguida, clique em Ok.
E o resultado da diferença será apresentado em dias, horas e minutos.
Clique em Assistente de Data e Hora para saber mais sobre como utilizar esta funcionalidade.
Clique em Kutools para Excel para conhecer todas as funcionalidades deste extra.
Clique em Transferência Gratuitapara obter uma avaliação gratuita de 30 dias do Kutools para Excel
Se pretender contar rapidamente os fins de semana, dias úteis ou um dia da semana específico entre duas datas e horas, o grupo Assistente de Fórmulas do Kutools para Excel pode ajudá-lo.
1. Selecione a célula onde será colocado o resultado calculado e clique em Kutools > Estatísticas > 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 caixa de diálogo Assistente de Fórmulas que surge, especifique as datas de início e de término; se aplicar Número de dias da semana entre duas datas, terá também de especificar o dia da semana.
Para contar um dia da semana específico, consulte a nota sobre como utilizar os números de 1 a 7 para indicar domingo a sábado.
3. Clique em Ok e, em seguida, arraste a alça de autorreenchimento sobre as células onde for necessário contar o número de fins de semana, dias úteis ou um dia da semana específico.
Clique em Kutools para Excelpara descobrir todas as funcionalidades deste extra.
Clique em Transferência Gratuitapara obter uma avaliação gratuita de 30 dias do Kutools para Excel
2. Adicionar ou subtrair datas e horas
Além de calcular a diferença entre duas datas e horas, adicionar ou subtrair dias também é uma operação comum de data e hora no Excel. Por exemplo, pode precisar de determinar a data de vencimento com base na data de produção e no número de dias de conservação do produto.
2,11 Adicionar ou subtrair dias a uma data
Existem duas formas distintas de adicionar ou subtrair um número específico de dias a uma data.
Supondo que pretende adicionar 21 dias a uma data na célula A2, escolha um dos métodos abaixo para resolver o problema:
Método 1 data+dias
Selecione uma célula e introduza a fórmula:
=A+21
Prima a tecla Enter para obter o resultado.
Se pretender subtrair 21 dias, basta substituir o sinal de mais (+) pelo sinal de menos (–).
Método 2 Colar Especial
1. Introduza o número de dias que pretende adicionar numa célula, por exemplo na célula C2, e depois prima Ctrl+C para copiá-lo.
2. Em seguida, selecione as datas às quais pretende adicionar 21 dias, clique com o botão direito do rato para abrir o menu de contexto e escolha Colar Especial....
3. Na caixa de diálogo Colar Especial, assinale a opção Adicionar(se pretender subtrair dias, assinale a opção)Subtrair). Clique em OK.
4. Agora, as datas originais foram convertidas em números de cinco dígitos; formate-as novamente como datas.
2,12 Adicionar ou subtrair meses a uma data
Para adicionar ou subtrair meses a uma data, utilize a função DATAM.
Clique em DATAM para estudar os seus argumentos e utilização.
Supondo que pretende adicionar 6 meses à data na célula A2, utilize a fórmula seguinte:
=EDATE(A2,6)
Prima a tecla Enter para obter o resultado.
Se pretender subtrair 6 meses à data, altere 6 para -6.
2,13 Adicionar ou subtrair anos a uma data
Para adicionar ou subtrair n anos a uma data, utilize uma fórmula que combine as funções DATA, ANO, MÊS e DIA.
Supondo que pretende adicionar 3 anos à data na célula A2, utilize a fórmula seguinte:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Prima a tecla Enter para obter o resultado.
Se pretender subtrair 3 anos à data, substitua 3 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 pretende adicionar 4 semanas à data na célula A2, utilize a fórmula seguinte:
=A2+4*7
Prima a tecla Enter para obter o resultado.
Se pretender subtrair 4 semanas à data, substitua o sinal de mais (+) pelo sinal de menos (–).
2,15 Adicionar ou subtrair dias úteis incluindo ou excluindo feriados
Esta secção explica como utilizar a função DIA.TRABALHO para adicionar ou subtrair dias úteis a uma data específica, incluindo ou excluindo feriados.
Visite DIA.TRABALHO para saber mais detalhes sobre os seus argumentos e utilização.
Adicionar dias úteis incluindo feriados
Na célula A2 está a data que utiliza e na célula B2 encontra-se o número de dias que pretende adicionar; utilize a fórmula seguinte:
=WORKDAY(A2,B2)
Prima a tecla Enter para obter o resultado.
Adicionar dias úteis excluindo feriados
Na célula A5 está a data que utiliza, na célula B5 encontra-se o número de dias que pretende adicionar e no intervalo D5:D8 estão listados os feriados; utilize a fórmula seguinte:
=WORKDAY(A5,B5,D5:D8)
Prima a tecla Enter para obter o resultado.
Nota:
A função DIA.TRABALHO considera sábado e domingo como fim de semana. Se os seus dias de folga forem sábado e domingo, pode utilizar a função DIA.TRABALHO.INTL, que lhe permite definir quais os dias considerados fim de semana.
Visite DIA.TRABALHO.INTL para mais detalhes.
Para subtrair dias úteis a uma data, basta introduzir um número negativo na fórmula.
2,16 Adicionar ou subtrair ano, mês e dias específicos a uma data
Se pretender adicionar um ano, um mês e dias específicos a uma data, a fórmula que combina as funções DATA, ANO, MÊS e DIA pode ajudá-lo.
Para adicionar 1 ano, 2 meses e 30 dias a uma data em A11, utilize a fórmula seguinte:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Prima a tecla Enter para obter o resultado.
Se pretender subtrair, substitua todos os sinais de mais (+) por sinais de menos (-).
2,21 Adicionar ou subtrair horas/minutos/segundos a uma data e hora
Aqui estão algumas fórmulas para adicionar ou subtrair horas, minutos ou segundos de uma data e hora.
Adicionar ou subtrair horas a uma data e hora
Supondo que pretende adicionar 3 horas a uma data e hora (ou apenas a uma hora) na célula A2, utilize a fórmula seguinte:
=A2+3/24
Prima a tecla Enter para obter o resultado.
Adicionar ou subtrair minutos a uma data e hora
Supondo que pretende adicionar 15 minutos a uma data e hora (ou apenas a uma hora) na célula A5, utilize a fórmula seguinte:
=A2+15/1440
Prima a tecla Enter para obter o resultado.
Adicionar ou subtrair segundos a uma data e hora
Supondo que pretende adicionar 20 segundos a uma data e hora (ou apenas a uma hora) na célula A8, utilize a fórmula seguinte:
=A2+20/86400
Prima a tecla Enter para obter o resultado.
2,22 Somar horários superiores a 24 horas
Suponha que tem uma tabela do Excel com o registo do tempo de trabalho de todos os colaboradores numa semana. Para somar o tempo total de trabalho e calcular os pagamentos, pode utilizar SOMA(intervalo) para obter o resultado. Contudo, normalmente, o resultado da soma é apresentado como um tempo que não ultrapassa as 24 horas, tal como mostra a captura de ecrã abaixo. Como obter o resultado correto?
Na realidade, basta formatar o resultado como [hh]:mm:ss.
Clique com o botão direito na célula do resultado, escolha Definir formato de célula no menu de contexto e, na caixa de diálogo Definir formato de célula que surge, selecione Personalizado na lista e introduza [hh]:mm:ss na caixa de texto à direita. Clique em OK.
O resultado da soma será exibido corretamente.
2,23 Adicionar horas úteis a uma data excluindo fins de semana e feriados
Apresenta-se aqui uma fórmula abrangente para calcular a Data de Término, somando um número específico de horas úteis à Data de Início e excluindo fins de semana (sábados e domingos) e feriados.
Numa tabela do Excel, a célula A11 contém a data e hora de início, e a B11, as horas de trabalho. Nas células E11 e E13 estão definidos os horários de início e fim do expediente, e a célula E15 indica o feriado a ser excluído.
Utilize a seguinte 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))
Prima a tecla Enter para obter o resultado.
Se tiver o Kutools para Excel instalado, apenas uma ferramenta – Assistente de Data e Hora – resolve a maioria dos cálculos relacionados com a adição e subtração de datas e horas.
1. Clique numa célula onde pretende obter o resultado e aplique esta ferramenta ao selecionar Kutools > Assistente de Fórmulas > Assistente de Data e Hora.
2. Na caixa de diálogo Assistente de Data e Hora, ative a opção Adicionar ou a opção Subtrair, conforme necessário. Em seguida, selecione a célula ou introduza diretamente a data/hora que pretende utilizar na secção Entrada de Argumento. Depois, especifique os anos, meses, semanas, dias, horas, minutos e segundos que deseja adicionar ou subtrair e clique em Ok. Veja a captura de ecrã:
Pode pré-visualizar o resultado calculado na secção Resultado.
O resultado já está apresentado. Basta arrastar a alça de autorreenchimento para outras células e obter os restantes resultados.
Clique em Assistente de Data e Horapara saber mais sobre como utilizar esta funcionalidade.
Clique em Kutools para Excelpara descobrir todas as funcionalidades deste extra.
Clique em Transferência Gratuitapara obter uma avaliação gratuita de 30 dias do Kutools para Excel
2,41 Verificar ou destacar se uma data está expirada
Se existir uma lista de Data de Expiração de produtos, poderá querer verificar e destacar as datas que já expiraram com base na data atual, tal como mostra a captura de ecrã abaixo.
Na realidade, o recurso Usar Formatação Condicional resolve rapidamente esta tarefa.
1. Selecione as datas que pretende verificar e clique em Base > Utilizar Formatação Condicional > Nova Regra.
2. Na caixa de diálogo Nova Regra de Formatação, selecione Utilizar uma fórmula para determinar as células a formatar na secção Selecionar um Tipo de Regra e introduza =B2<HOJE() na caixa de entrada (B2 é a primeira data que pretende verificar). Clique em Formatar para abrir a caixa de diálogo Definir formato de célula e escolha uma formatação distinta para destacar as datas de expiração, conforme necessário. Clique em OK > OK.
2,42 Devolver o último dia do mês atual/primeiro dia do Próximo Mês
A Data de Expiração de alguns produtos corresponde ao último dia do mês de produção ou ao primeiro dia do mês seguinte. Para listar rapidamente as datas de expiração com base na data de produção, siga esta parte.
Obter o último dia do mês atual
Está aqui uma data de produção na célula B13. Utilize a seguinte fórmula:
=EOMONTH(B13,0)
Prima a tecla Enter para obter o resultado.
Obter o 1.º dia do Próximo Mês
Está aqui uma data de produção na célula B18. Utilize a seguinte fórmula:
=EOMONTH(B18,0)+1
Prima a tecla Enter para obter o resultado.
3. Calcular idade
Esta secção apresenta métodos para calcular a idade com base numa data específica ou num número de série.
3,11 Calcular idade com base na data de nascimento fornecida
Obter a idade em número decimal com base na data de nascimento
Clique em FRAÇÃOANO para obter detalhes sobre os seus argumentos e utilização.
Por exemplo, para obter as idades com base na lista de datas de nascimento na coluna B2:B9, utilize a seguinte fórmula:
=YEARFRAC(B2,TODAY())
Prima a tecla Enter e, de seguida, arraste a alça de autorreenchimento para baixo até que todas as idades estejam calculadas.
Dica:
1) Pode especificar o número de casas decimais conforme necessário na caixa de diálogo Definir formato de célula.
2) Se pretender calcular a idade numa data específica com base numa data de nascimento fornecida, substitua HOJE() pela data específica entre aspas duplas, por exemplo =YEARFRAC(B2,«[[PH_324]]»)
3) Se pretender obter a idade no Próximo Ano com base na data de nascimento, basta adicionar 1 à fórmula, por exemplo: =FRAÇÃOANO(B2;HOJE())+1.
Obter a idade em número inteiro com base na data de nascimento
Clique em DATADIF para obter detalhes sobre os seus argumentos e utilização.
Usando o exemplo anterior, para obter a idade com base nas datas de nascimento listadas em B2:B9, utilize a seguinte fórmula:
=DATEDIF(B2,TODAY(),«y»)
Prima a tecla Enter e, em seguida, arraste a alça de autorreenchimento para baixo até que todas as idades estejam calculadas.
Dica:
1) Se pretender calcular a idade numa data específica com base numa data de nascimento fornecida, substitua HOJE() pela data pretendida entre aspas duplas, por exemplo: =DATEDIF(B2,«[[PH_329]]»,«y»).
2) Se pretender obter a idade no próximo ano com base na data de nascimento, basta adicionar 1 à fórmula — por exemplo: =DATEDIF(B2;TODAY();«y»)+1.
3,12 Calcular a idade em anos, meses e dias com base na data de nascimento fornecida
Se pretender calcular a idade com base numa data de nascimento fornecida e apresentar o resultado como xx anos, xx meses e xx dias, tal como mostra a captura de ecrã abaixo, aqui está uma fórmula longa que o pode ajudar.
Para obter a idade em anos, meses e dias com base na data de nascimento na célula B12, utilize a seguinte fórmula:
=DATEDIF(B12,TODAY(),«Y»)&« Years, »&DATEDIF(B12,TODAY(),«YM»)&« Months, »&DATEDIF(B12,TODAY(),«MD»)&« Days»
Prima a tecla Enter para obter a idade e, em seguida, arraste a alça de autorreenchimento para outras células.
Dica:
Se pretender calcular a idade numa data específica com base numa data de nascimento fornecida, substitua HOJE() pela data pretendida entre aspas duplas, por exemplo: =DATEDIF(B12,«[[PH_337]]»,«Y»)&« Anos, »&DATEDIF(B12,«1/1/2021»,«YM»)&« Meses, »&DATEDIF(B12,«1/1/2021»,«MD»)&« Dias».
3,13 Calcular a idade com base na data de nascimento anterior a 1/1/1900
No Excel, não é possível introduzir nem calcular corretamente datas anteriores a 1/1/1900. Contudo, se pretender calcular a idade de uma pessoa famosa com base na data de nascimento (anterior a 1/11900) e na data de falecimento, apenas um código VBA o poderá ajudar.
1. Prima as teclas Alt+F11 para abrir a janela do Microsoft Visual Basic for Applications, clique no separador 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 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. Guarde o código, regresse à folha e selecione uma célula para inserir a idade calculada. Digite =AgeFunc(datadenascimento,datadefalecimento); neste caso, =AgeFunc(B22,C22). Prima Enter para obter a idade e, se necessário, utilize a alça de preenchimento automático para aplicar esta fórmula a outras células.
Se tiver o Kutools para Excel instalado no Excel, pode utilizar a ferramenta Assistente de Data e Hora para calcular a idade.
1. Selecione uma célula onde pretende inserir a idade calculada e clique em Kutools > Assistente de Fórmulas > Assistente de Data e Hora.
2. Na caixa de diálogo Assistente de Data e Hora,
- 1) Marcar a opção Idade;
- 2) Escolha a célula da data de nascimento ou introduza diretamente a data de nascimento ou clique no ícone do calendário para selecionar a data de nascimento;
- 3) Escolha a opção Hojese pretender calcular a idade atual; escolha a opção Data específicae introduza a data se pretender calcular a idade no passado ou no futuro;
- 4) Especifique o tipo de saída a partir do Lista suspensa;
- 5) Pré-visualize o resultado. Clique em OK.
Clique em Assistente de Data e Horapara saber mais sobre a utilização desta funcionalidade.
Clique em Kutools para Excel para conhecer todas as funcionalidades deste suplemento.
Clique em Transferência Gratuitapara obter uma avaliação gratuita de 30 dias do Kutools para Excel
3,31 Obter data de nascimento a partir do número de identificação
Se tiver uma lista de números de identificação cujos primeiros 6 dígitos correspondem à data de nascimento — por exemplo, 920315330 indica a data de nascimento 15/03/1992 — como pode extrair rapidamente essa data para outra coluna?
Vamos agora considerar como exemplo a lista de números de identificação que começa na célula C2 e utilizar a seguinte fórmula:
=MID(C2,5,2)&«/»&MID(C2,3,2)&«/»&MID(C2,1,2)
Prima a tecla Enter. Em seguida, arraste a alça de preenchimento automático para baixo para obter os restantes resultados.
Nota:
Na fórmula, pode ajustar as referências conforme necessário. Por exemplo, se o número de identificação for 13219920420392 — cuja data de nascimento é 04/20/1992 — basta 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 existir uma lista de números de identificação cujos primeiros 6 dígitos indicam a data de nascimento — por exemplo, 920315330 corresponde a 15/03/1992 —, como calcular rapidamente a idade com base em cada número de identificação no Excel?
Vamos agora considerar como exemplo a lista de números de identificação que começa na célula C2 e utilizar a seguinte 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»)
Prima a tecla Enter. Depois, arraste a alça de preenchimento automático para baixo para obter os restantes resultados.
Nota:
Nesta fórmula, se o ano for inferior ao ano atual, considera-se que começa por 20 — por exemplo, 20203943 será interpretado como o ano 2020. Se for superior ao ano atual, considera-se que começa por 19 — por exemplo, 920420392 será interpretado como o ano 1992.
Mais tutoriais do Excel:
Combinar vários livros de trabalho ou folhas de cálculo num só
Este tutorial aborda praticamente todos os cenários de combinação com que se possa deparar e oferece soluções profissionais adaptadas a cada caso.
Dividir células de texto, número e data (separar em múltiplas colunas)
Este tutorial está dividido em três partes: dividir células de texto, dividir células de número e dividir células de data. Cada secção inclui exemplos práticos para o ajudar a resolver com facilidade situações semelhantes.
Combinar conteúdos de várias células sem perder dados no Excel
Este tutorial foca-se na extração de um elemento numa posição específica de uma célula e reúne diferentes métodos para ajudá-lo a extrair texto ou números com base na posição exata dentro da célula no Excel.
Comparar duas colunas para correspondências e diferenças no Excel
Este artigo aborda a maioria dos cenários que poderá encontrar ao comparar duas colunas e pretende ajudá-lo.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel Resolve a Maior Parte dos Seus Problemas e Aumenta a Sua Produtividade em 80 %
- Super Barra de Fórmulas (edite facilmente várias linhas de texto e fórmulas); Disposição de Leitura (leia e edite com facilidade um grande número de células); Colar em Intervalo de Filtro...
- Mesclar Células, Linhas ou Colunas e Manter os Dados; Dividir o Conteúdo das Células; Combinar Linhas Duplicadas e Calcular Soma/Média... Evitar Entradas Duplicadas nas Células; Comparar Intervalos...
- Selecionar linhas duplicadas ou únicas;Selecionar linhas em branco (todas as células vazias);Super Localizar e Pesquisa Aproximada em vários livros; seleção aleatória...
- Cópia Exata de múltiplas células sem alterar a referência da fórmula;Criar Referências Automaticamente para várias folhas;Inserir Marcadores, caixas de seleção e muito mais...
- Fórmulas Favoritas e Inserção Rápida, intervalos, gráficos e imagens; Criptografar Células com palavra-passe; Criar Lista de Correspondência e envie e-mails...
- Extrair Texto, adicionar texto, remover caracteres de uma posição, remover espaços; criar e imprimir estatísticas da página de dados; converter entre conteúdo das células e comentários...
- Super Filtro (guarde e aplique esquemas de filtro noutras folhas); Classificação Avançada por mês, semana, dia, frequência e muito mais; Filtro Especial por negrito, itálico…
- Combinar Livros e Folhas de Cálculo; mesclar tabelas com base numa coluna-chave; dividir dados em várias folhas; conversão automática de xls, xlsx e PDF...
- Agrupamento por Tabela Dinâmica número da semana, dia da semana e muito mais...Mostrar desbloqueadas, bloquear seleção com cores diferentes;Destacar células que contêm fórmula/nome...
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
