Célula referenciada contendo data-hora aparecendo como um número no Excel

Question

Eu estou puxando valores de uma folha para outra. A folha 1 tem ticket ID e a Folha 2 tem vários valores associados a ticket ID . Estou encontrando o ticket ID correspondente na Folha 2 e puxando os vários valores associados a esse ticket ID para a Folha 1.

O problema

A Folha 2 contém alguns valores que estão no formato de data e hora (AAAA / MM / DD HH: MM: SS). A fórmula está retornando o valor de data e hora como um número, como 42010.4 , em vez do valor original.

Como faço para que os valores de data e hora sejam exibidos em seu formato original?

Os detalhes de como estou puxando os valores não são relevantes para minha pergunta. Mas apenas para contexto:

Estou usando a função MATCH para encontrar a linha correta na Folha 2 e armazenando na coluna C da Folha 1 para cada ticket ID .

Esse resultado é usado para preencher um número de células na mesma linha com valores da Folha 2. Por exemplo, o valor da coluna 2 da Folha 2 é preenchido usando uma fórmula semelhante a esta:

=IF($C2>0,INDIRECT("'Sheet2'!B"&C2),"")

A fórmula acima funciona e está retornando valores das células apropriadas. No entanto, não é uma referência de célula simples. Pelo contrário, é uma fórmula um pouco complexa e não está claro como obter o resultado para exibir como a fonte.

    

Answer 1

Este problema foi gerado por outra pergunta (lidando com a tarefa descrita no começo desta). A resposta aceita a esse oferece dois tipos de soluções, uma em que os valores da segunda folha são extraídos em células separadas e uma em que todos os valores da segunda folha são concatenados em uma única célula. Algumas das células na segunda planilha contêm data-hora, o que representa um problema para ambas as abordagens, e a correção é diferente para os dois casos. Vou descrever os dois.

O que está acontecendo

A data-hora armazenada está sendo copiada em seu formato "bruto" e não formatado. Por exemplo, janeiro 6, 2015 8:55:08 AM é armazenado como 42010.3716 porque é o dia 42.010 desde 31/12/1899 (a data de referência padrão) e 8:55:08 é 37.16% de um dia . A data-hora é armazenada na célula como um único número nesse formulário e, em seguida, o Excel obtém as informações de ano, mês, dia e hora conforme necessário para exibir o valor no formato desejado.

Valores copiados em células separadas

Este é o mais simples de corrigir. Nesse caso, ver a data-hora bruta é realmente bom porque significa que as informações de data e hora foram preservadas e podem ser usadas (você pode usar o valor para fazer testes de data e hora, aritmética de data e hora, etc. . Você só precisa formatá-lo.

Uma maneira é copiar a formatação da célula original. Use copy e, em seguida, na nova célula, cole-somente no formato especial. Você pode acessá-lo clicando com o botão direito do mouse nas células e usando os menus de contexto ou no menu da faixa de opções na parte superior.

A outra maneira, particularmente se você quiser usar um formato ligeiramente diferente para o valor copiado, é usar um formato personalizado. Para a nova célula, acesse a caixa de diálogo de formato de célula clicando com o botão direito do mouse na célula ou no menu da faixa de opções. Selecione Formato personalizado na lista de tipos de formato. Você pode escolher algo próximo como um ponto de partida da lista de formatos especiais apresentados ou apenas inserir os códigos desejados na janela de edição.

A lista completa de códigos de formato pode ser encontrada aqui . Para duplicar o formato descrito na pergunta, o código seria:

Yyyy/Mm/Dd Hh:mm:ss

A célula aparecerá como o original.

Valores concatenados em uma única célula

Nesse caso, o valor incorporado já é tratado como texto. Por isso, ele perdeu seu valor como uma data-hora utilizável. É apenas uma questão de fazer com que apareça como uma data-hora legível na string. Isso pode ser feito usando a função TEXTO para formatar o valor enquanto a seqüência está sendo construída. Vou pegar emprestado algum código da outra pergunta como base para essa solução:

=IF($C2>0,INDIRECT("'tickets_info'!B" & $C2)
  & " " & INDIRECT("'tickets_info'!C" & $C2)
  ...
  & " " & INDIRECT("'tickets_info'!N" & $C2),"")

Aqui, C2 é um valor de pesquisa para encontrar a linha correspondente e os & são usados para concatenar as várias células de origem em uma única string contendo todas as informações para o ticket correspondente. Digamos que a coluna C em tickets_info seja uma data e hora. Quando essa string é criada, essa posição na string mostrará o valor bruto de data e hora em vez do valor formatado exibido em tickets_info .

Você pode usar a função TEXTO para formatar esse valor. A função TEXTO usa códigos muito semelhantes aos códigos de formato personalizado para a célula. Os códigos e um mini-tutorial podem ser encontrados aqui . Para o formato descrito na pergunta, o resultado seria assim:

=IF($C2>0,INDIRECT("'tickets_info'!B" & $C2)
  & " " & TEXT(INDIRECT("'tickets_info'!C" & $C2),"yyyy/mm/dd hh:mm:ss")
  ...
  & " " & INDIRECT("'tickets_info'!N" & $C2),"")

A função TEXTO é aplicada apenas à referência de célula derivada (o resultado da função INDIRECTO).