Generische Formel
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Zusammenfassung
Wenn Sie ein Datum in Excel in ein julianisches Datumsformat konvertieren müssen, können Sie dazu eine Formel erstellen, die die Funktionen TEXT, YEAR und DATE verwendet.
Hintergrund
"Julianisches Datumsformat" bezieht sich auf ein Format, bei dem der Jahreswert eines Datums mit dem "Ordnungszeitpunkt für dieses Jahr" (dh 14. Tag, 100. Tag usw.) kombiniert wird, um einen Datumsstempel zu bilden.
Es gibt verschiedene Variationen. Ein Datum in diesem Format kann ein 4-stelliges Jahr (JJJJ) oder ein zweistelliges Jahr (JJJ) enthalten, und die Tagesnummer kann mit Nullen aufgefüllt sein oder nicht, um immer 3-stellig zu verwenden. Für das Datum 21. Januar 2017 wird möglicherweise Folgendes angezeigt:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Lösung
Für ein zweistelliges Jahr + eine Tageszahl ohne Polsterung verwenden Sie:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Für ein zweistelliges Jahr + eine mit Nullen auf 3 Stellen aufgefüllte Tageszahl:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Für ein vierstelliges Jahr + eine mit Nullen auf 3 Stellen aufgefüllte Tageszahl:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Erläuterung
Diese Formel erstellt das Endergebnis in zwei Teilen, verbunden durch die Verkettung mit dem kaufmännischen Und (&) -Operator.
Links vom kaufmännischen Und generieren wir den Jahreswert. Um ein zweistelliges Jahr zu extrahieren, können wir die TEXT-Funktion verwenden, die ein Zahlenformat innerhalb einer Formel anwenden kann:
TEXT(B5,"yy")
Verwenden Sie die YEAR-Funktion, um ein ganzes Jahr zu extrahieren:
YEAR(B5)
Auf der rechten Seite des kaufmännischen Und müssen wir den Tag des Jahres herausfinden. Dazu subtrahieren wir den letzten Tag des Vorjahres von dem Datum, mit dem wir arbeiten. Da Daten nur Seriennummern sind, erhalten wir den "n-ten" Tag des Jahres.
Um den letzten Tag des Jahres des Vorjahres zu erhalten, verwenden wir die DATE-Funktion. Wenn Sie DATE einen Jahres- und Monatswert und eine Null für den Tag geben, erhalten Sie den letzten Tag des Vormonats. So:
B5-DATE(YEAR(B5),1,0)
gibt uns den letzten Tag des Vorjahres, im Beispiel den 31. Dezember 2015.
Jetzt müssen wir den Tageswert mit Nullen auffüllen. Auch hier können wir die TEXT-Funktion verwenden:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Julisches Datum umkehren
Wenn Sie ein julianisches Datum wieder in ein reguläres Datum konvertieren müssen, können Sie eine Formel verwenden, die das julianische Datum analysiert und die Datumsfunktion mit einem Monat von 1 und einem Tag durchführt, der dem "n-ten" Tag entspricht. Dies würde beispielsweise ein Datum aus einem jjjjyddischen julianischen Datum wie 1999143 erstellen.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Wenn Sie nur eine Tagesnummer haben (z. B. 100, 153 usw.), können Sie das Jahr fest codieren und den Tag wie folgt einfügen:
=DATE(2016,1,A1)
Wobei A1 die Tagesnummer enthält. Dies funktioniert, weil die DATE-Funktion weiß, wie Werte außerhalb des Bereichs angepasst werden.
