Tareas de Excel: FUNCIONES DE FECHA Y HORA

FUNCIONES DE FECHA Y HORA

1.CONTENIDO

1.1.DEFINICIÓN:

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.

Estas funciones permiten hacer cálculos y manipular los datos que son de tipo fecha y hora. Puede obtener la fecha actual, la hora actual. Está en condiciones de establecer el número de días que hay entre dos fechas. Si quiere saber a qué día de la semana corresponde una fecha determinada, por ejemplo, si es lunes o martes. Si quiere extraer solo el año o el mes de una fecha. En fin, son muchas las posibilidades que se tienen en Excel para llevar a cabo todos estos trabajos.

En cualquiera de las versiones que se posea de Excel, las funciones van a ser las mismas o van a cambiar en pequeña medida. El funcionamiento de la hoja electrónica va a ser similar, lo único que se hace es incluirle mejoras.

1.2.FUNCIONES

a. FUNCIÓN AHORA

La función AHORA en Excel devuelve la fecha y hora actuales del sistema. Esta función no tiene argumentos ya que consulta la información del computador en el momento exacto en que pulsamos la tecla Entrar.

Utilizar la función AHORA

Para poder utilizar la función AHORA en Excel es suficiente con introducir su nombre y los paréntesis de la función:

=AHORA()

De inmediato podrás observar la fecha y hora actuales en la celda:

La información tendrá un formato dd/mm/aaaa hh:mm y lo podemos comprobar haciendo clic derecho sobre la celda y seleccionando la opción Formato de celdas:

El formato predeterminado de la función AHORA muestra solamente la hora y los minutos pero si queremos mostrar también los segundos devueltos por la función debemos utilizar un formato personalizado diferente.

Mostrar segundos y milisegundos

Para hacer que el resultado de la función AHORA muestre los segundos podemos remplazar el formato personalizado de la celda:

`dd/mm/aaaa hh:mm:ss`

Con este nuevo formato la celda mostrará el segundo exacto en que fue introducida la función AHORA:

Para mostrar los milisegundos debemos modificar el formato personalizado de la celda por el siguiente:

`dd/mm/aaaa hh:mm:ss.000`

Resultado de la función AHORA actualizado

Debes estar consiente de que el resultado de la función AHORA se actualizará cada vez que Excel vuelva a calcular las fórmulas de la hoja. Por ejemplo, cuando introduzcas cualquier otra fórmula en la hoja de Excel se volverá a realizar el cálculo de la función AHORA y por lo tanteo el resultado se actualizará con la nueva fecha y hora del sistema.

Otra manera de actualizar el resultado de la función AHORA es pulsando la tecla F9. Si deseas conservar el valor original devuelto por la función AHORA, deberás copiar su valor (y no la fórmula) de manera que ya no sea actualizado.

b. FUNCIÓN HOY

La función HOY en Excel nos devuelve el número de serie de la fecha actual. Un número de serie es el número que Excel utiliza para identificar cualquier fecha a partir del 1 de enero de 1900.

CÓMO UTILIZAR LA FUNCIÓN HOY EN EXCEL

La función HOY en Excel no tiene parámetros así que para utilizarla solo tienes que colocar su nombre seguido de paréntesis en la celda donde quieres ver la fecha actual:

=HOY ()

Si el formato de la ceda donde has colocado la función HOY es General, entonces Excel cambiará el formato de la celda a Fecha y mostrará la fecha de hoy:

NÚMERO DE SERIE DE LA FUNCIÓN HOY

Para conocer el número de serie que nos ha devuelto la función HOY será suficiente con cambiar el formato de la celda a General:

En este ejemplo la fecha 20/09/2012 tiene asignado el número de serie 41172. Excel volverá a mostrar la fecha si cambiamos de nuevo el formato de la celda a Fecha.

Debes recordar que la función HOY actualizará la fecha cada vez que se actualicen las fórmulas de la hoja así que, si guardar y abres el libro de Excel el día de mañana, la función HOY regresará una nueva fecha, es decir, la fecha del día actual.

CALCULAR LA EDAD CON LA FUNCIÓN HOY

La función HOY en Excel es de utilidad para calcular la edad de una persona si conocemos el año de su nacimiento. En la siguiente fórmula utilizo la función AÑO para obtener el año de la fecha actual y posteriormente le resto el año de la fecha de nacimiento de la persona:

