Cálculos con Fechas y Horas

En Excel los datos de tipo fecha son almacenados internamente como números de serie que representan la cantidad de días transcurridos desde el 01/01/1900.

25-Jun-2016

En Excel los datos de tipo fecha son almacenados internamente como números de serie que representan la cantidad de días transcurridos desde el 01/01/1900, es decir para Excel la fecha 01/02/1900 sería el nº de serie 32 (31 días de enero + 1 día de febrero). Debido a esta característica los cálculos que realicemos entre fechas siempre devolverán el intervalo de días entre esas dos fechas.

Atendiendo a la característica descrita en el apartado anterior, si deseamos realizar cálculos o conversiones entre fechas tendremos que realizar estos cálculos pensando en días.

Cálculos con FECHAS

CALCULAR DIAS ENTRE DOS FECHAS

Para realizar este cálculo sólo tendremos que restar las dos fechas.

DIAS -> Fecha2 – Fecha1

Nota: si el resultado nos lo muestra como otra fecha, le aplicaremos un formato de número para verlo como un valor numérico.

CALCULAR HORAS ENTRE DOS FECHAS

Para realizar este cálculo tendremos que restar las dos fechas (obtenemos días) y el resultado multiplicarlo por las 24 horas de un día.

HORAS -> (Fecha2 – Fecha1) * 24

CALCULAR AÑOS ENTRE DOS FECHAS

La resta de dos fechas, dividido entre 365,25 días de un año devolverá los años entre esas dos fechas. Como este cálculo nos dará un resultado con decimales, tendremos que despreciarlos en el cálculo utilizando la función ENTERO.

AÑOS -> ENTERO((Fecha2-Fecha1)/365,25)

CALCULAR MESES ENTRE DOS FECHAS

Para realizar este cálculo tendremos que restar las dos fechas (obtenemos días) y el resultado dividirlo por la cantidad de días de un mes. Para este cálculo utilizamos el valor aproximado de 30,437 días del mes, este valor lo obtenemos del promedio de días mensuales durante un periodo de 4 años.

MESES -> ENTERO((Fecha2-Fecha1)/30,437)

La función SIFECHA

Función mágica para el cálculo de fechas

Para realizar cálculos con funciones, utilizaremos la función SIFECHA.

La función SIFECHA es una función de Excel a la cual no tenemos acceso desde el catálogo de funciones y sin embargo ha estado presente en las últimas 7 versiones del programa incluyendo la versión 2010 de Excel.

Esta función nos ayuda a obtener la diferencia entre dos fechas y además nos permitirá elegir el tipo de dato que deseamos como resultado y que pueden ser días, meses o años. La sintaxis de la función SIFECHA es la siguiente:

=SIFECHA(fecha1, fecha2, tipo)

El primero y segundo argumentos son fechas en donde la fecha1 tiene que ser menor que fecha2. Dicho de otra manera, la fecha2 debe ser la fecha más reciente. Si estos parámetros se especifican al revés, entonces obtendremos un error del tipo #¡NUM!

El tercer argumentos de la función SIFECHA es de suma importancia porque es donde especificaremos la unidad de medición de tiempo que deseamos utilizar. A continuación la tabla de posibles valores para este tercer argumento:

Valor Significado Descripción
"d" Días Número de días entre la fecha1 y la fecha2
"m" Meses Número de meses entre la fecha1 y la fecha2
"y" Años Número de días años la fecha1 y la fecha2
"ym" Resto de meses Número de meses sobrantes después de realizar la diferencia en años entre la fecha1 y la fecha2
"md" Resto de días Número de días sobrantes después de realizar la diferencia en meses entre la fecha1 y la fecha2

El uso de la función SIFECHA es de gran utilidad especialmente para obtener las diferencias en meses o años entre dos fechas ya que dicho cálculo requiere de mucha pericia para ejecutarlo correctamente debido a las diferencias en el número de días que tiene cada mes del año.

Ejemplos:

A continuación muestro un ejemplo utilizando la función SIFECHA:

Función SIFECHA

El mismo ejemplo, comparando los cálculos realizados por medio de fórmula con los que utilizan la función SIFECHA.

Cálculos con formulas y funciones

Cálculos con Horas

Partes proporcionales de un día

En Excel las horas son tratadas como la parte proporcional de un día en formato de 24h y se representan internamente como un número entre 0 y 1, siendo el valor cero las 00:00:00 y el 1 las 23:59:59.

Según este criterio de representación interna de las horas el valor 0 representa las 00:00 (12 de la noche), el valor 0,25 representa las 06:00, el valor 0,5 representa las 12:00 (12 del mediodía), el valor 0,75 representa las 18:00.

Un día de 24h

00:00   06:00   12:00   18:00   24:00

Porción de la unidad que representa

0   0,25   0,5   0,75   1

Cuando realizamos cálculos con horas obtendremos un valor entre 0 y 1 que representa el intervalo de tiempo transcurrido entre ambas horas.

TIEMPO TRANSCURRIDO ENTRE DOS HORAS

Horas -> (hora2-hora1)*24

