🔝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:
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:
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.
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
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.