Manipulação de Data - excel

Introdução

O objetivo desta página é fornecer exemplos de cálculos comuns que envolvem datas e explicar como o Excel trata datas durante os cálculos.
Uma data é nada mais que um número. Mais precisamente, uma data é um número de série que representam o número de dias transcorridos desde a data fictícia de 0 de Janeiro de 1900. Logo, um número de série 1 corresponde a 1 de Janeiro de 1900. O número 2 corresponde a 2 de Janeiro de 1900, e assim por diante, até o ponto em que 2.958.465 corresponde a 31 de Dezembro de 9999.
Para saber que dia, mês e ano um número de série situa, basta escrever um número na Planilha e em seguida formatar a célula como data.
Obviamente, se 1 corresponde a um dia, 0.5 corresponde à 12 horas e:
Uma hora é equivalente a 1/24
Um minuto é equivalente a 1/(24*60)
Um segundo é equivalente a 1/(24*60*60)



Obter Data Atual
Use a fórmula =HOJE() para mostrar a data atual.
Você também pode usar a função TEXTO para mostrar a data como quiser, como em:

A fórmula utilizada foi:
="Hoje é " & TEXTO(HOJE();"dddd, d, mmmm, aaaa")

Mostrar uma Data Específica

Use a função DATA(ano;mês;dia) para obter uma data específica:

A fórmula em A8 é:
=DATA(D8;C8;B8)
A função DATA tem a vantagem de corrigir automaticamente valores excedentes de dias e meses de uma data, exemplo:

A fórmula em A12 é
=DATA(2011;20;1)
No entanto, como o valor dos meses é maior que 12, o Excel automaticamente adiciona um ano à data final e mostra uma data válida (já que 20 meses é igual a um ano e 8 meses). Essa função é muito útil quando se deseja, por exemplo responder à pergunta: Qual será a data daqui a 100 dias, a partir de hoje?
Respondendo a pergunta, basta fazer uma conta como:
=DATA(ANO(HOJE());MÊS(HOJE());DIA(HOJE()+100))

Criar uma Série de Datas

Utilizando o princípio anterior, para criar uma série de datas (por exemplo, separadas por dois meses), basta escrever na célula A14 a data de início e, em A15, escrever a fórmula:

=DATA(ANO(A14);MÊS(A14)+2;DIA(A14))
Depois, basta arrastar a fórmula até onde desejar.
Outro exemplo: criar lista de datas com apenas dias da semana (excluindo sábados e domingos):

A fórmula em A29 é:
=SE(DIA.DA.SEMANA(A28)=6;A28+3;A28+1)
Depois, basta arrastar a fórmula para baixo. Observe que a formatação da coluna B está como ddd, para mostrar os dias da semana de uma data.

Calcular Número de Dias Transcorridos entre Duas Datas

Já que datas são números de série, basta fazer a diferença entre duas datas para obter o número de dias entre elas. Veja duas formas de se fazer isso:

=A47-A46
=A47-A46+1
Normalmente, no dia-a-dia, usa-se mais a segunda forma porque a noção de diferença entre duas datas contempla as datas de início e de término como um dia aproveitado. Por definição matemática, a operação de subtração elimina uma ponta, logo, deve-se somar 1 nessa fórmula.

Dias Úteis

O Excel possui a função de calcular o número de dias trabalhados entre duas datas, chamada DIATRABALHOTOTAL. Adicionalmente, é possível que essa função exclua dos dias trabalhados uma lista de feriados personalizados. Observe a figura abaixo:

A fórmula de DIATRABALHOTOTAL é:
=DIATRABALHOTOTAL(primeiro_dia;último_dia;lista_exceções)
A fórmula em C15 é:
=DIATRABALHOTOTAL(A15;B15;$A$2:$A$11)

E se quisermos saber o contrário? Suponha: qual data será daqui a 10 dias úteis a partir de 25/04/2011?
Enquanto a função DIATRABALHOTOTAL retorna o número de dias trabalhados, a função DIATRABALHO retorna a data de término. Obviamente, DIATRABALHO exclui sábados e domingos. Veja o exemplo a seguir:

Logo, 10 dias úteis a partir de 25/04/2011 será dia 09/05/2011.
No entanto, tome muito cuidado ao usar a forma presente em B56:
=DIATRABALHO("25/04/2011";10)
Não use data dessa forma porque se uma Pasta de Trabalho for aberta com o Excel numa versão em inglês, a célula retornará um erro, já que o formato de data americano é mm/dd/aaaa e então a data acima seria reconhecida como inválida.
Prefira usar as fórmulas mostradas em B54 e B56:
=DIATRABALHO(A53;10)
=DIATRABALHO(DATA(2011;4;25);10)

Calcular a Idade de uma Pessoa
A fórmula mostrada abaixo exibe a idade atual de uma pessoa:

=INT(FRAÇÃOANO(A1;HOJE();1))
A função HOJE() retorna a data atual do sistema. O parâmetro 1 diz respeito a base do ano, isto é, quantos dias é para se considerar que tenha o ano. No nosso caso exposto, 1 representa o número real de dias para cada ano, respeitando anos bissextos.
Observe que não poderia ser usado a fórmula:
=ANO(HOJE())-ANO(A1)
Essa não calcula o número de anos completos transcorridos de uma data até a outra, e sim apenas uma operação simples de diferença.

A função DATADIF
No Excel, existe uma função muito útil chamada DATADIF. Um fato curioso é que ela não aparece na lista de fórmulas quando digitada:

No entanto, você pode digitá-la que o Excel irá interpretar resultado. A função DATADIF retorna a diferença entre duas datas numa unidade que você especificar. Veja a figura abaixo:

A fórmula em D4 é:
=DATADIF(A4;B4;"d")

Dia do Ano e Dias Restantes para Fim do Ano

Observe a figura abaixo:

Sabemos que 3 de Janeiro é o terceiro dia do ano, mas em relação a 10 de Maio?
Em A61:
=A60-DATA(ANO(A60);1;0)
E para saber quantos dias faltam para terminar o ano a partir de 10 de Maio, escreva em A65:
=DATA(ANO(A64);12;31)-A64

Determinar Dia da Semana

Observe a figura abaixo

As fórmulas, que neste caso são equivalentes, são:
=DIA.DA.SEMANA(A69)
=DIA.DA.SEMANA(DATA(2011;12;6))
A função DIA.DA.SEMANA retorna o dia da semana de uma data. Como temos 7 dias na semana, os números retornados vão de 1 a 7 e cada número representa um dia da semana, de acordo com a tabela abaixo:

Então, 3, que foi retornado pela fórmula anterior, significa que 06/12/2011 foi uma terça-feira.

Dias do Mês

Veja a figura abaixo:

Em A85 há uma fórmula para retornar a data do último dia do mês definido pelo mês e ano de A84:
=DATA(ANO(A84);MÊS(A84)+1;0)
A fórmula em A89 retorna o número de dias que o mês da data definida por A88 possui:
=DIA(DATA(ANO(A88);MÊS(A88)+1;0))

Determinar Semana do Ano

Em A104 há uma fórmula para retornar qual é o número da semana transcorrida em relação a seu ano. Assume que 1 é a semana que começa no dia 1 de Janeiro do ano, seja qual for o dia da semana. A fórmula é:
=TRUNCAR(((A103-DATA(ANO(A103);1;0))+6)/7)

Descobrir se Ano é Bissexto

Observe a figura abaixo:

A fórmula em A94, que retorna VERDADEIRO ou FALSO no caso do ano da data definida em A93 ser um ano bissexto é:

=SE(MÊS(DATA(ANO(A93);2;29))=2;VERDADEIRO;FALSO)

Atenção: O Excel acusa, erroneamente, que o ano 1900 foi bissexto, quando isso é falso. Veja a última seção desta página para entender a causa disso.

Determinar Trimestre de Data

Observe a figura abaixo:

A fórmula em A99 é:
=ARREDONDAR.PARA.CIMA(MÊS(A98)/3;0)
Essa fórmula retorna os números 1, 2, 3 ou 4, dependendo do trimestre em que a data de A98 se encontra.

Obter Datas Especiais Especiais

Problemas e Cuidados com Datas
Atenção às seguintes particularidades com datas no Excel:

Bug do Ano Bissexto
O Excel reconhece, errôneamente, que o ano 1900 foi um ano bissexto. A origem desse erro está no Lotus 1-2-3, e quando a Microsoft decidiu fazer o Excel, preservaram esse erro senão todas datas de todas as Pastas de Trabalho atrasariam um dia. Na prática, atualmente, esse erro quase não atrapalha usuários do Excel porque não é comum trabalhar em Planilhas com datas do ano de 1900.

Datas Anteriores à 1900
Conforme dito no início desta página, datas são números seriais, e o modelo do Excel define que são datas válidas dias a partir da fictícia data de 0 de Janeiro de 1900. Não é possível representar datas anteriores à 1900 por meios convencionais. Se tentar escrever uma data como, por exemplo, 1856, o Excel simplesmente tratará essa entrada como um texto.

Sistemas de Data 1900 e 1904

O Excel suporta dois sistemas de data: O 1900 e o 1904. O sistema 1900 define o número 1 como a data 1 de Janeiro de 1900, e o sistema 1904 define o número 1 como a data 1 de Janeiro de 1904. Por padrão, o Excel para Windows usa o sistema 1900 e o Excel para Macintosh usa o sistema 1904. Para ambas as plataformas, o Excel faz compatibilidade para mostrar a data correta na Pasta de Trabalho ao usuário.
Um cuidado que deve ser tomado é em relação à Pastas de Trabalho linkadas: se uma Pasta de Trabalho com sistema 1900 está linkada à uma de sistema 1904, as datas mostradas estarão adiantadas em 4 anos. Ambas Pastas de Trabalho possuem o mesmo número de série para a data, mas o Excel interpreta a data de acordo com o sistema da Pasta de Trabalho aberta.
A vantagem de se usar o sistema de datas 1904 é a possibilidade de expressar datas negativas, obtidas através da diferença entre datas ou horas. O sistema 1900 não permite isso.

Gerar um Calendário Dinamicamente

Atenção: Para entender este exemplo, é necessário que você domine Fórmulas Matriciais.
Observe a Planilha abaixo:

Se você alterar o conteúdo da célula B2, isto é, a data de referência, os dias no calendário do intervalo B4:H9 irão se distribuir automaticamente, de acordo com seu dia na semana. Para obter esse efeito, foi utilizada uma fórmula matricial. Nesse exemplo, selecione o intervalo B4:H9, e com esse intervalo selecionado, digite a fórmula abaixo:
=SE(MÊS(DATA(ANO(B2);MÊS(B2);1))<>MÊS(DATA(ANO(B2);MÊS(B2);1) - (DIA.DA.SEMANA(DATA(ANO(B2);MÊS(B2);1))-1) + {0;1;2;3;4;5}*7+{0\1\2\3\4\5\6});"";DATA(ANO(B2);MÊS(B2);1) - (DIA.DA.SEMANA(DATA(ANO(B2);MÊS(B2);1))-1) + {0;1;2;3;4;5}*7+{0\1\2\3\4\5\6})
Atenção: se seu Excel for 2007, troque o símbolo \ por .