Fórmula genérica
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Resumo
Se você precisar converter uma data em um formato de data juliana no Excel, poderá fazê-lo criando uma fórmula que use as funções TEXTO, ANO e DATA.
fundo
"Formato de data juliana" refere-se a um formato onde o valor do ano de uma data é combinado com o "dia ordinal para aquele ano" (ou seja, 14º dia, 100º dia, etc.) para formar um carimbo de data.
Existem várias variações. Uma data neste formato pode incluir um ano de 4 dígitos (aaaa) ou um ano de dois dígitos (aa) e o número do dia pode ou não ser preenchido com zeros para sempre usar 3 dígitos. Por exemplo, para a data de 21 de janeiro de 2017, você pode ver:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Solução
Para um ano de dois dígitos + um número de dia sem uso de preenchimento:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Para um ano de dois dígitos + um número de dia preenchido com zeros até 3 casas:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Para um ano de quatro dígitos + um número de dia preenchido com zeros até 3 casas:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Explicação
Esta fórmula cria o resultado final em 2 partes, unidas por concatenação com o operador E comercial (&).
À esquerda do E comercial, geramos o valor do ano. Para extrair um ano de 2 dígitos, podemos usar a função TEXT, que pode aplicar um formato de número dentro de uma fórmula:
TEXT(B5,"yy")
Para extrair um ano completo, use a função YEAR:
YEAR(B5)
No lado direito do E comercial, precisamos descobrir o dia do ano. Fazemos isso subtraindo o último dia do ano anterior da data com a qual estamos trabalhando. Como as datas são apenas números de série, isso nos dará o "enésimo" dia do ano.
Para obter o último dia do ano do ano anterior, usamos a função DATE. Quando você atribui a DATE um valor de ano e mês e um zero para o dia, obtém o último dia do mês anterior. Assim:
B5-DATE(YEAR(B5),1,0)
nos dá o último dia do ano anterior, que é 31 de dezembro de 2015 no exemplo.
Agora precisamos preencher o valor do dia com zeros. Novamente, podemos usar a função TEXT:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Data juliana reversa
Se precisar converter uma data juliana de volta em uma data normal, você pode usar uma fórmula que analisa a data juliana e a executa por meio da função de data com um mês de 1 e dia igual ao "enésimo" dia. Por exemplo, isso criaria uma data a partir de uma data juliana aaaa, como 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Se você tiver apenas um número de dia (por exemplo, 100, 153, etc.), poderá codificar o ano e inserir o dia:
=DATE(2016,1,A1)
Onde A1 contém o número do dia. Isso funciona porque a função DATE sabe como ajustar os valores que estão fora do intervalo.
