Existem muitas maneiras de converter datas formatadas como texto em datas reais no Excel e este tutorial tem como objetivo explorá-las todas.
Quando você importa dados para o Excel, eles podem vir em muitos formatos diferentes que o Excel não reconhece. Às vezes, quando as datas são importadas de uma fonte externa, elas vêm formatadas como texto.
Também é importante saber que o Excel aplica formatos de data com base nas configurações de região do sistema. Portanto, ao importar dados de um país diferente para o Excel, o Excel pode não reconhecê-los e armazená-los como entradas de texto.
Se você tiver esse problema, há muitas maneiras que você pode usar para converter texto em uma data no Excel e iremos abordá-las todas neste tutorial.
Métodos para converter texto em data
Se sua planilha contém datas formatadas como texto, em vez de datas reais, não é possível usá-las em nenhum cálculo. Portanto, você precisa convertê-los de volta às datas reais.
Se você vir suas datas alinhadas à esquerda, isso significa que elas estão formatadas como texto porque os textos são alinhados à esquerda por padrão. E o número e as datas estão sempre alinhados à direita.
Existem algumas maneiras diferentes de converter texto em uma data no Excel, são elas:
- Opção de verificação de erros
- Texto do Excel para recurso de colunas
- Encontrar e substituir
- Ferramenta Colar especial
- Fórmula e funções do Excel
Converter texto em data usando a opção de verificação de erros
O Excel possui um recurso de verificação de erros embutido que identifica alguns erros óbvios em seus dados. Se encontrar algum erro, ele mostrará um pequeno triângulo verde (um indicador de erro) no canto superior esquerdo da célula, que contém o erro. Se você selecionar essa célula, um sinal de cuidado com um ponto de exclamação amarelo aparecerá. Quando você move o cursor sobre aquele sinal, o Excel irá informá-lo sobre o possível problema com aquela célula.
Por exemplo, quando você insere o ano no formato de dois dígitos em sua data, o Excel assume essa data como um texto e a armazena como texto. E se você selecionar essa célula, um ponto de exclamação aparecerá com um aviso: ‘Esta célula contém uma string de data representada por apenas 2 dígitos do ano’.
Se suas células mostrarem esse indicador de erro, clique no ponto de exclamação e ele exibirá algumas opções para converter datas formatadas como texto em datas reais. O Excel mostrará opções para convertê-lo em 19XX ou 20XX (19XX para 1915, 20XX para 2015). Selecione a opção apropriada.
Em seguida, o texto será convertido no formato de data adequado.
Como ativar a opção de verificação de erros no Excel
Normalmente, a opção Verificação de erros está ativada no Excel por padrão. Se o recurso de verificação de erros não funcionar para você, é necessário habilitar a verificação de erros no Excel.
Para fazer isso, clique na guia ‘Arquivo’ e selecione ‘Opções’ no painel esquerdo.
Na janela Opções do Excel, clique em "Fórmulas" no painel esquerdo e, no painel direito, ative "Ativar verificação de erros em segundo plano" na seção Verificação de erros. E marque 'Células contendo anos representados como 2 dígitos' na seção de regras de verificação de erros.
Converter texto em data usando o recurso de texto em coluna do Excel
Texto para coluna é um ótimo recurso do Excel que permite dividir dados em várias colunas e também é uma ferramenta poderosa para converter valores de texto em valores de data. Este método reconhece vários formatos de dados diferentes e os converte no formato de data adequado.
Convertendo Strings de Texto Simples em Datas
Digamos que suas datas sejam formatadas em strings de texto como este:
Você pode usar o assistente Texto para colunas para reformatar rapidamente todas as datas.
Primeiro, selecione o intervalo de entradas de texto que deseja converter em datas. Em seguida, vá para a guia ‘Dados’ na faixa de opções e clique na opção ‘Texto para colunas’ no grupo Ferramentas de dados.
O assistente Text to Columns aparecerá. Na etapa 1 do assistente de Texto para coluna, escolha a opção ‘Delimitado’ em Tipo de dados original e clique em ‘Avançar’.
Na etapa 2, desmarque todas as caixas "Delimitadores" e clique em Avançar.
Na etapa final do assistente, selecione ‘Data’ em Formato de dados da coluna e escolha seu formato de data na lista suspensa ao lado de ‘Datas’ e clique no botão ‘Concluir’. Em nosso caso, estamos convertendo as datas do texto representadas como “01 02 1995” (dia, mês, ano), então escolhemos ‘DMY’ na lista suspensa ‘Data:’.
Agora, o Excel converte as datas do texto em datas reais e as exibe alinhadas à direita nas células.
Observação: Antes de começar a usar o recurso Texto para coluna, certifique-se de que todas as sequências de texto estejam em um formato idêntico, caso contrário, os textos não serão convertidos. Por exemplo, se algumas de suas datas de texto são formatadas como formato mês / dia / ano (MDY), enquanto outras são dia / mês / ano (DMY), e quando você escolhe 'DMY' na etapa 3, você obterá resultados incorretos. Como é mostrado na imagem abaixo.
Convertendo string de texto complexo em data
O recurso Texto para colunas é útil quando você deseja converter sequências de texto complexas em datas. Ele permite que você use os delimitadores para identificar onde seus dados devem ser divididos e exibidos em 2 ou mais colunas. E combine as partes divididas das datas em datas inteiras usando a função DATE.
Por exemplo, se suas datas forem exibidas em strings de texto com várias partes, como este:
Quarta-feira, 1 de fevereiro de 2020
01 de fevereiro de 2020, 4:10 PM
Você pode usar o assistente Texto para coluna para separar as informações de dia, data e hora delimitadas por uma vírgula e exibi-las em várias colunas.
Primeiro, selecione todas as sequências de texto que deseja converter em datas. Clique no botão ‘Texto para colunas’ na guia ‘Dados’. Na etapa 1 do assistente de Texto para coluna, escolha a opção ‘Delimitado’ em Tipo de dados original e clique em ‘Avançar’.
Na etapa 2 do assistente, selecione os delimitadores que suas strings de texto contêm e clique em ‘Avançar’. Nossas strings de texto de exemplo separadas por vírgula e espaço - “segunda-feira, 1 de fevereiro de 2015, 13h”. Devemos escolher ‘Vírgula’ e ‘espaços’ como delimitadores para dividir as sequências de texto em várias colunas.
Na etapa final, selecione o formato ‘Geral’ para todas as colunas na seção Visualização de dados. Especifique onde as colunas devem ser inseridas no campo ‘Destino’; caso contrário, os dados originais serão substituídos. Se você deseja ignorar alguma parte dos dados originais, clique sobre eles na seção Visualização de dados e selecione a opção ‘Não importar coluna (pular)’. Em seguida, clique em ‘Concluir’.
Agora, as partes das datas (dias da semana, mês, ano, hora) são divididas nas colunas B, C, D, E, F e G.
Em seguida, combine as partes da data com a ajuda de uma fórmula DATE para obter a data completa.
A sintaxe da função DATE do Excel:
= DATA (ano, mês, dia)Em nosso exemplo, as partes do mês, dia e ano estão nas colunas C, D e E, respectivamente.
A função DATE reconhece apenas números, não texto. Como nossos valores de mês na coluna C são todos strings de texto, precisamos convertê-los em números. Para fazer isso, você precisa usar a função MONTH para alterar o nome do mês para o número do mês.
Para converter o nome de um mês para o número de um mês, use esta função MONTH dentro da função DATE:
= MÊS (1 e C1)A função MONTH adiciona 1 à célula C2 que contém o nome do mês para converter o nome do mês para o número do mês correspondente.
Esta é a função DATE que precisamos usar para combinar partes de datas de diferentes colunas:
= DATA (E1, MÊS (1 e C1), D1)
Agora use a alça de preenchimento no canto inferior da célula da fórmula e aplique a fórmula à coluna.
Converter texto em data usando o método Localizar e substituir
Este método usa delimitadores para alterar o formato do texto para datas. Se dia, mês e ano em suas datas forem separados por algum delimitador diferente de um traço (-) ou barra (/), o Excel não os reconhecerá como datas e irá armazená-los como texto.
Para corrigir esse problema, use o recurso Localizar e Substituir. Ao alterar os delimitadores de período não padrão (.) Com barras (/) ou traço (-), o Excel alterará automaticamente os valores para datas.
Primeiro, selecione todas as datas de texto que deseja converter em datas. Na guia ‘Página inicial’, clique no botão ‘Localizar e selecionar’ no canto direito da faixa de opções e selecione ‘Substituir’. Alternativamente, pressione Ctrl + H para abrir a caixa de diálogo Localizar e substituir.
Na caixa de diálogo Localizar e substituir, digite o delimitador que o seu texto contém (em nosso caso ponto final (.) No campo 'Localizar' e uma barra (/) ou traço (-) no campo 'Substituir por' Clique no botão 'Substituir tudo' para substituir os delimitadores e clique em 'Fechar' para fechar a janela.
Agora, o Excel reconhece que suas sequências de texto agora são datas e as formata automaticamente como datas. Suas datas serão alinhadas conforme mostrado abaixo.
Converter texto em data usando a ferramenta Colar especial
Outra maneira rápida e fácil de converter strings de texto em datas é adicionar 0 à string de texto usando a opção especial de colar. Adicionar zero ao valor converte o texto no número de série da data, que você pode formatar como uma data.
Primeiro, selecione uma célula vazia e copie-a (selecione-a e pressione Ctrl + C copiar).
Em seguida, selecione as células que contêm as datas de texto que deseja converter, clique com o botão direito e selecione a opção ‘Colar especial’.
Na caixa de diálogo Colar especial, selecione ‘Tudo’ na seção Colar, escolha ‘Adicionar’ na seção Operação e clique em ‘OK’.
Você também pode realizar outras operações aritméticas subtrair / multiplicar / dividir o valor na célula de destino com o valor colado (como multiplicar células por 1 ou dividir por 1 ou subtrair zero).
Quando você seleciona ‘Adicionar’ na Operação, ele adiciona ‘zero’ a todas as datas de texto selecionadas, já que adicionar um ‘0’ não altera os valores, você obterá o número de série para cada data. Agora, tudo o que você precisa fazer é mudar o formato das células.
Selecione os números de série e na guia ‘Página inicial’, clique na lista suspensa ‘Formato do número’ no grupo Número. Selecione a opção 'Data abreviada' no menu suspenso.
Como você pode ver agora, os números são formatados como datas e alinhados à direita.
Converter texto em data usando fórmulas
Existem duas funções usadas principalmente para converter texto em data: DATEVALUE e VALUE.
Usando a função DATEVALUE do Excel
A função DATEVALUE do Excel é uma das maneiras mais fáceis de converter uma data representada como texto em um número de série de data.
A sintaxe da função DATEVALUE:
= DATEVALUE (texto_data)Argumento: texto_de_data especifica a sequência de texto que você deseja converter ou fazer referência à célula que contém as datas do texto.
A fórmula:
= DATEVALUE (A1)A imagem a seguir ilustra como a função DATEVALUE lida com alguns formatos de data diferentes que são armazenados como texto.
Você obteve os números de série da data, agora você precisa aplicar o formato de data a esses números. Para fazer isso, selecione as células com números de série, vá para a guia ‘Página inicial’ e escolha ‘Data abreviada’ na lista suspensa ‘Formato do número’.
Agora você tem suas datas formatadas na coluna C.
Mesmo se não houver parte do ano na data do seu texto (A8), DATEVALUE usará o ano atual do relógio do seu computador.
A função DATEVALUE converterá apenas os valores de texto que se parecem com uma data. Ele não pode converter um texto que se pareça com um número até a data, nem pode alterar um valor numérico até a data, para isso você precisará da função VALUE do Excel.
Usando a função VALUE do Excel
A função VALUE do Excel é capaz de converter qualquer string de texto que se pareça com uma data ou número em um valor numérico, então é muito útil quando se trata de converter qualquer número, não apenas datas.
A função VALUE:
= VALUE (texto)texto- a sequência de texto que queremos converter ou referência à célula que contém a sequência de texto.
A fórmula de exemplo para converter a data do texto:
= VALOR (A1)A fórmula VALUE abaixo pode alterar qualquer sequência de texto que se pareça com uma data em um número, conforme mostrado abaixo.
No entanto, a função VALUE não suporta todos os tipos de valores de data. Por exemplo, se as datas usam casas decimais (A11), ele retornará o #VALOR! erro.
Assim que tiver o número de série de sua data, você precisará formatar a célula com o número de série da data para que pareça uma data, como fizemos para a função DATEVALUE. Para fazer isso, selecione os números de série e selecione a opção ‘Data’ no menu suspenso ‘Formato do número’ na guia ‘Página inicial’.
É isso, essas são as 5 maneiras diferentes de converter datas formatadas como texto em datas no Excel.