Neste artigo você aprenderá como converter uma data no formato ano mês dia, aaaammdd em data válida no Excel.
1. Introdução
Muitos sistemas, inclusive ERPs, ao exportarem dados, seja em TXT ou ainda em pastas de trabalho, podem exibir datas com o formato aaaammdd. Este formato considera os 4 primeiros dígitos para o ano (aaaa), os 2 seguintes e intermediários para o mês (mm) e os 2 últimos para o dia (dd). A data 10/09/2022, por exemplo, é elencada no formato 20220910.
Neste artigo objetivamos mostrar algumas alternativas que possuímos no Excel para tratar o dado no formato aaaammdd e obter uma data válida e correspondente. Utilizaremos fórmulas, texto para colunas, preenchimento relâmpago e Power Query.
2. Converter usando fórmulas
2.1. Usando a função DATA
Podemos separar o dado em 3 partes e montar a data usando a função DATA. Para extrair o ano, visto que são os primeiros 4 caracteres, podemos usar a função ESQUERDA. Para obter o mês, que são o 5º e 6º caracteres, podemos usar a função EXT.TEXTO. Por fim, para obter o dia, que são os 2 últimos caracteres, podemos usar a função DIREITA. A imagem a seguir ilustra a solução descrita.
2.2. Usando a função DATA. VALOR
Partindo das 3 partes da data devidamente extraídas (ano, mês e dia, como na seção 2.1) podemos lançar mão da função DATA.VALOR. Vamos concatenar as 3 partes separadas por barra (poderia ser também hífen) e encapsular isto na função DATA.VALOR. Aqui, a ordem deverá ser dia, mês e ano. A imagem a seguir ilustra a aplicação.
A função DATA.VALOR converte em data válida um conteúdo textual que pode representar uma data. Partindo das partes concatenadas com barras como separadores (imagem anterior), poderíamos inclusive abrir mão da função DATA.VALOR e realizar uma operação matemática contendo elemento neutro para “forçar” que o texto, resultado da concatenação, se torne uma data. É isto que está ilustrado na imagem a seguir, na qual multiplicamos o texto resultante por 1 (poderíamos, por exemplo, somar ou subtrair 0).
2.3. Usando a função MUDAR para converter a data no Excel
Curiosamente, podemos obter as datas válidas usando apenas a função MUDAR. Utilizaremos esta função para inserir barras entre os dígitos. Para o primeiro dado, o aninhamento da função MUDAR resultará em “2022/10/17”. Após isso, realizaremos uma operação matemática com elemento neutro, como comentado anteriormente. Desta vez optamos por somar 0. A imagem a seguir ilustra a solução.
3. Converter usando Texto para Colunas
Podemos realizar o mesmo trabalho usando o recurso de Texto para Colunas, disponível na Guia Dados. Devemos selecionar todos os dados a tratar e acessar a Guia Dados, no grupo Ferramentas de Dados.
Em seguida, já nas opções de Texto para Colunas, clicamos em “Avançar” 2 vezes até a etapa a seguir. Nesta etapa, escolhemos a opção “Data” e escolhemos o formato atual dos dados (AMD). Em seguida, especificamos a célula a partir da qual ocorrerá o despejo dos dados tratados (no nosso caso será C6). Basta agora clicar para “Concluir”.
A imagem a seguir exibe o resultado final que será obtido.
4. Preenchimento relâmpago
Este recurso, que surgiu no Excel 2013, pode ser útil para resolvermos nosso problema. Para utilizá-lo, devemos informar manualmente resultado(s) esperado(s) a fim de que o padrão seja identificado e preenchido para as demais células em branco. O recurso está presente na Guia Dados, no Grupo Ferramentas de Dados e pode ser ativado através do atalho CTRL + E.
Em nosso exemplo, podemos preencher a primeira data conforme esperado.
Com a realização da seleção da célula do dado preenchido manualmente ou com a realização da seleção da célula abaixo desta, ativamos o recurso (através da Guia Dados ou CTRL + E). Note na imagem a seguir que as datas são preenchidas, mas todas constam com o mesmo dia. Para o nosso conjunto de dados todas as datas estão com o dia 17, o que visivelmente está errado.
Agora, para complementar os inputs que fizemos para o preenchimento relâmpago identificar o padrão, selecionamos a célula abaixo da data fornecida manualmente (que é a primeira célula preenchida com o preenchimento relâmpago). Também para esta célula, informamos a data esperada (03/05/2018).
Ao teclarmos ENTER o preenchimento relâmpago é atualizado, fornecendo os dados corretos.
P.S: caso ainda existam datas incorretas, vale a pena alterar mais alguma data que esteja diferente do esperado a fim de que o preenchimento relâmpago seja atualizado, fornecendo os resultados corretos.
5. Power Query para converter data no Excel
Por fim, neste artigo expomos a possibilidade de usar o Power Query, suplemento de ETL que suporta o Excel e também o Power BI. A nossa abordagem é semelhante ao que foi realizado na seção 2.1 com fórmulas: extrair as 3 partes da data e montar a data com uma função apropriada.
Para extrair as partes, vamos proceder com a divisão dos dados em 3 colunas. Em seguida, para montar a data, vamos usar a função #date.
Tomaremos como base uma tabela chamada tabDatas. Com a seleção nela posicionada, acessamos a guia Dados e grupo Obter e Transformar, escolhendo em seguida a opção Da Tabela.
O editor do Power Query será iniciado e trará uma etapa automaticamente aplicada que é a alteração do tipo, a qual é irrelevante neste momento e podemos remover.
Ao selecionar a coluna contendo os dados e clicar com o botão direito, pode estar disponível a opção de Dividir Coluna, a depender do tipo dos dados da coluna.
Caso a opção não esteja disponível, poderá ser acessada na guia Página Inicial e grupo Transformar.
Após escolher a opção Dividir Coluna, devemos escolher a opção Por Posições e discriminar a seguir as posições 0, 4 e 6 separadas por vírgulas e confirmar com Ok.
Com isso, teremos 3 colunas que são respectivamente o ano, mês e dia. Note que uma etapa automática de alteração de tipo foi criada após a divisão das colunas pelas posições.
Agora, utilizaremos a função #date para montar a data utilizando as 3 colunas obtidas. Para isto, acessamos a guia Adicionar Coluna e escolhemos a opção Coluna Personalizada.
Na etapa a seguir, especificamos um nome para a nova coluna (no nosso caso renomeamos para Datas ajustadas). No espaço reservado para a fórmula da coluna personalizada, invocamos a função #date e inserimos os seus 3 argumentos separados por vírgula. Clicamos Ok.
Feito isso, teremos como resultado a criação da nossa coluna personalizada que ocupará a última posição.
Devemos remover as colunas anteriores, contendo ano, mês e dia. Para isso, basta selecionarmos estas colunas que agora são indesejadas e escolher a opção Remover Colunas (com o botão direito do mouse ou na Página Inicial e grupo Gerenciar Colunas).
Como resultado, teremos as datas corretamente obtidas.
Ainda nos cabe alterar o tipo dos dados para garantir que sejam datas. Clicamos no canto esquerdo do rótulo da coluna e escolhemos a opção correspondente.
Agora podemos decidir como consumir estes dados. Podemos criar conexão ou ainda carregar para uma tabela e despejar o conteúdo em planilha (esta opção é ilustrada nas imagens a seguir).
Escolhemos inserir os dados na mesma planilha dos dados originais, ao lado destes e clicamos em Carregar para finalizar o processo de converter data no Excel.
Download Planilha de Exemplo Converter Data Excel
Realize o download da planilha para converter data no Excel neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.
Curso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
