Funciones de fecha y hora en Excel

Las funciones de fecha y hora de Microsoft Excel son utilizadas para buscar fechas específicas, para conocer la hora actual, para encontrar la diferencia en días laborales entre dos fechas y muchas cosas más que serán de gran utilidad al momento de estar trabajando con este tipo de datos.

Calcular la edad con Excel

Calcular la edad con Excel puede llegar a ser un poco complicado porque los cálculos no solo dependen del año sino también del día actual. La solución se complica cuando te das cuenta que es necesario considerar los años bisiestos. En esta ocasión presentaré tres maneras diferentes de calcular la edad con Excel. Todas las fórmulas asumen que la celda A2 contiene la fecha nacimiento y que la celda B2 tiene la fecha actual que es calculada por la función HOY.

Calcular la edad dividiendo los días

El primer método obtiene la diferencia en días entre ambas fechas y el resultado lo divide entre 365.25. Este último número es porque cada cuatro años tenemos un año con 366 días (bisiesto) por lo que 365.25 es el promedio de los cuatro años. La función ENTERO eliminará los decimales del resultado.

La desventaja de este método es que no es muy exacto y tiene problemas serios cuando intentas calcular la edad con Excel de niños. Por ejemplo, para un niño que tenga exactamente 1 año de edad cumplido obtendremos el resultado 0 (cero) por haber realizado la división entre 365.25.

Calcular la edad con la función FRAC.AÑO

La función FRAC.AÑO devuelve la fracción de un año a partir del número total de días que existen entre dos fechas. Observa el resultado:

De igual manera he utilizado la función ENTERO para remover los decimales del resultado.

Calcular la edad con la función SIFECHA

La función SIFECHA es el método más exacto para calcular la edad con Excel. Para obtener los años de diferencia entre dos fechas utilizamos el argumento “y” en la función:

Así que solamente elige el método que te parezca más adecuado para calcular la edad con Excel e impleméntalo.

Determinar una fecha de vencimiento en Excel

En esta ocasión hablaremos sobre las fechas de vencimiento en Excel y para eso utilizaremos como ejemplo un rango de datos donde la primera columna contiene la fecha de inicio de un contrato y la segunda columna contiene su duración. Ahora necesito una tercera columna que me permita calcular la fecha de vencimiento del contrato.

Duración en días

Si la columna de duración está en días, entonces el cálculo es muy sencillo porque solamente necesito sumar ambas columnas para obtener la fecha de vencimiento.

Duración en meses

Por el contrario, una duración en meses nos obliga a utilizar algunas funciones de Excel para obtener el resultado correcto. La primera función que utilizaremos será la función FECHA, la cual nos ayuda a obtener una fecha en base a un número de año, mes y día. Considera la siguiente fórmula:

=FECHA(2011, 12, 14)

Con esta fórmula obtendremos la fecha para el 14 de diciembre del 2011. Esta función será clave para calcular la fecha de vencimiento de nuestro ejemplo porque es precisamente el segundo parámetro de la función (los meses) donde haremos la suma de la duración del contrato para obtener la fecha de vencimiento correcta, pero iremos paso por paso.

El primer argumento de la función FECHA es el año por lo que utilizaré la función AÑO para extraer el año de la fecha de inicio de la siguiente manera:

El siguiente paso en este procedimiento será extraer el mes de la fecha de inicio utilizando la función MES y además sumarle el número de meses de la duración. Esto hará que Excel sume los meses necesarios que necesitamos para llegar a la fecha de vencimiento

El paso final será extraer los días de la fecha de inicio con la función DIA.

Excel hará los cálculos adecuados por nosotros y nos devolverá la fecha de vencimiento al transcurrir el número de meses especificado.

Es importante notar que la función FECHA ajusta automáticamente el año en caso de que sea necesario. En nuestro ejemplo, al sumar 2 meses a la fecha 30 de diciembre del 2011 forzosamente tendría que dar como resultado una fecha en el año 2012 y Excel ha realizado el cálculo correctamente.

