Como converter datas malformadas em formatos mistos?

Question

Estou trabalhando em um projeto para limpar os dados da minha empresa. Um arquivo particularmente horrível é uma planilha do Excel com aproximadamente 30.000 linhas.

As colunas de data nesta planilha são preenchidas com datas em muitos formatos diferentes, muitos dos quais não são formatos de data válidos do Excel.

Aqui estão alguns dos formatos:

8/26/2017
20170925
2017-08-10
43026
43040.46597

Eu preciso de alguma forma obter todos eles para um formato padrão sem timestamp.

Devido a tantos formatos diferentes, tentei verificar o LEN() para agrupar os formatos (embora alguns formatos diferentes também tenham o mesmo tamanho).

Eu passei horas literais passando por vários métodos para converter as datas e funciona bem para obter o formato correto em uma nova coluna, mas não consigo copiar / colar o formato corrigido de volta na coluna original devido à filtragem. precisa usar.

Selecionar apenas as linhas visíveis funciona bem, mas ao colar, acabo perdendo muitos dados nas linhas ocultas, não importa o que eu faça.

Existe alguma maneira de corrigir isso sem ter que percorrer todas as 30.000 linhas uma de cada vez e copiar / colar ou digitar manualmente o formato de data correto?

    

Answer 1

Seu problema real parece ser a possibilidade de mover os dados corrigidos de uma coluna de volta para a coluna original.

Uma maneira de contornar esses problemas de copiar / colar nas linhas filtradas é localizar a coluna com os dados corrigidos adjacentes à coluna de dados Original à esquerda.

Em seguida, selecione o intervalo de valores na coluna correta e pressione Ctrl + Shift + Seta para direita para selecionar as células correspondentes na coluna original e depois Ctrl + R.

Isso copiará os dados da maioria das células à esquerda de sua seleção para o restante das células da sua seleção, sem tocar nos dados das linhas não visíveis.

Espero que isso ajude!

    

Answer 2

Uma solução fora do Excel usando dateconv de dateutils :

$ dateconv -i %F -i %m/%d/%Y -i %Y%m%d <<EOF
8/26/2017
20170925
2017-08-10
EOF
=>
  2017-08-26
  2017-09-25
  2017-08-10

A idéia é especificar todos os possíveis formatos de entrada usando vários -i flags. O formato de saída padrão é %F , mas pode ser alterado usando -f .

Eu não sei como converter os dois últimos números, não é uma coisa de número de dia padrão (como os dias de Matlab desde 0000-01-01 ou o número do dia de Julian ou o número do dia de Lilian).