Algunas cavilaciones sobre Excel, Power Query y fechas

Los cálculos de tiempo en Excel tienen sus bemoles como ya hemos visto por ejemplo en el post anterior. Excel (y no sólo Excel) usa una serie de números enteros para representar las fechas. Excel tiene dos años-base distintos para los cálculos de tiempo: 1900 y 1904 de manera que el 1 representa el 01/01/1900 o el 01/01/1904 según el caso.
Fueran cuales fueran las razones (ver el comentario de Jaime en el post mencionado) la base 1900 conlleva un serio problema: Excel considera al año 1900 como bisiesto cuando no lo es (y recordemos que por defecto la base de cálculo es 1900).
Podemos ver este bug haciendo este ejercicio:

1. creamos una columna con una serie de números enteros del 31 al 61;
2. copiamos esta serie en la columna contigua.
3. en la segunda columna cambiamos el formato a Fecha.

Vemos la correspondencia entre el número de serie (31, por ejemplo) y la fecha (31 de enero 1900). Si nos fijamos en el final de nuestra serie vemos el problema: Excel reconoce la fecha 29 de febrero 1900 

pero esta fecha ¡no existe! 1900 no es una año bisiesto. 

El problema con este bug es que influye en el cálculo de lapsos. Por ejemplo si calculamos 

01/02/1900 - 31/01/1900

obtenemos, naturalmente 1; pero si calculamos 

01/03/1900 - 28/02/1900

obtenemos, erróneamente, 2.

Es fácil entender por qué Microsoft nunca corrigió este bug.¿Cuántas veces tuvieron que calcular diferencias de fechas que involucren el año 1900?.

¿Qué sucede en Power Query?

Power Query está libre de este error. Podemos comprobarlo creando una serie de fechas en el editor de Power Query

1 - creamos una consulta en blanco y en la barra de fórmulas ponemos esta expresión: {0..63}

Ésto crea una lista con una serie de números enteros.
2 -Convertimos la lista a tabla usando la opción A la tabla en el menú Transformación

Power Query crea una tabla con una única columna; la cambiamos el nombre a Nro de Serie.

3 - Duplicamos la columna, cambiamos el nombre a Fechas y cambiamos el tipo de datos a Fecha.

Aquí podemos ver dos diferencias significativas con Excel:

1 - El número de serie de la fecha 1 de enero 1900 es 2, no 1 como en Excel;

2 - Power Query permite cálculos con fechas anteriores al 1900 (de hecho, desde el 1 de enero del año 100, ver más adelante aquí y también en este post).

Si avanzamos hacia el final de la tabla veremos que Power Query no crea la "fecha" 29/02/1900, como sucede con Excel

Respecto a las fechas anteriores al 1900, de acuerdo a la documentación de Microsoft (julio 2019):

 A date is encoded as a number of days since epoch, starting from January 1, 0001 Common Era on the Gregorian calendar. The maximum number of days since epoch is 3652058, corresponding to December 31, 9999.

o en el idioma de Cervantes:

La fecha está codificada como un número de días desde la época, a partir del 1 de enero de 0001 Era Común en el calendario Gregoriano. El número máximo de días desde la época es 3652058, correspondiente a diciembre 31, 9999.

Es decir, Power Query usa números negativos para las fechas anteriores al 30/12/1899 (ya vimos que 1 representa el 31/12/1899 y 2 el 01/01/1900).
Sin embargo el primer número de serie que Power Query acepta convertir en fecha es -657434 que corresponde al 1 de enero del 100. Números de series menores de -657434 no pueden ser convertidos a fecha y producen un error

Sin embargo Power Query si nos permite crear la fecha 1 de enero 0001 si usamos la expresión #date(1,1,1). Sin embargo, si queremos obtener el número de serie de la fecha, obtenemos 0 y no un número negativo como era de esperar

Si hacemos este ejercicio con cualquier fecha posterior al 01/01/0001 y anterior al 01/01/0100 obtenemos un error.

A pesar de lo que parece ser una inconsistencia, Power Query nos permite hacer operaciones con fechas desde el año 1 de nuestra era sin errores, lo que supongo que sólo tendrá importancia para historiadores o arqueólogos que usen Excel (si es que los hay).