El cálculo de edad mostrado no toma en cuenta el día de nacimiento, pero nos da una idea clara sobre la edad de la persona. Recuerda que la función HOY en Excel siempre nos devolverá la fecha actual del sistema.

b. FUNCIÓN AÑO

La función AÑO en Excel devuelve el número de año de una fecha que sea especificada como argumento de la función. De esta manera, el único argumento de la función es la fecha de la que deseamos conocer el año.

SINTAXIS DE LA FUNCIÓN AÑO

Núm. _ de_ serie (obligatorio): La fecha de la que será devuelto el año.

La función AÑO devolverá un número entero que se encontrará en el rango 1900-9999 ya que Excel utiliza un sistema de fechas basado en el año 1900 donde la fecha más antigua es el día 1 de enero de 1900.

EJEMPLOS DE LA FUNCIÓN AÑO

Podemos especificar la fecha como una cadena de texto en el argumento de la función AÑO y obtendremos de regreso el número de año:

De igual manera podemos especificar una fecha que se encuentre en alguna otra celda y que tenga un dato de fecha.

OBTENER LA DIFERENCIA EN AÑOS

Si tienes dos fechas y deseas conocer la diferencia en años que existe entre ambas fechas podemos utilizar la función AÑO de la siguiente manera:

=AÑO (B1) – AÑO (A1)

Esta fórmula obtendrá el año de cada una de las fechas y posteriormente realizará la resta:

La función AÑO en Excel regresará siempre el año de la fecha que hayamos especificado.

C. FUNCIÓN MES

La función MES en Excel nos permite obtener el número de mes de una fecha proporcionada. La función MES devolverá un número entero entre 1, que representa el mes de enero, y 12 que representa al mes de diciembre.

SINTAXIS DE LA FUNCIÓN MES

Núm. _de _serie (obligatorio): La fecha de la cual deseamos conocer el mes.

EJEMPLOS DE LA FUNCIÓN MES

En la siguiente fórmula he especificado la fecha 15 de marzo del 2012 como argumento de la función MES:

=MES ("15/03/2012")

La función MES devolverá el número 3 que corresponde al mes de marzo. El resultado será consistente independientemente del formato que se haya utilizado para la fecha. Por ejemplo, en la siguiente imagen puedes observar que he colocado la misma fecha en la celda A1 pero con un formato de fecha diferente:

La función MES devolverá el número 3 al establecer como argumento el valor de la celda A1.

OBTENER EL MES ACTUAL

Si queremos una fórmula que siempre nos devuelva el mes actual podemos utilizar la función MES junto con la función HOY de la siguiente manera:

=MES (HOY ())

Esta fórmula regresará el número del mes actual de acuerdo a la fecha establecida en tu computador.

OBTENER EL NOMBRE DEL MES

Si deseas mostrar en una celda el nombre del mes en lugar de su número, podemos utilizar la función TEXTO dando un formato especial a una fecha. En la siguiente imagen puedes observar que en la celda B1 aplico el formato “m mmm” a la fecha de la celda A2:

A diferencia del formato que podemos dar con la función TEXTO, la función MES en Excel siempre nos devolverá el valor numérico del mes de la fecha que indiquemos como argumento. Este número será de mucha utilidad en caso de querer hacer algún cálculo entre fechas.

e. FUNCIÓN FECHA

Obtiene el número de serie que representa a la fecha especificada.

Sintaxis

FECHA(año, mes, día)

año (Obligatorio): Número que representa el año de la fecha.
mes (Obligatorio): Número que representa el mes de la fecha.
día (Obligatorio): Número que representa el día de la fecha.

Ejemplos

FECHA(2010,6,25) = 40354
FECHA(2011,3,30) = 40632

Copie los datos de ejemplo en la tabla siguiente y péguelos en la celda A1 de una hoja de cálculo nueva de Excel. Para que las fórmulas muestren los resultados, selecciónelas, presione F2 y luego ENTRAR. Si lo necesita, puede ajustar el ancho de las columnas para ver todos los datos.

AÑO	MES	DÍA
2011	1	{1}
Datos
20111125
FÓRMULA	DESCRIPCIÓN	RESULTADO
=FECHA(A2;B2;C2)	Fecha de serie para la fecha derivada mediante el uso de las celdas A2, B2 y C2 como argumentos para la función FECHA.	40544
=FECHA(AÑO(HOY());31;12)	Fecha de serie para el último día del año actual.	41274
=FECHA(IZQUIERDA(A4;4);MED(A4;5;2); DERECHA(A4;2))	Fórmula que convierte la cadena de texto de A4 (20111125) que representa una fecha en formato "AAAAMMDD" a una fecha.	25/11/2011

f. FUNCIÓN HORA

DESCRIPCIÓN DE LA FUNCIÓN HORA EN EXCEL

La función HORA en Excel extrae de una hora escrita en horas, minutos (puede o no tener segundos), solamente el valor de la hora. El resultado siempre es un número entero que va de 0 a 23 o lo que es equivalente a las 12:00 a.m. a las 11:00 p.m.

SINTAXIS

HORA (núm. _de _serie)

Núm. _ de_ serie: Es un argumento obligatorio y es el valor de hora que contiene la hora que se desea obtener. Los valores de horas pueden escribirse como cadenas de texto entre comillas (por ejemplo, “01:00 p.m.”, como números decimales (por ejemplo 0.5416, que representa la 01:00 p.m.), o bien como resultado de otras fórmulas o funciones. Recuerda que las ANOTACIONES

El sistema de fechas predeterminado de Microsoft Excel para Windows y Microsoft Excel para Macintosh es distinto.

Los valores de hora son parte de un valor de fecha y se representan mediante un número decimal (por ejemplo 0.5 representa las 12:00 p.m. porque es la mitad de un día).

OBSERVACIÓN

Los valores de hora son parte de un valor de fecha y se representan mediante un número decimal (por ejemplo, 0,5 representa las 12:00 p.m. porque es la mitad de un día).

EJEMPLO

HORA
0,75
18/7/2011 7:45
21/4/2012
FÓRMULA	DESCRIPCIÓN	RESULTADO
=HORA(A2)	Devuelve el 75% de 24 horas.	18
=HORA(A3)	Devuelve la parte correspondiente a la hora del valor de fecha y hora.	7
=HORA(A4)	Una fecha sin parte de hora especificada se considera 12:00 AM o 0 horas.	0

g. FUNCIÓN MINUTO

La función MINUTO en Excel extrae los minutos de una hora. El valor resultante se encuentra entre los números 0 a 59, que son los valores comprendidos en una hora.

Por ejemplo si tienes la hora 10:15:00 a.m. la función MINUTO regresa el valor 15, pues extrae los minutos de la hora.

SINTAXIS

MINUTO (núm._ de_ serie)

Núm. _de_ serie: Es un argumento obligatorio y es la hora que contiene el valor de minutos que necesitas extraer. Las horas pueden escribirse como cadenas de texto entre comillas (por ejemplo, “10:15 a.m.”), como números decimales (por ejemplo, 0.44, que representa las 10:30 a.m.), o bien como resultado de otras fórmulas o funciones, por ejemplo VALHORA (“10:45 a.m.”).

ANOTACIONES

El sistema de fechas predeterminado de Microsoft Excel para Windows y Microsoft Excel para
Macintosh es distinto. Los valores de hora son parte de un valor de fecha y vienen representados por un número decimal (por ejemplo 0.5 representa las 12:00 p.m. ya que es la mitad de un día).

EJEMPLO

En el área de producción estás implementando un nuevo proceso para reducir el tiempo de ensamblado de su producto estrella. Como es un nuevo proceso necesitas saber el tiempo promedio en que un empleado termina el proceso para estimar el tiempo de total de producción.

Para sacar este dato cuentas con la siguiente información:

Como puedes observar, las fechas de inicio del Proceso 1 no son iguales para todos los empleados, empezaron en diferentes momentos. Lo mismo pasa con la hora de finalización del proceso. La pregunta es en base a estos datos cómo sacamos el promedio que un empleado invierte en realizar el proceso 1.

Como primer paso, debemos saber cuántos minutos transcurrieron entre la “HORA DE INICIO” y la “HORA DE FIN” por empleado. ¿Cómo realizamos este cálculo?

Vamos a emplear la función MINUTO en Excel que nos permitirá extraer los minutos de cada hora, para después realizar una resta: HORA DE FIN – HORA DE INICIO. Con este tendremos los minutos totales que cada empleado tardó en realizar el proceso

1.Estos datos los vamos a poner en la columna E a la que llamaremos “MINUTOS TOTALES”. Empezaremos con el primer empleado. En la celda E2 vamos a escribir la función MINUTOS con los siguientes argumentos:

=MINUTO (D2)-MINUTO (C2)

Con esta fórmula, estamos diciéndole a Excel que de la “HORA FIN” 10:45:00 a.m. extraiga los minutos que son 45. Aplicamos la misma función para la “HORA DE INICIO”, en este caso 10:06:00 a.m. que nos da como resultado 6.

La operación algebraica es:

= 45 – 6

Esto da como resultado 39 que son los minutos totales que el primer empleado de la lista utilizó para terminar el proceso 1.

Aplicamos la misma fórmula para el resto de los empleados.

Una vez que ya tenemos el total de minutos, ahora sí sacamos el promedio que tarda un empleado en realizar el proceso 1. Para esto vamos a escribir la función PROMEDIO en Excel en la celda E9 con los siguientes argumentos:

=PROMEDIO (E2:E8)

Con el rango E2:E8 estamos incluyendo todos los minutos.

La tabla con todos los resultados obtenidos es:

El promedio para realizar el proceso 1 es de 38 minutos, así que ya puedes definir el tiempo total de ensamblado de tu producto estrella.

Si tienes alguna duda o comentario escríbenos.

h. FUNCIÓN DIA

La función DIA en Excel nos devuelve el número de día de una fecha y que estará expresado como un número entero entre 1 y 31. La función DIA tiene solamente un argumento que es el número de serie de la fecha a analizar.

SINTAXIS DE LA FUNCIÓN DIA

Núm. _de_ serie (obligatorio): La fecha de Excel que será analizada para extraer el número de día.

EJEMPLOS DE LA FUNCIÓN DIA

Para ejemplificar el uso de la función DIA observa la siguiente fórmula:

=DIA ("05/08/2012")

El argumento de la función indica la fecha 5 de Agosto del 2012 por lo que el resultado de la función será 5 que es el número de día. La función DIA también puede tomar como argumento una fecha que se encuentre almacenada en otra celda:

LA FUNCIÓN DIA Y LOS NÚMEROS DE SERIE

Las fechas en Excel son realmente números de serie que identifican a cada uno de los días a partir del 1 de Enero de 1900 el cual tiene asignado el número 1 y a partir de ahí la cuenta progresiva. Por ejemplo, el 5 de agosto el 2012 tiene asignado el número de serie 41126 y si pasamos dicho número como argumento de la función DIA obtendremos como resultado el número 5:

OBTENER EL NOMBRE DEL DÍA

La función DIA nos regresa solamente el número de día de la fecha proporcionada pero en ocasiones necesitamos saber el nombre del día de la semana, es decir, saber si el día corresponde a un domingo, lunes, martes, etc.

Para conocer el nombre del día de una fecha determinada podemos utilizar la función TEXTO de la siguiente manera:

La función DIA en Excel siempre nos devolverá el número de día de la fecha especificada.

i. FUNCIÓN DIALAB

La función DIAS.LAB en Excel nos ayuda a obtener el número de días laborables entre dos fechas determinadas. La función DIAS.LAB nos permite especificar un conjunto de días de vacaciones que serán excluidos de los días laborables contabilizados.

SINTAXIS DE LA FUNCIÓN DIAS.LAB

A continuación una descripción de los argumentos de la función DIAS.LAB:

Fecha inicial (obligatorio): Es la fecha a partir de la cual se comenzarán a contar los días laborables.

Fecha final (obligatorio): La fecha que marca el final de la contabilización de días laborables.

Vacaciones (opcional): Conjunto de una o varias fechas que serán excluidas del calendario de días laborables.

La función DIAS.LAB contabiliza los días laborables de lunes a viernes y excluye los fines de semana (sábado y domingo).

OBSERVACIONES

Microsoft Excel almacena las fechas como números de serie secuenciales para que se puedan usar en cálculos. De manera predeterminada, la fecha 1 de enero de 1900 es el número de serie 1 y la fecha 1 de enero de 2008 es el número de serie 39448, porque es 39.448 días posterior al 1 de enero de 1900.

Si uno de los argumentos no es una fecha válida DIA.LAB devuelve el valor de error # ¡VALOR!

Si el argumento fecha inicial más el argumento días produce una fecha no válida, DIA.LAB devuelve el valor de error # ¡NUM!

Si el argumento días no es un entero, se trunca.

EJEMPLO


1/10/2008	Fecha inicial
1:51	Días hasta la finalización
26/11/2008	Días no laborables
4/12/2008	Días no laborables
21/1/2009	Días no laborables
FÓRMULA	DESCRIPCIÓN (RESULTADO)	RESULTADO
=DIA.LAB(A2;A3)	Fecha 151 días laborables posterior a la fecha inicial (30/4/2009)	30/4/2009
=DIA.LAB(A2;A3;A4:A6)	Fecha 151 días laborables posterior a la fecha inicial, excluidos los días festivos (5/5/2009)	5/5/2009

j. FUNCIÓN FIN.MES

La función FIN.MES en Excel, regresa el número de serie del último día del mes, anterior o posterior a la fecha inicial del número de mes indicado. Por ejemplo puedes usar la función FIN.MES para calcular las fechas de vencimiento que caen en el último día del mes.

SINTAXIS

FIN.MES (fecha inicial, meses)

Fecha inicial: Es un argumento obligatorio y es una fecha que representa la fecha inicial. Las fechas deben especificarse utilizando la función FECHA o como resultado de otras fórmulas o funciones. Por ejemplo, utilice FECHA (2012, 1, 30) para el 30 de enero de 2012. Pueden producirse problemas si las fechas se escriben como texto.

Meses: Es un argumento obligatorio y representa el número de meses anteriores o posteriores al argumento fecha inicial. Cuando meses es un valor positivo, el resultado es una fecha futura, y cuando es un valor negativo, el resultado es una fecha pasada. Si el argumento meses no es un número entero, se trunca.

ANOTACIONES

Microsoft Excel almacena las fechas como números de serie secuenciales para que se puedan utilizar en cálculos. De manera predeterminada, la fecha 1 de enero de 1900 es el número de serie 1 y la fecha 1 de enero de 2008 es el número de serie 39448, porque es 39.448 días posterior al 1 de enero de 1900. Microsoft Excel para Macintosh utiliza un sistema de fechas predeterminado diferente.

Si el argumento fecha inicial no es una fecha válida, FIN.MES en Excel regresa el valor de error # ¡NUM!

Si el argumento fecha inicial más el argumento meses da como resultado una fecha que no es válida, FIN.MES en Excel regresa el valor de error # ¡NUM!

Para ver el número como una fecha, seleccione la celda y, en la pestaña Inicio, en el grupo Número, haga clic en la flecha que aparece junto a Formato de número; a continuación, haga clic en Fecha corta o Fecha largas.

OBSERVACIONES

Si el argumento fecha inicial no es una fecha válida, FIN.MES devuelve el valor de error #¡NUM!

Si la suma de los argumentos fecha inicial y meses da como resultado una fecha que no es válida, FIN.MES devuelve el valor de error # ¡NUM!

EJEMPLO DE LA FUNCIÓN FIN.MES

FECHA
1-Ene-11
FÓRMULA	DESCRIPCIÓN	RESULTADO
=FIN.MES(A2;1)	Fecha del último día del mes, un mes después de la fecha de A2.	28/2/2011
=FIN.MES(A2;-3)	Fecha del último día del mes, tres meses antes de la fecha de A2.	31/10/2010

2.RESUMEN

Estas funciones son importantes para realizar cálculos de fechas son muy útiles al calcular fechas largas. Estas funciones nos ayudaran a obtener un resultado de manera más rápida y eficaz.

Aquí las más resultantes de los que ya explicamos anteriormente: FECHA

Estas funciones permiten hacer cálculos y manipular los datos que son de tipo fecha y hora. Puede obtener la fecha actual, la hora actual. Está en condiciones de establecer el número de días que hay entre dos fechas. Si quiere saber a qué día de la semana corresponde una fecha determinada, por ejemplo, si es lunes o martes. Si quiere extraer solo el año o el mes de una fecha. En fin, son muchas las posibilidades que se tienen en Excel para llevar a cabo todos estos trabajos.

3.RECOMENDACIONES

Tener mucho cuidado a la hora de colocar los datos por que el mínimo error puede generar errores grandes.

Se debe tener en cuenta que datos se desea calcular para usar la función adecuada.

A la hora de desarrollar un ejercicio debemos elegir la función correcta.

4.CONCLUSIONES

- Las funciones ya antes mencionadas son de mucha importancia y productividad para cualquier persona al momento de calcular enormes base de datos de fechas, estas funciones le serán de mucha ayuda.

- Reducen procedimientos extensos ahorrando tiempo y trabajo.