#ExcelPedroWave

Análisis de las fechas de Excel

Este fin de semana me propuse un ejercicio que consistía en rellenar una tabla de Excel con fechas y horas incrementadas aleatoriamente. ¡Cosa trivial en un principio! ¿o no?

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?

Formato y valor de fechas y horas

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.

Precisión decimal

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:

• 0,958333333333333 = 23/24 son 23 horas.
• 0,0409722222222222 = 59/60/24 son 59 minutos.
• 0,000671296296296296 = 58/60/60/24 son 58 segundos.

La suma de estos 3 números decimales da:

• 0,999976851851852 = (23 + (59 + 58 / 60) / 60) / 24
• son 23 horas, 59 minutos y 58 segundos como parte decimal de un día.

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á:

• 0,95833333333333333000 como 23 horas.
• 0,04097222222222222200 como 59 minutos.
• 0,00067129629629629629 como 58 segundos.

La suma de los 3 decimales anteriores con 17 dígitos de precisión es el decimal:

• 0,99997685185185184 que se representa como la hora 23:59:58 y que se visualiza redondeado a 15 dígitos decimales: 0,999976851851852

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:

www.mrexcel.com - 17 or 15 digits of precision

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?

Fechas y horas aleatorias

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?

Problema con fechas imprecisas

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?

Precisión del formato numérico de fechas

Si has leído el artículo de Bill Jelen:

www.mrexcel.com - 17 or 15 digits of precision

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:

• 44275,9999999999 con el número de serie de 5 cifras 44275, representa el día 20-03-2021
• pero muestra el día 21/03/2021 00:00:00, debido al redondeo interno del formato numérico de fechas de Excel. ¡Cosa peligrosa! ¿o no?

Solución propuesta para fechas desbordadas

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?

Plantilla de fechas desbordadas

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:

Buscar rápida y eficazmente en Excel