PASAR HORAS A MINUTOS

Minutos -> hora*24*60 (obtenemos el valor de la hora y lo pasamos a minutos)

PASAR MINUTOS A DÍAS

Días -> ENTERO(TotalMinutos/60/24)

PASAR MINUTOS A HORAS

Horas -> ENTERO(TotalMinutos/60), el resto de los minutos sobrantes en el cálculo los obtendremos de la siguiente manera -> RESIDUO(TotalMinutos;60)

Cálculos con Fechas y Horas

En los apartados anteriores hemos visto como trabajar con datos que sólo representan fechas o sólo representan horas. Cuando el dato representa una fecha y hora los cálculos que tenemos que realizar hay que condicionarlos a que se tengan en cuenta las dos partes del dato la fecha y la hora.

Como ya hemos comentado al principio del módulo, los cálculos con fechas siempre se realizan utilizando como unidad de medida de tiempo el día. Según esta regla sí el 1 representa un unidad de tiempo de tipo día, si quisiéramos trabajar con unidades de tiempo que representen horas dividiríamos el 1 entre las 24 horas que lo componen, y si quisiéramos trabajar con unidades de tiempo que representen minutos dividiríamos el 1 entre los 1440 minutos que lo componen.

TABLA DE UNIDADES PARA EL CÁLCULO CON DATOS DE TIPO FECHA/HORA

UNIDAD DE TIEMPO UNIDAD DE CÁLCULO SE CALCULA
1 Día 1  
1 Hora 0,041666667 UNIDAD DÍA / 24
1 Minuto 0,000694444 UNIDAD HORA / 60

CÁLCULO DE FECHAS/HORAS FUTURAS

Obtener Fecha/Hora sumando Días, Horas y Minutos

Si deseamos obtener una fecha/hora a partir de otra fecha/hora sumándole unidades de día, hora y minuto, tendremos que sumar a la fecha/hora inicial el total de días por su unidad de cálculo, más el total de horas por su unidad de cálculo, más el total de minutos por su unidad de cálculo.

Nueva fecha/hora = fecha/hora inicial + días*und.día + horas*und.hora + minutos*und.minutos

Supongamos que disponemos las fechas horas de salida de una serie de mercancías de un almacén y deseamos obtener la fecha hora prevista de llegada de la mercancía al destino conociendo los días, horas y minutos que dura el trayecto.

Obtener fecha a partir de los días, horas y minutos.

Como se puede observar en la fórmula escrita en la celda F12, a la fecha fecha/hora de salida se le han ido sumando el producto de los días, horas y minutos de la duración del trayecto por sus unidades de tiempo correspondientes.

Obtener Fecha/Hora sumando Horas y Minutos

Utilizando como ejemplo el mismo caso del apartado anterior, obtendremos las nuevas fechas/horas de llegada a partir de horas y minutos de trayecto.

Obtener fecha a partir de las horas y minutos.

Como se puede observar en la fórmula escrita en la celda E12, para este cálculo sólo necesitaremos sumarle a la fecha/hora salida el producto de las horas y minutos de la duración del trayecto por sus unidades de tiempo correspondientes.

Obtener Fecha/Hora sumando Minutos

Utilizando como ejemplo el mismo caso de los apartados anteriores, deseamos obtener las nuevas fechas/horas de llegada a partir de los minutos de trayecto.

Obtener fecha a partir de los minutos.

En este último caso sumamos a la fecha/hora de salida el total de minutos por su unidad de tiempo como se puede comprobar en la fórmula escrita en la celda D12.

OBTENER DESGLOSADOS LOS DÍAS, HORAS Y MINUTOS ENTRE DOS DATOS DE TIPO FECHA/HORA

Vamos a ver el caso inverso a los anteriores, donde a partir de dos datos de tipo fecha/hora obtendremos el total de horas, minutos y segundos transcurridos entre ambas fechas.

Obtener los días

Se calculan con la diferencia entre las dos fechas y despreciando la parte decimal que representarían horas y minutos.

Dias = ENTERO(Fecha2-Fecha1)

Obtener las horas

Se calculan sobre los decimales obtenidos en el cálculo de los días, multiplicándolos por 24 y quedándose con la parte entera del cálculo.

Horas= ENTERO(RESIDUO(Fecha2-Fecha1;1)*24)

Obtener los minutos

Se calculan sobre los decimales obtenidos en el cálculo de las horas, multiplicándolos por 60 y redondeando el resultado a 0 decimales.

Minutos= REDONDEAR(RESIDUO((Fecha2-Fecha1)*24;1)*60);0)

Veamos un ejemplo en el que a partir de las horas de salida y llegada de unos transportes, deseamos obtener la duración del trayecto en días horas y minutos:

Obtener días, horas y minutos entre dos fechas.

Para calcular los días del primer transporte la fórmula utilizada sería:

Cálculo para obtener los días

Para calcular las horas del primer transporte la fórmula utilizada sería:

Cálculo para obtener las horas

Para calcular los minutos del primer transporte la fórmula utilizada sería:

Cálculo para obtener los minutos