Guia Completo para Datas e Horas no Excel

Pessoal, horas e datas são um dos tipos de dados que mais encontramos no Excel, mas diferente de lidar com número ou caracteres é preciso entender como o Excel trata esses dados para não se sentir frustrado, especialmente se você está aprendendo.

Uma das dificuldades é o padrão numérico que o Excel adota para representar meses, dias, anos, horas, minutos ou segundos. Fica um pouco mais complicado quando vemos que datas também podem ser representadas por números que são os dias da semana e etc. Além disso são diversas as funções que trabalham com data e hora com descrições não muito intuitivas.

Então, para sanar todas suas dúvidas sobre como funcionam horas e datas no Excel reuni todas as informações neste único artigo.

Nas próximas seções você encontrará explicação para as principais fórmulas usadas, soma, subtração, multiplicação e divisão com datas e horas no Excel.

Como o Excel Armazena Datas

A maior fonte de confusão com datas no Excel ocorre por desconhecermos a forma como o programa armazena essa informação. Você espera que o programa registre dia, mês, e ano separados por “/” ou “-“, mas não é assim que funciona.

O Excel armazena datas como uma série numérica que representa o número de dias que passaram desde o ano 1900. Por exemplo, 01/01/1900 é o número 1, então o número 2 é 02/01/1900. E hoje que dia seria? 43464 equivale a 30/12/2018.

Para checar basta alterar a formatação da sua célula de data para número que verá o dia numérico que o Excel registra.

Concluímos que datas inferiores a 01/01/1900 não são reconhecidas no Excel como “datas”, logo se entrarmos em uma célula com 01/01/1875 teremos apenas texto.

Parece confuso armazenas datas neste formato, mas o motivo é que fica muito mais fácil computacionalmente falando realizar operações de soma, subtração, contar dias e etc.

Como o Excel Armazena Horas

O Excel armazena horas usando o mesmo formato de série numérica que vimos para datas. Os dias começam às 00h ou 12am. Então cada hora é 1/24 do dia, logo é representado pelo valor decimal: 0.04166…

Isso significa que a data e hora 30/12/2018 09:00 é armazenada como:

43464 + 9*0.04166 = 43464.375 

Quando específicamos ao Excel um horário sem data, o Excel armazena como se a data fosse a inicial, ou seja, 01/01/1900.

Isso pode ser um problema: imagine que estamos subtraindo 6 horas de 3 horas, ou seja, 01/01/1900 06:00 menos 01/01/1900 03:00. O resultado numérico aproximado seria: 0.125 – 0.25 = -0.125. Um valor negativo que é interpretado como um erro, pois a formatação de datas e horas no Excel não permite negativos. Então teremos na célula o chato #######.

Minutos e segundos tem o mesmo tratamento no Excel.

Se um minuto é 1/60 da hora, que por sua vez é 1/24 do dia, então um minuto é 1/1440 do dia, algo em torno de 0.0006944…

Se um segundo é 1/60 do minuto, que por sua vez é 1/1440 do dia, então o segundo é 1/86400 do dia, algo em torno de 0.00001157407…

Pronto, agora que entendemos como datas e horas são armazenadas no Excel fica tudo mais fácil.

Como Usar as Funções DATA() e TEMPO() no Excel

Vimos nas seções anteriores que o Excel armazena datas e horas como série numéricas, entretanto esse formato não é amigável. Portanto, o Excel tem algumas funções que tornam o uso de datas e horas mais intuitivo.

As funções DATA() e TEMPO() funcionam da seguinte maneira:

=DATA(ano;mês;dia)

=TEMPO(hora;minuto;segundo)

Por exemplo, se quisermos especificar o dia de hoje em uma célula poderíamos digitar: =DATA(18;12;30), ou seja 30/12/2018. Caso eu deseja-se digitar o horário 09:00 seria =TEMPO(9;0;0)

O Excel automaticamente formata a célula para exibir o formato de datas e horas quando utilizamos estas fórmulas, então não veremos os seus valores como números.

Se for necessário exibir 30/12/2018 09:00 seu equivalente nestas duas funções seria: =DATA(18;12;30)+TEMPO(9;0;0)

