View
10
Download
0
Category
Preview:
DESCRIPTION
Programacion
Citation preview
7/17/2019 Fechas y Horas en Excel(1)
http://slidepdf.com/reader/full/fechas-y-horas-en-excel1 1/10
Lic Carlos Ruiz Hernandez 1
Fechas y Horas en ExcelLo 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 muyimportante 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:
7/17/2019 Fechas y Horas en Excel(1)
http://slidepdf.com/reader/full/fechas-y-horas-en-excel1 2/10
Lic Carlos Ruiz Hernandez 2
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 losdías laborables?
7/17/2019 Fechas y Horas en Excel(1)
http://slidepdf.com/reader/full/fechas-y-horas-en-excel1 3/10
Lic Carlos Ruiz Hernandez 3
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 ymuchas 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.
7/17/2019 Fechas y Horas en Excel(1)
http://slidepdf.com/reader/full/fechas-y-horas-en-excel1 4/10
Lic Carlos Ruiz Hernandez 4
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:
Pasemos a las horas 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
7/17/2019 Fechas y Horas en Excel(1)
http://slidepdf.com/reader/full/fechas-y-horas-en-excel1 5/10
Lic Carlos Ruiz Hernandez 5
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.
7/17/2019 Fechas y Horas en Excel(1)
http://slidepdf.com/reader/full/fechas-y-horas-en-excel1 6/10
Lic Carlos Ruiz Hernandez 6
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ómocalcular 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
7/17/2019 Fechas y Horas en Excel(1)
http://slidepdf.com/reader/full/fechas-y-horas-en-excel1 7/10
Lic Carlos Ruiz Hernandez 7
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 25minutos” 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
7/17/2019 Fechas y Horas en Excel(1)
http://slidepdf.com/reader/full/fechas-y-horas-en-excel1 8/10
Lic Carlos Ruiz Hernandez 8
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:
7/17/2019 Fechas y Horas en Excel(1)
http://slidepdf.com/reader/full/fechas-y-horas-en-excel1 9/10
Lic Carlos Ruiz Hernandez 9
Me gustaría contarte la función HORA(), MINUTO() Y SEGUNDO(). Estas
funciones extraen de una hora (h:mm:ss) las horas, los minutos y los
segundos respectivamente.
Por ejemplo si en la celda A1 hay escrito 19:25:30 la función =HORA(A1)
dará 19, la función =MINUTO(A1) dará 25 y la función =SEGUNDO(A1)
devolverá 30.
Esto te resultará muy útil en aquellos casos en los que hay que pasarlo
todo a minutos para multiplicar por algún importe en concreto. Por ejemplo:
Te explico la fórmula de la celda D4. Por un lado sacas las horas de la
celda C4 y las multiplicas por 60 para pasarlo a minutos HORA(C4)*60,
por otro lado extraes los minutos con MINUTO(C4) y por último calculas
cuantos minutos son los 20 segundos de la celda C4 con
SEGUNDO(C4)/60. Todo eso sumado nos dice cuantos minutos son 5
horas, 44 minutos y 20 segundos. Nos quedaba por pasar a minutos los
10 días con B4*24*60 que sumado a lo anterior da los minutos totales:
7/17/2019 Fechas y Horas en Excel(1)
http://slidepdf.com/reader/full/fechas-y-horas-en-excel1 10/10
Lic Carlos Ruiz Hernandez 10
Creo que con estas técnicas serás capaz de resolver la mayoría de los
problemas que se te planteen en Excel con el tema de las fechas y las
horas, sobre todo es importante no perder de vista el valor real quealmacena la celda y jugar con el formato.
Recommended