Comportamiento extraño de fechas filtradas en hojas de cálculo.

El mayor problema con las fechas no es la limitación del campo numérico almacenarlas en una hoja de cálculo.

El gran problema y le he dado por las múltiples formas que los humanos tenemos de expresar las fechas.

Hoy vamos a analizar los dos formatos más habituales de expresar fechas, que podríamos denominar, el europeo y el americano.

En Europa habitualmente se indica primero el día, luego el mes y finalmente el año, este formato se suele identificar como DDMMAAAA, haciendo referencia la D a día, la M a mes y la A a año.

En América se suele indicar en primer lugar el número de mes, luego el del día y finalmente el del año, este formato se suele identificar como MMDDAAAA.

En el ejemplo de hoy, vamos a importar el fichero de texto con las fechas que vemos a continuación, todas ellas en formato MMDDAAAA.

En este caso era sencillo darse cuenta que estás fechas entran en formato Americano, ya que las fechas son consecutivas y nos dan una pista importante, pero en el caso de venir mezcladas podría ser difícil darse cuenta.

Importamos el fichero a Microsoft Excel, desde "Obtener datos externos" desde la opción "Desde un archivo de texto".

Seleccionamos el fichero pulsamos el botón importar.

Vemos que los.datos se reconocen correctamente y pulsamos en "Finalizar"

Seleccionamos la celda A1 y aceptamos.

Ya tenemos los datos en el Excel.

Ahora aplicamos un filtro, para quedarnos solo con los datos que nos interesan.

Y al desplegar el filtro notamos algo extraño.

Algunas de las fechas no aparecen agrupadas por meses, es como si no reconociese a que mes pertenecen.

En realidad, si nos fijamos un poco más resulta que las fechas que no aparecen acumuladas son fechas que no pueden existir con el día 02 del mes 13 del año 2021.

Es más, si ampliamos el ancho de la columna nos sorprenderá que algunas fechas están alineadas a la izquierda, como si fuesen textos, y otras a la derecha como si fuesen números.

Este es uno de los peores errores que podemos cometer al importar datos en una hoja de cálculo.

Lo que ha sucedido es que nuestra hoja de cálculo ha asumido que las fechas están en formato DDMMAAAA, pero el fichero de texto las traía en formato MMDDAAAA.

Pero lo.peor que nos puede suceder es creer que solo las fechas no reconocidas están mal.

Las que se han reconocido pueden estar bien o mal, por ejemplo el uno de Enero no será errónea porque tanto el día como el mes son iguales haciendo que su representación en ambos formatos de fecha coincida.

Sin embargo no es lo mismo el dos de Enero que el uno de Febrero.

Lo peor de estas coincidencias es que podríamos llegar a comprobar que los datos para el día uno de Enero son correctos y confiarnos.

Comparativa fechas en formato 1900 en distintas hojas de cálculo (Microsoft Excel, Libreoffice Calc, Google Sheets)

A pesar de que todas las hojas de cálculo soportan el formato de fechas 1900, no todas los implementan igual. 

En Microsoft Excel se asigna el día 00/01/1900 al valor cero. 

Es una situación curiosa, ya que este día no existe.

El valor 1 se corresponde al día 01/01/1900.

Mientras que para el día 01/08/2021 se corresponde el valor 44.409.

En Libreoffice Calc los valores para el día actual son los mismos, sin embargo para el valor 0 se asigna el 30/12/1899 y para el 1 el 31/12/1899.

A pesar de que para el día 01/08/2021 se corresponde el mismo valor que en Microsoft Excel, no sucede esto para el valor 1, que se corresponde al día anterior.

Los valores en Google Sheets se corresponden a los mostrados en Libreoffice Calc.

Mostrando los datos en paralelo podemos comprobar que a pesar de que los datos para los primeros días puede haber una diferencia en los datos de fechas actuales son equivalentes.

Cuando trabajemos con fechas recientes no tendremos problemas, pero en el caso de tener que recurrir a fechas cercanas al 1900 deberemos tener clara la diferencia entre las distintas herramientas.

Tratamiento de fechas en hojas de cálculo (Microsoft Excel, Libreoffice Calc y Google Sheets)

