Generieke formule
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Samenvatting
Als u een datum in Excel naar een Juliaanse datumnotatie moet converteren, kunt u dit doen door een formule te bouwen die de functies TEXT, YEAR en DATE gebruikt.
Achtergrond
"Juliaanse datumnotatie" verwijst naar een formaat waarbij de jaarwaarde van een datum wordt gecombineerd met de "ordinale dag voor dat jaar" (dwz 14e dag, 100e dag, enz.) Om een datumstempel te vormen.
Er zijn verschillende variaties. Een datum in dit formaat kan een 4-cijferig jaar (jjjj) of een tweecijferig jaar (jj) bevatten en het dagnummer kan al dan niet worden opgevuld met nullen om altijd 3 cijfers te gebruiken. Voor de datum 21 januari 2017 ziet u bijvoorbeeld mogelijk:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Oplossing
Gebruik voor een jaar van twee cijfers + een dagnummer zonder opvulling:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Voor een tweecijferig jaar + een dagnummer opgevuld met nullen tot 3 plaatsen:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Voor een viercijferig jaar + een dagnummer opgevuld met nullen tot 3 plaatsen:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Uitleg
Deze formule bouwt het eindresultaat op in 2 delen, samengevoegd door aaneenschakeling met de ampersand (&) operator.
Aan de linkerkant van het ampersand genereren we de jaarwaarde. Om een 2-cijferig jaar te extraheren, kunnen we de TEXT-functie gebruiken, die een getalnotatie binnen een formule kan toepassen:
TEXT(B5,"yy")
Gebruik de functie JAAR om een volledig jaar te extraheren:
YEAR(B5)
Aan de rechterkant van het ampersand moeten we de dag van het jaar uitzoeken. Dit doen we door de laatste dag van het voorgaande jaar af te trekken van de datum waarmee we werken. Omdat datums slechts seriële getallen zijn, geeft dit ons de "n-de" dag van het jaar.
Om de laatste dag van het jaar van het voorgaande jaar te krijgen, gebruiken we de DATUM-functie. Als u DATE een jaar- en maandwaarde opgeeft, en een nul voor de dag, krijgt u de laatste dag van de vorige maand. Zo:
B5-DATE(YEAR(B5),1,0)
geeft ons de laatste dag van het voorgaande jaar, in het voorbeeld 31 december 2015.
Nu moeten we de dagwaarde opvullen met nullen. Nogmaals, we kunnen de TEXT-functie gebruiken:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Omgekeerde Juliaanse datum
Als u een Juliaanse datum terug naar een normale datum moet converteren, kunt u een formule gebruiken die de Juliaanse datum parseert en deze door de datumfunctie laat lopen met een maand van 1 en dag gelijk aan de "n-de" dag. Dit zou bijvoorbeeld een datum creëren op basis van een Juliaanse datum jjjjddd zoals 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Als u alleen een dagnummer heeft (bijv. 100, 153, enz.), Kunt u het jaar hard coderen en de dag als volgt invoeren:
=DATE(2016,1,A1)
Waar A1 het dagnummer bevat. Dit werkt omdat de functie DATUM weet hoe te corrigeren voor waarden die buiten het bereik vallen.
