Datum in Julianisches Format konvertieren

Wenn Sie ein Datum in Excel in ein Julianisches Datumsformat konvertieren müssen, können Sie dies tun, indem Sie eine Formel erstellen, die die Funktionen TEXT, JAHR und DATUM verwendet.

Hintergrund

„Julianisches Datumsformat“ bezieht sich auf ein Format, bei dem der Jahreswert eines Datums mit dem „Ordinaltag für dieses Jahr“ (d. h. 14. Tag, 100. Tag usw.) kombiniert wird, um einen Datumsstempel zu bilden.

Es gibt mehrere Variationen. Ein Datum in diesem Format kann ein vierstelliges Jahr (yyyy) oder ein zweistelliges Jahr (yy) enthalten und die Tagesnummer kann mit Nullen aufgefüllt werden oder nicht, um immer 3 Ziffern zu verwenden. Für das Datum 21. Januar 2017 könnten Sie beispielsweise Folgendes sehen:

= YEAR (date)& TEXT (date- DATE ( YEAR (date),1,0),'000')

Lösung

Für ein zweistelliges Jahr + eine Tageszahl ohne Auffüllung verwenden Sie:

 
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD

Für ein zweistelliges Jahr + eine mit Nullen aufgefüllte Tageszahl an 3 Stellen:

 
= TEXT (B5,'yy')&B5- DATE ( YEAR (B5),1,0)

Für ein vierstelliges Jahr + eine mit Nullen aufgefüllte Tageszahl an 3 Stellen:

 
= TEXT (B5,'yy')& TEXT (B5- DATE ( YEAR (B5),1,0),'000')

Diese Formel erstellt das Endergebnis in 2 Teilen, die durch Verkettung mit dem kaufmännischen Und-Operator (&) verbunden sind.

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:

 
= YEAR (B5)& TEXT (B5- DATE ( YEAR (B5),1,0),'000')

Um ein ganzes Jahr zu extrahieren, verwenden Sie die YEAR-Funktion:

 
 TEXT (B5,'yy')

Auf der rechten Seite des kaufmännischen Und-Zeichens müssen wir den Tag des Jahres ermitteln. Wir tun dies, indem wir den letzten Tag des Vorjahres von dem Datum, mit dem wir arbeiten, abziehen. Da Datumsangaben 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 Wert für Jahr und Monat und eine Null für Tag geben, erhalten Sie den letzten Tag des Vormonats. So:

 
 YEAR (B5)

liefert uns den letzten Tag des Vorjahres, im Beispiel der 31. Dezember 2015.

wie man Daten in Excel codiert

Jetzt müssen wir den Tageswert mit Nullen auffüllen. Auch hier können wir die TEXT-Funktion verwenden:

 
B5- DATE ( YEAR (B5),1,0)

Julianisches Datum umkehren

Wenn Sie ein Julianisches Datum wieder in ein reguläres Datum umwandeln müssen, können Sie eine Formel verwenden, die das Julianische Datum analysiert und durch die Datumsfunktion mit einem Monat von 1 und einem Tag gleich dem n-ten Tag durchläuft. Dies würde beispielsweise ein Datum aus einem jjjjdtt julianischen Datum wie 1999143 erstellen.

 
 TEXT (B5- DATE ( YEAR (B5),1,0),'000')

Wenn Sie nur eine Tagesnummer haben (z. B. 100, 153 usw.), können Sie das Jahr hart kodieren und den Tag folgendermaßen einfügen:

 
= DATE ( LEFT (A1,4),1, RIGHT (A1,3)) // for yyyyddd

Wobei A1 die Tagesnummer enthält. Dies funktioniert, weil die DATE-Funktion weiß, wie Werte außerhalb des zulässigen Bereichs angepasst werden.