Converta uma variedade de entradas de texto em Excel
Tentei várias das soluções sugeridas no StackExchange para converter texto em Excel, no entanto, nenhuma delas funciona de forma confiável para os dados que possuo. Consulte a explicação abaixo.
Eu tenho uma coluna com uma "data de início" nela. Ele contém a data nas seguintes formas:
(Estado inicial) 17/09/2019 9:30:59 AM 20/10/2020 4:45:59 PM 26/02/2020 12:20:59 PM
Esta imagem mostra os dados e o estado final desejado ...
Quero converter toda a entrada em um valor de data do Excel para que eu possa fazer cálculos úteis sobre ele, por exemplo, subtrair um horário de início em, por exemplo, 20/11/2020 de um horário de término em 21/11/2020, então, por exemplo, eu quero o segundo a entrada acima deve terminar como (preferencialmente) 23/02/2020 20:31:59 (porque sou britânico), mas mesmo que eu consiga a versão dos EUA, tudo bem, posso trabalhar com isso.
(Estado final desejado) 17/09/2019 09:30 20/10/2020 16:45 26/02/2020 12:20
Não me indique outras respostas que parecem resolver isso. Eu tentei usá-los. Cada um parece ter um problema e não consigo ver como resolvê-lo ou não teria postado esta pergunta.
por exemplo, se eu tentar =datevalue(left(a1,9)), ele não obterá entradas com um mês de dois dígitos (ou seja, 10, 11, 12).
Se eu tentar a =date(mid.... etcmesma coisa: ele não captura todas as informações de maneira confiável porque as diferentes células têm diferentes comprimentos de informação em.
Eu usei o método Text-To-Columns , que o divide em células de formato não-data / hora e corta o PM ou AM em outra coluna que posso descartar, MAS isso significa que em vez de 20:31:59 eu recebo 08:31:59 em uma coluna e PM em outra, e não consigo ver como fazer isso retornar 20:31:59.
Se alguma coisa nesta questão não estiver clara, pergunte.
E se perdi algo realmente óbvio, peço desculpas, mas passei horas tentando resolver isso.
Respostas
Minha primeira tentativa seria tentar combinar um formato de data personalizado na célula de texto. Se funcionar, o texto na célula recuará um espaço e você poderá dizer = A1 + 0 para adicionar zero dias à data sem gerar um erro. Em seguida, reformate a nova célula de acordo com sua preferência.
Na pior das hipóteses, você pode dividir a string em um formato de data nativo que o Excel entenda. Você não precisa de comprimentos fixos, mas pode usar o espaço e os caracteres / como delimitadores, por exemplo, para obter o dia entre os dois / 's
=mid(A1,search("/",A1)+1,search("/",A1,search("/",A1)+1)-1-search("/",A1))
O que semanticamente significa
=mid(A1, from first "/" position +1, with length of second "/" position -1 - first "/" position)
Dê ou receba +1 ou -1. Não estou na frente do meu computador agora para testar a fórmula. Mas se retornar, por exemplo, um caractere para pouco, basta adicionar +1 ao argumento de comprimento. Depois de isolar o dia, o mês e o ano (e a hora, se necessário), você pode reconstruir uma data com a função de data.
Ok, então acho que posso ter uma solução adequada para você. Parece que a maneira como o Excel converte o texto até a data depende das configurações de região do Windows. Definir a formatação da data da célula no Excel altera apenas como os valores da data são exibidos , não como são convertidos em primeiro lugar.
Minhas configurações de região no Windows estão atualmente definidas para a África do Sul. Minha planilha resolve isso, com a fórmula adjacente = A1 + 0:
Agora, se eu for ao painel de controle do Windows > Alterar formatos de data, hora ou número e alterar temporariamente o formato para Inglês (Estados Unidos), então volte para a planilha e reavalie a fórmula (pressione ctrl + alt + F9 se não atualiza automaticamente):
Agora, copie e cole como valores a nova coluna de valor de data em cima dela mesma. Salve o arquivo. Altere as configurações de sua região de volta ao jeito que eram. Selecione a coluna e arquive o formato de célula de data e hora de sua preferência em "personalizado":
De modo a:
