Generisk formel
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Sammendrag
Hvis du trenger å konvertere en dato til et juliansk datoformat i Excel, kan du gjøre det ved å lage en formel som bruker funksjonene TEKST, ÅR og DATO.
Bakgrunn
"Juliansk datoformat" refererer til et format der årverdien til en dato kombineres med "ordinær dag for det året" (dvs. 14. dag, 100. dag osv.) For å danne et datostempel.
Det er flere varianter. En dato i dette formatet kan omfatte et firesifret år (åååå) eller et tosifret år (åå), og dagnummeret kan eller ikke være polstret med nuller for alltid å bruke tre sifre. For eksempel kan du se datoen 21. januar 2017:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Løsning
For et tosifret år + et dagsnummer uten polstring:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
For et tosifret år + et dagnummer polstret med nuller til 3 steder:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
For et firesifret år + et dagnummer polstret med nuller til 3 steder:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Forklaring
Denne formelen bygger det endelige resultatet i to deler, sammen med sammenkobling med ampersand (&) -operatøren.
Til venstre for ampersand genererer vi årverdien. For å trekke ut et tosifret år kan vi bruke TEKST-funksjonen, som kan bruke et tallformat i en formel:
TEXT(B5,"yy")
For å trekke ut et helt år, bruk YEAR-funksjonen:
YEAR(B5)
På høyre side av ampersand må vi finne ut året. Vi gjør dette ved å trekke den siste dagen i forrige år fra datoen vi jobber med. Fordi datoer bare er serienumre, vil dette gi oss den "niende" dagen i året.
For å få den siste dagen i året året før, bruker vi DATE-funksjonen. Når du gir DATE en års- og månedsverdi, og null for dag, får du den siste dagen i forrige måned. Så:
B5-DATE(YEAR(B5),1,0)
gir oss den siste dagen i året før, som er 31. desember 2015 i eksemplet.
Nå må vi putte dagsverdien med nuller. Igjen kan vi bruke TEKST-funksjonen:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Omvendt Julian-dato
Hvis du trenger å konvertere en juliansk dato tilbake til en vanlig dato, kan du bruke en formel som analyserer juliansk dato og kjører den gjennom datofunksjonen med en måned på 1 og dag som tilsvarer den "nte" dagen. For eksempel vil dette skape 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 hardkode året og sette inn dagen dette:
=DATE(2016,1,A1)
Hvor A1 inneholder dagsnummeret. Dette fungerer fordi DATE-funksjonen vet hvordan man kan justere for verdier som er utenfor området.
