Fecha y hora en excel

INTRODUCCIÓN

En Excel, se encuentran distintas formulas para ser aplicadas dependiendo de la problemática. Aunque se pueden llegar a obviar, las funciones Fecha y Hora de Excel son muy útiles, ya sea para el calculo de valores a partir de la fecha o la hora actual, o como un requisito para un seguimiento de algún proceso en particular. El presente trabajo solo se enfocara en ambas funciones, debido a que en Excel existen 4 funciones mas que se encuentran relacionadas con la función Fecha, pero estas serán tratadas en una entrada posterior.

OBJETIVO

• Conocer un poco mas acerca de las funciones de fecha y hora en Excel.

COMPETENCIA

Alumnos de segundo semestre de finanzas y sistemas contables aprendiendo sobre las diferentes opciones de tecnología, en este caso diseñamos un blogger sobre funciones en Excel.  

METODOLOGÍA

La metodología empleada consistió en la búsqueda de información en diferentes paginas web, haciendo uso también de los vídeos disponibles de la página de YouTube. Hay que aclarar que, la información suministrada fue probada en Excel 2007 

DESARROLLO

FUNCIÓN FECHA:

Esta función tiene como finalidad devolver la fecha almacenada en sus campos o un numero que corresponda a la fecha indicada, mas adelante se definirá mejor esta característica. Su sintaxis es:

=FECHA( año, mes, dia )

Los campos o atributos necesarios para esta función son:

• Año: Es un campo obligatorio, numérico y de una longitud de 4 dígitos. Se recomienda el empleo de los cuatro dígitos. Algunas restricciones son:

• Si se colocan solamente dos dígitos, Excel entenderá esto como el primer año que corresponda a partir de 1900, que es el primer año por defecto en el programa. Así si un usuario introduce el valor 15, el sistema tomara como año el numero 1915.

• No puede ser un valor negativo y tampoco exceder el valor de 10000. En este caso devolverá un error, haciendo que la celda muestre el mensaje #!NUM!

• Mes: Es un campo numérico obligatorio. Tiene unas características muy particulares:
• Si el campo es negativo, muestra el mes que le haría falta para completar el año descrito. Es decir, si se coloca =FECHA(2015,-4,15), el sistema muestra: o bien 08/15/2014 o el numero 29448.

•  Si es un valor positivo la fecha se coloca tal cual. Pero si excede el valor de 12, el sistema realiza un calculo y divide ese numero entre 12. El resultado se añade al año y el resto serian los meses de dicho año. Por ejemplo, si tenemos =FECHA(2013,34,15), el sistema devuelve: 10/15/2015 o sino el numero 42292. 

• Día: Es un campo numérico obligatorio. Tiene el mismo funcionamiento que el campo mes, solo que aplicado a los días. 

Anteriormente, se había comentado acerca del tipo de celda. Esta función puede representarse acorde al formato en la que se encuentre la celda a afectar. Así, si esta en formato "general" mostrara una fecha común y corriente y si esta en formato "numérico" mostrara un numero que corresponda a la fecha introducida. Para saber mas acerca de como cambiar el tipo de celda pulse aqui

A continuación, se mostrara un pequeño tutorial que resume lo anteriormente explicado:

FUNCIÓN HORA:

Devuelve el número decimal de una hora determinada. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha.
El número decimal que HORA devuelve es un valor comprendido entre 0 (cero) y 0,99988426 que representa las horas entre 0:00:00 (12:00:00 a.m.) y 23:59:59 (11:59:59 p.m.).

Sintaxis

=HORA(hora, minuto, segundo)

La función HORA tiene los siguientes argumento:

• Hora: Obligatorio. Es un número entre 0 (cero) y 32767 que representa las horas. Todo valor mayor de 23 se dividirá por 24 y el resto se considerará como el valor horario. Por ejemplo, HORA(27,0,0) = HORA(3,0,0) = 0,125 o 3:00 a.m.

• Minuto: Obligatorio. Es un número entre 0 y 32767 que representa los minutos. Todo valor mayor de 59 se convertirá a horas y minutos. Por ejemplo, HORA(0,750,0) = HORA(12,30,0) = 0,520833 o 12:30 p.m.

• Segundo: Obligatorio. Es un número entre 0 y 32767 que representa los segundos. Todo valor mayor de 59 se convertirá en horas, minutos y segundos. Por ejemplo, HORA(0,0,2000) = HORA(0,33,22) = 0,023148 o 12:33:20 a.m.

A continuación, se mostrara un pequeño tutorial que resume lo anteriormente explicado:

BIBLIOGRAFIA

OFFICE. Funcion Fecha. Recuperado el dia 27 de febrero desde https://support.office.com/es-ar/article/Funci%C3%B3n-FECHA-9b619152-52d4-48f0-b1ab-a32dee59b6e4?ui=es-ES&rs=es-AR&ad=AR

MICROSOFT SUPPORT. Como convertir textos a numeros en Excel. Recuperado el dia 27 de febrero desde https://support.microsoft.com/kb/291047/es

VIDEOTUTOLANDIA (2011). 4.10 Funcion Fecha: Formulas y funciones en Excel. Youtube.com. Recuperado el dia 27 de febrero desde https://www.youtube.com/watch?v=lJdUe3lfBn0

OFFICE. Funcion Hora. Recuperado el dia 01 de marzo desde https://support.office.com/es-es/article/HORA-funci%C3%B3n-HORA-9a5aff99-8f7d-4611-845e-747d0b8d5457?ui=es-ES&rs=es-ES&ad=ES

VIDEOTUTOLANDIA. 4.15 Funcion Hora: Formulas y funciones en Excel. Youtube.com Recuperado el dia 01 de marzo desde https://www.youtube.com/watch?v=2Lmn5nvcs6U

CONCLUSIÓN

Las funciones fecha y hora nos ayudan a resolver diferentes problemas en donde muchas de las situaciones, en su mayoria de tipo general, requieren este tipo de dato. Por otro lado, a manera de conclusion personal, la creación del blogger nos muestra que tenemos diferentes opciones a nivel de tecnología para compartir información de interés para todos.

Ejemplo Hora

Hora y Minutos: 

Cuando en una celda escribes 9:54 Excel le asigna el formato de horas y minutos h:mm y cuando escribes 9:54:25 se le asigna formato de horas, minutos y segundos h:mm:ss

Pero “detrás” de ese formato también hay un número. Las horas, minutos y segundos tienen una equivalencia numérica que es con la que Excel opera. ¿Cuál es esa equivalencia? Pues resulta que cualquier horario que escribas tiene un valor numérico de cero y unos decimales que representan la parte proporcional de un día de 24 horas. Por ejemplo las 6 de la mañana (6:00) equivale a un cuarto de día, es decir, 0,25. Las 12:00 sería 0,5

Escribe en cualquier celda de Excel 8:15 y cuando le cambies el formato a número verás que sale 0,34375. Ve haciendo pruebas hasta que lo asimiles. Las 24:00 será el número 1.

Por eso también puedes restar horas minuto y segundos porque realmente lo estás haciendo con números.

Hasta aquí es bastante básico. Los problemas se plantean cuando hablamos de horarios de diferentes días. Debes saber que en la misma celda puedes escribir una fecha y un horario dejando un espacio entre ambos. Por ejemplo para indicar el día 6 de agosto de 2011 a las 5 y 20 de la tarde lo puedes expresar en una celda como 6/8/11 17:20, prueba a escribirlo en una celda vacía de Excel. Si entras a ver el formato de la celda verás dd/mm/aaaa hh:mm.

En este caso el valor numérico que Excel le asigna es un número con decimales. La parte entera es la que se le asigna a la fecha y la parte decimal es la equivalente a la hora. Si le cambias el formato a la fecha anterior y la muestras como número aparecerá la cantidad 40761,72222. Es la suma de 40761 de la fecha y de 0,72222 de la hora. Incluso podrías sumar una celda que contenga una fecha con una celda que contenga un horario, eso sí, las tres celdas tienen formato diferente.

Una vez que comprendemos la manera en que Excel interpreta la información de fechas y horas estamos en disposición de afrontar cualquier cálculo. Por ejemplo si tienes diferentes fechas y horas, ¿Cómo calcular el tiempo que pasan entre ellas?, es decir, ¿Cómo calcular cuántos días, horas y minutos hay entre dos fechas?

Primero te pongo un ejemplo en el que tienes en la misma celda la fecha y la hora:

Si te fijas en las fechas a la ligera dirías que han pasado 3 días sin embargo si compruebas con detenimiento las horas falta 1 hora y 5 minutos para completarse el tercer día.

¿Cómo es posible que se resuelva así de fácil? Te cuento. El valor real de la celda A3 es 40762,4375 (prueba a cambiar el formato de la celda A3 a numérico) y el valor real de la celda B3 es 40765,39236. Recuerda que la parte entera representa el valor numérico de la fecha y la parte decimal es equivalente a la hora. Si restas esos dos valores da 2,954861111. La parte entera es 2. La función RESIDUO se queda con la parte decimal (0,954861111), que al darle formato de horas y minutos da el resultado que esperábamos.

En ocasiones te encontrarás con que la fecha y la hora están en celdas diferentes:

En este caso lo que debes hacer (como puedes comprobar en la imagen) es sumar la fecha y la hora antes de hacer las restas, es decir, sumas 40762 (A4) más 0,4375 (B4) y da 40762,4375 que es la cifra con la que partíamos en el ejemplo anterior.

Un matiz muy importante a tener en cuenta es que cuando escribes en una celda 13:25 estás indicando a Excel que son las 13:25 en el reloj. Cuando insertas en una celda 13:25:40 significa que son las 13 horas, 25 minutos y 40 segundos.

A veces no querrás indicarle a Excel una hora de reloj sino que ha pasado un tiempo determinado. No es lo mismo decir “son las 13 horas y 25 minutos” que “han pasado 13 horas y 25 minutos”. Por ejemplo en el primer caso no tendría sentido escribir 43:50 en una celda de Excel, esa hora no existe, pero en el segundo caso sí.

Para que Excel lo entienda en un sentido o en el otro el truco está en el formato de la celda. El formato h:mm indica  hora de reloj y el formato [h]:mm indica tiempo. De esta manera podrás poner en Excel 43:50 siempre y cuando el formato de esa celda sea [h]:mm

Aquí tienes un ejemplo:

Si hubieras sumado la columna D sin más no hubiera salido ese resultado pero con sólo cambiar el formato a [h]:mm ya lo tenemos. Por eso te decía al principio de este tutorial que con el tema de las fechas y las horas un simple cambio de formato puede ser la solución.

El valor real de la celda D10 es 1,431944444. Cada vez que las horas superen 24 se le suma 1 a la parte entera y el resto es la parte decimal. Por ejemplo 50:25 han pasado dos días completos y 2 horas y 25 minutos. Por eso el valor real de 50:25 sería 2,100694444. El 2 indica los días y el 0,100694444 sería la parte proporcional de día que queda, que pasado a formato de horas y minutos sería las 2:25 que quedaban.

Por ejemplo, partiendo de un tiempo hay que calcular cuántos días y horas son:

Ejemplo de Fecha

Ejemplo Fecha 

Lo primero que debes saber es que en el tema de las fechas y horas hay que estar jugando constantemente con el formato de la celda. A veces un simple cambio de formato te da la solución que necesitabas de forma inesperada. Cuando escribes en una celda de Excel 6/8/11 se le asigna de forma automática a esa celda el formato de fecha (dd/mm/aaaa) pero debes saber que afectos de cálculos esa fecha tiene asignado un número (40761). Ese número corresponde con un contador que empieza con la fecha 1/1/1900, a esa fecha se le asigna el número 1, al 2/1/1900 se le asigna el número 2, y así sucesivamente. De manera que es muy importante que entiendas que para Excel 6/8/11 es lo mismo que 40761 es cuestión de elegir el formato (fecha o número) que mejor se adapte a lo que necesitamos.

Por ejemplo, para calcular cuántos días hay entre dos fechas sólo tendrás que restar la fecha mayor menos la menor y si el resultado sale en el formato inadecuado (fecha) sólo le tendrás que cambiar a formato número.

Prueba el siguiente ejemplo:

 

Recuerda que al resultado de la celda C3 seguramente le tendrás que cambiar el formato a número.

¿Cómo es posible que Excel reste fechas? Es debido a que realmente resta números, acuérdate que “detrás” de cada fecha hay un número así que si cambias el formato de la celda A3 y B3 el resultado es el siguiente:

En otras ocasiones te interesará que el resultado lo de en formato de fecha. Si nos dan una fecha de solicitud y unos días máximos de resolución nos interesará calcular la fecha límite de resolución:

Es como si a 40761 le sumaras 15, el resultado es 40776 que al pasarlo a formato de fecha da 21/8/11

Ya has visto que cuando restas dos fechas calculas los días que han pasado entre una y otra. Pero, claro, hablamos de días naturales ¿y los días laborables? Para ello existe la función =DIAS.LAB(Fecha inicio; Fecha final; Festivos). Para poder usar esta función en Excel 2003 hay que activar un complemento. Puedes entrar en Herramientas, Complementos y marcar Herramientas para análisis, de esta manera podrás usar esta función y muchas más que no estaban instaladas por defecto.En Excel 2007 y 2010 viene “de fábrica”.

Prueba el siguiente ejemplo:

La función DIAS.LAB calcula los días transcurridos entre dos fechas quitando los fines de semana (sábados y domingos) y los festivos indicados en el rango del tercer argumento.

Aquí hay diferencia de opiniones porque hay quien dice que el sábado es laborable y hay quien dice que el fin de semana entero es festivo, la función DIAS.LAB considera esto último.

Para solucionar este dilema la versión 2010 de Excel ha sacado la función =DIAS.LAB.INTL(fecha_inicial; fecha_final; fin_de_semana; festivos) donde en el argumento “fin de semana” se puede elegir qué consideramos como festivo siguiendo los siguientes códigos:

Tipo de Función y Ejercicio

Función AHORA()

Esta función nos devuelve la fecha y la hora actual del sistema con formato de fecha y hora.

Ejemplo: =AHORA() devuelve 09/09/2004 11:50.

Función AÑO(núm_de_serie)

Esta función tiene como parámetro un número de serie y devuelve el año en formato año entre el rango de 1900-9999.

Ejemplo: =AÑO(38300) devuelve 2004. En vez de un número de serie le podríamos pasar la referencia de una celda que contenga una fecha: =AÑO(B12) devuelve también 2004 si en la celda B12 tengo el valor 01/01/2004.

Función DÍA(núm_de_serie)

Devuelve el día del mes correspondiente al número de serie proporcionado.

Ejemplo: =DIA(38300) devuelve 9.

Función DIA.LAB.INTL(fecha_inicial; días_lab; [fin_de_semana]; [festivos])

Sólo son obligatorios la fecha inicial y los días laborales.

Ejemplo: =DIA.LAB.INTL(FECHA(2010;3;1);5) devuelve 8/03/2010.

Función DIA.LAB(fecha_inicial; [días_lab]; [festivos])

Sólo son obligatorios la fecha inicial y los días laborales. Calculará en qué fecha se cumplen el número de días laborales indicados.

