Cálculos com data no Excel

Nesta postagem apresentamos algumas soluções alternativas para tratar com datas no Excel. Baixe a planilha gratuita para usuários cadastrados.

Planilha protegida sem senha. Funciona na maioria dos programas de planilhas. Use-a protegida para evitar apagamento acidental de fórmulas e dados. O cadastro gratuito é no site Saber sem filtro.

Identificar o dia da semana de uma data

Veja como identificar o dia da semana associado a uma data no Excel usando um caminho alternativo que dispensa a função DIA .DA.SEMANA. Para entender melhor baixe a planilha:

Usando uma data de referência

Para associar uma data ao seu dia respectivo dia da semana precisamos de uma referência. Vamos adotar 04-03-1900 que é o primeiro domingo em que o Excel faz a associação correta entre data e dia da semana.

A sequência de cálculo é a seguinte:

1. Tire a diferença entre a data procurada e a data de referência (04-03-1900). O resultado é o número de dias transcorridos no intervalo.
2. Use a função MOD que retorna o resto de uma divisão. Os argumentos são o número de dias do intervalo e a divisão é por sete. Isso nos fornece o número de dias avulsos que sobram além das semanas completas do intervalo.
3. Some os dias avulsos com 1 que corresponde ao domingo da data de referência.
4. Use uma função PROCV para associar o dia de semana em forma numérica com o nome do dia da semana.

Você pode resolver tudo em uma única fórmula como esta:

=PROCV(MOD(B3-B6;7)+B7;E4:F10;2;0)

Evite janeiro e fevereiro de 1900

O Excel considera erroneamente 1900 como ano bissexto. Em função disso, os dias da semana de janeiro e fevereiro de 1900 não são consistentes no Excel. Usando a função DIA.DA.SEMANA ou tirando a diferença entre datas desse período ocorrem erros, pois para o Excel 29-02-1900 é uma data válida.

Conversão de datas para número serial

O Excel guarda datas na memória na forma de números seriais. O número 36891, por exemplo, representa a data 31-12-2000. Para o Excel 01-01-1900 é a data 1. Essa forma de tratar datas como números seriais permite fazer cálculos com datas facilmente. Um exemplo: basta tirar a diferença entre dois números seriais para saber quantos dias transcorreram entre as duas datas correspondentes.

Para fazer a conversão de um número serial em data e vice-versa é preciso conhecer as regras do calendário gregoriano. Primeiramente temos que considerar o número de dias de cada mês.

Jan	31
Fev	28/29
Mar	31
Abr	30
Mai	31
Jun	30
Jul	31
Ago	31
Set	30
Out	31
Nov	30
Dez	31

O segundo problema é determinar quando ocorrem os anos bissextos, aqueles em que temos o dia 29 de fevereiro. São bissextos os anos múltiplos de 4 como 2012 e 2016. Fogem à regra os anos que também são múltiplos de 100 e não são múltiplos de 400. Exemplos: 1900 e 2100.

Método estendido

Adotamos um método de conversão que gera números seriais iguais ao do Excel na maioria dos casos. Só ocorre uma diferenciação em datas anteriores a 01-03-1900.  Isso ocorre por dois motivos: o Excel não trabalha com seriais negativos para datas e tem uma inconsistência ao tratar o ano de 1900 como bissexto.

Pelo método que propomos em datas anteriores a 01-03-1900 o número serial não bate com o Excel, mas tem a vantagem de desconsiderar 29-02-1900 que é aceito pelo Excel. Além disso, dá resultado para datas anteriores a 01-01-1900. O método pode ser usado até 15-10-1582, quando foi inaugurado o calendário gregoriano. Para datas mais antigas é preciso usar um método que considere as regras do calendário Juliano que é o calendário da Antiguidade.

