Não é possível obter o Excel para reconhecer a data na coluna

Question

Eu constantemente tenho problemas para trabalhar com datas no Excel, eu devo estar fazendo algo errado, mas eu não entendo o que.

Eu tenho uma planilha, exportada do nosso servidor Exchange, que contém uma coluna com datas. Eles saíram no formato norte-americano, apesar de eu estar no Reino Unido.

A coluna em questão é assim

04/08/2012
04/09/2009
04/01/2010
04/21/2011
04/05/2012
08/30/2009
08/29/2010
08/28/2011

No Excel, destaquei a coluna e selecionei Format Cells... . Nessa caixa de diálogo, selecionei a Date , selecionei English (United States) como a localidade e escolhi o formato de data correspondente na lista. Eu bati OK e tente classificar os dados por esta coluna.

Na caixa de diálogo "Classificar", escolho essa coluna, selecione "Classificar em valores", mas a ordem só me fornece opções de A a Z, e não mais antigas para as mais recentes, como seria de esperar.

Isso, por sua vez, classifica os dados de data pelos dois primeiros dígitos.

Estou ciente que eu poderia re-formatar esses dados para ISO e, em seguida, o tipo de A a Z funcionaria, mas eu não deveria ter também, obviamente estou faltando alguma coisa. O que é isso?

EDIT: Eu estraguei a recompensa, mas isso deveria ter ido para @ r0berts responder, sua primeira sugestão de texto para colunas sem delimitador e escolhendo 'MDY' como o tipo de dados funciona. Além disso, se você tiver um tempo (por exemplo, 04/21/2015 18:34:22 ), será necessário primeiro eliminar os dados de tempo. No entanto, depois disso, o método sugerido por @ r0berts funciona bem.

    

Answer 1

O problema: O Excel não quer reconhecer as datas como datas, embora através de "Formatar células - Número - Personalizado" você esteja explicitamente tentando dizer que estas são datas " mm/dd/yyyy " . Como você sabe; Quando o Excel reconhece algo como uma data, ele também armazena isso como um número - como " 41004 ", mas é exibido como data de acordo com o formato especificado. Para aumentar a confusão, o Excel pode converter apenas parte de suas datas, como 08/04/2009, mas deixar outras, e. 28/07/2009 não convertido.

Solução: etapas 1 e 2

1) Selecione a coluna de data. Em Dados, escolha o botão Texto para Colunas. Na primeira tela, deixe o botão de opção " delimitado " e clique em Próximo . Desmarque qualquer uma das caixas de delimitadores (quaisquer caixas em branco ; sem marcas de verificação) e clique em Próximo . No formato de dados da coluna, escolha Data e selecione MDY na caixa de combinação adjacente e clique em Concluir . Agora você tem valores de data (ou seja, o Excel reconheceu seus valores como Date data type), mas a formatação provavelmente ainda é a data de localidade, não a mm/dd/yyyy desejada.

2) Para obter o formato de data desejado dos EUA exibido corretamente, primeiro é necessário selecionar a coluna (se não selecionado) e, em Formato da célula - Número escolha Data e selecione Idioma: Inglês (EUA) . Isto lhe dará o formato como " m/d/yy ". Em seguida, você pode selecionar Personalizado e lá você pode digitar " mm/dd/yyyy " ou escolher isso na lista de sequências personalizadas.

Alternativa : use o LibreOffice Calc. Ao colar dados da postagem de Patrick, escolha Colar especial ( Ctrl+Shift+V ) e escolha Texto não formatado. Isso abrirá a caixa de diálogo "Importar texto". O conjunto de caracteres permanece em Unicode, mas para idioma, escolha inglês (EUA); você também deve marcar a caixa "Detectar números especiais". Suas datas aparecem imediatamente no formato padrão dos EUA e são classificáveis por data. Se você deseja o formato especial US MM / DD / AAAA, você precisa especificá-lo uma vez através de "Células de formato" - antes ou depois de colar.

Pode-se dizer - o Excel deveria ter datas reconhecidas assim que eu as contasse via "Cell Format" e eu não poderia concordar mais . Infelizmente, é somente através da etapa 1 acima que consegui fazer o Excel reconhecer essas strings de texto como datas. Obviamente, se você faz isso muito, é dor no pescoço e você pode montar uma rotina básica visual que faria isso para você com o apertar de um botão.

    

Answer 2

Selecione toda a coluna e vá para Localizar e substituir e apenas substitua "/" por / .

    

Answer 3

Eu tive exatamente o mesmo problema com as datas no Excel. O formato correspondia aos requisitos para uma data no Excel, mas sem editar cada célula, ela não reconheceria a data e, quando você estiver lidando com milhares de linhas, não é prático editar manualmente cada célula. A solução é executar um localizar e substituir na linha de datas em que substituí o hífen por um hífen. Excel corre através da coluna substituindo como com gosto, mas o fator resultante é que agora reconhece as datas! FIXO!

    

