Não consigo que o Excel reconheça a data na coluna

O problema: O Excel não quer reconhecer datas como datas, embora através de “Formatar células - Número - Personalizado” esteja explicitamente a tentar dizer-lhe que são datas por “mm/dd/yyyy”. Como sabe, quando o Excel reconhece algo como uma data, armazena isto como um número - tal como “41004” mas apresenta como data de acordo com o formato especificado. Para aumentar a confusão, o excel pode converter apenas parte das suas datas, tal como 08/04/2009, mas deixe outras, por exemplo, não convertidas.

Solução: passos 1 e depois 2

1) Seleccione a coluna da data. Em Dados, escolha o botão Texto para as colunas. No primeiro ecrã, deixe o botão de rádio em “ delimited” e clique em Next. Desmarque qualquer uma das caixas delimitadoras (quaisquer caixas *em branco; sem marcas de verificação) e clique em **Next. No formato de dados da coluna, escolha *Date*** e seleccione MDY* na caixa combinada adjacente e clique em Finish*. Agora tem valores de data (ou seja, o Excel reconheceu os seus valores como Date data type), mas a formatação é provavelmente ainda a data local, não a mm/dd/yyyy que pretende.

2) Para obter o formato de data desejado nos EUA exibido correctamente precisa primeiro de seleccionar a coluna (se não estiver seleccionada), depois em Cell Format - Number escolha Date* E seleccione Locale : Inglês (US). Isto irá dar-lhe um formato como “m/d/yy”. Depois pode seleccionar Personalizado e aí pode digitar “mm/dd/yyyy” ou escolher isto da lista de cadeias de caracteres personalizadas.

Alternativo : use o LibreOffice Calc. Ao colar dados do correio do Patrick escolha Paste Special (Ctrl+Shift+V) e escolha Unformatted Text. Isto abrirá a caixa de diálogo “Importar Texto”. O conjunto de caracteres permanece Unicode mas para a língua escolha Inglês(EUA); também deve marcar a caixa “Detectar números especiais”. As suas datas aparecem de imediato no formato padrão dos EUA e são datáveis. Se desejar o formato especial dos EUA MM/DD/AAAA, deve especificá-lo uma vez através de “format Cells” - antes ou depois da colagem.

Pode dizer-se que - o Excel deve ter datas reconhecidas assim que eu o disser através de “Cell Format” e I can’t agree more. Infelizmente só a partir do passo 1 é que consegui fazer com que o Excel reconhecesse estas cadeias de texto como datas. Obviamente, se fizer isto muitas vezes é uma dor no pescoço e pode montar uma rotina básica visual que o faria a um aperto de botão.

Data | Text to Columns

Update on leading apostrophe after pasting: Pode ver na barra de fórmulas que na célula onde a data não é reconhecida existe um apóstrofo principal. Isto significa que na célula formatada como um número (ou data) existe uma cadeia de texto. Podes dizer - o apóstrofo principal impede a folha de cálculo de reconhecer o número. Para isso, tens de saber procurar na barra de fórmulas - porque a folha de cálculo mostra simplesmente o que se parece com um número alinhado à esquerda. Para lidar com este problema, seleccione a coluna que pretende corrigir, escolha no menu Data | Text to Columns e clique em OK. Por vezes poderás especificar o tipo de dados, mas se tiveres definido previamente o formato da coluna para ser o teu tipo de dados em particular - não vais precisar dele. O comando destina-se realmente a dividir uma coluna de texto em duas ou mais usando um delimitador, mas também funciona como um encanto para este problema. Testei-o em Libreoffice, mas existe o mesmo item de menu em Excel também.

Seleccione toda a coluna e vá para Locate and Replace e substitua apenas "/" por /.

Isto pode não ser relevante para o autor da pergunta original, mas pode ajudar alguém que não consiga ordenar uma coluna de datas.

Descobri que o Excel não reconheceria uma coluna de datas que fossem todas anteriores a 1900, insistindo que eram uma coluna de texto (uma vez que 1/1/1900 tem o equivalente numérico 1, e aparentemente não são permitidos números negativos). Assim, fiz uma substituição geral de todas as minhas datas (que estavam no século XIX) para as colocar no século XIX, por exemplo 180 -> 190, 181 -> 191, etc. O processo de triagem então funcionou bem. Finalmente fiz uma substituição da outra forma, e.g. 190 -> 180.

Espero que isto ajude algum outro historiador por aí.

Estava a tratar de uma questão semelhante com milhares de linhas extraídas de uma base de dados SAP e, inexplicavelmente, dois formatos de datas diferentes na mesma coluna de datas (sendo a maioria o nosso padrão “YYYY-MM-DD” mas cerca de 10% “MM-DD-YYYY”). A edição manual de 650 linhas uma vez por mês não foi uma opção.