Como Usar a Função HOJE() no Excel

A função HOJE() no Excel é uma alternativa para facilitar a entrada da data de, obviamente, hoje em uma célula. É uma função simples que não tem nenhum parâmetro.

=HOJE()

Por exemplo, ao invés de colocar em uma célula a função =DATA(18;12;30) que é o dia de hoje, poderia simplesmente escrever =HOJE() que seria seu equivalente atual.

ATENÇÃO: Essa função é recalculada toda vez que o Excel realiza uma operação. Muito cuidado ao inseri-la dentro de outras fórmulas como parâmetro de cálculo, porque, a depender do tamanho dos cálculos, pode deixar sua planilha lenta. Ou mais grave você pode concluir resultados incorretos acreditando que ela registrou data e hora anteriores.

Como Usar a Função AGORA() no Excel

De forma similar a função HOJE(), a função AGORA() não tem parâmetros.

=AGORA()

A diferença entre as duas é que a função AGORA() além de exibir a data atual exibe também o tempo no formato de data padrão do Excel brasileiro: dd/mm/aa hh:mm:ss (dias, mês, ano, hora, minuto, segundo).

ATENÇÃO: Essa função é recalculada toda vez que o Excel realiza uma operação. Muito cuidado ao inseri-la dentro de outras fórmulas como parâmetro de cálculo, porque, a depender do tamanho dos cálculos, pode deixar sua planilha lenta. Ou mais grave você pode concluir resultados incorretos acreditando que ela registrou data e hora anteriores.

Como Usar a Função DATAM() no Excel

A função DATAM() é mais uma facilidade que o Excel nos oferece para trabalhar com datas. Esta função calcula qual a data exata estaremos após decorridos uma certa quantidade de meses de uma data especificada. Os parâmetros da função são os seguintes:

=DATAM(data_inicial;quantidade_de_meses)

O uso da fórmula é simples agora que entendemos que as datas para o programa Excel são um número. Logo se colocarmos em uma célula =DATAM(30/12/2018;3) o resultado não seria 30/03/2018, ou seja 2 meses depois. O motivo é que 30/12/2018 não é o formato que o Excel reconhece como uma data propriamente dita. Então se usarmos =DATAM(43464;3) teremos o resultado correto.

Mas utilizar o formato numérico é pouco intuitivo não é? Sim, a solução é formatar uma outra célula para data, inserir nela a data desejada, então utilizar =DATAM(A1;3), por exemplo.

O função DATAM() não converte automaticamente a célula para o formato de data, então é necessário faze-lo manualmente

Como Usar a Função FIMMÊS() no Excel

A função FIMMÊS() é semelhante a função explicada na seção anterior. Os parâmetros que recebe são os mesmos. Entretanto seu objetivo é relativamente diferente.

=FIMMÊS(data_inicial;quantidade_de_meses)

O uso da função deve ser como explicado anteriormente. Devemos tomar cuidado para inserir no parâmetro data_inicial o valor numérico da data que desejamos operar.

Esta função tem o objetivo de retornar o último dia do mês, considerando a soma da data_inicial com a quantidade_de_meses.

Por exemplo, considere que em A1 tenho a data 30/12/2018. A fórmula =FIMMÊS(A1;3) terá como resultado na célula 31/03/2019.

O função FIMMÊS() não converte automaticamente a célula para o formato de data, então é necessário faze-lo manualmente.

Como Usar a Função DIATRABALHO() no Excel

A função DIATRABALHO() é a solução para deslocar uma data em relação aos chamados dias úteis, ou seja segunda à sexta-feira, ao invés dos totais 7 dias da semana. A sintaxe da função é a seguinte:

=DIATRABALHO(data_inicial;quantidade_dias;feriados)

A data_inicial é o valor que iniciaremos nosso deslocamento. Lembrar que o formato deve ser o reconhecido como data no Excel.

A quantidade_dias são quantos dias úteis iremos deslocar nossa data_inicial. Vale lembrar que aqui o deslocamento pode ser positivo ou negativo.