Aunque aparentemente los datos relativos a fechas y horas parecen un tipo de datos por sí mismo, y así es en alguno lenguajes de programación, en las hojas de cálculo no tienen un tipo propio.

Para tratar datos relativos a fechas y a la vez facilitar los cálculos relativos a las mismas, tradicionalmente se representan las fechas como números enteros.

Para ello se elige un día al que se asigna el valor 1.

Lamentablemente no existe un único sistema de fechas.

En concreto existen dos sistemas de fechas denominados 1900 y 1904 por el año que usan como año 1.

A día de hoy el formato por defecto suele ser el 1900.

En este formato el número 1 se corresponde con el día 01/01/1900 

Por ejemplo a fecha de hoy día 01/08/2021.

Si modificamos el formato de celdas de Fecha.

Y asignamos el formato General.

Veremos un número en lugar de la fecha.

Para el día 01/08/2021 el número mostrado es 44409, que se corresponde con el número de días transcurridos entre el día de hoy y el día de inicio del sistema de fechas, en el ejemplo el día  01/01/1900.

En la siguiente imagen muestro diversos valores numéricos y la fecha a la que se corresponden en el sistema 1900. 

Como se puede apreciar los valores inferiores al 1 muestran la fecha 00/01/1900.

También se aplica el mismo formato en Libreoffice Calc.

También sucede lo mismo en Google Sheets.

Sin embargo existen ciertas diferencias y peculiaridades que iré mostrando en futuras publicaciones.

Los calendarios del caos

Si algo ha sido un pequeño caos a lo largo de la historia de la humanidad, es el cálculo del tiempo.

En concreto los calendarios.

Sin llegar a meternos en calendarios como el maya, el chino, etc. en el propio mundo occidental tenemos nuestras dificultades para medir el tiempo.

https://es.wikipedia.org/wiki/Calendario_chino

En occidente, durante la mayor parte de la historia se han usado dos calendarios principalmente.

Primero el calendario implementado por el mismísimo Cayo Julio Cesar, el calendario Juliano, el cual ha estado en vigor hasta bien entrado el siglo XVI.

https://es.wikipedia.org/wiki/Calendario_juliano

A partir del año 1582, impulsado por el papa Gregorio XIII, fué sustituyendo poco a poco al calendario Juliano.

https://es.wikipedia.org/wiki/Calendario_gregoriano

El caso es que este calendario fué aplicado poco a poco por distintos paises del viejo continente, a lo largo del tiempo, provocando un desfase de fechas entre los países que tenían cada calendario.

Este cambio de calendarios en un país provocaba la desaparición de días. En cada país existe una serie de días, que realmente nunca han existido.

La imagen recogida de la wikipedia muestra el salto de fecha en un cambio de calendario.

Esto ha provocado que durante parte de los siglos XVI y XVII, en Europa convivían ambos calendarios, por ejemplo en España fué adoptado en el año 1582 mientras que en Inglaterra no se implantó hasta el año 1752, siendo Turquía el último país en adoptarlo en el año 1926.

https://es.wikipedia.org/wiki/Cambio_al_calendario_gregoriano

Como anécdota comentar que el día del libro se celebra el 23 de Abril, conmemorando el día del fallecimiento de do grandes escritores, Miguel de Cervantes Saavedra, y William Shakespeare.

Pero el año de la muerte de ambos, 1616 España ya había cambiado el calendario pero Inglaterra aún no. Aunque Shakespeare murió el 23 de abril de 1616 según el calendario Juliano, según el calendario gregoriano murió el 3 de mayo de ese año. Esto significa que Shakespeare pudo haber asistido personalmente al funeral de Cervantes.

Mas información en el siguiente enlace:

https://hablacultura.com/cultura-textos-aprender-espanol/curiosidades/cervantes-y-shakespeare-no-murieron-el-mismo-dia/

A pesar de que el calendario Gregoriano es más preciso sigue sin ser perfecto, por lo que es preciso el uso de algunos arreglos, como son el día adicional en los años bisiestos o el segundo intercalar.

Como curiosidad adicional, comentar que los años bisiesto no son solo cada cuatro años.

Un año es bisiesto si cumple los siguientes criterios:

• Es bisiesto si es divisible entre 4.
• Pero no es bisiesto si es divisible entre 100.
• Pero sí es bisiesto si es divisible entre 400.

Una tercera curiosidad, en el calendario Gregoriano no existe el año Cero, se pasa directamente del año uno al menos uno.

Evidentemente esto se traslada a la informática.

Mañana aclararemos como se tratan los dato de fechas en las hojas de cálculo.

Nota: Derechos de las imágenes Wikipedia.

Comparación precisión en distintas Hojas de Cálculo (Microsoft Excel, Libreoffice Calc y Google Sheets)

Comparación entre los distintos resultados en las distintas hojas de cálculo.

Mostramos un mismo ejemplo en tres distintas hojas de cálculo.

En Microsoft Excel tenemos un redondeo a 15 decimales, redondeando tanto los números de tarjeta como los de la suma.

El resultado es idéntico en Google Sheets que en Microsoft Excel.

En LibreOffice Calc encontramos una diferencia, ya que si es posible tratar los números de tarjeta sin pérdida de precisión, pero no así la suma.

Este es un tema a tener en cuenta a la hora de compartir una hoja de cálculo con otros usuarios.

Perdida precisión en Libreoffice Calc

Toca comparar la precisión de Microsoft Excel con uno de sus competidores LibreOffice Calc.

Realizamos el mismo proceso copiamos los datos del fichero de texto que simulan números de tarjetas de crédito y los pegamos en una hoja de cálculo vacía de LibreOffice Calc.

En este caso el proceso es un poco distinto ya que al detectar el pegado LibreOffice Calc abre una ventana de importación de datos.

Cómo parece reconocer correctamente los datos numéricos pulsamos el botón aceptar y procedemos a la importación de los datos a la hoja de cálculo.

La primera sorpresa es que los datos se importan correctamente sin ningún tipo de redondeo.

También podemos constatar que los datos son numéricos y que permiten hacer operaciones con ellos.

Por lo tanto, queda demostrado que, la precisión para datos numéricos en LibreOffice Calc es mayor que en Microsoft Excel.

Procedemos a realizar el sumatorio de todos los números de tarjeta para ver qué número nos calcula LibreOffice Calc.

Nuevamente aparece el consabido formato exponencial.

Modificamos el formato de celda para no mostrar números decimales y que así nos muestre el número entero completo.

Aunque LibreOffice Calc soporta más precisión en los números enteros que en Microsoft Excel el resultado de esta suma supera la precisión admisible provocando un redondeo.

Podemos confirmar que la precisión en LibreOffice Calc es solo de un decimal más que en Microsoft Excel.

Cómo vemos el número calculado y el resultado correcto, realizando el cálculo a mano, no coinciden.

Está diferencia de precisiones puede provocar que una misma hoja de cálculo vista por 2 usuarios con dos programas distintos arroje resultados distintos.

Es, quizá, la primera cosa que tenemos que tener en cuenta, cuando un usuario nos diga que tiene un resultado y no se corresponda con el que nosotros vemos si tenemos los mismos datos de partida.

Mostrar más de 15 dígitos numéricos en Power BI

A diferencia de Microsoft Excel que solo es capaz de trabajar con 15 decimales en los números, en Power BI es posible trabajar con rangos mayores.

Si cargamos los datos de la tarjeta de crédito que estábamos usando estos días en Power BI ai veremos que se muestran los 16 dígitos sin ningún problema.

Aunque a priori al cargar los datos a través de power query parece que no se cargan correctamente ya que como vimos ayer al promover los encabezados se muestran en formato exponencial, power query para power bi es capaz de gestionar datos con mayor rango que Microsoft Excel.

De esta forma si en Power Query pinchamos en uno de los números, que se muestran en formato exponencial, veremos en la parte inferior el valor completo.

Al cargar los datos a power bi AI sin ningún tipo de cálculo veremos cómo se muestran correctamente y exactamente en el mismo formato en el que estaban en el fichero de texto, manteniendo la precisión del dato completo.

En este aspecto power bi hay es más preciso que Microsoft Excel ya que nos permite trabajar con números de mayor rango, sin perder precisión.