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
-
Derivada aritmética - Este original concepto fue presentado por el matemático español José Mingot Shelly en 1911 con el título "Una cuestión de la teoría de los números", trab...Hace 2 días
-
3 Métodos para Validar Emails en Excel: De Básico a Avanzado - Cuando capturamos datos en Excel, garantizar la calidad de la información es clave para evitar futuros dolores de cabeza al […] The post 3 Métodos para V...Hace 3 días
-
5 Ways To Show All Notes in Microsoft Excel - Are you wondering how to show all notes in Excel? To unlock all the secrets of Microsoft Excel worksheet notes, keep reading! Managing notes in Excel can f...Hace 3 días
-
Jugando al Rabino en Excel - Parte 4 - 🔝*To translate this blog post to your language, select it in the top left Google box. * Parte 4 de un juego del Rabino Esta es la cuarta parte de u...Hace 3 días
-
Debra’s Excel News–November 2024 - Fix pivot table errors, show images in cells, and more, in this month’s Excel news. Visit my Excel website for more tips, tutorials and videos, and check t...Hace 1 semana
-
Accountex 2024 Excel a Power BI - En Accountex 2024 el jueves 7 noviembre presenté: «BI transición de Excel a Power BI» (Sesión coordinada por el Consejo General de Economistas)Esta sesió...Hace 1 semana
-
PASAR DATOS A FILAS SEGÚN ELEMENTOS ENTRE GUIONES UTILIZANDO TYPESCRIPT - Hola a todos! Después de haber realizado el ejercicio de pasar datos de una columna a filas por cada elemento entre guiones, con VBA y … La entrada PASA...Hace 1 semana
-
Installing 3DFrame-py - As mentioned in the previous post, the installation process for the 3DFrame-py spreadsheet has changed with the new version. Also there have been some sign...Hace 2 semanas
-
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 semanas
-
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 2 meses
-
Trucos de Excel: Referencias Relativas, Absolutas y Mixtas Explicadas ✨ [VIDEO] - ¡Hola a todos! Hoy hablaremos sobre un tema muy importante para quienes usan Excel: los tipos de referencia. Las referencias son fundamentales al momento d...Hace 2 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 2 meses
-
How to calculate WEEKNUMBER in Month / Quarter / Year with Excel? - Let's say you have daily data and your boss wants to see the trends by week in month or week in quarter? How do you calculate the week number in a month ...Hace 3 meses
-
Excel: Cálculo de Finiquito, Beneficios Sociales Bolivia - Es importante tener nociones mínimas acerca el cálculo del finiquito y Beneficios Sociales de un trabajador en Bolivia, ya sea para la verificación de nues...Hace 3 meses
-
-
Minutos de juego y puntos. El Espanyol, sus finales de partido y mis enfados - Pienso que el Espanyol este 2024 se está dejando muchos puntos al final de los partidos. Cuando el ... Leer más » The post Minutos de juego y puntos. El ...Hace 7 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 8 meses
-
MASTERCLASS Gratis – Gráfica de Gestión Proyectos en #EXCEL. - Aprende a crear un Gráfico de CURVA S, ideal para GESTIÓN DE PROYECTOS, porque te permite identificar como esta tu proyecto tanto en COSTOS como en TIEMP...Hace 9 meses
-
Interés compuesto con Excel - Este es un mapa mental de las distintas funciones Excel para calcular el valor del dinero en el tiempo aplicando la ley de capitalización compuesta. En ...Hace 9 meses
-
Demos cursos de Excel 2007, 2010, 2013, 2016, 365 - Puedes consultar las demostraciones de los siguientes capítulos de los cursos Excel. Demo cursos ExcelHace 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
-
-
Navigating Outlook Favorites - I have these four favorites defined in Outlook: From the inbox, I could hit Shift+F6 to get into the Favorites area but sometimes I would end up in no man’...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 1 año
-
Progress on the Block Protocol - Since the 1990s, the web has been a publishing place for human-readable documents. Documents published on the web are in HTML. HTML has a little bit of… Re...Hace 1 año
-
Hello world! - [image: Hello world!] Welcome to WordPress. This is your first post. Edit or delete it, then start writing!Hace 2 años
-
Decálogo para realizar Trabajos de Fin de Grado (TFGs) y de Fin de Máster (TFMs) - 1.- Tanto TFGs como TFMs son un requisito para graduarse a la vez que una oportunidad para aprender. Así, el tiempo que se le dedica es muy variable. De...Hace 3 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
-
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
-
-
-
-
-
-
-
-
-
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.