Traducir el blog
Fechas desbordadas aleatoriamente en Excel
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:
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:
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:
Mi lista de blogs
-
Regresos 14 – (2) Bases de cuatro cubos con suma cero - En la anterior entrada estudiamos las sumas de cubos cuyas bases suman cero. En esta otra ampliaremos el estudio a cuatro cubos. *Sumas con cua...Hace 9 horas
-
Problema 2: Errores en la nube de OneDrive - 🔝*To translate this blog post to your language, select it in the top left Google box. * Este año ha comenzado con dos problemas detectados en Microso...Hace 1 día
-
Crea con IA tablas Excel de ejemplo - Crea con IA tablas Excel de ejemplo para practicar y simplificar tareas de buscar datos o crearlos. A veces, encontrar el conjunto de datos perfecto para...Hace 2 días
-
What Does #### Mean in Microsoft Excel? - If you’re wondering what does #### mean in Excel, read this Microsoft Excel guide until the end, and learn all the ways Excel uses the pound or number sign...Hace 2 días
-
Cómo graficar totales de una tabla dinámica en Excel - Las tablas dinámicas son una de las herramientas más poderosas en Excel para organizar y analizar grandes cantidades de datos. Sin embargo, cuando necesi...Hace 3 días
-
OBTENER LA TENDENCIA EN POWER APPS CALCULANDO LA PENDIENTE - Hola a todos, He estado estos días implementando un nuevo input en el borrador de un proyecto de Apps que estoy creando. Se trata de … La entrada OBTENE...Hace 3 días
-
3DFrame-NLGeom - The VBA version of the 3DFrame spreadsheet has now been updated to allow geometric non-linear effects to be included. The new version can be downloaded fro...Hace 5 días
-
Fin - Llevo tiempo pensando esta entrada y no sé que decir. Analisis y Decisión nació en 2008 y no ... Leer más »Hace 1 semana
-
Validación de Datos en Excel: de lo Básico a lo Avanzado - En Excel, uno de los errores más comunes es dar por sentado que sabemos lo básico. Muchas personas que se […] The post Validación de Datos en Excel: de l...Hace 1 semana
-
📊🔎 Cómo usar SI y UNIRCADENAS para manejar múltiples resultados en un búsqueda - ¿Cómo buscar múltiples resultados en Excel y unirlos en una sola celda?Si has trabajado con Excel, seguramente conoces la función BUSCARV, que permite enco...Hace 2 semanas
-
Happy Excel Day 45678 - Where does the time go? Two years ago, we were celebrating Excel Day 45000, and suddenly it’s Excel Day 45678! Do you have any special events planned? If n...Hace 4 semanas
-
FREE 4 Hours Complete Excel Course - FREE 4 Hours Complete Excel Course Here is a FREE, complete end-to-end Excel course to help you learn all the key and essential aspects of Microsoft Exce...Hace 1 mes
-
Análisis DAFO (FODA, DOFA) las decisiones con Excel - Para conocer la situación de una empresa, proyecto o persona, recurrimos al análisis DAFO (FODA, DOFA) en la toma de decisiones con Excel. El los años sese...Hace 3 meses
-
Color, Conditions, and Copilot: How to save time using conditional formatting with Copilot in Excel - Hi everyone, this is part 11 in a series of posts to show you some of the things that are possible to do with Copilot in Excel. *What is conditional f...Hace 3 meses
-
4 ejemplos de utilización de validación de datos con fórmulas - Ya sabes que la herramienta de Validación de datos es de gran utilidad para controlar y restringir la introducción de datos y así, asegurarte de…Hace 5 meses
-
How To Predict Bearing Life With Excel - When you work in mechanical engineering, understanding the reliability and performance of bearings under various conditions is crucial. Bearings are the co...Hace 5 meses
-
-
TikTok’s search evolution - 2 in 5 Americans use TikTok as a search engine. Nearly 1 in 10 Gen Zers are more likely to rely on TikTok than Google as a search engine. More than half of...Hace 10 meses
-
Unblocking and Enabling Macros - When Windows detects that a file has come from a computer other than the one you're using, it marks the file as coming from the web, and blocks the file....Hace 1 año
-
Office Scripts: Trabajando con Tablas - [image: Office Scripts: Trabajando con Tablas] Me he dado cuenta que últimamente solo escribo de lenguaje M (es mi pequeño vicio)... pero hay que liberar l...Hace 2 años
-
Hello world! - [image: Hello world!] Welcome to WordPress. This is your first post. Edit or delete it, then start writing!Hace 2 años
-
La importancia de saber mecanografía en 2022 - [image: Resultado de imagen de mecanografía viñeta escribiendo a máquina] Según la RAE, la mecanografía es el arte de escribir a máquina. Hace unos cuantos...Hace 2 años
-
London Excel Meetup Workbooks - The workbooks used in my presentation on “Analytical and Interactive Dashboards in Excel” at the London Excel Meetup, September 3, 2020Hace 4 años
-
Cálculo de jornada que termina al día siguiente (Power Query) y despedida - [image: Cálculo de jornada que termina al día siguiente (Power Query) y despedida] Este blog se ha ocupado de cálculos de tiempo con bastante intensidad, c...Hace 4 años
-
Agenda Perpetua Excel Calendario Perpetuo - Saludos a los ingenieros y a todos los que forman parte de nuestra comunidad de planillas Excel para ingeniería civil, ya se vienen las fiestas navideñas y...Hace 5 años
-
International Keyboard Shortcut Day 2019 - The first Wednesday of every November is International Keyboard Shortcut Day. This Wednesday, people from all over the world will become far less efficient...Hace 5 años
-
Welcome, Prashanth! - Last March, I shared that we were starting to look for a new CEO for Stack Overflow. We were looking for that rare combination of someone who… Read more "W...Hace 5 años
-
Visualize parts and whole - combine clustered column and stacked column charts - *Inga: Disa what?* *Igor: -ppeared.* by The FrankensTeam ------------------------------ Really it was 3 years ago we posted our last article? *Freddy: Th...Hace 5 años
-
Salvador Sostres, analfabeto profesional - Los nuevos tiempos traen nuevas profesiones. Internet, además, ha revolucionado el mundo del periodismo y la palabra escrita. Adaptarse o morir, ese es el ...Hace 6 años
-
Planificación de compras - Realizar una lista con los productos que necesitamos y que formarán parte de nuestra cesta de la compra nos ayuda a *encontrar la combinación de bienes p...Hace 12 años
-
-
-
-
-
-
-
-
-
No Response to "Fechas desbordadas aleatoriamente en Excel"
Leave A Reply
Indícame las erratas que encuentres y qué es lo que te gustaría ver en los próximos artículos.