Üldine valem
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Kokkuvõte
Kui peate Excelis kuupäeva teisendama Juliani kuupäevavorminguks, saate seda teha valemi loomisega, mis kasutab funktsioone TEKST, AASTA ja KUUPÄEV.
Taust
"Juliani kuupäevavorming" viitab vormingule, kus kuupäeva pitseri moodustamiseks on kuupäeva aasta väärtus ühendatud "selle aasta järjestuspäevaga" (st 14. päev, 100. päev jne).
Variatsioone on mitu. Selles vormingus kuupäev võib sisaldada neljakohalist aastat (aaaa) või kahekohalist aastat (aaa) ja päeva number võib olla nullidega täidetud või mitte, et kasutada alati 3 numbrit. Näiteks kuupäevaks 21. jaanuar 2017 võite näha järgmist:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Lahendus
Kahekohaline aasta + päevanumber ilma polstrit kasutamata:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Kahekohaline aasta + nullkohaga täidetud päeva number 3 kohta:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Neljakohaline aasta + nullkohaga täidetud päevanumber kolme kohta:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Selgitus
See valem ehitab lõpptulemuse kaheks osaks, mis ühendatakse liidetuna ampersandi (&) operaatoriga.
Ampersandi vasakul poolel genereerime aasta väärtuse. Kahekohalise aasta väljavõtmiseks võime kasutada funktsiooni TEXT, mis saab valemi sees rakendada arvuvormingut:
TEXT(B5,"yy")
Terve aasta väljavõtte tegemiseks kasutage funktsiooni YEAR:
YEAR(B5)
Ampersandi paremal küljel peame välja selgitama aastapäeva. Seda tehes lahutatakse eelmise aasta viimane päev kuupäevast, millega me töötame. Kuna kuupäevad on vaid seerianumbrid, saab see meile aasta n-nda päeva.
Eelmise aasta aasta viimase päeva saamiseks kasutame funktsiooni KUUPÄEV. Kui annate kuupäevale DATE aasta ja kuu väärtuse ning päeva jaoks nulli, saate eelmise kuu viimase päeva. Niisiis:
B5-DATE(YEAR(B5),1,0)
annab meile eelmise aasta viimase päeva, mis on näites 31. detsember 2015.
Nüüd peame päeva väärtuse nullidega märkima. Jällegi saame kasutada funktsiooni TEXT:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Juliani tagurpidi kuupäev
Kui teil on vaja teisendada Juliuse kuupäev tavaliseks kuupäevaks, võite kasutada valemit, mis sõelub Juliuse kuupäeva ja viib selle läbi kuupäevafunktsiooni, kus kuu on 1 ja päev, mis võrdub "n-nda" päevaga. Näiteks loodaks see kuupäev alates juulikuu juuni kuupäevast nagu 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Kui teil on lihtsalt päevanumber (nt 100, 153 jne), saate aasta kodeerida ja päeva sisestada järgmiselt:
=DATE(2016,1,A1)
Kus A1 sisaldab päeva numbrit. See töötab, kuna funktsioon DATE teab, kuidas kohandada väärtuste piires, mis jäävad vahemikust välja.