Os feriados é um parâmetro opcional para que a função desconsidere dias de feriado na contagem do deslocamento. Em feriados poderíamos fazer uma referência a uma coluna que possui todos os feriados, considerando o deslocamento que iremos realizar. Por exemplo, se vou trabalhar com um deslocamento de dezembro a janeiro do ano que vem, talvez adicionar véspera de natal, natal, véspera de ano novo e ano novo seja relevante.

Seria como no exemplo abaixo:

No caso do exemplo com GIF, ao retirarmos um feriado em 01/01/2019 temos um dia a menos no deslocamento, devido o incremento de um dia útil, logo vamos para 14/01/2019 que é uma segunda-feira. Então ao removermos mais um feriado é possível concluir o deslocamento de quantidade_dias na sexta-feira 11/01/2019.

Como Usar a Função DIATRABALHO.INTL() no Excel

A função DIATRABALHO.INTL() tem objetivo idêntico a função DIATRABALHO() a única diferente é que apresenta um parâmetro a mais. Esta parâmetro permite que você defina os dias que a contagem deve pular, se são outros ao invés do usual Sábado e Domingo.

=DIATRABALHO.INTL(data_inicial;quantidade_dias;fim_de_semana;feriados)

O parâmetro fim_de_semana pode ser personalizado como na figura abaixo:
Existem tristes casos de profissões que o final de semana é apenas domingo 🙁

Como Usar a Função DIATRABALHOTOTAL() no Excel

A função DIATRABALHOTOTAL() tem o uso similar a DIATRABALHO(). A diferença é que nesta função iremos calcular o total de dias úteis entre duas datas, enquanto na anterior nós encontrávamos um dia após informarmos quantos dias úteis teriam transcorrido.

=DIATRABALHOTOTAL(data_inicial;data_final;feriados)

Os dias úteis calculados serão em relação a diferença entre a data_final e data_inicial. Os feriados como expliquei anteriormente serão um parâmetro opcional para que o Excel desconsidere certas datas como dias úteis.

Como Usar a Função DIATRABALHOTOTAL.INTL() no Excel

A função DIATRABALHOTOTAL.INTL() tem o mesmo objetivo da função TRABALHOTOTAL(), ou seja, contar dias úteis entre duas datas. Entretanto esta tem um parâmetro adicional que chamo de fim_de_semana, nela podemos determinar quantos dias tem o chamado final de semana e etc. Assim como DIATRABALHO.INTL().

Caso tenha dificuldades no uso desta função leia a seção que explico sobre DIATRABALHO.INTL().

Como Usar as Funções DIA(), MÊS() E ANO() no Excel

As funções DIA(), MÊS() e ANO() são funções que permitem extrair de uma data seu valor numérico que representa o dia, o mês ou o ano, obviamente.

=DIA(data)

=MÊS(data)

=ANO(data)

O resultado destas funções não são o seu equivalente numérico para data, mas sim o valor inteiro correspondente ao nome da função.

Por exemplo:

Digamos que seja necessário fazer uma operação considerando apenas o valor do dia de uma data. Formatar a célula para exibir apenas o dia não adiantaria, pois o valor da data ainda seria seu numérico equivalente. Então a solução seria usar, por exemplo, a função DIA()

Como Usar as Funções HORA(), MINUTO() E SEGUNDO() no Excel

Assim como na seção anterior, HORA(), MINUTO() e SEGUNDO() são utilizados para extrair seus correspondentes de uma célula com uma horas com ou sem uma respectiva data.

=DIA(tempo)

=MÊS(tempo)

=ANO(tempo)

Imagine que seja necessário realizar uma operação matemática utilizando apenas a quantidade de horas de uma célula no Excel. Não seria possível utilizar a célula formatada como data e hora devido seu número de série armazenado ser muito diferente do valor inteiro que esperamos. Portanto utilizar a função HORA() seria a melhor solução.

Como Usar a Função DIA.DA.SEMANA() no Excel

As datas no Excel tem mais informação do que apenas seus números. O Excel consegue calcular com 30/12/2018 e concluir que é uma sexta-feira.

