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?

El misterioso caso de las fechas cambiantes

Todo empezó cuando un colega en el trabajo copió una tabla de fechas de un cuaderno Excel a otro. Era uno de esos raros días en que su atención esta centrada más en el trabajo que en cosas agradables.
Con sorpresa observó que las fechas cambiaban al ser copiadas de un cuaderno al otro. Esta es la situación (por supuesto que no se trataba de fechas de nacimiento)

Después de rascarse repetidamente el cuero cabelludo, acción que, está por demás decir, no incrementa la capacidad analítica del rascado, decidió consultarme.

Debo reconocer que tuve unos momentos de embarazosa vacilación hasta dar con la clave del misterio. Excel tiene dos bases distintas para los cálculos de fecha: 1900 y 1904.

Como (casi) todos sabemos Excel usa una serie de números enteros para representar las fechas. La base por defecto (para todas las versiones de Excel para Windows) es el 1 de enero del 1900, fecha representada por el número 1. El número 2 representa el 2 de enero de 1900 y así sucesivamente.
Pero sucede que Excel permite también usar como base el año 1904. De manera que el  mismo número de serie puede representar dos fechas distintas dependiendo de las definiciones del cuaderno Excel.
De esta manera, al copiar las fechas Excel "traduce" el número de serie a distintas fechas.
En la imagen de arriba podemos ver que la diferencia entre las fechas en los cuadernos es de 4 años; más precisamente 1462 días.

Cambiamos la base en Archivo-Opciones-Avanzadas-Al calcular este libro

En esta captura de pantalla podemos ver cambian las fechas al cambiar el año-base




Volviendo al caso de mi colega, ¿cómo remediar esta situación?

Una posibilidad es cambiar la definición en el cuaderno de mi colega. Esta solución no es recomendable ya que si hay otras fechas en el cuaderno, éstas cambiaran también.
Otra posibilidad es poner el número 1462 en alguna celda vacía y copiarla; luego seleccionar el rango de fechas a modificar y usar Pegado Especial-Valores-Restar para corregir la fechas.

¿Por qué conviven estas dos bases en Excel? Aparentemente por motivos de compatibilidad con las versiones de Excel para Mac. En "Mac" la base es 1904 (la primer versión de Excel fue para Mac).

Como no fuera esta dualidad es problemática y puede conducir a errores importantes.

Total Anual Móvil con PowerPivot - versión mejorada

En el post anterior mostré como calcular el TAM (Total Anual Móvil) con PowerPivot. La solución tiene algunos problemas:

1. no muestra los totales para cada año;
2. el cálculo del TAM promedio es inexacto para el primer año (2015 en nuestro ejemplo).

Aparentemente podemos solucionar el primer problema con facilidad; sencillamente agregamos los subtotales para el campo Year (el campo que arrastramos al área de las filas), pero entonces nos topamos con un nuevo problema