Fórmula genérica
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Resumen
Si necesita convertir una fecha a un formato de fecha juliana en Excel, puede hacerlo creando una fórmula que use las funciones TEXTO, AÑO y FECHA.
Antecedentes
El "formato de fecha juliana" se refiere a un formato en el que el valor del año de una fecha se combina con el "día ordinal de ese año" (es decir, día 14, día 100, etc.) para formar un sello de fecha.
Hay varias variaciones. Una fecha en este formato puede incluir un año de 4 dígitos (aaaa) o un año de dos dígitos (aa) y el número de día puede o no rellenarse con ceros para usar siempre 3 dígitos. Por ejemplo, para la fecha 21 de enero de 2017, es posible que vea:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Solución
Para un año de dos dígitos + un número de día sin relleno, use:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Para un año de dos dígitos + un número de día rellenado con ceros en 3 lugares:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Para un año de cuatro dígitos + un número de día relleno con ceros en 3 lugares:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Explicación
Esta fórmula genera el resultado final en 2 partes, unidas por concatenación con el operador y comercial (&).
A la izquierda del y, generamos el valor del año. Para extraer un año de 2 dígitos, podemos usar la función TEXTO, que puede aplicar un formato numérico dentro de una fórmula:
TEXT(B5,"yy")
Para extraer un año completo, use la función AÑO:
YEAR(B5)
En el lado derecho del signo comercial, necesitamos averiguar el día del año. Hacemos esto restando el último día del año anterior de la fecha con la que estamos trabajando. Debido a que las fechas son solo números de serie, esto nos dará el "enésimo" día del año.
Para obtener el último día del año del año anterior, usamos la función FECHA. Cuando le da a DATE un valor de año y mes, y un cero para el día, obtiene el último día del mes anterior. Entonces:
B5-DATE(YEAR(B5),1,0)
nos da el último día del año anterior, que es el 31 de diciembre de 2015 en el ejemplo.
Ahora necesitamos rellenar el valor del día con ceros. Nuevamente, podemos usar la función TEXTO:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Fecha juliana inversa
Si necesita convertir una fecha juliana en una fecha normal, puede usar una fórmula que analice la fecha juliana y la ejecute a través de la función de fecha con un mes de 1 y un día igual al día "n". Por ejemplo, esto crearía una fecha a partir de una fecha juliana yyyyddd como 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Si solo tiene un número de día (por ejemplo, 100, 153, etc.), puede codificar el año e insertar el día:
=DATE(2016,1,A1)
Donde A1 contiene el número de día. Esto funciona porque la función FECHA sabe cómo ajustar los valores que están fuera de rango.
