Múltiplos formatos de data em uma única coluna no excel

Question

Eu tenho um arquivo csv com uma coluna de data com datas em vários formulários da seguinte forma:

dd-mm-yyyy

yyyy-mm-dd

yyyy-mm

yyyy

Esses dados irregulares estão em uma única coluna.

Como faço para converter todos eles para o dd-mm-yyyy e extrair o ano deles usando a fórmula do Excel? A saída desejada é a seguinte:

Input       Output
25-03-1954  25-03-1954
22-09-1987  22-09-1987
1990-01-25  25-01-1990
1968-11-15  15-11-1968
1919-01     01-01-1919
1873-02     01-02-1873
1945        01-01-1945
1933        01-01-1933

    

Answer 1

Se tudo o que você realmente precisa é o Ano, use a fórmula @robinCTS.

SE você quiser que o resultado seja "Datas reais do Excel" e

• se seus dados forem TEXTO e não forem datas reais,
• e se suas configurações de data regionais forem DMY ,
• e a coluna está formatada como dd-mm-yyyy

você pode usar essa fórmula:

=IFERROR(IFERROR(IFERROR(DATEVALUE(A2),DATEVALUE(A2&"-01")),DATEVALUE(A2&"-01-01")),"illegal date format")

Observe que as datas anteriores a 1900 não podem ser datas reais no Excel, mas podem ser representadas como sequências de texto. Vou deixar para você ajustar a fórmula.

Se a sua saída precisar estar ciente internacionalmente de países que possuem outros formatos de data, uma solução de VBA poderá ser melhor. Se isso não for possível, colunas auxiliares para dividir os dados e, em seguida, remontar apropriadamente, ou uma fórmula muito complexa para fazer o mesmo, poderiam ser usadas.

Observe que essa UDF produzirá a data real ou apenas o ano, dependendo do segundo argumento opcional.

Option Explicit
Function ConvertToDate(S As String, Optional Yr As Boolean = False) As Variant
    Dim V
    Dim dtTemp As Date

V = Split(S, "-")
Select Case UBound(V)
    Case 0
        dtTemp = DateSerial(V(0), 1, 1)
    Case 1
        dtTemp = DateSerial(V(0), V(1), 1)
    Case 2
        If Len(V(0)) = 4 Then
            dtTemp = CDate(S)
        Else
            dtTemp = DateSerial(V(2), V(1), V(0))
        End If
End Select

If Yr = True Then
    ConvertToDate = Year(dtTemp)
Else
    If Year(dtTemp) < 1900 Then
        ConvertToDate = Format(dtTemp, "dd-mm-yyyy")
    Else
        ConvertToDate = dtTemp
    End If
End If

End Function

Ambos os métodos analisam o primeiro segmento e assumem que é um ano se LEN > 4 e, em seguida, examinam quantos segmentos decidir se anexam os -01 's necessários

A solução do VBA emitirá datas anteriores a 1900 como uma cadeia de texto.

    

Answer 2

Como seus dados de entrada contêm datas anteriores a 1900, as coisas ficam um pouco mais complicadas. A maneira mais simples de lidar com isso é trabalhar exclusivamente com as representações textuais das datas.

Se você realmente não precisar da data completa, mas apenas precisar do ano, insira essa fórmula básica em qualquer célula da linha 2:2 :

=IFERROR(VALUE(LEFT(A2,4)),VALUE(RIGHT(A2,4)))

Para a data completa, a fórmula é um pouco mais complicada:

=IF(MID(A2,3,1)="-",A2,CHOOSE((LEN(A2)-4)/3+1,"01-01","01"&MID(A2,5,3),RIGHT(A2,2)&MID(A2,5,3))&"-"&LEFT(A2,4))

Explicação:

A versão pretendida da fórmula de data completa é a seguinte:

=
IF(
  MID(A2,3,1)="-",
  A2,
  CHOOSE(
    (LEN(A2)-4)/3+1,
    "01-01",
    "01"&MID(A2,5,3),
    RIGHT(A2,2)&MID(A2,5,3)
  )
  &"-"&LEFT(A2,4)
)

É bastante simples. A única coisa um pouco complicada é o primeiro argumento da função CHOOSE() , (LEN(A2)-4)/3+1 . Isso mapeia os comprimentos das strings de data de entrada para índices baseados em 1, ou seja, [yyyy, yyyy-mm, yyyy-mm-dd, dd-mm-yyyy] → [4, 7, 10, 10] → [1, 2, 3, 3] .

Notas:

• A fórmula prettificada realmente funciona se for inserida.

Advertências:

• As fórmulas assumem que todas as datas de entrada são armazenadas como texto. (As fórmulas são facilmente modificáveis para atender a datas armazenadas como números de série.)
• Se as datas de entrada contiver espaços iniciais / finais, as fórmulas acima podem não funcionar corretamente. Eles definitivamente não funcionarão com espaços (ou outros caracteres) em outras partes das strings de data. Substituir todos os A2 s nas fórmulas por TRIM(A2) , ou melhor ainda com SUBSTITUTE(A2," ","") , resolverá esse problema.