Convertendo uma data de mm / dd / aaaa para seu valor numérico

Question

Estou recebendo dados de uma ferramenta de relatórios. O relatório contém as colunas Date e User (nessa ordem). A ferramenta retorna datas no formato mm / dd / aaaa.

O Excel não converte os dados importados para uma data numérica; ele é armazenado como um valor de texto (os dados brutos da ferramenta de relatório são alinhados à esquerda, e não à direita).

Eu preciso concatenar User e o valor numérico da data (nessa ordem). O uso dos dados importados brutos produz um resultado no formato userAmm/dd/yyyy em vez do formato necessário, como userA42379 . A concatenação de data como um valor numérico não formatado só acontece corretamente se os dados brutos estiverem no formato dd / mm / aaaa (caso contrário, os dados de entrada não serão interpretados como uma data).

Tentei converter as datas do formato mm / dd / aaaa para o formato dd / mm / aaaa usando MID / LEFT / RIGHT e a formatação numérica no Excel, mas isso não funcionou. A data ainda aparece no formato mm / dd / aaaa quando concatenada com outra célula com texto.

Alguma idéia?

    

Answer 1

Você disse que o relatório contém as colunas Date e User (nessa ordem).

Você também disse que a ferramenta retorna datas no formato de texto na forma de mm/dd/yyyy . Digamos que você tenha o seguinte:

VocêprecisaconcatenarUsereDatevalue(nessaordem).IstoéoquevamosfazernacélulaC2

Agoraestouconfusosobrecomovocêdesejaqueoformatodedataconcatenadaseja.Vocêquerqueemmm/dd/yyyyoudd/mm/yyyyvocêqueiraqueocódigodedataforneçaumasaídacomouserA42379?

Eucobrireitodosostrês.

Paraconcatenarmantendooformatodedata(mm/dd/yyyy),useaseguintefórmula

=B2&" "&A2

O&" "& no meio adiciona o espaço entre eles.

Para concatenar a data no formato ( dd/mm/yyyy ), use a seguinte fórmula

=B2&" "&MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,4)

Paraconcatenarocódigodedata,como42379,useaseguintefórmula

=B2&" "&DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))

    

Answer 2

Aqui está uma solução geral que funcionará para qualquer ^* formato de data que comece com um mês, independentemente de ser armazenado como um valor de texto ou um datetime adequado (serial data) valor:

InsiraaseguintefórmulaemD2ectrl-enter/copy-paste/fill-downnorestantedacoluna:

=TEXT(IF(ISNUMBER(C2),C2,DATE(RIGHT(SUBSTITUTE(C2,"/"," "),4),LEFT(SUBSTITUTE(C2,"/"," "),3),MID(SUBSTITUTE(C2,"/"," "),4,3))),"dd/mm/yyyy")

O resultado é armazenado como um valor de texto. Se um valor de data serial for necessário, simplesmente omita a função TEXT() e defina o formato numérico da célula como dd/mm/yyyy

Se o formato da data de origem for sempre mm/dd/yyyy , a fórmula simplifica para o seguinte:

=TEXT(IF(ISNUMBER(C2),C2,DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2))),"dd/mm/yyyy")

Para o seu caso específico em que você gera uma coluna UserDate concatenada, apenas omita a função TEXT() :

="UserName"&IF(ISNUMBER(C2),C2,DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2)))

Se a data for sempre armazenada como um valor de texto, a fórmula poderá ser simplificada:

="UserName"&DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2))

    

Answer 3

Selecione datas no intervalo manualmente ou usando a opção de busca, formato e data, algo como, astrick / astrick / 2018.

Altere o formato de todos que são encontrados para dd/mm/yyyy , se é isso que você queria.

Eu tentei o =CONCATENATE e ele me deu o mesmo valor numérico com dd/mm/yy e mm/dd/yy , então alterar o formato não tem impacto numérico.

Para que ele apareça no formato de data em vez de numérico na função Concatenate , tive que inserir "&TEXT(A4,"mm/dd/yy") com A4 como o local de teste da minha data. Isso permitiu que ele mostrasse algo como A data em A4 is mm/dd/yyyy

Sample

Row A1 Japan
    A2 June
    A3 02/07/2018

=CONCATENATE("The date in ", A1, " is ", ""&TEXT(A3,"mm/dd/yy"), " currently.")

Saída = The date in Japan is 07/02/18 currently .

Espero que isso ajude. Eu acho que é o que você está procurando.

    

Answer 4

Verifique a captura de tela:

NacélulaM1Escrevaestafórmula,convertaadatadoformatoMM/DD/AAAAparaDD/MM/AAAA.

=IF(ISNUMBER(K1),TEXT(K1,"DD/MM/YYYY"),K1)

Editado: data no formato MM / DD / AAAA como Texto até a data no formato DD / MM / AAAA.

vocêpodeusarestafórmula:=TEXT(DATEVALUE(A97),"dd/mm/YYYY")

Observação: ajuste o endereço da célula nas duas fórmulas de acordo com sua necessidade.

    

Answer 5

Eu não tenho certeza de como isso funcionará para os outros, mas isso é o que funcionou para mim.

=IFERROR(DATEVALUE(MID($Q4,4,2)&"/"&LEFT($Q4,2)&"/"&RIGHT($Q4,4)),"")