En profundidad: Guía de uso de fechas horas en Excel

Las fechas y las horas son dos de los tipos de datos más comunes con los que solemos trabajar a diario con Excel, pero también son, seguramente, los más complicados, sobre todo si no tienes mucha experiencia, ¿es tu caso?

Estas dificultades se dan normalmente porque Excel utiliza números de serie para representar tanto fechas como horas, en vez de utilizar números para los días, meses, años, horas, minutos o segundos. Almacenar estos datos como números de serie es una ventaja, ya que además de fechas u horas, se guardan otros datos como los días de la semana.

En esta guía te mostraré en profundidad la mejor forma de trabajar con fechas y horas.

Comenzamos…

¿Cómo se guardan las fechas?

Como decía al principio, el origen de la mayoría de los problemas al trabajar con fechas viene dado por la forma de guardar los datos. Seguro que piensas que es mucho más fácil recordar un día y un mes que un número de serie que no sabes ni de qué año puede ser. Pues si piensas así, tienes razón, pero como te mostraré ahora, un número de serie es bastante más eficiente para hacer determinados cálculos, que un conjunto de días, meses y años.

Los números de serie que guarda Excel representan el número de días que han transcurrido desde el 1 de enero de 1900. De esta forma, el 01/01/1900 es el número 1, el 02/01/1900 es el número 2, y así hasta el día de hoy. Por ejemplo, el 27/02/2016 se representa con el número 42427.

Hay dos cosas que debes tener en cuenta:

• Excel no reconoce ninguna fecha anterior a 1900, por lo que no podrás hacer cálculos con esas fechas de la misma forma en que lo harías con fechas posteriores.
• No existen números de serie negativos para representar fechas.

Insisto en que, aunque te parezca bastante lioso, es mucho más fácil para hacer cálculos. Si quieres contar una semana a partir del 2 de marzo de 2016, sólo tienes que sumar 7 al número 42431.

¿Cómo se guardan las horas?

De una forma parecida se guardan las horas. Si los días se representan con números enteros, las horas son las partes decimales de cada número entero. Un día comienza a las 00.00 y termina a las 23.59. Como un día tiene 24 horas, cada hora se representa dividiendo el día entre 24 (1/24), es decir, que cada hora tiene el valor de 0,0416666…

Por ejemplo, las 9.00 h. (09 am.) del 27 de febrero de 2016, se representan como 42427,375.

Cuando escribes una hora sin una fecha definida, Excel lo almacena como si fuese el 0 de enero de 1900, es decir, que las 15.00 h. se guarda como 0,625. Es bueno saber esto, ya que si intentas restarle 6 horas a la hora 03:00, el resultado será negativo (-0,125) y dará error, mostrando #############.

Los minutos y los segundos funcionan exactamente igual que las horas.

Un segundo es 1/60 de un minuto, que es 1/60 de una hora, que es 1/24 de un día… o si lo prefieres, un segundo es 1/86400 de un día, lo que equivale a 0,00001157407….

Cómo trabajar con fechas y horas

Las funciones FECHA y HORA

Como los números de serie no son nada intuitivos de utilizar, Excel cuenta con un par de funciones para facilitar el uso de fechas y horas.

=FECHA(año;mes;día)
=HORA(horas;minutos;segundos)
En ambas funciones debes especificar los años, meses y días y las horas, los minutos y los segundos en formato de número Por ejemplo, el 27 de febrero de 2016  sería =FECHA(2016;2;27).

Al introducir esta fórmula, el valor que se almacena internamente es 42427, que es igual a las 00.00 del 27 de febrero de 2016.

En el caso de las horas, por ejemplo, las 18.00 h. podría introducirse como =HORA(18;0;0). Si introduces esta fórmula, se guardará el valor 0,75 o lo que es igual, las 10.00 h del 0 de enero de 1900.

Si quieres utilizar una fecha y una hora en concreto puedes agregar las dos funciones juntas. Por ejemplo, las 18.00 h. del 27 de febrero de 2016 habría que introducirlo de la siguiente manera:

=FECHA(2016;2;27) + HORA(18;0;0)

El resultado sería 42427,75, que es exactamente la fecha que has introducido.

Otras funciones para declarar fechas

Excel tiene también otras funciones que sirven para introducir fechas:

HOY()

La función HOY devuelve siempre el número de serie de la fecha actual. Si la introduces así:

=HOY()

Devolverá el día en el que introduces la función.

AHORA()

La función AHORA devuelve siempre la fecha actual más la hora actual. Se introduce así:

=AHORA()

Al introducir esta función, el resultado será el número de serie que representa la fecha + la hora exacta (hasta los segundos)

FECHA.MES() y FIN.MES()

La función FECHA.MES devuelve el número de serie de una fecha dada, más o menos el número de meses indicado.

El número de meses puede ir hacia adelante o hacia atrás en el tiempo:

=FECHA.MES(fecha_inicial;meses)

FIN.MES devuelve el último día del mes después de sumar o restar un número de meses a una fecha determinada. Es similar a FECHA.MES:

=FIN.MES(fecha_inicial;meses)

• Fecha_inicial puede ser cualquier referencia de celda que contenga una fecha o número de serie.
• Meses puede ser cualquier número. En caso de introducir números decimales, Excel sólo utiliza la parte entera (si se introduce 3,8, sólo se tendrá en cuenta el 3).

Te pongo un ejemplo práctico. Observa la imagen:

En la celda A1 he introducido una fecha. En la columna C he introducido el número de meses que se deben contar desde la fecha. En las columnas D y E he utilizado las funciones que figuran en los encabezados. Como puedes ver, Excel devuelve el número de serie correspondiente a las fechas calculadas. Para visualizar correctamente las fechas, selecciona el rango D2:E7 y pulsa Ctrl + 1. Cuando se muestre el cuadro de diálogo Formato de número, selecciona Fecha y pulsa en Aceptar.

Fíjate ahora en las fechas de las columnas D y E. Te darás cuenta enseguida de cómo funcionan.

En la cela D2 he introducido =FECHA.MES($A$2;$C2) y en E2 =FIN.MES($A$2;$C2).

En ambos casos, y en los restantes, se devuelve la hora 0.00 h.

DIA.LAB()

Sirve principalmente para contar los días laborables (de lunes a viernes) que transcurren desde una fecha dada hasta un número de días determinado. En esta cuenta se omiten los días festivos que le indiques. Su sintaxis es la siguiente:

=DIA.LAB(fecha_inicial;días;[festivos])

• Fecha_inicial: es la fecha desde la que se comenzarán a contar los días, ya sea hacia adelante o hacia atrás.
• Días: Es el número de días a sumar o a restar desde la fecha_inicial (hacia adelante número positivo y hacia atrás número negativo).
• [festivos]: Aunque es un argumento opcional, es muy útil, ya que permite ignorar los días festivos (Año Nuevo, Navidad, etc.). Si no se especifica este argumento y algún día festivo cae entre semana, la función DIA.LAB lo considerará laborable. Para especificar este argumento te aconsejo que escribas antes en un rango de celdas los días festivos y al escribir la función las selecciones.

Por ejemplo, para averiguar qué fecha era 12 días laborables antes del 3 de enero de 2016, puedes utilizar esta fórmula:

DIA.LAB.INTL()

Esta función es parecida a la anterior. La diferencia está en que con esta puedes establecer fines de semana personalizados. Su sintaxis es:

=DIA.LAB.INTL(fecha_inicial;días;[fines_de_semana];[festivos])

• Fecha_inicio, días y festivos funcionan de la misma forma que la función DIA.LAB.
• Fin_de_semana sirve para establecer fines de semana personalizados. Observa las opciones que puedes elegir:

Algunas formas de mostrar fechas

Ahora ya sabes cómo definir una fecha, pero también debes ser capaz de trabajar con ellas. También sabes que los números de serie no son demasiado “amigables” para trabajar con fechas y horas, así que Excel cuenta con una serie de funciones para extraer cada uno de los datos que componen estos valores:

=DIA(núm_de_serie)

=MES(núm_de_serie)

=AÑO(núm_de_serie)

• El num_de_serie de cada función puede ser cualquier referencia a una celda con formato de celda. Por ejemplo,

Algunas formas de mostrar horas

Para mostrar horas el proceso es exactamente igual que para fechas. Obviamente, cambian las funciones a utilizar. La sintaxis es idéntica:

=HORA(núm_de_serie)

=MINUTO(núm_de_serie)

=SEGUNDO(núm_de_serie)

• En este caso, el núm_de_serie puede ser cualquier hora o referencia a una celda con formato de hora. Por ejemplo, si en la celda A1 escribes 06:15:30 pm, del 27/02/16, puedes utilizar cada una de las fórmulas anteriores. Observa la imagen:

 

Algunos otros datos para mostrar

Los días, meses, años, horas, minutos y segundos pueden considerarse como datos “directos”. Trabajar con números de serie, le ofrece a Excel guardar otro tipo de información “indirecta” como el día de la semana o la semana del año a la que corresponde una fecha. Excel cuenta con dos funciones específicas para esto:

=DIASEM(núm_de_serie;[tipo])

=NUM.DE.SEMANA(núm_de_serie;[tipo])

• El núm_de_serie de ambas funciones puede ser una referencia a una celda con formato de fecha. El tipo es opcional, aunque debes tener que si no lo introduces, Excel asume que la semana comienza en domingo.
• El tipo te permite especificar un comienzo de semana diferente. Si introduces lunes como comienzo de semana, la cuenta comenzará el lunes y terminará en domingo.

Las opciones para DIASEM son las siguientes:

Las opciones 2 y 11 son exactamente la misma pero se mantienen las dos por compatibilidad con versiones anteriores de Excel.

El argumento tipo de NUM.DE.SEMANA tiene las siguientes opciones:

 

Contar y otras operaciones con fechas

Como te dije más arriba, las fechas se pueden sumar y restar como números normales, ya que lo que se almacena son números de serie. Eso te permite contar los días entre dos fechas diferentes aunque, a veces es necesario establecer algunas condiciones.

DIAS.LAB()

Antes te mostré el funcionamiento de DIA.LAB, que te permite avanzar y retroceder un número determinado de días laborables, es decir, sin contar los fines de semana y los días festivos, para devolver una fecha. Ahora lo que quieres hallar son los días comprendidos entre las dos fechas, así que hay que utilizar la función DIAS.LAB. La sintaxis es la siguiente:

=DIAS.LAB(fecha_inicial;fecha_final;[festivos])

• La fecha_inicial y la fecha_final pueden ser cualquier número de serie o referencia de celda con formato de fecha.
• El argumento festivos no es obligatorio, pero te permite omitir días específicos (días no laborables) Para especificar varios días festivos te aconsejo utilizar un rango de celdas que contentan esas fechas.

Te pongo un ejemplo: si A2 contiene la fecha 27/04/16 y B2 contiene 12/05/16, puedes utilizar DIAS.LAB para calcular los días laborables entre las dos.

Además de calcular los días laborables puedes excluir el día 01/05/16, que es el Día del Trabajo, fecha que se encuentra en C2:

 

DIAS.LAB.INTL()

Además de DIAS.LAB puedes contar también con la función DIAS.LAB.INTL que, además de contar los días laborables entre dos fechas, puedes establecer fines de semana personalizados. Observa la sintaxis:

=DIAS.LAB.INTL(fecha_inicial;fecha_final;[fin_de_semana];[festivos])

• La fecha_inicial y la fecha_final funcionan igual que en DIA.LAB y DIAS.LAB.
• El fin_de_semana tiene las siguientes opciones:

FRAC.AÑO()

Es una útil función que da como resultado la diferencia entre dos fechas, expresado en años, redondeando hacia abajo. La sintaxis es la siguiente:

=FRAC.AÑO(fecha_inicial;fecha_final;[base])

• Como en funciones anteriores, fecha_inicial y fecha_final pueden ser un número de serie o referencia a una celda con formato de fecha.
• El argumento base es opcional. Permite especificar las “reglas” sobre cómo calcular la diferencia entre las fechas. Casi todas las veces utilizarás la opción 1, pero te muestro la lista completa:

Te pongo un ejemplo: Si la celda A2 contiene la fecha 27/02/16 y B2 contiene 15/12/16, puedes utilizar FRAC.AÑO para hallar los años (con decimales) que han transcurrido entre las dos fechas:

=FRAC.AÑO(A2;B2;1)

 

SIFECHA (función oculta)

Si FRAC.AÑO devuelve la diferencia en años entre dos fechas, SIFECHA llega más allá. Es capaz de devolver la diferencia entre dos fechas en la unidad de medida que quieras (en una o dos unidades a la vez). Esta es su sintaxis:

=SIFECHA(fecha_inicial;fecha_final;unidad)

• Como siempre, fecha_inicial y fecha_final pueden ser números de serie o referencias a celdas con formato de número.
• En el argumento unidad debes especificar una cadena de texto que represente el tipo de salida que desees. Aunque parece complicado, no lo es en absoluto. Esta unidad debe ir encerrada entre comillas (“”).

Por ejemplo, si A2 contiene la fecha 10/09/16 y B2 tiene 02/12/17, puedes utilizar SIFECHA para hallar el número de años que hay entre las dos:

=SIFECHA(A2;B2;”Y”)

Verás que el resultado es 1.

Te muestro todas las combinaciones de unidades en las que puedes expresar el resultado de la función:

Atención: En todas las funciones de Excel, en que se pueden introducir como argumento formato de fechas, el parámetro del año está traducido excepto en SIFECHA. Por ejemplo, en la función TEXTO puedes emplear la letra “a” para referirte a los años. En la función SIFECHA debes utilizar la letra “y” para que no devuelva un error.

Recuerda que todas las unidades deben ir entre comillas.

 

Funciones de conversión de fechas

Todas las funciones anteriores funcionan perfectamente con números de serie y con fechas introducidas a mano. Por desgracia, muchas fechas y horas se importan desde otros sistemas informáticos o bases de datos, en forma de texto. Casi todas las funciones anteriores son lo suficientemente “inteligentes” para convertir estas fechas sobre la marcha, pero otras veces Excel es incapaz de reconocer fechas y hay que ayudarle un poco. Existen dos funciones que pueden facilitarte la labor:

=FECHAUMERO(texto_de_fecha)

=HORANUMERO(texto_de_hora)

• El texto_de_fecha y el texto_de_hora aceptan cualquier cadena de texto que se parezca a una fecha o a una hora.

Estos son algunos valores de los que acepta como argumento la función FECHANUMERO:

HORANUMERO también tiene su propia tabla de valores que acepta (y alguno que no):

 

Cómo cambiar el formato de fechas u horas con funciones

Las dos funciones anteriores son muy útiles para convertir un texto en una fecha o en una hora, pero también puedes hacer lo contrario. Excel cuenta con una función para convertir una fecha u hora en una cadena de texto. Te muestro su sintaxis:

=TEXTO (valor;formato_de_texto)

• Como argumento valor puedes introducir cualquier valor de fecha u hora o referencia a una celda que contenga estos valores.
• Como formato_de_texto, tienes muchas opciones para elegir. Te las muestro en esta tabla:

Puedes convertir en texto cualquier elemento de la fecha o la hora o también puedes hacer una combinación de dos o más elementos. Si utilizo como ejemplo el valor 27/02/16 05:07:03 am, estas son algunas de las posibles combinaciones que podrías extraer:

Ten cuidado al compartir libros de Excel que contengan la función TEXTO con usuarios que utilizan otros idiomas en el ordenador, porque el argumento formato_de_texto no se traduce. Mi equipo está en español y utilizo el elemento aaaa para designar un año de 4 cifras (2016). Al enviar el libro a un usuario que utiliza Excel en inglés, la función le dará error porque para él, el año se designaría como yyyy.

 

Resumen

Como has podido ver, Excel es muy flexible a la hora de gestionar fechas y horas. Cualquier operación que tengas que hacer con este tipo de datos, seguro que la puedes hacer sin ninguna dificultad.

Comenta si has aprendido algo nuevo con este artículo.