Nenhuma (zero… 0… nula) das opções acima funcionou. Sim, experimentei-as todas. Copiar para um ficheiro de texto ou exportar explicitamente para txt ainda, de alguma forma, não teve qualquer efeito no formato (ou falta dele) das datas de 10% que simplesmente se recusaram a comportar-se.

A única forma de o corrigir foi inserir uma coluna em branco à direita da coluna das datas de comportamento incorrecto e utilizar a seguinte fórmula bastante simplista:

(assumindo que os seus dados de comportamento incorrecto estão em Column D)

=IF(MID(D2,3,1)="-",DATEVALUE(TEXT(CONCATENATE(RIGHT(D2,4),"-",LEFT(D2,5)),"YYYY-MM-DD")),DATEVALUE(TEXT(D2,"YYYY-MM-DD")))

Eu poderia então copiar os resultados na minha nova coluna calculada para além das datas de comportamento incorrecto, colando apenas “Valores” após os quais poderia apagar a minha coluna calculada.

Tive exactamente o mesmo problema com as datas em excelência. O formato correspondia aos requisitos para uma data em Excel, mas sem editar cada célula não reconheceria a data, e quando se lida com milhares de linhas não é prático editar manualmente cada célula. A solução é executar um find e substituir na linha de datas onde eu substituí a hipen por um hífen. O Excel percorre a coluna substituindo-a como por uma semelhante, mas o factor resultante é que agora volta a reconhecer as datas! CORRIGIDO!

Parece que o Excel não reconhece as suas datas como datas, reconhece o seu texto, daí que obtenha as opções de Ordenar como A a Z. Se o fizer correctamente, deverá obter algo como isto:

Daí a importância de assegurar que o Excel reconhece a data. A forma mais simples de o fazer, é usar o atalho CTRL+SHIFT+3.

Aqui está o que fiz aos vossos dados. Simplesmente copiei-os do vosso post acima e colei-os em Excel. Depois apliquei o atalho acima, e obtive a opção de ordenação necessária. Veja a imagem.

Suspeito que a questão é o Excel não conseguir compreender o formato… Embora seleccione o formato Data, este permanece como Texto.

Pode testar isto facilmente: Quando tentar actualizar o formato das datas, seleccione a coluna e clique com o botão direito do rato sobre ela e escolha as células de formato (como já faz) mas escolha a opção 2001-03-14 (perto do fundo da lista). Depois reveja o formato das suas células.

Eu suspeitarei que apenas alguns dos formatos de data actualizam correctamente, indicando que o Excel ainda o trata como uma string, não como uma data (note os diferentes formatos na imagem abaixo)!

Existem soluções para isto, mas, nenhuma delas será automatizada simplesmente porque está a exportar cada vez. Eu sugeriria usar a VBa, onde pode simplesmente correr uma macro que converte do formato de data dos EUA para o Reino Unido, mas, isto significaria copiar e colar a VBa na sua folha de cada vez.

Em alternativa, cria um Excel que lê as novas folhas de Excel exportadas (a partir da troca) e depois executa a VBa para actualizar o formato de data para si. Vou assumir que o ficheiro exportado do Exchange Excel terá sempre o mesmo nome/directório e fornecer um exemplo de trabalho:

Então, crie uma nova folha Excel, chamada ImportedData.xlsm (excel activado). Este é o ficheiro onde vamos importar o ficheiro Export Exchange Excel para!

Adicionar esta VBa ao ficheiro ImportData.xlsm

Sub DoTheCopyBit()

Dim dateCol As String
dateCol = "A" 'UPDATE ME TO THE COLUMN OF THE DATE COLUMN

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "C:\Users\Dave\Desktop\" 'UPDATE ME
fileName = Dir(directory & "ExportedExcel.xlsx") 'UPDATE ME (this is the Exchange exported file location)

Do While fileName <> ""

'MAKE SURE THE EXPORTED FILE IS OPEN
Workbooks.Open (directory & fileName)

Workbooks(fileName).Worksheets("Sheet1").Copy _

Workbooks(fileName).Close

fileName = Dir()

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Dim row As Integer
row = 1
Dim i As Integer
Do While (Range(dateCol & row).Value <> "")

     Dim splitty() As String
     splitty = Split(Range(dateCol & row).Value, "/")
     Range(dateCol & row).NumberFormat = "@"
     Range(dateCol & row).Value = splitty(2) + "/" + splitty(0) + "/" + splitty(1)
     Range(dateCol & row).NumberFormat = "yyyy-mm-dd"
     row = row + 1
