Hace 1 día
Cómo calcular fechas antes de 1900 en Excel y VBA
Posted on octubre 22, 2020 by Excel Pedro Wave
🔝To translate this blog post to your language, select it in the top left Google box.
Desde que me dedico en serio a los cálculos me ha apasionado la conversión de fechas, con sus días y sus horas.
Esta entrada del blog es la primera parte de una trilogía sobre el cálculo de fechas anteriores al año 1900 con funciones en VBA. La trilogía explicará:
A) Cómo calcular fechas antes de 1900 en Excel y VBA
B) Cómo calcular la edad y el día de la semana antes de 1900
En esta entrada explicaré lo que interesa conocer sobre las fechas en Excel y en VBA para poder calcular correctamente las fechas anteriores y posteriores al año 1900:
1) Fechas correctas y erróneas en Excel
2) Fechas correctas y erróneas en VBA
3) Calendario Gregoriano
4) Fechas según norma ISO 8601
5) Listado de fechas curiosas
6) Plantilla con fechas Excel y VBA
En esta imagen se puede ver cómo se pretenden calcular las fechas anteriores y posteriores al 1 de marzo de 1900, tanto con fórmulas en Excel como con macros en VBA.
Esta tabla está en la hoja 'Fechas' de la plantilla que se puede descargar al final de esta entrada y donde se puede obtener la fórmula que convierte las fechas en formato ISO 8601. Los números de serie y días de la semana de enero y febrero de 1900 con calculados incorrectamente por Excel, lo que se explica si lees más abajo.
1) Fechas correctas y erróneas en Excel
Excel es una herramienta invaluable para calcular fechas, eso sí, para días desde el 1 de marzo de 1900 hasta el 31 de diciembre de 9999.
En una celda de Excel las fechas se guardan como la parte entera positiva de un número decimal y se define como el número de serie de un día, comenzando en el número de serie 1, que corresponde con el día 1 de enero de 1900, que es incorrectamente calculado por Excel.
Los meses de enero y febrero de 1900 no se deben usar en los cálculos, pues hay un error conocido en Excel para esos dos meses, ya que se definió erróneamente 1900 como año bisiesto, por lo que el primer día correcto para usarlo en los cálculos de fechas en las celdas, fórmulas y funciones de Excel es el 1 de marzo de 1900, con el número de serie 61.
Ver el siguiente enlace donde se explica el error cometido por los desarrolladores de Excel:
En esta tabla se muestran los números de serie admitidos por Excel para el rango de fechas correctas:
Las horas se guardan en la parte decimal del número. De las horas no hablaré en esta entrada por lo que, si quieres saber más sobre las horas, abre el siguiente enlace:
2) Fechas correctas y erróneas en VBA
Para calcular fechas anteriores al 1 de marzo de 1900 vienen en nuestra ayuda las funciones de fecha de VBA, ya que permiten cálculos con fechas anteriores al 1 de marzo de 1900, gracias a que trabajan con números de serie negativos para fechas anteriores al año 1900.
El primer número de serie negativo es el -1 que se corresponde con la fecha del 29 de diciembre de 1899.
El último número de serie negativo es el -657.434 que se corresponde con la fecha del 1 de enero del año 100.
El rango de fechas posible de un tipo de dato Date en VBA es desde el 1 de enero de 100 hasta el 31 de diciembre de 9999.
En esta tabla se muestran los números de serie admitidos por VBA para el rango de fechas correctas:
Para calcular el número de serie en VBA he definido la siguiente función:
=ObtenerSerieFechaISO($B2)
Para calcular el nombre del día de la semana en VBA he definido la siguiente función:
=ObtenerDíaSemanaISO($B2)
El formato de fecha en la celda B2 debe ser según norma ISO 8601, explicada en el apartado 4).
3) Calendario Gregoriano
En este artículo en adelante siempre se representarán las fechas en el Calendario Gregoriano (enlace aquí) ya que el Calendario Juliano (enlace aquí) prácticamente sólo lo usan los historiadores.
Para calcular según el Calendario Juliano se puede estudiar el siguiente enlace:
4) Fechas según norma ISO 8601
Desde este momento voy a seguir la norma internacional ISO 8601 para representar las fechas en formato texto, cosa muy recomendable para fechas anteriores al 1 de marzo de 1900. Este formato de fechas se puede consultar en el siguiente enlace:
La norma ISO 8601 ayuda a eliminar las dudas que pueden surgir de las diversas convenciones, culturas y zonas horarias de días y fechas que afectan a una operación global. Ofrece una forma de presentar fechas y horas claramente definidas y comprensibles tanto para las personas como para las máquinas.
El formato de fechas ISO 8601 es así: AAAA-MM-DD
En inglés: YYYY-MM-DD
Las cifras están separadas por guiones "-" y las letras representan el año, mes y día, rellenadas con ceros por la izquierda:
- AAAA o YYYY: son las 4 cifras del año.
- MM: son las 2 cifras del mes.
- DD: son las 2 cifras de día.
Por ejemplo, el día 2000-02-29 fue el 29 de febrero de 2000.
5) Listado de fechas curiosas
La siguiente tabla está en la hoja 'Año<1900' de la plantilla que se puede descargar al final de esta entrada.
Para no cometer errores, conocidos como bugs en inglés, en el cálculo de fechas se deben tener en cuenta las siguientes fechas, según norma ISO 8601:
- 9999-12-31 (Nº Serie: 2.958.465) es el último día correcto en Excel y en VBA. A partir de ese día Excel y VBA dejarán de funcionar... (tal y como los conocemos hoy en día).
- 1900-03-01 (Nº Serie: 61) es el primer día correcto, por lo que puede ser tratado por las funciones de fecha y hora de Excel.
- 1900-02-29 (Nº Serie: 60 en Excel; 61 en VBA) es una fecha errónea tanto en Excel como en VBA pues el año 1900 no fue bisiesto.
- 1900-01-01 (Nº Serie: 1 en Excel; 2 en VBA) es el primer día erróneo en Excel y el segundo día correcto en VBA con un número de serie positivo.
- 1899-12-31 (Nº Serie VBA: 1) es el primer número de serie positivo en VBA. En Excel no existen números de serie negativos.
- 1899-12-30 (Nº Serie VBA: 0) es el número de serie cero en VBA.
- 1899-12-29 (Nº Serie VBA: -1) es el primer número de serie negativo en VBA.
- 1752-09-14 (Nº Serie VBA: -53.797) fue el primer día Gregoriano en Inglaterra.
- 1582-12-20 (Nº Serie VBA: -115.792) fue el primer día Gregoriano en Francia.
- 1582-10-15 (Nº Serie VBA: -115.858) fue el primer día Gregoriano en España, decretado en Roma por el papa Gregorio XIII, que dio nombre al Calendario Gregoriano.
- 0100-01-01 (Nº Serie VBA: -657.434) es el primer día correcto para las funciones de fecha en VBA.
- 0099-12-31 Fecha interpretada por Excel y VBA por compatibilidad. Cuando el año se representa con 2 cifras de la 30 a la 99 se interpreta como los años 1930 a 1999.
- 0000-01-01 Fecha interpretada por Excel y VBA por compatibilidad. Cuando el año se representa con 2 cifras de la 00 a la 29 se interpreta como los años 2000 a 2029.
Con estas fechas ¿qué quiero decir?
¡Que no todo vale cuando calculamos con las funciones de fecha!
¡Tanto si hablamos de Excel como de las macros en VBA!
No solo tenemos que tener en cuenta las limitaciones de los números de serie de fechas en Excel, o el límite del año 100 en VBA, sino que debemos asegurar que las fechas se corresponden con fechas del Calendario Gregoriano según el primer día en que se decretó su uso en cada país (en la lista anterior he incluido los 3 días más importantes en que se pasó a usar el Calendario Gregoriano y se dejó de usar el Calendario Juliano.
6) Plantilla con fechas Excel y VBA
Descarga la plantilla totalmente gratuita, con las macros visibles y las hojas protegidas sin contraseña, desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive:
Fechas anteriores a 1900 PW1.xlsm
Con ella ya puedes experimentar el placer de dominar las fechas en Excel y en VBA. Puedes escribir un comentario si tienes alguna duda.
Atento a la próxima entrada donde explicaré cómo calcular la edad y el día de la semana si alguna fecha es anterior al año 1900.
Suscribirse a:
Enviar comentarios (Atom)
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...
-
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 2 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 2 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 1 semana
-
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
3 Response to "Cómo calcular fechas antes de 1900 en Excel y VBA"
Ya hemos visto que Excel es una pena para crear un árbol genealógico de los Reyes de España o de cualquier otro reino porque obliga a programar en VBA fechas anteriores a 1900.
Ismael Fanlo me ha comentado que LibreOffice Calc permite fechas menores de 1900 (y tiene en cuenta que ese año no fue bisiesto) sin necesidad de programar, con solo introducir las fechas en las celdas.
El último día del calendario juliano fue el día 1582-10-04, por lo que las fechas julianas deberán ser convertidas al Calendario Juliano. Como vivimos en días marcados por el Calendario Gregoriano, habitualmente no hará falta usar el Calendario Juliano, a no ser que seamos historiadores...
El rango de años de Calc está entre 1583 y 9957, siendo el 1582-10-15 el primer día admitido por la función DATE, lo que es de sentido común, pues es el primer día del Calendario Gregoriano.
Un punto a favor para pasarse a LibreOffice Calc para trabajar con fechas del Calendario Gregoriano, al menos hasta que Microsoft Excel arregle la limitación de años anteriores a 1900, lo que espero que sea antes de que acabe el siglo XXI.
https://help.libreoffice.org/3.3/Calc/DATE/es
hola como seria la macro para que calcule la edad sin poner la fecha final y calcule con la funcion hoy()
Hola Aprendiz59, con esta función:
Function CalcularEdad(iAño As Integer) As Integer
'
' Calcular edad desde el año 100
'
CalcularEdad = DateDiff("yyyy", DateSerial(iAño, 1, 1), Date)
End Function
Mi edad es de 64 años: CalcularEdad(1957)
Salu2
Leave A Reply
Indícame las erratas que encuentres y qué es lo que te gustaría ver en los próximos artículos.