Formule générique
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Sommaire
Si vous devez convertir une date au format de date julien dans Excel, vous pouvez le faire en créant une formule qui utilise les fonctions TEXTE, ANNÉE et DATE.
Contexte
«Format de date julien» fait référence à un format où la valeur de l'année d'une date est combinée avec le «jour ordinal de cette année» (c'est-à-dire le 14e jour, le 100e jour, etc.) pour former un horodatage.
Il existe plusieurs variantes. Une date dans ce format peut inclure une année à 4 chiffres (aaaa) ou une année à deux chiffres (aa) et le numéro du jour peut ou non être complété par des zéros pour toujours utiliser 3 chiffres. Par exemple, pour la date du 21 janvier 2017, vous pourriez voir:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Solution
Pour une année à deux chiffres + un numéro de jour sans remplissage, utilisez:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Pour une année à deux chiffres + un numéro de jour complété par des zéros à 3 places:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Pour une année à quatre chiffres + un numéro de jour complété par des zéros à 3 places:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Explication
Cette formule construit le résultat final en 2 parties, jointes par concaténation avec l'opérateur esperluette (&).
Sur la gauche de l'esperluette, nous générons la valeur de l'année. Pour extraire une année à 2 chiffres, nous pouvons utiliser la fonction TEXT, qui peut appliquer un format de nombre à l'intérieur d'une formule:
TEXT(B5,"yy")
Pour extraire une année complète, utilisez la fonction YEAR:
YEAR(B5)
Sur le côté droit de l'esperluette, nous devons déterminer le jour de l'année. Nous faisons cela en soustrayant le dernier jour de l'année précédente de la date avec laquelle nous travaillons. Parce que les dates ne sont que des numéros de série, cela nous donnera le "nième" jour de l'année.
Pour obtenir le dernier jour de l'année de l'année précédente, nous utilisons la fonction DATE. Lorsque vous donnez à DATE une valeur pour l'année et le mois, et un zéro pour le jour, vous obtenez le dernier jour du mois précédent. Donc:
B5-DATE(YEAR(B5),1,0)
nous donne le dernier jour de l'année précédente, qui est le 31 décembre 2015 dans l'exemple.
Nous devons maintenant compléter la valeur du jour avec des zéros. Encore une fois, nous pouvons utiliser la fonction TEXT:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Date julienne inversée
Si vous devez reconvertir une date julienne en une date normale, vous pouvez utiliser une formule qui analyse la date julienne et l'exécute via la fonction de date avec un mois de 1 et un jour égal au "nième" jour. Par exemple, cela créerait une date à partir d'une date julienne yyyyddd comme 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Si vous n'avez qu'un numéro de jour (par exemple 100, 153, etc.), vous pouvez coder l'année en dur et insérer le jour:
=DATE(2016,1,A1)
Où A1 contient le numéro du jour. Cela fonctionne car la fonction DATE sait comment ajuster les valeurs qui sont hors limites.
