Všeobecný vzorec
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Zhrnutie
Ak potrebujete previesť dátum na juliánsky formát dátumu v programe Excel, môžete to urobiť vytvorením vzorca, ktorý používa funkcie TEXT, ROK a DÁTUM.
Pozadie
„Juliánsky formát dátumu“ označuje formát, v ktorom sa ročná hodnota dátumu kombinuje s „radovým dňom pre daný rok“ (tj. 14. deň, 100. deň atď.), Aby sa vytvorila dátumová pečiatka.
Existuje niekoľko variácií. Dátum v tomto formáte môže obsahovať štvormiestny rok (rrrr) alebo dvojciferný rok (rr) a číslo dňa môže alebo nemusí byť doplnené nulami, aby sa vždy používali 3 číslice. Napríklad pre dátum 21. januára 2017 sa môžu zobraziť:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Riešenie
Pre dvojciferný rok + číslo dňa bez polstrovania použite:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Pre dvojciferný rok + číslo dňa doplnené nulami na 3 miesta:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Za štvorciferný rok + číslo dňa doplnené nulami na 3 miesta:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Vysvetlenie
Tento vzorec zostavuje konečný výsledok z 2 častí, ktoré sú spojené zreťazením s operátorom ampersand (&).
Naľavo od ampersandu generujeme hodnotu roku. Na extrahovanie dvojciferného roku môžeme použiť funkciu TEXT, ktorá vo vzorci môže použiť číselný formát:
TEXT(B5,"yy")
Ak chcete získať celý rok, použite funkciu ROK:
YEAR(B5)
Na pravej strane ampersandu musíme zistiť deň v roku. Robíme to tak, že odčítame posledný deň predchádzajúceho roka od dátumu, s ktorým pracujeme. Pretože dátumy sú iba poradové čísla, dá nám to „n“ deň v roku.
Na získanie posledného dňa v roku predchádzajúceho roka používame funkciu DÁTUM. Keď dáte hodnotu DATE rok a mesiac a nulu pre deň, získate posledný deň predchádzajúceho mesiaca. Takže:
B5-DATE(YEAR(B5),1,0)
nám dáva posledný deň predchádzajúceho roka, ktorým je v príklade 31. december 2015.
Teraz musíme vložiť dennú hodnotu nulami. Opäť môžeme použiť funkciu TEXT:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Obrátiť juliánsky dátum
Ak potrebujete previesť juliánsky dátum späť na bežný dátum, môžete použiť vzorec, ktorý analyzuje juliánsky dátum a spustí ho cez funkciu dátumu s mesiacom 1 a dňom rovnajúcim sa „n “mu dňu. Takto by sa vytvoril napríklad dátum z juliánskeho rrrrrddd dátumu, napríklad 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Ak máte iba číslo dňa (napr. 100, 153 atď.), Môžete napevno napísať rok a vložiť deň takto:
=DATE(2016,1,A1)
Kde A1 obsahuje číslo dňa. Funguje to preto, lebo funkcia DATE vie, ako sa upraviť pre hodnoty, ktoré sú mimo rozsahu.
