Generisk formel
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Resumé
Hvis du har brug for at konvertere en dato til et juliansk datoformat i Excel, kan du gøre det ved at opbygge en formel, der bruger funktionerne TEKST, ÅR og DATO.
Baggrund
"Juliansk datoformat" henviser til et format, hvor årsværdien af en dato kombineres med "ordinær dag for det år" (dvs. 14. dag, 100. dag osv.) For at danne et datostempel.
Der er flere variationer. En dato i dette format kan omfatte et firecifret år (åååå) eller et tocifret år (åå), og dagnummeret er måske ikke polstret med nuller for altid at bruge 3 cifre. For eksempel for datoen 21. januar 2017 kan du muligvis se:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Løsning
For et tocifret år + et dagnummer uden polstring:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
For et tocifret år + et dagnummer polstret med nuller til 3 steder:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
For et firecifret år + et dagnummer polstret med nuller til 3 steder:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Forklaring
Denne formel bygger det endelige resultat i 2 dele, sammenføjet med sammenkædning med ampersand (&) operatoren.
Til venstre for ampersand genererer vi årværdien. For at udtrække et 2-cifret år kan vi bruge TEKST-funktionen, som kan anvende et talformat i en formel:
TEXT(B5,"yy")
For at udtrække et helt år skal du bruge YEAR-funktionen:
YEAR(B5)
På højre side af ampersand er vi nødt til at finde ud af dagen på året. Vi gør dette ved at trække den sidste dag i det foregående år fra den dato, vi arbejder med. Fordi datoer kun er serienumre, vil dette give os den "niende" dag i året.
For at få den sidste dag i året i det foregående år bruger vi funktionen DATO. Når du giver DATE en års- og månedsværdi og et nul for dag, får du den sidste dag i den foregående måned. Så:
B5-DATE(YEAR(B5),1,0)
giver os den sidste dag i det foregående år, som er den 31. december 2015 i eksemplet.
Nu skal vi padde dagsværdien med nuller. Igen kan vi bruge TEKST-funktionen:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Omvendt Julian-dato
Hvis du har brug for at konvertere en juliansk dato tilbage til en almindelig dato, kan du bruge en formel, der analyserer den julianske dato og kører den gennem datofunktionen med en måned på 1 og dag svarende til den "niende" dag. For eksempel vil dette oprette en dato fra en yyyyddd juliansk dato som 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Hvis du bare har et dagnummer (f.eks. 100, 153 osv.), Kan du hårdkode året og indsætte dagen dette:
=DATE(2016,1,A1)
Hvor A1 indeholder dagnummeret. Dette fungerer, fordi DATE-funktionen ved, hvordan man justerer for værdier, der er uden for området.
