Cuál fue mi sorpresa cuando encontré un problema que no me esperaba con los formatos de números decimales que representan la fecha y hora. ¡Cosa insignificante si no eres muy meticuloso! ¿o no?
La premisa fundamental con las fechas en Excel es que el día se almacena como un número de serie entero y la hora es la parte decimal del día. ¡Cosa de cajón! ¿o no?
Mi descubrimiento me dice que las fechas almacenadas como decimales no siempre se corresponden con las fechas visualizadas con formato de fecha. ¡Cosa nunca vista! ¿o no?
Formato de fecha y hora en Excel (ver enlace aquí):
Muestra los números de serie que representan fechas y horas como valores de fecha, según el tipo y la configuración regional (ubicación) especificados. Los formatos de fecha que comienzan con un asterisco (*) responden a cambios de la configuración regional de fecha y hora, que se especifican en el Panel de control. Los formatos sin asterisco no se ven afectados por la configuración del Panel de control.
¡Vaya lío en el que me he metido! ¿o no?
En la celda A2 escribo la fecha: 20/03/2021 23:59:58
Su valor decimal se visualiza con 15 dígitos: 44275,9999768519
La parte entera es el número de serie que representa el número de días transcurridos desde el 1 de enero de 1900. Al día 20/03/2021 le corresponde el número entero: 44275
La parte decimal es la fracción de un día. El valor decimal 0,9999768519 equivale a 23:59:58 en formato hh:mm:ss, siendo hh: horas; mm: minutos; ss: segundos.
Excel puede mostrar la hora como un número decimal con 15 dígitos significativos, como vamos a comprobar sumando la parte decimal de las horas, los minutos y los segundos:
La suma de estos 3 números decimales da:
Si te gustan los números como a mí, verás que esa suma con 15 dígitos significativos sería de 0,999976851851851 siendo la última cifra un 1 en lugar de un 2. Esto se debe a que Excel representa 15 dígitos significativos pero almacena secretamente 17 dígitos significativos, por lo que la suma calculada será:
La suma de los 3 decimales anteriores con 17 dígitos de precisión es el decimal:
Bill Jelen, uno de los máximos gurús de Excel, conocido mundialmente como MrExcel, escribió un excelente artículo avisando de alguno de los problemas derivados de la precisión interna del motor de cálculo de Excel. Accede al siguiente enlace en inglés para estudiarlo:
Sobre este importante asunto de la precisión interna de Excel volveré a hablar más abajo. Sigue leyendo este artículo hasta el final para saber más sobre la precisión de las fechas. ¡Caso de que te interese! ¿o no?
En la celda A3 escribo la siguiente fórmula y la arrastro hacia abajo:
=A2+ALEATORIO()/100000
Lo que hago es sumar a una fecha y hora un valor aleatorio muy pequeño, dividido por cien mil, para que el incremento de fecha y hora sea mínimo.
En esta imagen puedes ver el resultado obtenido en la hoja 'Aleatorias', que puedes descargar más abajo:
Nos fijamos en las celdas marcadas con color de fondo rojo, en las que se ha calculado la parte entera de la fecha. ¡Cosa inesperada! ¿o no?
Se puede comprobar que no coincide el día representado en la columna A, el 21/03/2021, con el día 20/03/2021 de la columna B, que equivale a la parte entera de la fecha, igual a 44275. Este problema ocurre 2 veces en esta ejecución aleatoria, pero puede ocurrir varias veces más. ¡Cosa indeseable! ¿o no?
Cuando se generan fechas aleatoriamente, la parte decimal de una fecha próxima a uno, hace que el formato de números de fecha en Excel se desborde, mostrando el día siguiente, cuando la parte entera del número de serie de fecha se corresponde con el día anterior. ¡Cosa inexplicable! ¿o no?
En la siguiente imagen, de la hoja 'Fechas', se aprecia la precisión decimal de varios formatos de fechas que generan desbordamiento.
En color de fondo rojo están marcadas las fechas mostradas para las que no coincide su día correspondiente, o sea su número de serie que es su parte entera. ¡Cosa imperdonable! ¿o no?
Si has leído el artículo de Bill Jelen:
habrás visto que el motor de cálculo de Excel almacena y calcula los decimales con más precisión (17 dígitos) que la representada (15 dígitos). ¡Cosa que ya sabías! ¿o no?
Con los decimales de las fechas también pasa lo mismo. Las fechas almacenadas pueden tener más decimales que las fechas mostradas, lo que genera un problema cuando los dígitos decimales están próximos a la unidad, como podemos comprobar con este ejemplo:
La solución propuesta por Bill Jelen, en su artículo sobre la precisión interna del motor de cálculo de Excel, se puede aplicar también a este nuevo problema con el formato de fechas.
Se consigue resolver el problema redondeando la fecha hacia abajo a 5 decimales, con esta fórmula:
=REDONDEAR.MENOS([@Fecha];5) = 44275,99999
con sólo 10 dígitos de precisión, 5 para el día y otros 5 para la parte decimal del día, que se visualiza como la fecha y hora correctas: 20/03/2021 23:59:59 ¡Cosa deseada! ¿o no?
Descarga la plantilla con la que he escrito este artículo desde Microsoft OneDrive, o juega con la plantilla incrustada en mi blog desde aquí:
ATENCIÓN: Excel Online no permite recalcular con la tecla F9. En la hoja 'Aleatorias' se puede modificar la celda A2 y se recalculará todo. ¡Cosa mejorable! ¿o no?
Próximamente publicaré la tabla de fechas generadas aleatoriamente y verás qué motivó este simple ejercicio. ¡Cosa curiosa! ¿o no?
Ya puedes leer el artículo que me sirvió de inspiración al detectar el problema con las fechas desbordadas cuando estaba preparando una macro para rellenar aleatoriamente un millón de visitas a mi blog:
No Response to "Fechas desbordadas aleatoriamente en Excel"
Leave A Reply
Dime si te gusta lo que lees y, si no te gusta, dime por qué. Tengo habilitada la moderación de comentarios. Tu comentario se publicará pronto.