A função que retorna valores que representam segunda-feira, terça-feira, quarta-feira e etc é a função DIA.DA.SEMANA(), seus parâmetros são os seguintes:

=DIA.DA.SEMANA(data;tipo) 

Onde data é uma data qualquer no formato de armazenamento do Excel e tipo é a opção de retorno da função. A função DIA.DA.SEMANA() retorna para a planilha um número que representa os dias da semana de segunda à domingo, como na figura abaixo:

Essa função sozinha parece um pouco inútil, mas ela é muito importante dentro de composições mais complexas de fórmulas, onde ela pode ser utilizada para validar se uma data é um dia específico da semana ou não.

Como Usar a Função NÚMSEMANA() no Excel

O Excel é uma ferramenta super completa. Existem profissões em que a contagem usual dos dias do ano acontece pelo número de semanas. A função NÚMSEMANA() atende essa demanda e faz essa contagem para nós.

=NÚMSEMANA(data;tipo)

Assim como na função anterior que contávamos os dias da semana essa função conta as semanas do ano. O parâmetro tipo é utilizado para determinar qual dia da semana será considerado o início da nossa semana.

A função NÚMSEMANA() pode ser utilizada para, por exemplo, converter um eixo de datas de dias para número de semanas em um gráfico.

Como Usar a Função FRAÇÃOANO() no Excel

A função FRAÇÃOANO() retorna, como o próprio nome diz, a fração de ano que passou em relação a um intervalo de datas. Por exemplo, de janeiro a junho de um ano teremos a resposta 0.5.

=FRAÇÃOANO(data_inicial;data_final;base)

Em base podemos escolher qual será o denominador da fração do ano. Existem opções de considerar que o ano tem 12 meses de 30 dias cada, ou seja 360 dias total, que seria a opção 2. Ou o usual 365 dias que é a opção 3. A escolha vai depender da aplicação.

Como Usar a Função DATADIF() no Excel

A função DATADIF() no Excel é um pouco diferente, pois ela não apresenta aquela ajuda inteligente abaixo da fórmula com orientação sobre parâmetros. O objetivo desta função é calcular a diferença entre duas datas e retornar um valor em número de dias, meses ou anos a depender de parâmetro que você irá fornecer.

=DATADIF(data_inicial;data_final;tipo)

O parâmetro tipo pode ser preenchido com os seguintes valores: “y” para ter o retorno da função em anos, “m” para ter o retorno da função em meses e “d” para ter o retorno da função em dias.

ATENÇÃO: As aspas devem ser mantidas dentro da função, caso contrário você terá o erro #NOME?

Essa função é tão esquisita que o padrão para anos em datas do Excel está em inglês “y”, enquanto que no Excel em português deveria ser “a”, rsrs.

Como Usar a Função DATA.VALOR() no Excel

A função DATA.VALOR() acho muito legal porque, diferente das outras ela não precisa de parâmetros bem formatadinhos para retornar um valor correto. O objetivo desta função é retornar a data que um determinado texto quer dizer.

=DATA.VALOR(data_texto)

É comum termos que retirar dados de arquivos de texto para só então analisarmos no Excel. Portanto, considera essa função importante nesse cenário, onde precisamos fazer um tratamento prévio dos dados para então ser possível manipula-los no Excel. A figura abaixo exemplifica seu uso.

A função é bem completa, mas caso ela não identifique o data_texto como um formato válido, ela retornará o erro #VALOR!.

Como Usar a Função VALOR.TEMPO() no Excel

A função VALOR.TEMPO() tem utilidade semelhante a função da seção anterior. O seu objetivo é tentar interpretar um texto e converte-lo para o formato de tempo no Excel.

=VALOR.TEMPO(tempo_texto)

Essa função pode ser utilizada em conjunto com a função DATA.VALOR() para extrair data e hora de textos para análise no Excel. Considero a função muito bacana, porém ela deixa a desejar em alguns formatos de tempo que encontramos comumente em textos.

Portanto quando a função não consegue reconhecer o texto ela retorna o erro chato #VALOR!

Como Usar a Função TEXTO() no Excel

