Obecný vzorec
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
souhrn
Pokud potřebujete převést datum do juliánského formátu data v aplikaci Excel, můžete tak učinit vytvořením vzorce, který používá funkce TEXT, YEAR a DATE.
Pozadí
„Juliánský formát data“ označuje formát, ve kterém je roční hodnota data kombinována s „pořadovým dnem pro daný rok“ (tj. 14. den, 100. den atd.) Za účelem vytvoření datového razítka.
Existuje několik variant. Datum v tomto formátu může obsahovat čtyřmístný rok (rrrr) nebo dvouciferný rok (rr) a číslo dne může nebo nemusí být vyplněno nulami, aby se vždy používaly 3 číslice. Například pro datum 21. ledna 2017 můžete vidět:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Řešení
Pro dvouciferný rok + číslo dne bez polstrování použijte:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Pro dvouciferný rok + číslo dne doplněné nulami na 3 místa:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Pro čtyřmístný rok + číslo dne doplněné nulami na 3 místa:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Vysvětlení
Tento vzorec vytváří konečný výsledek ve 2 částech spojených zřetězením s operátorem ampersand (&).
Vlevo od ampersandu generujeme hodnotu roku. Chcete-li extrahovat 2místný rok, můžeme použít funkci TEXT, která může použít formát čísla uvnitř vzorce:
TEXT(B5,"yy")
Chcete-li získat celý rok, použijte funkci ROK:
YEAR(B5)
Na pravé straně ampersandu musíme zjistit den v roce. Děláme to odečtením posledního dne předchozího roku od data, se kterým pracujeme. Protože data jsou jen pořadová čísla, získáme tím „n“ den v roce.
Chcete-li získat poslední den roku předchozího roku, použijeme funkci DATUM. Když zadáte DATE hodnotu roku a měsíce a nulu pro den, získáte poslední den předchozího měsíce. Tak:
B5-DATE(YEAR(B5),1,0)
nám dává poslední den předchozího roku, kterým je v příkladu 31. prosinec 2015.
Nyní musíme denní hodnotu podložit nulami. Opět můžeme použít funkci TEXT:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Obrátit Julian datum
Pokud potřebujete převést juliánské datum zpět na běžné datum, můžete použít vzorec, který analyzuje juliánské datum a spustí jej pomocí funkce data s měsícem 1 a dnem rovným „n -tému“ dni. Tím by se například vytvořilo datum z yyyyddd juliánského data, jako je 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Pokud máte pouze číslo dne (např. 100, 153 atd.), Můžete napevno naprogramovat rok a vložit den takto:
=DATE(2016,1,A1)
Kde A1 obsahuje číslo dne. To funguje, protože funkce DATE ví, jak se upravit pro hodnoty, které jsou mimo rozsah.
