Convertendo data de texto em formato de data padrão no Excel 2010

Question

Eu tenho uma planilha do Excel 2010 com um grande número de datas para o ano de 2014 inseridas no seguinte formato: Thursday 24th July que preciso converter em formato de data e hora padrão (o resultado final será importado em uma tabela SQL e as datas serão como YYYYMMDD , então a data mencionada acima precisaria ser 20140724 , posso lidar com essa conversão, mas o Excel não reconhecerá Thursday 24th July como uma data).

    

Answer 1

OK, então esta é uma fórmula muito feia, e tenho certeza que alguém pode criar um produto muito mais limpo. Ainda testei e funciona, o que eu acho que é o que importa.

Em uma coluna adjacente, insira esta fórmula:

Onde as datas começam em A2

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
  RIGHT($A2,LEN($A2)-FIND(" ",$A2)),"th","",1),"rd","",1),"st","",1"),"nd","",1))

DATEVALUE converte uma data armazenada como texto em um código de data do Excel, para que possa ser formatado corretamente, mas a data de 24 de julho tem alguns problemas;

1. DATEVALUE não pode fazer uso do dia da semana e, de qualquer maneira, não é relevante
2. os números th ou rd no final do dia também são valores que a fórmula não pode usar
3. Não há ano armazenado nesta data

Esta fórmula não pode fazer nada sobre o ano, mas o que ela faz é isso (trabalhando do meio):

RIGHT($A2,LEN($A2)-FIND(" ",$A2))

Retorna o número do dia e mês descobrindo quanto tempo a data do texto está e subtraindo o número de caracteres entre o início e o primeiro espaço (o espaço após o nome do dia)

SUBSTITUTE([FindText],"th","")

Substitua qualquer instância de th na string retornada por nada

SUBSTITUTE([FirstSubstitute],"rd","")

Substitua qualquer instância de rd na string retornada por nada

=DATEVALUE([StringReplacers])

Converta o resultado em uma data. Com nenhum ano fornecido, ele assumirá o ano atual.

    

Answer 2

Encontrei uma solução para isso enquanto escrevia a pergunta, então pensei em enviá-la de qualquer maneira, caso isso ajude alguém:

Não é uma solução ideal, mas parece funcionar. Várias ações de lote Localizar / Substituir para converter as datas para o que o Excel entenderá. Importante observar o espaçamento, faça as buscas excluindo os apóstrofos, mas observe se existe um espaço dentro deles

1. Localizar / Substituir, localize 'Monday ' e substitua por '' , mesmo por 'Tuesday ' , 'Wednesday ' etc, para remover os dias da data.
2. Localize / substitua, localize 'January' e substitua por 'January 2014' , o mesmo para 'February' , 'March' etc para adicionar o ano que você deseja
3. Localize / substitua, encontre 'st ' e substitua por ' ' , mesmo para 'nd ' , 'rd ' e 'th '

Depois, você pode formatar células como data e o Excel deve reconhecê-las e convertê-las. O espaçamento correto na pesquisa / substituição é importante porque você não deseja encontrar 'st' para '1st' e remover o final de 'August'

    

Answer 3

Bem, você pode usar algo um pouco mais curto:

=(IFERROR(MID(K32,FIND(" ",K32)+1,2)*1,MID(K32,FIND(" ",K32)+1,1))&
  MID(K32,FIND(" ",SUBSTITUTE(K32," ","",1))+1,100))*1

A primeira parte:

IFERROR(MID(K32,FIND(" ",K32)+1,2)*1,MID(K32,FIND(" ",K32)+1,1))

Extrai a data. Ele tenta extrair 2 caracteres após o primeiro espaço primeiro e, se for um número (não retorna um erro quando multiplicado por 1), adiciona-o à segunda parte, que é o mês.

Se isso for um erro (por exemplo, dois primeiros caracteres em 1st são 1s , que é um número inválido), então essa é uma data menor que 10 e, portanto, extrair apenas um caractere.

MID(K32,FIND(" ",SUBSTITUTE(K32," ","",1))+1,100)

Extrai o mês com o espaço anterior.

Em seguida, multiplique a coisa toda por 1 para obter o valor de dados. Você pode formatar como yyyymmdd através de formatação personalizada e as datas estarão prontas para serem importadas para o SQL.

Assim como na fórmula do @ClockeWork, como nenhum ano é mencionado na fórmula, a planilha terá o ano atual. Se você precisar especificá-lo, você terá que fazê-lo manualmente, por exemplo, assim:

=(IFERROR(MID(K32,FIND(" ",K32)+1,2)*1,MID(K32,FIND(" ",K32)+1,1))&
  MID(K32,FIND(" ",SUBSTITUTE(K32," ","",1))+1,100)&" 2013")*1

Para o ano de 2013.

    

Answer 4

Você pode usar essa fórmula para converter para uma data em 2014

=(REPLACE(REPLACE(A2,FIND(" ",A2,11)-2,2,""),1,FIND(" ",A2),"")&2014)+0

que retornará o número de série da data, formato no formato de data necessário

A primeira função FIND localiza o primeiro espaço no caractere 11 ou posterior (que deve ser o segundo espaço na string dada a duração dos dias da semana) e, com base nisso, REPLACE remove os dois caracteres imediatamente antes (que será o "st", "th", "rd" ou "nd")

... e depois o segundo FIND encontra o primeiro espaço e REPLACE substitui todo o texto anterior, deixando-o com apenas 24 de julho ou similar

Você pode então concatenar o ano [2014] até o final e usar +0 para co-perfazer uma data

Se você quiser ir diretamente para 20140724 (como uma string), você pode usar esta versão

=2014&TEXT(REPLACE(REPLACE(A2,FIND(" ",A2,11)-2,2,""),1,FIND(" ",A2),""),"mmdd")