A função TEXTO() não é exclusiva para uso com datas e horas no Excel, mas pode nos auxiliar com uma importante tarefa. Em seção anterior convertemos texto para o formato de data e hora do Excel, certo? Nada mais justo termos sua contrapartida que retorno data e hora para texto. Esse é o objetivo da função TEXTO(), pelo menos no contexto deste artigo.

=TEXTO(valor;formato)

O parâmetro valor será a célula alvo com a data, hora ou ambos, que desejamos converter para texto.

O parâmetro formato é o formato que desejamos a saída dos dados da função texto. O Excel já tem suas máscaras padrão para formatação de datas e horas como texto. Você pode checar por elas indo no menu personalizado de formatação de célula que as máscaras de formato são as mesmas, como na figura abaixo:

Vale lembrar que:

d - número de dias com apenas um dígito para os primeiros dez dias
dd - número de dias com dois dígitos
ddd - abreviação do dia da semana
dddd - nome do dia da semana completo
m - número do mês com apenas um dígito para os primeiros dez dias
mm - número do mês com dois digitos
mmm - abreviação do nome do mês
mmmm - nome completo do mês
aa - ano com dois digitos finais
aaaa - ano completo com seus quatro digitos

O funcionamento para “h“, “m“, e “s” é semelhante para horas, minutos e segundos respectivamente.

Como Inserir Data e Hora em uma Célula no Excel com Atalho

Para exibir a hora usando o sistema de medição de 12h, você deve digitar ‘am” ou “pm” após o horário desejado, como por exemplo, 5:00 AM. Caso você queira digitar apenas “a” ou “p” em vez de “am” ou “pm” não está errado, apenas garanta que haja um espaço entre a hora e a letra. O Excel automaticamente irá formatar a exibição para o sistema 12 horas.

Para exibir a hora usando o sistema usual de 24 horas basta digitar o horário utilizando “:” para separar horas (hh), minutos (mm) e segundos (ss), como no formato da máscara “hh:mm:ss”

Você deve notar que o Excel exibe a hora utilizando o relógio de 24h, ou seja, 17:00 ao invés de 5:00. Isso acontece porque relógio de 24h é oficialmente o padrão utilizado no Brasil.

Considerando as máscaras de formatação que vimos na seção anterior você deve perceber que sempre deve haver um espaço para separar data de hora. Por exemplo: 23/12/2018 01:10. E que se usa sempre barras “/” como separador de datas ou então o hífen “-“ e “:” para separar horas. Veja só: 23-12-2018 01:10.

ATENÇÃO: Nunca use pontos “.”, pois o Excel não reconhece datas separadas por pontos.

Agora, veja como fazer isso em um segundo no Excel:

1. Clique na célula vazia na qual você quer digitar a data e a hora atual;
2. Para digitar a data atual, aperte: CTRL + ;
   Veja que aparece a data atual do relógio do seu PC.
3. Nessa mesma célula, após digitar a data, dê um espaço e aperte: CTRL + SHIFT + ;
   

Tchantchantchantchaaannnn!! Veja que agora você tem a data e a hora na mesma célula

ATENÇÃO: Utilizando este recurso não temos o inconveniente das funções HOJE() e AGORA(), que recalculam data e hora a cada operação no Excel

Como Somar ou Subtrair Horas no Excel

Para você somar horas no Excel, ou trabalhar com horas em qualquer cálculo que não seja só somar, a célula deve estar formatada como hora, no padrão 37:30:55. Não necessariamente todas as células, mas sim a que trará o resultado da conta. Veja um exemplo:

Temos aqui dois resultados totais das horas trabalhadas por data, ambas realizadas através da mesma formula. Entretanto, apenas na célula B9 temos a formatação 37:30:55 que é a correta para esta operação.

Então é válido observar que como a “hora” no Excel vai ate 24h, é necessário realizar a formatação da célula. Mas saiba que formatar simplesmente a célula como “Hora” não é o suficiente para que o Excel apresente o resultado correto para todas as operações. É preciso considerar qual a formatação adequada para o tipo de operação que será realizadas com horas.

Quando a célula está formatada apenas como “Hora”, o Excel vai somando as horas e zerando a cada 24h, mostrando apenas o resto do resultado. É o exemplo das 23 horas mostrado na imagem acima. O resultado correto é 71 horas.

Quando formatamos a célula como hora no formato 37:30:55, estamos informando ao Excel que a hora vai além das 24 horas.  Veja como é simples formatar:

1. Clique com o botão direito do mouse sobre a célula que você deseja formatar
2. Selecione a opção “Formatar Células”
3. Abrirá uma janela chamada: Formatar Células. Na caixa “Número” em “Categoria” selecione “Hora” e em “Tipo” escolha a opção 37:30:55
   
   

Como Calcular Horas Negativas no Excel

Em alguns casos no Excel o resultado de uma operação com horas é negativo. Por exemplo, digamos que temos 2 horas disponíveis em um banco de horas da empresa, mas utilizamos 8 horas. O resultado no nosso banco de horas restante deve ser -6 horas, certo? Sim, porém o Excel irá te responder que o resultado é ####, rsrs.

ATENÇÃO: Formate suas células como na seção anterior, no padrão 37:30:55, caso nosso valor negativo exceda 24 horas.

A correção para calcular horas negativas no Excel é a seguinte:

1. Na barra de ferramentas do Excel navegue até a aba “Arquivo”, então selecione “Opções”
2. No menu de Opções selecione o separador “Avançado”, então procure por “Usar sistema de data 1904” e marque esta opção. Clique em “OK” para confirmar a alteração
   

Pronto,

O resultado da operação então deverá ficar assim:

Como Multiplicar ou Dividir Horas no Excel

Suponhamos que você tenha que calcular as horas extras  de um funcionário, de acordo com as horas trabalhadas e o valor em reais da hora. Como no exemplo abaixo.

A célula E2 foi preenchida com o valor da hora, enquanto na célula E3 foi calculado o valor a receber, portanto com uma conta de multiplicação =B14*E2.

É fácil perceber que algo está errado na formula, pois se especificamos que o valor da hora é equivalente a R$56,00 não faz sentido que uma pessoa trabalhe por volta de 100 horas e receba a quantia de 233,00

Isso ocorre porque o Excel não aceita efetuar cálculo entre células formatadas como valor e hora. Isso se refere a qualquer formato, seja número, moeda, geral e outros. O cálculo é realizado entre células formatadas como “Horas”.

Para tanto existe uma saída: Você pode multiplicar o total de horas trabalhadas por 24  (de 24 horas), e a célula deve estar formatada como “Geral”. Como isso transforma a hora em número, basta em seguida multiplica pelo valor da hora.

A pergunta é: porque 99:56:00, quando multiplicado por 24, resulta em 99,93333 e não 99,56 ?

Isso acontece porque temos que transformar os minutos em horas.

Nesse caso como são 56 minutos, se dividirmos por 60 resultará em 9,93333 (56/60 = 0,9333). Concluímos que 99 horas + 0,9333 horas = 99,93333.

Processo semelhante pode ser utilizado na divisão, por exemplo, o inverso do que fizemos, calcular quanto custa a hora baseado no total divido pelo total de horas trabalhado.

Conclusão

Neste artigo acredito ter abordado todas a maioria das ferramentas para trabalhar com horas no Excel.

Sei que é normal em artigos muito longos irmos direto para as partes que temos dúvida para resolver um problema. Porém, recomendo fortemente que dedique um minutinho para ler as duas primeiras seções do artigo que falam sobre como o Excel armazena datas e horas.

Uma vez que entendemos o que o Excel está pensando fica muito mais fácil interpretar eventuais erros que sempre aparecem. Principalmente naquelas longas fórmulas em que um pequeno detalhe pode resultar acabar em um erro difícil de localizar.

Não posso deixar de mencionar que este artigo, na disposição dos itens e parte do conteúdo, teve inspiração no artigo deste amigo aqui, quem eu agradeço por compartilhar conhecimento.

Continue lendo sobre Excel básico aqui ou mais dicas e truques no Excel aqui.