EXCEL: Convertir fecha a formato juliano

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, el 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 uso de relleno:

=TEXTO(B5,"yy")&B5-FECHA(AÑO(B5),1,0)

Para un año de dos dígitos + un número de día rellenado con ceros a 3 lugares:

=TEXTO(B5,"yy")&TEXTO(B5-FECHA(AÑO(B5),1,0),"000")

Para un año de cuatro dígitos + un número de día rellenado con ceros a 3 lugares:

=AÑO(B5)&TEXTO(B5-FECHA(AÑO(B5),1,0),"000")

Cómo funciona esta fórmula

Esta fórmula genera el resultado final en 2 partes, unidas por concatenación con el operador ampersand (&).

A la izquierda del ampersand, 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 de número dentro de una fórmula:

TEXTO(B5,"yy")

Para extraer un año completo, use la función AÑO:

AÑO(B5)

En el lado derecho del ampersand 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, utilizamos la función FECHA. Cuando le da a FECHA un valor de año y mes, y un cero por día, obtiene el último día del mes anterior. Entonces:

B5-FECHA(AÑO(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:

TEXTO(B5-FECHA(AÑO(B5),1,0),"000")

Fecha juliana inversa

Si necesita convertir una fecha juliana a 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 "enésimo". Por ejemplo, esto crearía una fecha a partir de una fecha juliana aaaaa, como 1999143.

=FECHA(IZQUIERDA(A1,4),1,DERECHA(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 así:

=FECHA(2016,1,A1)

Donde A1 contiene el número del día. Esto funciona porque la función FECHA sabe cómo ajustar los valores que están fuera de rango.