Answer 4

Isso pode não ser relevante para o questionador original, mas pode ajudar alguém que não possa classificar uma coluna de datas.

Eu descobri que o Excel não reconheceria uma coluna de datas que eram todas antes de 1900, insistindo que elas eram uma coluna de texto (desde 1/1/1900 tem o equivalente numérico 1, e números negativos aparentemente não são permitidos). Então eu fiz uma substituição geral de todas as minhas datas (que eram em 1800) para colocá-las nos anos 1900, por exemplo 180 - > 190, 181 - > 191, etc. O processo de classificação funcionou bem. Finalmente eu fiz uma substituição do outro lado, por exemplo 190 - > 180.

Espero que isso ajude algum outro historiador por aí.

    

Answer 5

Parece que o Excel não reconhece suas datas como datas, ele reconhece o texto, portanto, você obtém as opções de classificação como A a Z. Se fizer isso corretamente, você deve obter algo assim:

Portanto,éimportantegarantirqueoExcelreconheçaadata.Amaneiramaissimplesdefazerisso,éusaroatalhoCTRL+SHIFT+3.

Vejaoqueeufizcomseusdados.Eusimplesmentecopieidoseupostacimaecoleinoexcel.Emseguida,apliqueioatalhoacimaeobtiveaopçãodeclassificaçãonecessária.Verifiqueaimagem.

    

Answer 6

Eu suspeito que o problema é o Excel não ser capaz de entender o formato ... Embora você selecione o formato de Data, ele permanece como Texto.

Você pode testar isso facilmente: Quando você tenta atualizar o formato de datas, selecione a coluna e clique com o botão direito nela e escolha formatar células (como você já faz), mas escolha a opção 2001-03-14 (perto da parte inferior da Lista). Em seguida, revise o formato das suas células.

Eu suspeitarei que apenas alguns dos formatos de data sejam atualizados corretamente, indicando que o Excel ainda o trata como uma string, não como uma data (observe os diferentes formatos na captura de tela abaixo)!

Existemalternativasparaisso,masnenhumadelasseráautomatizadasimplesmenteporquevocêestáexportandotodasasvezes.EusugeririausaroVBa,ondevocêpodesimplesmenteexecutarumamacroqueconvertedosEUAparaoformatodedatadoReinoUnido,masissosignificariacopiarecolaroVBaemsuaplanilhaacadavez.

Comoalternativa,vocêcriaumExcelquelêasplanilhasExcelrecém-criadas(datroca)e,emseguida,executaoVBaparaatualizaroformatodedataparavocê.AssumireiqueoarquivodoExceldoExchangeexportadosempreteráomesmonomedearquivo/diretórioeforneçaumexemplodetrabalho:

Portanto,crieumanovaplanilhadoExcel,chamadaImportedData.xlsm(excelativado).EsteéoarquivoparaoqualimportaremosoarquivoexportadodoExceldoExchange!

AdicioneesteVBaaoarquivoImportedData.xlsm

SubDoTheCopyBit()DimdateColAsStringdateCol="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 eu também fiz foi atualizar o formato de data para aaaa-mm-dd porque, mesmo que o Excel forneça o filtro de classificação A-Z em vez dos valores mais recentes, ele ainda funciona!

Tenho certeza que o código acima tem erros, mas obviamente não tenho idéia do tipo de dados que você tem, mas testei com uma única coluna de datas (como você tem) e funciona bem para mim!

Como eu adiciono o VBA no MS Office?

    

Answer 7

Eu estava lidando com um problema semelhante com milhares de linhas extraídas de um banco de dados SAP e, inexplicavelmente, dois formatos de data diferentes na mesma coluna de data (sendo a maioria o nosso padrão "AAAA-MM-DD", mas cerca de 10% "MM-DD-YYY"). Editar manualmente 650 linhas uma vez por mês não era uma opção.

Nenhum (zero ... 0 ... nada) das opções acima trabalhadas. Sim, eu tentei todos eles. Copiar para um arquivo de texto ou exportar explicitamente para txt ainda, de alguma forma, não teve efeito no formato (ou falta dela) das datas de 10% que simplesmente ficaram no canto recusando-se a se comportar.

A única maneira de corrigi-lo foi inserir uma coluna em branco à direita da coluna de data com comportamento incorreto e usar a seguinte fórmula bastante simplista:

(supondo que seus dados de mau comportamento estejam 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 em minha nova coluna calculada sobre as datas com comportamento incorreto colando "Valores" somente depois do que eu poderia excluir minha coluna calculada.

    

Answer 8

link

Solução simples aqui - crie uma nova coluna use = datevalue (cell) e copie a fórmula para as outras linhas - alguns segundos para corrigir

    

Answer 9

Eu descobri!

Existe um espaço no início e no final da data.

1. Se você usar localizar e substituir e pressionar a barra de espaço, NÃO trabalhos.
2. Você precisa clicar logo antes do número do mês, pressione shift e a seta para a esquerda para selecionar e copiar. Às vezes você precisa usar o mouse para selecionar o espaço.
3. Em seguida, cole isso como o espaço em localizar e substitua e todas as datas serão datas.
4. Se houver espaço e data, selecione Dados > Vá para Dados > Texto para colunas > Delimitado > Espaço como separador e, em seguida, terminar.
5. Todos os espaços serão removidos.

Answer 10

Todas as soluções acima - incluindo r0berts - não tiveram sucesso, mas encontraram essa solução bizarra que acabou sendo a solução mais rápida.

Eu estava tentando classificar "datas" em uma planilha baixada de transações de conta.

Isso foi baixado via navegador CHROME. Nenhuma quantidade de manipulação das "datas" faria com que fossem reconhecidas como do tipo antigo para novo.

Mas, depois, baixei pelo navegador INTERNET EXPLORER - incrível - consegui classificar instantaneamente sem tocar em uma coluna.

Não consigo explicar por que navegadores diferentes afetam a formatação dos dados, exceto que isso claramente funcionou e foi uma solução muito rápida.

    

Answer 11

Minha solução no Reino Unido - eu tinha minhas datas com pontos nelas assim:

03.01.17

Eu resolvi isso com o seguinte:

1. Realce a coluna inteira.
2. Ir para encontrar e selecionar / substituir.
3. eu substitui todos os pontos completos com traço do meio, por exemplo, 03.01.17 03-01-17.
4. Mantenha a coluna destacada.
5. Formatar células, tabulação numérica, selecionar data.
6. Use o tipo 14-03-12 (essencial para o meu ter o traço do meio)
7. Inglês local (Reino Unido)

Ao classificar a coluna, todas as datas do ano são classificadas.

    

Answer 12

Então eu tive o mesmo problema. Nenhuma das soluções acima funcionou para mim ou eu as achei muito complexas para o meu nível de habilidade.

O que acabei fazendo foi fazer um texto para as colunas no delimitador de data. Em seguida, usei a função date () para recriar a data usando as células dia, mês e ano. Trabalhou.

    

Answer 13

Eu tentei as várias sugestões, mas encontrei a solução mais fácil para mim foi a seguinte ...

Veja as imagens 1 e 2 para o exemplo ... (note - alguns campos foram escondidos intencionalmente, pois não contribuem para o exemplo). Espero que isso ajude ...

1. Campo C - um formato misto que me deixou absolutamente louco. É assim que os dados vieram diretamente do banco de dados e não tinham espaços extras ou caracteres malucos. Ao exibi-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 é.

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 é formato geral para simplificar.

3. Campo G - obtendo o componente do mês da data mista - com base no resultado do tamanho no campo F (5 ou 10), eu obtenho o mês do valor de data no campo C, ou obtenho os caracteres do mês na string.

4. Campo H - obtendo o componente dia da data mista - com base no resultado do tamanho no campo F (5 ou 10), eu obtenho o dia do valor da data no campo C, ou obtenho os caracteres do dia na string.

Também posso fazer isso para o ano e criar uma data dos três componentes que seja consistente. Caso contrário, posso usar os valores de dia, mês e ano individuais em minha análise.

Imagem 1: planilha básica mostrando valores e nomes de campos

Imagem 2: planilha mostrando as fórmulas que correspondem à explicação acima

Espero que isso ajude.

    

Answer 14

COMPARTILHANDO UMA SOLUÇÃO LONGA MAS MUITO MAIS FÁCIL PARA ASSUNTOS ..... Não há necessidade de executar macros ou coisas nerds .... simples ms excel fórmulas e edição.

datas combinadas excel snapshot:

• Adataestánoformatomisto.
• Portanto,parafazerumsimétricoformatodedata,foramcriadascolunasextrasqueconvertemcolunadedatadoformato'paraTEXTO.
• Apartirdessetexto,identificamosoposiçõesde"/" e meio texto foi extraído determinando data, mês, ano usando a fórmula MID.
• Aqueles que originalmente eram datas, a fórmula terminou com ERROR / #VALUE result. - Finalmente, da string que criamos, convertemos esses dados em datas pela fórmula DATE.
• #VALUE foram escolhidos assim como IFERROR foi adicionado à fórmula DATE & a coluna foi formatada conforme necessário (aqui, dd / mmm / aa)

* CONSULTE O INSTANTÂNEO DA FOLHA DE EXCEL ACIMA (= datas combinadas excel instantâneo) *

    

Answer 15

Selecione as datas e execute o código acima dele.

On Error Resume Next     Para cada xcell na seleção         xcell.Value = CDate (xcell.Value)
    Próximo xcell
End Sub

    

Answer 16

Isso acontece o tempo todo ao trocar dados de data entre locais no Excel. Se você tiver controle sobre o programa VBA que exporta os dados, sugiro exportar o número que representa a data em vez da data propriamente dita. O número se correlaciona exclusivamente com uma única data, independentemente da formatação e do código do idioma. Por exemplo, em vez do arquivo CSV mostrando 24/09/2010, ele mostrará 40445.

No loop de exportação quando você segura cada célula, se for uma data, converta para número usando CLng (myDateValue). Este é um exemplo do meu loop executando todas as linhas de uma tabela e exportando para CSV (observe que eu também substituo vírgulas em strings por uma tag que eu removo quando importo, mas você pode não precisar disso):

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

    

Answer 17

Descobri que CAST AS smalldatetime resolve meu problema. Solução encontrada aqui: link

Isso, claro, só funciona se você tiver acesso à consulta subjacente.

    

Answer 18

Eu não estava tendo sorte com todas as sugestões acima - surgiu com uma solução muito simples que funciona como um encanto. Cole os dados no Planilhas Google, destaque a coluna da data, clique no formato, em seguida, em número e em número novamente para alterá-lo para o formato numérico. Em seguida, copio e colo os dados na planilha do Excel, clico nas datas e, em seguida, formato as células até a data. Muito rápido. Espero que isso ajude.

    

Answer 19

Este problema estava me deixando louco, então eu tropecei em uma solução fácil. Pelo menos funcionou para meus dados. É fácil de fazer e lembrar. Mas eu não sei porque isso funciona, mas a mudança de tipos, como indicado acima, não. 1. Selecione a (s) coluna (s) com as datas 2. Pesquise por um ano em suas datas, por exemplo, 2015. Selecione Substituir Tudo pelo mesmo ano de 2015. 3. Repita para cada ano. Isso altera os tipos para datas reais, ano a ano.
Isso ainda é complicado se você tiver muitos anos em seus dados. Mais rápido é procurar e substituir 201 por 201 (para substituir 2010 a 2019); substituir 200 por 200 (para substituir 2000 a 2009); e assim por diante.

    

Answer 20

Consegui corrigir o problema depois de perceber que o "Show Formulas" foi selecionado. no Excel 2016, vá para Fórmulas, depois na seção Auditoria de fórmulas e verifique se "Mostrar fórmulas" não está selecionado. Corrigido meu problema.

    

Answer 21

Eu tive o problema semelhante, mas notei que eu tinha um espaço extra no final da data. Eu removi os espaços com o comando replace e agora a classificação de data funciona bem.

    

Answer 22

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

• Adicione uma nova coluna à direita da coluna antiga
• Clique com o botão direito do mouse na nova coluna e selecione Format
• Defina o formato como date
• Realce toda a coluna antiga e copie-a
• Realce a célula superior da nova coluna e selecione Paste Special e cole apenas values
• Agora você pode remover a coluna antiga.

Answer 23

Teve problema semelhante, tentei formatar os dados como data, mas sem sucesso. Então percebi que havia um espaço de liderança na data, editei o campo e retirei o espaço - ei tudo pronto ok. Não ótimo para grandes quantidades de dados, mas pensando nisso eu poderia ter verificado e editado os dados no Wordpad ou em um editor similar.

    

Answer 24

Eu simplesmente copiei o número 1 (um) e o multipliquei para a coluna de data (dados) usando a função PASTE SPECIAL. Em seguida, muda para a formatação geral , ou seja, 42102 Em seguida, vá aplicar a Data na formatação e ela agora a reconhece como uma data.

Espero que isso ajude

    

Answer 25

Ainda tive problemas depois de seguir várias respostas. Do que no formato, escolheu 14-Mar-01 e mudou de localidade para Inglês (Estados Unidos) .. E funcionou perfeitamente. Espero que isto ajude.

    

Answer 26

Eu uso um mac.

Ir para as preferências do Excel > Editar > Opções de data > Automaticamente converter sistema de data

Além disso,

Ir para Tabelas & Filtros > Agrupar datas ao filtrar.

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

    

Answer 27

Eu encontrei uma resposta muito fácil. As datas foram formatadas em 10.11.2018 e não estavam sendo reconhecidas como datas. Tendo primeiro formatado a coluna como datas, eu fiz uma pesquisa Ctrl-H e substituir "." com "/". Todas as datas foram instantaneamente reconhecidas.

    

Answer 28

Eu costumo apenas criar uma coluna extra para fins de classificação ou totalização, então use year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1) .

Isso fornecerá um resultado yyyymmdd que pode ser classificado. Usar o len(a1) apenas permite que um zero extra seja adicionado nos meses de 1 a 9.