Ejemplo: =DIA.LAB("1/5/2010";30;"3/5/2010") devuelve 14/06/2010.

Función DIAS360(fecha_inicial; fecha_final; método)

Calcula el número de días entre las dos fechas proporcionadas basándose en años de 360 días. Los parámetros de fecha inicial y fecha final es mejor introducirlos mediante la función Fecha(año;mes;día). El parámetro método es lógico (verdadero, falso), V --> método Europeo, F u omitido--> método Americano.

Método Europeo: Las fechas iniciales o finales que corresponden al 31 del mes se convierten en el 30 del mismo mes

Método Americano: Si la fecha inicial es el 31 del mes, se convierte en el 30 del mismo mes. Si la fecha final es el 31 del mes y la fecha inicial es anterior al 30, la fecha final se convierte en el 1 del mes siguiente; de lo contrario la fecha final se convierte en el 30 del mismo mes

Ejemplo: =DIAS360(Fecha(1975;05;04);Fecha(2004;05;04)) devuelve 10440.

Función DIASEM (núm_de_serie; tipo)

Devuelve un número del 1 al 7 que identifica al día de la semana, el parámetro tipo permite especificar a partir de qué día empieza la semana, si es al estilo americano pondremos de tipo = 1 (domingo=1 y sábado=7), para estilo europeo pondremos tipo=2 (lunes=1 y domingo=7).

Ejemplo: =DIASEM(38300;2) devuelve 2.

Función FECHA(año; mes; día)

Devuelve la fecha en formato fecha, esta función sirve sobre todo por si queremos que nos indique la fecha completa utilizando celdas donde tengamos los datos del día, mes y año por separado.

Ejemplo: =FECHA(2004;2;15) devuelve 15/02/2004.

Función FECHA.MES(fecha_inicial; meses)

Suma el número de meses indicado a la fecha, devolviendo una fecha como resultado.

Ejemplo: =FECHA.MES("1/7/2010";99) devuelve 01/10/2018O 

EJERCICIO

Ejercicio paso a paso.

1. Si no tienes abierto Excel2010, ábrelo para realizar el ejercicio.

2. Sitúate en la celda A1 y pulsa sobre , selecciona la categoria de fecha y hora y elige la función AHORA().

3. Pulsa el botón Aceptar. Aparece un cuadro de diálogo indicando que la función no tiene argumentos.

4. Pulsa de nuevo sobre Aceptar.

5. Sitúate en la celda B2 y escribe el día de hoy en número. Por ejemplo: 13

6. Sitúate en la celda B3 y escribe el mes actual en número. Por ejemplo: 4

7. Sitúate en la celda B4 y escribe el año actual en número. Por ejemplo: 2010

8. Sitúate en la celda C5 y pulsa sobre , escoge la función FECHA() y pulsa el botón Aceptar.

9. Selecciona como argumentos las celdas B4 --> para año, B3 --> para mes y B2 --> para día, pulsa Aceptar.

Vamos a calcular nuestra edad.

10. Sitúate en la celda D1 y escribe tu fecha de nacimiento en formato (día/mes/año)

11. En la celda E1 escribe =HOY()

12. En la celda E2 selecciona la función DIAS360, como fecha inicial la celda D1 (fecha nacimiento), como fecha final E1 (el día de hoy) y en método escribe Verdadero.

Como resultado nos aparece los días transcurridos desde la fecha D1 y la fecha E1.

13. Ahora en la celda F3 escribe =E2/360 para obtener los años.

El resultado aparece con decimales, para que nos salga solo la parte entera podemos utilizar la función =ENTERO(E2/360).

Hemos utilizado cuatro de las funciones más utilizadas y que ofrecen muchas posibilidades.

14. Guarda el libro de trabajo en la carpeta Mis documentos del disco duro con el nombre de Funciones con fechas.

15. Cierra el libro de trabajo.