Estou no Reino Unido, tenho um problema em que tenho uma lista de datas fornecidas no formato americano. Excel parece tratar os que são válidos em ambos os formatos como datas do Reino Unido, (por exemplo, 01/03/2012 torna-se 3 de janeiro, em vez de 01 de março), e tratar os que não são datas válidas no Reino Unido (por exemplo, 03/13 / 2012) como texto básico. Presumo que essa escolha tenha algo a ver com minhas configurações regionais.
O que eu quero é que o sistema reconheça que essa coluna de texto é fornecida no formato de data dos EUA e a converta na representação de data subjacente para cálculos.
Como faço isso?
EDIT: As datas são fornecidas em um arquivo CSV do formulário:
3/ 1/2012, 09:01 , 18:58 ,9.4,0.6
em que 3/1 é 1º de março
Para o Excel 2010, em vez de abrir seu arquivo CSV, crie uma nova pasta de trabalho e, em seguida, na guia DATA , selecione Obter dados externos → Do texto . Isso chega à interface onde você pode especificar como interpretar seus dados de texto, incluindo como lidar com datas.
Altere a extensão do arquivo de ".csv" para ".txt" e abra com o Excel. O Excel lhe dará um assistente de importação de texto. Selecione "delimitado" na primeira página, marque "vírgula" no segundo e, no terceiro, você poderá selecionar o tipo para cada coluna de dados. Um dos tipos é date e tem uma lista suspensa com vários formatos (m / d / y, d / m / y, etc, etc.).
Eu me deparei com o mesmo problema, mas de uma maneira um pouco diferente. As datas que estão sendo importadas de um arquivo CSV são do Reino Unido, mas as datas são válidas nos EUA & O UK Excel trata-os como formato dos EUA. Minha solução é analisar as datas como texto (usando NumberFormat="@") na planilha, em seguida, reformatando as células posteriormente como o formato de data correto (usando NumberFormat="dd / mm / yyyy").
Parece (para mim de qualquer maneira) a maneira mais simples de fazer isso.
O verdadeiro problema é que as datas são fornecidas de forma formatada, a primeira coisa que todo programador iniciante aprende é que nunca formata as datas, elas são apenas números que são interpretados para corrigir o formato.
Então a solução real é: 1. Tendo as datas em formato numérico, tendo falhado que 2. Transforme as datas em formato numérico e exiba-as em uma coluna separada em qualquer formato de data.
O número 2 não envolve programação, apenas mantendo o formato da coluna original como texto geral, usando uma fórmula (seria melhor calcular uma coluna da tabela) que a transforma no formato numérico e outra coluna que exibe esse valor como um formato de data ao seu gosto.
Provavelmente você pode fazer o seguinte:
1) Altere a configuração da data na barra de tarefas do Windows para o horário dos EUA. Aqui está um guia sobre como fazer isso: link
2) Defina o timing dos EUA no Excel. Vá para o formato células > Data > Local > Inglês (EUA)
Isso funcionou para mim, espero que ajude.
CONVERTE DATAS DOS EUA EM DATAS DO REINO UNIDO EM EXCEL / CSV
(você não quer mudar suas configurações de localidade)
Este é um processo de 2 etapas:
Para parar o Excel ao tentar converter automaticamente algumas datas para o formato do Reino Unido e, em seguida, atrapalhar a conversão, no Excel, destaque a coluna e vá para Dados > Texto para colunas.
Não tente definir apenas pelo menu de formatação (clique com o botão direito do mouse no formato de células), isso não funcionará. Não selecione Data no "Text to columns", pois isso converterá as datas nos EUA usando as configurações do Reino Unido, use "Texto"
[Veja também a resposta do dunxd para importar csvs também. Isso também funcionará se importado como txt.]
2. Em uma coluna de reposição próxima, copie a seguinte fórmula. Substitua as referências a F2 pela sua célula inicial. Formate a coluna em qualquer formato de data escolhido.
= DATEVALUE (CONCATENATE ((SE (MID (F2,4,1)="/", MID (F2,3,1), SE (MID (F2,2,1)="/", MID ( F2,3,2), IF (MID (F2,6,1)="/", MID (F2,4,2), MID (F2,4,1))))), "/", IFERROR ( (SE (SEARCH ("/", ESQUERDA (F2,2)), ESQUERDA (F2,1), ESQUERDA (F2,2))), ESQUERDA (F2,2)), "/", IF (ISERR (FIND ("/", DIREITA (F2,3), 1)), DIREITA (F2,4), DIREITA (F2,2))))
Observação: isso deve funcionar com datas nos seguintes formatos:
Se a sua data não estiver separada por "/" s, substitua esse delimitador na fórmula acima.
Espero que ajude.
ATUALIZAÇÃO 17-2-2014
Conforme solicitado, um resumo:
Datevalue e Concatenate criam a string final no formato de data do Reino Unido, portanto, para a parte interessante:
a. Receba o dia dos EUA (valor médio)
((IF (MID (F2,4,1)="/", MID (F2,3,1), IF (MID (F2,2,1)="/", MID (F2,3, 2), IF (MID (F2,6,1)="/", MID (F2,4,2), MID (F2,4,1)))))
A parte mais complicada. Não temos certeza do formato da data. Sabemos que haverá dois "/" em algum lugar, mas precisamos encontrar onde. A parte mais complicada é em um dado como 1/1/10 o segundo "/" pode ser tão cedo quanto o 4º chr, onde em uma data como 10/10/10 o primeiro "/" é o terceiro, onde o o segundo é o sexto chr. Precisamos de uma maneira de distinguir o primeiro e o segundo "/". É uma sorte que um formato '1/1 /' seja a única maneira de um "/" ser o 4º chr, então podemos usar isso:
b. Obtenha o mês dos EUA (valor à esquerda)
IFERROR ((SE (SEARCH ("/", ESQUERDA (F2,2)), ESQUERDA (F2,1), ESQUERDA (F2,2))), ESQUERDA (F2,2))
Bastante simples,
c. Obtenha o ano (valor correto)
SE (ISERR (FIND ("/", DIREITA (F2,3), 1)), DIREITA (F2,4), DIREITA (F2,2))
Simples,
Espero que isso ajude.
Eu tive o mesmo problema, mas em sentido inverso: do formato australiano para o americano.
No meu caso, executo uma macro que abre o arquivo CSV. Uma coluna do arquivo contém datas. Depois de adicionar uma coluna extra com alguns dados calculados, a macro a salva como um arquivo .XLS. No arquivo XLS, uma data válida em ambos os formatos será alterada, ou seja, 1/11/12 se tornará 11/11/12. Observe que minha macro não toca na coluna de data de qualquer forma e o CSV sempre tem o formato correto. Isso só parece acontecer no PC do cliente que executa o Office 2010. Não o vi no Office 2000 ou 2003.
A única maneira de contornar isso é alterar a macro para importar o CSV, em vez de abri-lo e especificar o formato de texto para a coluna de data.
Eu considerei outra possibilidade, que é ter a macro shell como um comando grep para colocar um "espaço" na frente das datas. Isso impede que o Excel o interprete como uma data (as cotações em torno das datas não ajudam). Eu decidi importar era o modo mais limpo entretanto. Como não há nenhum utilitário Win nativa para fazer a edição, eu teria que instalar o grep no PC do cliente.
Eu tive esse problema por algum tempo. Parece ser um problema comumente mal entendido. Especificamente, que o computador não necessariamente reconhecerá uma data no formato correto. Se você gravasse uma data no formato DD / MM / AAAA, o Excel a interpretaria em um formato MM / DD / AAAA. Então, 05/06/2012 seria entendido como 5 de junho de 2012, no entanto, o Excel o interpretaria como 6 de maio de 2012. Da mesma forma, se você fosse inserir 13/12/2012, onde você quis dizer 13 de dezembro de 2012, o Excel não reconhecer a data, pois não há mês 13.
Para corrigir esse problema, criei uma fórmula que inverte os dois primeiros números de data. Isso pode ser usado para converter os EUA para o Reino Unido, ou vice-versa.
=IF(ISERR(DATEVALUE(F10))=TRUE,DATE(RIGHT(TEXT(F10,"DD/MM/YYYY"),4),LEFT(TEXT(F10,"DD/MM/YYYY"),2),LEFT(RIGHT(TEXT(F10,"DD/MM/YYYY"),7),2)),IF(ISERR(DATEVALUE(TEXT(F10,"mm/DD/YYYY")))=TRUE,DATE(RIGHT(TEXT(TEXT(F10,"mm/DD/YYYY"),"DD/MM/YYYY"),4),LEFT(TEXT(TEXT(F10,"mm/DD/YYYY"),"DD/MM/YYYY"),2),LEFT(RIGHT(TEXT(TEXT(F10,"mm/DD/YYYY"),"DD/MM/YYYY"),7),2)),0))
Isso funcionou para mim exatamente como eu queria, e acredito que seja uma solução que ainda não existe no campo. Por favor, deixe-me saber se isso funciona tão bem para você como tem para mim.
PS. Eu estava usando o Excel 2007 para essa fórmula.
Recentemente, tive um problema semelhante com o Excel 2010. O Excel interpretou datas não interpretáveis como formato de data dos EUA corretamente, ou seja, qualquer data com um dia > 12. Para todas as datas com dia < 13, exibiu-os no formato US mm / dd / aa - todos na mesma coluna e todos no mesmo formato. Meu primeiro curso de ação foi verificar se minhas configurações no Excel e no Windows eram em inglês (Austrália) ou pelo menos em inglês (Reino Unido). Nem estava correto, então eu mudei ambos e reiniciei o Excel como recomendado. Isso não corrigiu o problema. Eu reformatei a coluna de data como texto, inseri algumas linhas entre as datas corretas e as datas dos EUA, preenchi o código arrastando a alça da cópia e digitei as datas corretas nas novas células, arrastei o conteúdo das outras células do registro ne localização e excluiu as linhas desonesto. O que eu descobri quando reformatei a coluna de data para o formato de data era que, enquanto minhas datas agora eram orientadas corretamente dd / mm / aaaa, o Excel ainda reconhecia dois formatos diferentes - um à esquerda justificado, o outro à direita justificado e nenhum do formato pintura ou outras opções resolveriam isso.
Estranhamente, quando eu marcava de uma das células justificadas à esquerda para a célula adjacente, a célula era reformatada para a versão justificada à direita e retinha o formato de data correto. Não consigo explicar isso, já que o Excel exibiu o descritor "Data" para ambos na seção "Número da faixa de opções, mas corrigiu o problema.
O problema é que você tem sua configuração de data no excel definida como (e alguma data) Formatos de data que começam com e asterik () respondem a mudanças nas configurações regionais de data e hora especificadas para o operacional sistema. Você precisa ir ao seu painel de controle e também ajustar o formato de data e hora para garantir que não haja inversão de datas. Às vezes, 01/11/2013 por exemplo, vai virar para 11/01/2013 (onde 11 é novembro) no entanto 30/11/2013 não vai virar, isso é porque ms não reconhece o 30o como um mês válido. nenhuma fórmula requerida
Informações relacionadas e a resposta para todos os problemas:
Salvar todas as informações de data e hora em somente um formato :
Anexe um campo de formato de exibição aos dados salvos, se necessário.
Clique com o botão direito do mouse na coluna, selecione as células de formatação, escolha a primeira guia "Número", selecione a data da categoria e, no lado direito, defina "Local" para "Inglês (EUA)".