O Excel armazena os valores de data e hora como números de série no back end.
Isso significa que, embora você possa ver uma data como “10 de janeiro de 2021-2022” ou “01/10/2020” em uma célula, no back-end, o Excel a está armazenando como um número. Isso é útil porque também permite aos usuários adicionar / subtrair facilmente a data e a hora no Excel.
No Microsoft Excel para Windows, “01 de janeiro de 1900” é armazenado como 1, “02 de janeiro de 1900” é armazenado como 2 e assim por diante.
Abaixo, as duas colunas têm os mesmos números, mas a Coluna A mostra números e a Coluna B mostra a data representada por esse número.
Esta também é a razão pela qual às vezes você pode esperar uma data em uma célula, mas acaba vendo um número. Vejo isso acontecendo o tempo todo quando faço download de dados de bancos de dados ou ferramentas baseadas na web.
Isso acontece quando o formato da célula é definido para mostrar um número como um número em vez de uma data.
Então, como podemos converta esses números de série em datas?
É disso que trata este tutorial.
Neste tutorial, eu mostraria duas maneiras realmente fáceis de converter números de série em datas no Excel
Nota: O Excel para Windows usa o sistema de datas de 1900 (o que significa que 1 representaria 01 de janeiro de 1900, enquanto o Excel para Mac usa o sistema de datas de 1904 (que significa que 1 representaria 01 de janeiro de 1904 no Excel no Mac)Então vamos começar!
Converter números de série em datas usando a formatação de números
A maneira mais fácil de converter um número de série de data em uma data é alterar a formatação das células que possuem esses números.
Você pode encontrar alguns dos formatos de data comumente usados na guia Página inicial da faixa de opções
Deixe-me mostrar como fazer isso.
Usando as opções de formato de data embutido na faixa de opções
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja converter todos esses números na coluna A na data correspondente que ela representa.
Abaixo estão as etapas para fazer isso:
- Selecione as células que têm o número que você deseja converter em uma data
- Clique na guia ‘Página inicial’
- No grupo ‘Número’, clique na lista suspensa Formatação de Número
- No menu suspenso, selecione a opção 'Data Longa' ou Data Curta '(com base no formato que você deseja que esses números tenham)
É isso!
As etapas acima converteriam os números no formato de data selecionado.
As etapas acima não alteraram o valor da célula, apenas a forma como está sendo exibido.
Observe que o Excel pega a formatação de data curta com base na configuração regional do seu sistema. Por exemplo, se você estiver nos EUA, o formato da data será MM / DD / AAAA, e se você estiver no Reino Unido, o formato da data será DD / MM / AAAA.
Embora seja um método rápido para converter números de série em datas, ele tem algumas limitações:
- Existem apenas dois formatos de data - data curta e data longa (e isso também em um formato específico). Portanto, se você deseja obter apenas o mês e o ano e não o dia, não poderá fazer isso usando essas opções.
- Você não pode mostrar a data e a hora usando as opções de formatação do menu suspenso. Você pode optar por exibir a data ou a hora, mas não ambas.
Portanto, se precisar de mais flexibilidade na maneira como deseja mostrar as datas no Excel, você precisará usar a opção Formatação de número personalizada (abordada a seguir neste tutorial).
Às vezes, quando você converte um número em uma data no Excel, em vez da data, você pode ver alguns sinais de hash (algo como #####). isso acontece quando a largura da coluna não é suficiente para acomodar a data inteira. Nesse caso, basta aumentar a largura da coluna.Criando um formato de data personalizado usando a caixa de diálogo Formatação de números
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja converter os números de série na coluna A em datas no seguinte formato - 01/01/2020
Observe que essa opção não está disponível por padrão no método da faixa de opções que abordamos anteriormente.
Abaixo estão as etapas para fazer isso:
- Selecione as células que têm o número que você deseja converter em uma data
- Clique na guia ‘Página inicial’
- No grupo Numbers, clique no ícone do iniciador da caixa de diálogo (é uma pequena seta inclinada no canto inferior direito do grupo)
- Na caixa de diálogo ‘Formatar células’ que se abre, certifique-se de que a guia ‘Número’ esteja selecionada
- Nas opções de ‘Categoria’ à esquerda, selecione ‘Data’
- Selecione a formatação desejada na caixa 'Tipo'.
- Clique OK
As etapas acima converteriam os números no formato de data selecionado.
Como você pode ver, há mais opções de formatação de data neste caso (em comparação com a data curta e longa que obtivemos com a faixa de opções).
E caso não encontre o formato que procura, você também pode criar seu próprio formato de data.
Por exemplo, digamos que eu queira mostrar a data no seguinte formato - 01/01/2020 (que ainda não é uma opção na caixa de diálogo de formatação de células).
Aqui está como posso fazer isso:
- Selecione as células que têm os números que você deseja converter em uma data
- Clique na guia Home
- No grupo Numbers, clique no ícone do iniciador da caixa de diálogo (é uma pequena seta inclinada na parte inferior direita do grupo)
- Na caixa de diálogo ‘Formatar células’ que se abre, certifique-se de que a guia ‘Número’ esteja selecionada
- Nas opções de ‘Categoria’ à esquerda, selecione ‘Personalizar’
- No campo à direita, digite
mm / dd / aaaa
- Clique OK
As etapas acima mudariam todos os números para o formato de número especificado.
Com o formato personalizado, você obtém controle total e permite mostrar a data no formato que desejar. Você também pode criar um formato onde mostra a data e a hora na mesma célula.
Por exemplo, no mesmo exemplo, se você deseja mostrar a data, bem como a hora, você pode usar o formato abaixo:
mm / dd / aaaa hh: mm AM / PM
Abaixo está a tabela que mostra os códigos de formato de data que você pode usar:
| Código de formatação de data | Como formata a data |
| m | Mostra o mês como um número de 1-12 |
| milímetros | Mostra os meses como números de dois dígitos de 01 a 12 |
| mmm | Mostra o mês com três letras, como em janeiro-dezembro |
| mmmm | Mostra o nome completo dos meses de janeiro a dezembro |
| mmmmm | Mostra o primeiro alfabeto do nome do mês como em J-D |
| d | Mostra os dias como 1-31 |
| dd | Mostra os dias como 01-31 |
| ddd | Mostra os dias como dom a sáb |
| dddd | Mostra os dias como domingo a sábado |
| yy | Mostra os anos como 00-99 |
| aaaa | Mostra os anos como 1900-9999 |
Converter números de série em datas usando a fórmula de texto
Os métodos descritos acima funcionam alterando a formatação das células que contêm os números.
Mas, em alguns casos, você pode não conseguir usar esses métodos.
Por exemplo, abaixo eu tenho um conjunto de dados onde os números têm um apóstrofo à esquerda - que converte esses números em texto.
Se você tentar usar as opções embutidas para alterar a formatação das células com esse conjunto de dados, não adianta nada. Isso ocorre porque o Excel não os considera como números e, como as datas são para números, o Excel recusa seu desejo de formatá-los.
Nesse caso, você pode converter esses textos em números e, em seguida, usar os métodos de formatação mencionados acima ou usar outra técnica para converter números de série em datas - usando a função TEXT.
A função TEXT leva dois argumentos - o número que precisa ser formatado e o código de formato.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja converter todos os números e a coluna A em datas.
Abaixo da fórmula que poderia fazer isso:
= TEXTO (A2, "MM / DD / AAAA")
Observe que, na fórmula, especifiquei que a formatação da data deve ser no formato MM / DD / AAAA. Se precisar usar qualquer outra formatação, você pode especificar o código para ela como o segundo argumento da fórmula TEXT.
Você também pode usar esta fórmula para mostrar a data, bem como a hora.
Por exemplo, se você deseja que o resultado final esteja no formato - 01/01/2020 12h, use a fórmula abaixo:
= TEXTO (A2, "mm / dd / aaaa hh: mm AM / PM")
Na fórmula acima, também adicionei o formato da hora, portanto, se houver partes decimais nos números, ela será mostrada como a hora em horas e minutos.
Se você quiser apenas a data (e não a fórmula subjacente), converta as fórmulas em valores estáticos usando colar especial.
Uma grande vantagem de usar a função TEXTO para converter números de série em datas é que você pode combinar o resultado da fórmula TEXTO com outras fórmulas. Por exemplo, você pode combinar a data resultante com o texto e mostrar algo como - A data de vencimento é 01 de janeiro de 2020 ou O prazo é 01-jan-2020
Nota: Lembre-se de que as datas e a hora são armazenadas como números, onde cada número inteiro representaria um dia inteiro e a parte decimal representaria essa parte do dia. Portanto, 1 significaria 1 dia inteiro e 0,5 significaria meio dia ou 12 horas.
Portanto, essas são duas maneiras simples que você pode usar para converter números de série em datas no Microsoft Excel.
Espero que você tenha achado este tutorial útil!