Duración en años

En caso de que tuviera la duración del contrato en años, entonces debo hacer un procedimiento similar al anterior solo que la suma de la duración se debe incluir en el primer argumento de la función FECHA que es el parámetro para el año, por ejemplo:
=FECHA(AÑO(A2) + B2, MES(A2), DIA(A2))

Fechas de vencimiento en Excel con formato condicional

Es común tener un listado en Excel con las facturas que deben ser pagadas en una fecha próxima, así que seria de gran utilidad tener algún método para resaltar automáticamente aquellas facturas cuya fecha de vencimiento está próxima.

En este ejemplo te mostraré como utilizar el formato condicional para resaltar con un color específico aquellas celdas que tienen una fecha de vencimiento cercana. Supongamos la siguiente tabla de datos.

En la celda B2 he colocado la fecha de hoy y en la columna Vencimiento podrás observar la fecha límite que tiene cada factura para ser pagada. Para poder identificar fácilmente aquellas facturas que están próximas a vencer se han decidido implementar las siguientes reglas:

• Las facturas que tengan 14 o menos días para su vencimiento serán marcadas en color amarillo.
• Las facturas que tengan menos de 7 días para su vencimiento serán marcadas en color rojo.

El resto de las facturas permanecerá sin color pero su estatus deberá actualizarse de manera automática conforme avancen los días.

Creación de reglas de formato condicional

El primer paso será crear las reglas de formato que nos darán la solución. Comenzaremos de lo general a lo particular por lo que la primera regla a crear será la de marcar en color amarillo las celdas que tengan menos de 14 días para su vencimiento. Para iniciar debo seleccionar el rango de celdas que contiene las fechas (C4:C13) y posteriormente, en la ficha Inicio, pulsar el botón Formato condicional y seleccionar la opción Nueva regla. Se mostrará el cuadro de diálogo Nueva regla de formato:

Una vez mostrado el cuadro de diálogo haremos lo siguiente:

• Selecciona la opción Utilice una fórmula que determine las celdas para aplicar formato.
• Pulsa el botón Formato y selecciona en la pestaña relleno el color amarillo.
• Finalmente colocaremos la fórmula que hará la comparación entre la fecha de hoy la fecha de vencimiento para saber si la diferencia es menor a 14. La fórmula utilizada es la siguiente: =(C4 – $B$1) <= 14

La fórmula que hemos creado obtiene la diferencia entre la fecha de vencimiento y la fecha de hoy que se encuentra en la celda B1. Observa el resultado al pulsar el botón Aceptar.

Aquellas fechas que tienen 14 o menos días para su vencimiento se han marcado con color amarillo. Para crear la segunda regla seguimos un procedimiento similar. Seleccionamos el rango de nuestra tabla que contiene las fechas y volvemos a crear una nueva regla, solo que esta vez indicaremos un color de relleno diferente y una fórmula diferente:

La fórmula utilizada en esta nueva regla es la siguiente: =(C4 – $B$1) < 7. Al pulsar el botón Aceptar veremos reflejados los cambios bajo la columna Vencimiento.

Las celdas con menos de 7 días para su vencimiento están ahora marcadas en color rojo de la manera exacta en que lo necesitamos.

Comprobar reglas de formato condicional

Para comprobar que las reglas de formato condicional funcionarán correctamente conforme pase el tiempo será suficiente con cambiar la fecha de la celda B1. En la siguiente animación podrás observar cómo se marcarán automáticamente nuevas celdas en color amarillo al reducirse la distancia entre la fecha de hoy y la fecha de vencimiento.

Conforme la fecha de vencimiento se acerque, las reglas de formato condicional aplicarán el color adecuado a las celdas. Una mejora definitiva para este ejemplo es utilizar la función HOY en la celda B1 de manera que el formato de las celdas se actualice automáticamente todos los días.

Si quieres conocer otros artículos parecidos a Funciones de fecha y hora en Excel puedes visitar la categoría .