Como posso converter uma célula como “12/04/2003” em formato de data?

Question

21/03/2004 não se registra como data, enquanto 7/3/2004 se registra como data. Alguém sabe como eu poderia remover todos os 0s em valores de datas com valores de mês começando em 0?

Assim, as colunas que estão coladas à margem esquerda de alguma forma não são autoconvertidas. as colunas coladas à direita estão bem.

    

Answer 1

Normalmente, isso pode ser resolvido usando a função DATEVALUE para converter uma data que é texto em um valor de data do Excel (ou seja, um número que o Excel reconhecerá como data). O uso seria DATEVALUE (date_string ou cell_address).

No entanto, isso não funcionará aqui porque 1) Dia precede Mês em suas sequências de caracteres de exemplo e DATEVALUE espera Mês seguido por Dia e 2) para algumas das seqüências de data em que um horário também é incluído.

Aqui está uma alternativa de fórmula de matriz que funciona com os exemplos que você forneceu. Ele precisaria ser inserido usando a combinação de teclas Controle - Shift - Enter .

  =DATE(
        RIGHT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),MATCH("/",MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-ROW(1:25),1),0)),

        LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-1),

        LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-MATCH("/",MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-ROW(1:25),1),0)-LEN(LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))+1)))
        ) 

Essa fórmula de matriz muito complicada (espero que alguém possa oferecer uma mais simples) usa a função DATE para construir um valor de data do Excel. (Aconteceu que eu tenho a string de data original na célula F8 quando construí a data.)

A string de data que precisa ser convertida não pode ser usada em sua forma bruta porque às vezes inclui um horário. O que é usado em vez disso é RIGHT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8) . Essa fórmula procura por um espaço na seqüência de data. Se encontrar um espaço, ele usa a função LEFT para obter todos os caracteres à esquerda do espaço; se não encontrar um espaço, simplesmente retorna a string original.

A parte mais externa da fórmula é a função DATE: quando recebe o ano, mês e dia, produz uma data do Excel. Eu quebrei o cálculo dessas três partes no código acima.

O cálculo do ano é o seguinte:

1. Inverte a string de data (com o tempo cortado como explicado acima).
2. Encontre a posição do primeiro "/" nesta string invertida usando a função SEARCH (Esta barra é na verdade a primeira barra na string de data não revertida.)
3. Leve muitos caracteres à direita da string de data com a função RIGHT.

O cálculo do mês tem os passos:

1. Encontre a posição da primeira barra na sequência de datas não revisto .
2. Determine o número de caracteres no mês (que pode ser 1 ou 2) calculando a diferença entre essa posição e a posição do segundo "/", conforme determinado no cálculo do ano.
3. Use a função MID para retornar os caracteres entre a primeira barra e a segunda barra.

O cálculo do dia é o mais simples.

1. Calcule a posição do primeiro "/" usando o mesmo método do cálculo do mês.
2. Use vários caracteres (menos 1) do lado esquerdo da string usando a função LEFT.

Existem maneiras menos elaboradas de obter o resultado, sendo que o mais simples seria, sem dúvida, o uso do comando Texto para Colunas na guia Dados da Faixa de Opções, conforme sugerido por @Barry.

Outra maneira de proceder é usar uma função de correspondência de padrões REGEX. Essa função não é construída no Excel, mas pode ser construída e acessada com o código VBA. Embora o código seja um pouco complicado, várias versões estão disponíveis na rede (como este exemplo ). No entanto, usar a função seria muito simples. Por exemplo, a expressão de correspondência para obter o dia pode ser algo como REGEXP("./",F8) , que poderia então ser usada na função DATE para obter o valor de data do Excel.

Por conveniência, aqui está a fórmula na sua forma completa.

  =DATE(RIGHT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),MATCH("/",MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-ROW(1:25),1),0)),LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-1),MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))+1)),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-MATCH("/",MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-ROW(1:25),1),0)-LEN(LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))+1))))

    

Answer 2

O valor que cola no lado esquerdo geralmente significa que é um valor textual, enquanto o valor numérico / data é alinhado à direita.

Talvez você também possa verificar essas coisas antes de fazer uma possível conversão de data e hora no Excel:

1. Verifique as configurações de idioma / região do sistema, especialmente o formato de data e hora. O Excel usa as configurações padrão de data e hora do sistema, e a conversão de data e hora será diferente para diferentes regiões como resultado.

2. Verifique se não há espaços em branco extras ou caracteres de quebra de linha ocultos no valor da célula, um valor de data e hora válido com um espaço em branco será tratado como o valor de texto, não o tempo de data. não será convertido automaticamente.

3. Use a opção de formato de célula personalizada do Excel, na qual você pode especificar suas próprias regras de conversão, como DD / MM / AAAA.

Answer 3

Presumivelmente, o formato da fonte é dd / mm / aaaa. Tente usar a funcionalidade "Texto para colunas" para converter.

Selecione a coluna de datas

Dados > Texto para colunas > Avançar > Avançar > em "formato de dados da coluna", selecione "data" e escolha o formato de origem na lista suspensa - "DMY" > OK

    

Answer 4

Eu vi que você acabou de encontrar uma solução, mas se isso funcionar, deve ser uma solução mais simples.

Selecione as células que não estão formatadas como datas. Clique com o botão direito, selecione as células de formato, escolha Números e a categoria Personalizada. Há um formato que é o formato de entrada no campo type, você digita manualmente porque não haverá formato adequado para você. No seu caso, insira a string dd / mm / aaaa, pressione OK. Agora o Excel sabe que esta é uma data (embora com o formato dia do mês), você pode copiar e colar esta célula em uma nova célula que é formatada como uma data do jeito que você quer.

    

Answer 5

O formato de dados padrão no Excel é: dd / m / aaaa. A margem esquerda não é autoconvertida porque você escolhe: m / dd / aaaa. Basta escolher o formato certo para resolver seu problema.