Data	Serial Excel	Serial estendido
15-10-1582		-115859
…	…	…
29-12-1899		-2
30-12-1899		-1
31-12-1899		1
01/01/1900	1	2
02/01/1900	2	3
03/01/1900	3	4
04/01/1900	4	5
05/01/1900	5	6
06/01/1900	6	7
07/01/1900	7	8
08/01/1900	8	9
09/01/1900	9	10
10/01/1900	10	11
11/01/1900	11	12
12/01/1900	12	13
13/01/1900	13	14
14/01/1900	14	15
15/01/1900	15	16
16/01/1900	16	17
17/01/1900	17	18
18/01/1900	18	19
19/01/1900	19	20
20/01/1900	20	21
21/01/1900	21	22
22/01/1900	22	23
23/01/1900	23	24
24/01/1900	24	25
25/01/1900	25	26
26/01/1900	26	27
27/01/1900	27	28
28/01/1900	28	29
29/01/1900	29	30
30/01/1900	30	31
31/01/1900	31	32
01/02/1900	32	33
02/02/1900	33	34
03/02/1900	34	35
04/02/1900	35	36
05/02/1900	36	37
06/02/1900	37	38
07/02/1900	38	39
08/02/1900	39	40
09/02/1900	40	41
10/02/1900	41	42
11/02/1900	42	43
12/02/1900	43	44
13/02/1900	44	45
14/02/1900	45	46
15/02/1900	46	47
16/02/1900	47	48
17/02/1900	48	49
18/02/1900	49	50
19/02/1900	50	51
20/02/1900	51	52
21/02/1900	52	53
22/02/1900	53	54
23/02/1900	54	55
24/02/1900	55	56
25/02/1900	56	57
26/02/1900	57	58
27/02/1900	58	59
28/02/1900	59	60
01/03/1900	61	61
02/03/1900	62	62
03/03/1900	63	63
…	…	…
31/12/2000	36891	36891
31/12/9999	2958465	2958465

Identificar o século de um ano

Uma maneira simples de definir o século de um ano é pensar no ano imediatamente superior múltiplo de 100 e retirar os dois zeros finais. Exemplo: 1789. O múltiplo de 100 imediatamente acima é 1800, logo 1789 pertence ao século 18 (XVIII) Para resolver o problema no Excel a regra é simples:

1. Divida o ano por 100.
2. Se a divisão der exata o resultado da divisão é o século procurado. Exemplo: 1600 dividido por 100 resulta exatamente em 16, logo 1600 pertence ao século XVI.
3. Se a divisão tiver resto considere apenas a parte inteira e adicione 1. Exemplo: 1985 dividido por 100 resulta em 19 inteiros e 85 de resto. Logo o século é 19 + 1 = 20. Século XX.

A determinação do século a partir do ano pode ser resolvida no Excel com uma única fórmula:

=ROMANO(SE(MOD(C4;100)=0;C4/100;INT(C4/100) +1);0)&” “&D4

onde C4 é a célula que contém o ano pesquisado e D4 informa se a data é a.C. ou d.C.

A fórmula utiliza as funções:

• MOD: fornece o resto de uma divisão.
• INT: fornece a parte inteira de um número decimal.
• ROMANO: converte um número em notação arábica para romana.

Assista no YouTube:

Como identificar anos bissextos no Excel

As regras que usamos hoje em dia para definir os anos bissextos, aqueles com 366 dias, foram adotadas em 1582, ano em que entrou em vigor o novo calendário ocidental a mando do papa Gregório XIII. No calendário anterior (Juliano) os anos bissextos já existiam, mas o modelo usado na Antiguidade não assegurava sincronia perfeita entre os dias do ano e o movimento da terra ao redor do sol. O calendário gregoriano utiliza duas regras para os anos bissextos:

1. São bissextos os anos múltiplos de 4 como 2012, 2016 e 2020.
2. São exceção à primeira regra anos que também sejam múltiplos de 100, mas não sejam múltiplos de 400.

Na prática, a maioria dos anos terminados em 00 não são bissextos, embora sejam múltiplos de 4. Somente a cada 400 anos é que o ano que finaliza o século é bissexto. Veja a imagem:

Calculadora de anos bissextos

Na planilha disponível para download você encontra a calculadora de anos bissextos e entende como é realizado o cálculo.

Para uma consulta rápida segue uma lista de anos bissextos recentes:

• 1980
• 1984
• 1988
• 1992
• 1996
• 2000
• 2004
• 2008
• 2012
• 2016
• 2020
• 2024
• 2028
• 2032
• 2034
• 2036
• 2040
• 2044
• 2048
• 2052

Fica a dica: as olimpíadas acontecem em anos bissextos.