☰

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

tag-icon • tag-icon microsoft-excel
Como posso converter uma célula como "04/12/2003" em formato de data?

21/03/2004 não é registrado como data, enquanto 7/3/2004 é registrado 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?

Portanto, as colunas coladas na margem esquerda de alguma forma não são convertidas automaticamente. as colunas coladas à direita estão bem.

http://puu.sh/27CEm

Responder1

Normalmente, isso poderia 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 strings de exemplo e DATEVALUE espera Mês seguido de Dia e 2) para algumas strings de data, uma hora também é incluída.

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 Control- .ShiftEnter

  =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)))
        )

Esta 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. (Acontece que eu tinha a sequência de data original na célula F8 quando criei a data.)

A sequência de data que precisa ser convertida não pode ser usada em sua forma bruta porque às vezes inclui uma hora. O que é usado é RIGHT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8). Esta fórmula procura um espaço na sequência de datas. Se encontrar um espaço, ele usa a função LEFT para colocar todos os caracteres à esquerda do espaço; se não encontrar espaço, simplesmente retornará a string original.

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

O cálculo do ano é o seguinte:

  1. Inverta a sequência de data (com a hora cortada conforme 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. Pegue tantos caracteres à direita da string de data com a função RIGHT.

O cálculo do Mês segue as etapas:

  1. Encontre a posição da primeira barra nonão revertidosequência de data.
  2. Determine o número de caracteres do mês (que pode ser 1 ou 2) calculando a diferença entre esta 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 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. Pegue tantos caracteres (menos 1) do lado esquerdo da string usando a função LEFT.

Existem formas menos elaboradas de se obter o resultado, a mais simples delas seria sem dúvida a utilização do comando Text to Columns na aba Data da Ribbon, conforme sugerido por @Barry.

Outra maneira de proceder é usar uma função de correspondência de padrões REGEX. Esta função não está incorporada no Excel, mas pode ser construída e acessada com código VBA. Embora o código seja um pouco complicado, várias versões estão disponíveis na rede (comoeste 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 da data do Excel.

Por conveniência, aqui está a fórmula em 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))))

Responder2

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

Talvez você também possa verificar estas 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, como resultado, a conversão de data e hora será diferente para diferentes regiões.

  2. Certifique-se de que não haja espaços em branco extras ou caracteres de quebra de linha ocultos anexados ao valor da sua célula, um valor de data e hora válido com um espaço em branco será tratado como o valor do texto, não como a data e hora, portanto, o Excel não o converterá automaticamente .

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

Responder3

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

Selecione a coluna de datas

Dados > Texto para colunas > Próximo > Próximo > em "formato de dados da coluna" selecione "data" e escolha o formato de origem no menu suspenso - "DMY" > OK

Responder4

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

informação relacionada