Texto do Excel para converter o formato de data

Question

Eu tenho um lote de dados com várias colunas em que a data é formatada assim:

Maio-31-2014.

Estou tentando descobrir uma maneira de converter isso em um formato de data útil 31/05/2014. Alguma idéia?

    

Answer 1

Existem alguns passos para alcançar isso. Essa é uma maneira de fazer isso usando fórmulas e células auxiliares. Se você precisa fazer isso sem células auxiliares (ou seja, você não quer colunas extras), então você deve mencionar isso e eu provavelmente poderia trabalhar em algum VBA para fazer a tarefa.

A primeira tarefa é dividir sua data em três partes: o ano, o mês, o dia. Uma vez que você tenha os três, é fácil juntá-los novamente.

Isto, é claro, presumindo que suas datas sempre sigam o Formato de Data Americana Bastante Idiota © de MMM-dd-aaaa.

Isso foi feito usando a página da Microsoft Divida o texto entre as colunas usando as funções como um exemplo.

1. Dividindo o mês:
   Isso é obtido usando a função search para encontrar o primeiro - e a ferramenta de divisão Left para desativá-lo.

   =LEFT(A1, SEARCH("-",A1,1)-1)

   que retorna May do seu exemplo.

2. Separando o dia:
   É semelhante, mas é necessário um uso muito mais complicado da função MID . Precisamos encontrar o primeiro e segundo - , em seguida, subtrair a localização do primeiro do segundo para obter o comprimento do texto necessário, pois esses resultados são uma função bastante longa:

   =MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)

   o SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1) retorna o comprimento enquanto o SEARCH("-",A1,1)+1 está simplesmente obtendo o primeiro local. a ferramenta MID os corta e retorna 23 do seu exemplo.

3. Dividindo o ano:
   A última ferramenta que precisamos é a ferramenta RIGHT .

   =RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1))

   Isso simplesmente obtém o comprimento da string e, em seguida, pesquisa duas vezes e retorna tudo o que resta (ou à direita).

O problema agora é que você ainda tem o mês em um formato de texto inutilizável. Para fazer é utilizável, precisamos usar a ferramenta DATEVALUE para convertê-lo. Essa ferramenta não aceitará um mês sozinha, mas aceitará uma string MMM YY . O que podemos fazer é então analisar o texto do mês em um número depois de concatenarmos um ano.

4. Obtendo o mês como um valor numérico:
   Por uma questão de argumento digamos que armazenamos a primeira função na célula B1.
   DATEVALUE(B1 &" 01") retornará a cadeia de valor de data que o excel usa internamente, então podemos agrupar isso em uma função MONTH para retornar o valor numérico para o mês:

   MONTH(DATEVALUE(B1 &" 01"))

5. Podemos, então, mesclar todos eles em uma função DATE(year,month,day) e, assim, convertê-los em uma data real que o Excel possa usar. O mês está na célula B1, o dia na célula C1 e o ano em D1 seguindo o Stupid American Date Format ™:

   DATE(D1, B1, C1)

   retornará 23/05/2014 na célula.

6. Se você quiser ficar realmente chique, pode ir em frente e combinar todas as fórmulas acima em uma só.

=DATE(RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1)),MONTH(DATEVALUE(LEFT(A1, SEARCH("-",A1,1)-1)&"  1")),MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1))

    

Answer 2

Outra abordagem simples é pegar uma string como May-31-2015 e convertê-la em 31-May-2015 . Essa conversão nos permitirá usar a fórmula DATEVALUE () .

Portanto, com dados em A1 , em B1 digite:

=DATEVALUE(SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-","") & "-" & LEFT(A1,3) & "-" & RIGHT(A1,4))

    

Answer 3

Eu sei que a pergunta foi respondida e agradeço todas as entradas. Gostaria de adicionar esta outra opção apenas para o caso de ser útil para alguém:

=DATEVALUE(CONCATENATE(MID(A1,5,2),"-",LEFT(A1,3),"-",RIGHT(A1,4)))

Isso converterá a data do formato de 31 de maio de 2014 para o formato de 5/31/2014. Observe que, no meu caso específico, os meses foram inseridos com apenas três letras (por exemplo, Jul)

Obrigado!