#ExcelPedroWave

Esta entrada del blog es la segunda de la trilogía sobre el cálculo de fechas anteriores al año 1900 con funciones en VBA. La trilogía explica:

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

C) Calendario Perpetuo desde antes de 1900

En esta entrada explicaré cómo calcular la edad y el día de la semana y a restar fechas anteriores al año 1900:

1) Formatos de celdas con fechas

2) Día de la semana en Excel y en VBA

3) Calcular diferencia de fechas en días

4) Calcular la edad en años

5) Plantilla con fechas Excel y VBA

 

1) Formatos de celdas con fechas

En las celdas de Excel se permiten fechas desde el 1 de enero de 1900 aunque, como ya sabemos por el primer artículo de esta trilogía, la primera fecha válida es el 1 de marzo de 1900.

La competencia se ha adelantado a Excel, pues permite fechas con números de serie negativos en sus celdas:

• LibreOffice Calc permite fechas desde el día 15 de octubre de 1582, que es el primer día del Calendario Gregoriano.

• Google Sheets permite las fechas del Calendario Gregoriano y desde el 1 de enero de -1, que deberían ser convertidas al Calendario Juliano.

Excel obliga a usar macros en VBA para poder tratar los números de serie negativos, como variables tipo Date en VBA, como se comentó en el primer artículo de esta trilogía, y es lo que vamos a hacer a continuación.

Para saber más sobre fechas en Excel puedes leer estos dos artículos en inglés:

How to Work with Dates Before 1900 in Excel

How to calculate ages before 1/1/1900 in Excel

Pero lo mejor es seguir leyendo...

2) Día de la semana en Excel y en VBA

Ya sabemos todos que para obtener el nombre del día de la semana en Excel, a partir de una celda con una fecha, la fórmula apropiada es:

=TEXTO(fecha;"dddd")

Pero esta fórmula sólo funciona para fechas a partir del 1 de marzo de 1900.

En el archivo (a descargar en el apartado 5) he preparado un ejercicio con las fechas en Excel y VBA que me sirvió para ayudar a responder sobre este asunto en el foro de ayudaexcel.com:

Calcular el día de la semana en fechas anteriores a 1900

Un usuario necesitaba saber el día de la semana (lunes, martes, miércoles...) de fechas anteriores a 1900, para lo que preparé un archivo con 3 macros en un módulo VBA.

La función ObtenerDíaSemanaISO() devuelve el día de la semana, si se le pasa una cadena de texto en formato ISO 8601, llamando a las funciones ObtenerNúmDíaSemanaISO() y ObtenerSerieFechaISO()

Es fácil convertir una string con una fecha en formato de texto al formato estándar ISO, lo que se puede ver en la hoja 'Fechas' del archivo que se puede descargar en el apartado 5) de esta entrada.

Si la fecha está en formato fecha, o en formato fecha sin convertir a ISO 8601, se deben usar las siguientes funciones:

Para obtener el número de serie de una fecha:

=ObtenerSerieFechaVBA($A2;ESNUMERO($A2))

Para obtener el día de la semana de una fecha:

=ObtenerDíaSemanaVBA($A2;ESNUMERO($A2))

Los cálculos están en la hoja 'Día Semana':

Como el año 1900 no fue bisiesto, devuelve error para el 29 de febrero.

3) Calcular diferencia de fechas en días

Para calcular la diferencia entre dos fechas en días se puede usar una función no documentada, herencia de Lotus 123, que se puede consultar en el siguiente enlace:

Calcular la diferencia entre dos fechas

La función SIFECHA, en inglés DATEDIF, permite calcular la diferencia en días, semanas, meses y años entre dos fechas. Por ejemplo, para calcular los días:

=SIFECHA("1-01-2014";"6-05-2016";"d")

El tercer argumento indica, con la letra "d", que la diferencia está expresada en días.

La forma más fácil de obtener los días es restar las fechas:

=ENTERO(Fecha1 - Fecha2)

De cualquiera de las maneras, si intentamos calcular los días para una o dos fechas anteriores al año 1900, nos dará error de valor: #¡VALOR! pues Excel no funciona con números de serie de fecha negativos.

Para años anteriores a 1900 se deben usar macros en VBA con la función:

= DateDiff("d", dtFecha1, dtFecha2)

Y eso se ha hecho en el archivo descargable, en el módulo "ModRestarFechas1900":

Function RestarFechas(sFecha1 As String, sFecha2 As String) As Long

que usa las funciones DateValue y DateDiff para calcular el número de días entre fechas desde el 1 de enero del año 100 hasta el 31 de diciembre de 9999, como se puede ver en la hoja 'Edad':

En color de fondo naranja, las fechas con dos cifras para el año, que se tranforma en los años 1930 y 1980. En color amarillo la fecha actual con la función: =HOY()

Si "Fecha Final" está vacía, toma esa fecha como la actual.

La columna con el cálculo de la Edad se explica en el siguiente apartado.

4) Calcular la edad en años

El cálculo de la edad en años se puede hacer con la siguiente fórmula para dos celdas de Excel:

=ENTERO(FRAC.AÑO(A1;B1))

Otra fórmula es usar el tercer argumento para años "y" de la función no documentada que ya conocemos:

=SIFECHA(A1;B1;"y")

Pero seguimos teniendo el problema de que las fechas en las celdas sólo sirven para números de serie positivos de fechas. 

Para años anteriores a 1900 se deben usar macros en VBA con la función:

= DateDiff("yyyy", dtFecha1, dtFecha2)

Y eso se ha hecho en el archivo descargable, en el módulo "ModRestarFechas1900":

Function ObtenerEdad(sFechaNacimiento As String, sFechaCálculo As String) As Long

que usa las funciones DateValue y DateDiff para calcular el número de años entre fechas, desde el 1 de enero del año 100 hasta el 31 de diciembre de 9999, como se puede ver en la hoja 'Edad'.

Esta función me sirvió para ayudar en el foro de ayudaexcel.com:

Cómo restar dos fechas si alguna es anterior al año 1900

5) 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 

La voz del usuario de Excel

He votado y comentado la siguiente sugerencia "Dates prior to 1900" en Excel UserVoice para poder usar fechas con número de serie negativo en las celdas. La sugerencia lleva desde el 27 de septiembre de 2015. En 5 años Microsoft Excel no ha dicho ni pío, cuando la competencia de Google Sheets y LibreOffice Calc funcionan perfectamente con todo el Calendario Gregoriano.

¿Me ayudas a que esta sugerencia sea popular, votando también?

Microsoft Excel UserVoice: Dates prior to 1900

Actualización 2022-06-06: Esa página provoca el Error 404: Page Not Found.

Microsoft ha borrado intencionadamente UserVoice, y ha desaparecido todo el historial de sugerencias enviadas por los usuarios de Office, con lo que se han perdido todos los votos de los últimos años.

Desde hace 7 meses Microsoft ha creado un nuevo portal de Feedback (en inglés), donde compartir comentarios y ayudar a hacer mejoras y crear nuevos productos, con lo que he vuelto a escribir un comentario y votar por esta sugerencia:

Dealing with dates before 1900 · Community (microsoft.com)

Atento a la próxima entrada donde explicaré cómo hacer un calendario para meses anteriores al año 1900.