Generisk formel
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Sammanfattning
Om du behöver konvertera ett datum till ett julianskt datumformat i Excel kan du göra det genom att bygga en formel som använder funktionerna TEXT, ÅR och DATUM.
Bakgrund
"Julianskt datumformat" avser ett format där årets värde för ett datum kombineras med "ordinarie dagen för det året" (dvs. 14: e dagen, 100: e dagen etc.) för att bilda en datumstämpel.
Det finns flera variationer. Ett datum i detta format kan innehålla ett fyrsiffrigt år (åååå) eller ett tvåsiffrigt år (åå) och dagnumret kan eller kanske inte fyllas med nollor för att alltid använda tre siffror. Till exempel för datumet 21 januari 2017 kan du se:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Lösning
För ett tvåsiffrigt år + ett dagnummer utan vaddering:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
För ett tvåsiffrigt år + ett dagnummer fyllt med nollor till 3 platser:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
För ett fyrsiffrigt år + ett dagnummer vadderat med nollor till 3 platser:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Förklaring
Denna formel bygger det slutliga resultatet i två delar, förenat med sammankoppling med ampersand (&) -operatören.
Till vänster om ampersand genererar vi årsvärdet. För att extrahera ett tvåsiffrigt år kan vi använda TEXT-funktionen, som kan använda ett nummerformat i en formel:
TEXT(B5,"yy")
Använd YEAR-funktionen för att extrahera ett helt år:
YEAR(B5)
På höger sida av ampersand måste vi räkna ut året. Vi gör detta genom att subtrahera den sista dagen föregående år från det datum vi arbetar med. Eftersom datum bara är serienummer kommer detta att ge oss den "n: a" dagen på året.
För att få den sista dagen på året föregående år använder vi funktionen DATUM. När du ger DATE ett års- och månadsvärde och noll för dag får du den sista dagen i föregående månad. Så:
B5-DATE(YEAR(B5),1,0)
ger oss den sista dagen föregående år, som är den 31 december 2015 i exemplet.
Nu måste vi lägga in dagvärdet med nollor. Återigen kan vi använda TEXT-funktionen:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Omvänd Julian-datum
Om du behöver konvertera ett julianskt datum tillbaka till ett vanligt datum kan du använda en formel som analyserar julianskt datum och kör det genom datumfunktionen med en månad på 1 och dag som är lika med den "n: te" dagen. Till exempel skulle detta skapa ett datum från ett yyyyddd Julian-datum som 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Om du bara har ett dagnummer (t.ex. 100, 153, etc.) kan du hårddiska året och infoga dagen detta:
=DATE(2016,1,A1)
Där A1 innehåller dagnumret. Detta fungerar eftersom DATE-funktionen vet hur man justerar för värden som ligger utanför intervallet.