Loop

End Sub

O que também fiz foi actualizar o formato da data para yyyy-mm-dd porque desta forma, mesmo que o Excel lhe dê o filtro de classificação A-Z em vez dos valores mais recentes, continua a funcionar!

Tenho a certeza que o código acima tem bugs mas obviamente não faço ideia que tipo de dados têm mas testei-o com uma única coluna de datas (como vocês fizeram) e funciona bem para mim! Como adiciono VBA no MS Office?

https://support.office.com/en-us/article/Convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680

Solução simples aqui - crie uma nova coluna use =datevalue(cell) formula then copy the formula into your other lines- alguns segundos para fixar

Descobri!

Há um espaço no início e no fim da data.

1. Se usar o find and replace e pressionar a barra de espaço, NÃO funcionará.
2. Tem de clicar antes do número do mês, premir shift e a seta para a esquerda para seleccionar e copiar. Por vezes tem de usar o rato para seleccionar o espaço.
3. Depois cole isto como o espaço em find e substitua e todas as suas datas se tornarão datas.
4. Se houver espaço e data Selecione Data>Vá para Data>Texto para colunas>Delimitado>Espaço como separador e depois termine.
5. Todos os espaços serão removidos.

Não estava a ter qualquer sorte com todas as sugestões acima - arranjei uma solução muito simples que funciona como um encanto. Colar os dados no Google Sheets, destacar a coluna da data, clicar no formato, depois no número, e número mais uma vez para alterar para o formato numérico. Depois copio e colo os dados na folha de cálculo Excel, clico nas datas, depois formato as células para datar. Muito rápido. Espero que isto ajude.

Limitei-me a copiar o número 1 (um) e multipliquei-o para a coluna de datas (dados) utilizando a função PASTE SPECIAL. Em seguida, muda para a formatação Geral i.e 42102 e vai aplicar Data na formatação e agora reconhece-a como uma data.

Espero que isto ajude

A minha solução no Reino Unido - tinha as minhas datas com pontos assim:

03.01.17

Resolvi isto com o seguinte:

1. Realce toda a coluna.
2. Ir à procura e seleccionar/substituir.
3. Substituí todos os pontos completos por traço intermédio, por exemplo 03.01.17 03-01-17.
4. Manter a coluna realçada.
5. Formatar células, separador número seleccionar data.
6. Utilizar o tipo 14-03-12 (essencial para que o meu tenha o traço central)
7. Locale English (Reino Unido)

Ao ordenar a coluna, todas as datas do ano são ordenadas.

SHARING A LITTLE LONG BONG BUT MUCH EASIER SOLUTION TO SUBJECT…… No need to run macros or geeky stuff….simple ms excel formula and editing.

mixed dates excel snapshot:

• A data está no formato misto.
  
• Portanto, para fazer um formato de data simétrico, foram criadas colunas extra convertendo essa coluna de data “formato misto” para TEXT.
• A partir desse texto identificamos as posições de “/” e o texto do meio foi extraído determinando a data, mês, ano usando a fórmula MID.
• Aquelas que eram originalmente datas, a fórmula terminou com o resultado ERROR / #VALUE. - Finalmente, a partir da string que criámos - convertemo-las para datas pela fórmula DATE.
• #VALUE foram escolhidas como é por IFERROR adicionada à fórmula DATE e a coluna foi formatada como requerido (aqui, dd/mmm/yyy)

* REFERIR A FOLHA DE EXCELÊNCIA (= datas mistas superam o snapshot) *

Tentei as várias sugestões, mas a solução mais fácil para mim foi a seguinte…

Ver imagens 1 e 2 para o exemplo… (nota - alguns campos foram ocultados intencionalmente por não contribuírem para o exemplo). Espero que isto ajude…

1. Campo C - um formato misto que me deixou absolutamente louco. Foi assim que os dados vieram directamente da base de dados e não tinham espaços extra ou caracteres malucos. Ao apresentá-lo como um texto, por exemplo, o 01/01/2016 exibido como um valor do tipo “42504”, misturado com o 04/15/2006, que mostrou como está.

2. Campo F - onde obtive o comprimento do campo C (a fórmula LEN seria um comprimento de 5 de 10, dependendo do formato da data). O campo é o formato geral para simplificar.

3. Campo G - obtenção da componente mensal da data mista - com base no resultado do comprimento no campo F (5 ou 10), ou obtenho o mês a partir do valor da data no campo C, ou obtenho os caracteres do mês na cadeia.

4. Campo H - obtenção do componente dia da data mista - com base no resultado do comprimento no campo F (5 ou 10), ou obtenho o dia a partir do valor da data no campo C, ou obtenho os caracteres do dia na cadeia.

Posso fazer isto também para o ano, depois criar uma data a partir dos três componentes que seja consistente. Caso contrário, posso usar os valores individuais do dia, mês e ano na minha análise. Imagem 1: folha de cálculo básica mostrando valores e nomes de campos Imagem 2: folha de cálculo mostrando as fórmulas que correspondem à explicação acima

Espero que isto ajude.

Este problema estava a enlouquecer-me, depois tropeçei numa solução fácil. Pelo menos funcionou com os meus dados. É fácil de fazer e de recordar. Mas não sei por que razão isto funciona, mas mudar de tipo, como indicado acima, não funciona. 1. Seleccione a(s) coluna(s) com as datas. 2. Procure por um ano nas suas datas, por exemplo, 2015. Seleccione Substituir Tudo pelo mesmo ano, 2015. 3. 3. Repita para cada ano. Isto muda os tipos para datas reais, ano a ano. Isto ainda é incómodo se tiver muitos anos nos seus dados. Mais rápido é procurar e substituir 201 por 201 (para substituir 2010 até 2019); substituir 200 por 200 (para substituir 2000 até 2009); e assim por diante.

Seleccionar datas e passar este código sobre ele…

On Error Resume Next
    For Each xcell In Selection            
        xcell.Value = CDate(xcell.Value)     
    Next xcell                              
End Sub

Todas as soluções acima - incluindo r0berts - não tiveram sucesso, mas encontrei esta solução bizarra que se revelou ser a solução mais rápida.

Estava a tentar ordenar “datas” numa folha de cálculo de transacções de contas descarregadas.

Isto tinha sido descarregado através do browser CHROME. Nenhuma quantidade de manipulação das “datas” faria com que fossem reconhecidas como sendo de velho para novo.

Mas, depois descarreguei através do browser INTERNET EXPLORER - espantoso - consegui ordenar instantaneamente sem tocar numa coluna.

Não consigo explicar porque é que diferentes browsers afectam a formatação dos dados, a não ser que o fez claramente e foi uma correcção muito rápida.

Se o Excel se recusar obstinadamente a reconhecer a sua coluna como data, substitua-a por outra:

• Adicione uma nova coluna à direita da coluna antiga
• Clique com o botão direito do rato na nova coluna e seleccione Format
• Defina o formato para date
• Realce toda a coluna antiga e copie-a
• Realce a célula superior da nova coluna e seleccione Paste Special, e apenas cole values
• Pode agora remover a coluna antiga.

Isto acontece a toda a hora quando se trocam dados de datas entre locais em Excel. Se tiver controlo sobre o programa VBA que exporta os dados, sugiro a exportação do número que representa a data em vez da própria data. O número correlaciona-se exclusivamente com uma única data, independentemente da formatação e do locale. Por exemplo, em vez do ficheiro CSV que mostra 24/09/2010, irá mostrar 40445.

No laço de exportação, quando se detém cada célula, se for uma data, converta para número usando CLng(myDateValue). Este é um exemplo do meu laço que percorre todas as linhas de uma tabela e exporta para CSV (nota: também substituo as vírgulas nas cordas por uma etiqueta que retiro quando importo, mas pode não precisar disto):

arr = Worksheets(strSheetName).Range(strTableName & "[#Data]").Value

    For i = LBound(arr, 1) To UBound(arr, 1)
        strLine = ""
        For j = LBound(arr, 2) To UBound(arr, 2) - 1
            varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
            strLine = strLine & varCurrentValue & ","
        Next j
        'Last column - not adding comma
        varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
        strLine = strLine & varCurrentValue

        strLine = Replace(strLine, vbCrLf, "<vbCrLf>") 'replace all vbCrLf with tag - to avoid new line in output file
        strLine = Replace(strLine, vbLf, "<vbLf>") 'replace all vbLf with tag - to avoid new line in output file
        Print #intFileHandle, strLine
    Next i

Eu uso um mac.

Go to excel preferences> Edit> Date options> Converte automaticamente o sistema de datas

Também,

Go to Tables & Filters> Group dates when filtering.

O problema provavelmente começou quando você recebeu um arquivo com um sistema de datas diferente, e isso estragou a função de data do seu excel

Eu normalmente apenas crio uma coluna extra para fins de triagem ou de totalização, portanto use year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1).

Que irá fornecer um resultado yyyymmdd que pode ser triado. A utilização do len(a1) permite apenas adicionar um zero extra durante os meses 1-9.