Contar condicional de fechas en Excel

jueves, marzo 01, 2007

Uno de mis lectores me pregunta cómo contar los días en un rango de fechas, excluyendo los domingos (o cualquier otro día de la semana).

En esta nota mostraremos dos formas de hacerlo, con y sin columna auxiliar. Ambas formas se basan en el uso de la función RESIDUO. Así que empezaremos por una pequeña explicación sobre esta función.

La función RESIDUO tiene dos parámetros, número y número divisor. Da como resultado el residuo o resto de la división entre número y el número divisor.

Por ejemplo, el resultado de RESIDUO(3,2) es 1.

Supongamos esta serie de 21 fechas, en la cual aparecen 3 domingos, Queremos una fórmula que nos de cómo resultado la cantidad de días, excluidos los domingos (18)




En la celda B1 escribimos esta fórmula

=RESIDUO(A1,7)

Y la copiamos al resto del rango (B1:B21). Esta fórmula da como resultado 1 si el día de la fecha es domingo.



Ahora podemos usar CONTAR.SI, con el criterio "<>1" (distinto de 1)

La fórmula =CONTAR.SI(B1:B21,"<>1") da como resultado 18.

Podemos usar la misma técnica usando la función DIASEM en lugar de RESIDUO. Si usamos DIASEM hay que tener en cuenta que testa función tiene dos parámetros: "número de serie", que representa la fecha y "tipo" que puede ser
1 - (u omitido): Números del 1 (domingo) al 7 (sábado).
2 - Números del 1 (lunes) al 7 (domingo).
3 - Números del 0 (lunes) al 6 (domingo).

Una solución más elegante, sin usar columnas auxiliares, es usar fórmulas matriciales o, mejor aún, SUMAPRODUCTO.

La fórmula será =SUMAPRODUCTO(--(RESIDUO(A1:A21,7)<>1))

SUMAPRODUCTO genera una matriz de resultados VERDADERO o FALSO; el doble signo "--" fuerza a Excel a transformar VERDADERO en 1 y FALSO en 0. Finalmente SUMAPRODUCTO suma los resultado, en este caso 18.
En la columna B hemos puesto la fórmula

=RESIDUO(A1,7)<>1

Y como puede verse resultan 18 casos de VERDADERO



Obviamente, si queremos excluir otro día, cambiamos el 1 por el número correspondiente.


Technorati Tags:

47 comments:

Anónimo,  19 abril, 2007 01:55  

Hola quisiera me ayudes con estas dudas:

1.Como podría hacer para calcular cuantos sabados y/o domingos hay en un mes dado el primer y ultimo dia.

2.Para saber que dia cae un segundo o tercer domingo de un mes X, por ejem el dia del padre o de la madre.

3.Calcular el dia de pascua (domingo santo)

Muchas Gracias,
David Cabrera Ojeda
Lima Peru
david_cabrera79@hotmail.com

Jorge L. Dunkelman 19 abril, 2007 23:00  

Hola David,

preguntas interesantes, les he dedicado esta nota que te invito a leer.

Anónimo,  19 julio, 2007 21:03  

Estiamdo, no te imaginas como me han ayudado tus sugerencia y ejemplos.

Me encantaría si me pudieras ayudar con lo siguiente. Necesito calcular el promedio con más de una condición, es decir, si tengo una columna con una serie de valores y quiseira saber el promedio entre >2 y >=4, es sencillo hacerlo con una condición per no he logrado hacerla con más.

Muchas gracias

Jorge L. Dunkelman 20 julio, 2007 08:45  

Fijate en esta nota que acabo de publicar.

Anónimo,  27 julio, 2007 01:12  

AYUDA POR FAVOR!! necesito saber si se puede hacer lo siguiente, si en una columna al yo introducir una fecha, suponiendo los sabados cambien de color.. no se si me xplico, gracias!

Jorge L. Dunkelman 27 julio, 2007 08:05  

Para esos tienes que usar Formato Condicional. Suponiendo que el rango de fechas empieza en la celda A1, en la opción fórmula del formato condicional pones esta fórmula

=DIASEM(A1;2)=6

En esta nota hay una explicación sobre cómo utilizar el formato condicional.

Anónimo,  31 agosto, 2007 03:24  

hola quisiera saber si me puedes ayudar con esto , mira colocar los dias de un meses , pero solo los dias habiles(lunes a viernes) ni sabados ni domingos y menos feriados de un mes , como lo puedo hallar y ademas , otra pregunta se puede hacer q al crear una nueva hoja automaticamente ingrese el otro solo con sus dias habiles

Jorge L. Dunkelman 01 septiembre, 2007 10:02  

Hola

como explico en la nota, para calcular qué día de la semana es una fecha determinada, usas DIASEM.
Podrías poner todos los días del mes requerido y en una columna auxiliar determinar el día de la semana.
Si quieres hacerlo en forma automática, tendrías que usar una macro que haga el cálculo e inserte los días en el rango de la hoja.
Si quieres que esto suceda en forma automática al abrir un cuaderno, o agregar una hoja al existente, debes usar un tipo especial de macros que se llaman eventos.

Pedro 14 mayo, 2008 19:45  

Hola Jorge:

Te escribo desde Chile. Bueno, te comento que estoy levantando un indicador de tiempos de demora en la entrga de un servicio, para lo cual debo calcular los días trancurridos entre la recepción de un requerimiento hasta la fecha de entrega del servicio.
Hasta el momento tengo las planillas excel con el registro de ambas fechas pero además debo descontar los fines de semana y días feriados. Ojalá puedeas orientarme con un ejmplo breve

Saludos

Anónimo,  15 julio, 2008 21:43  

hola jorge

me llamao cristian y tengo la siguiente pregunta.

nesecito calcular los percentiles 25 y 75 respectivamente de un rango de datos, el tema es que quiero que me los calcule sin tomar en cuenta los 0. ya que me cambian los calculos de los estadigrafos, de igual forma nesecito calcular la mediana y el promedio, pero el ultimo ya lo solucione leyendo una de las ayudas.

espero me puedas ayudar

atte

Cristian

Jorge L. Dunkelman 16 julio, 2008 22:20  

Hola Cristián

tienes que usar una fórmula matricial para generar una matriz que no contenga los ceros de la matriz original. Si los valores están en el rango A1:A15, esta fórmula resolvería tu problema

{=PERCENTIL(SI(A1:A15=0,"",A1:A15),0.25)}

Esta es una fórmula matricial que se introduce en la hoja pulsando simultáneamente Ctrl+Mayuscula+Enter.

En general la función de los comentarios es comentar temas relacionados con la nota en cuestión. Estás invitado a plantear tus consultas por mail, en caso que no estén relacionadas con la nota.

Tania 28 enero, 2009 06:19  

Hola!, primero que nada felicidades!!, muy buen block, mira no sé si me puedas ayudar necesesito generar numeros aleatorios con un porcentaje de incidencia, es decir:
De un rango de 1-4 y que me aparezca 40% 4, 20% 3, 20% 2, 10% 1.,cres que sea posible?, te lo agradecería muchisimo!!

Jorge L. Dunkelman 10 febrero, 2009 12:11  

Hmmm, no se me ocurre nada sofisticado por ahora.

Anónimo,  19 diciembre, 2009 23:31  

Hola quisiera me ayuden con una incognita, si en una celda tengo la fecha 1/03/2008 y quisiera q en otra celda se sume solo los meses del año siguiente (2009) como se podria realizar pues quiseira q la respuesta sea 9 meses ya q la fecha se encuentra en el mes de marzo

por fa..............

Jorge L. Dunkelman 20 diciembre, 2009 06:57  

Entiendo que querés calcular la cantidad de meses hasta el fin de año. Se puede hacer con facilidad usando la función MES. Suponiendo que la fecha se encuentra en la celda A1, la fórmulas sería

=12-MES(A1)

Felipe Blanco,  15 julio, 2010 19:37  

Hola Jorge.
me he dado el tiempo de ir leyendo tus notas y están muy prácticas.
Tengo una consulta haber si me ayudas.
Necesito realizar una cuenta regresiva para días del mes, que contemple los dias de trabajo, es decir poder mostrar "Faltan "x" días para fin de mes" pero que me haga el conteo de solo los dias por trabajar.

Saludos t gracias desde Chile.

Jorge L. Dunkelman 16 julio, 2010 10:45  

Felipe,
se puede hacer fácilmente con la función DIAS.LAB

Anónimo,  16 noviembre, 2010 19:44  

Hola una pregunta relacionado al tema...
quiero sacar los días trabajados de un mes, teniendo en cuenta que en el mes solo tengo 27 días, luego yo ingreso la fecha manualmente y que esta automáticamente me salga los días laborables ejemplo:


Fecha 03/09/2010 Días de trabajo mes 27
Días trabajados
que formula me permite sacar los días trabajados

Jorge L. Dunkelman 17 noviembre, 2010 18:46  

No me queda clara tu consulta. Los meses tienen entre 28 y 31 días. Este año setiembre tiene 22 días laborales (tomando en cuenta que no se trabaja los sábados y los domingos).
De todas maneras, suponiendo que la fecha de referencia se encuentra en la celda A1, esta fórmula te calculará los días laborales del mes de la fecha

=DIAS.LAB(FECHA(AÑO(A1),MES(A1),1),FIN.MES(A1,0))

David,  02 marzo, 2011 16:29  

Hola, aprovecho el buen nivel de las respuestas para hacerte esta nueva pregunta:
Necesito calcular la edad de alguien al 30/06/2010.
Las formulas que encontré me cuentan el año entero, no a una determinada fecha, ¿como hago?
Desde ya muchas gracias

David

Jorge L. Dunkelman 02 marzo, 2011 20:15  

Hay muchas formas de calcular la edad, por ejemplo con la función SIFECHA.

Anónimo,  31 julio, 2011 22:08  

Hola, quiero pedirle una ayuda.. necesito sacar una diferrencia de dias en la cual tengo que incluir los fines de semana y excluir los feriados.. hay alguna manera de hacer esto? he intentado con las funciones dias360 y dias.lab pero en ambas me falta una condicion.. por fa vor podria ayudarme.. Gracias de antemano

Edith.

Jorge L. Dunkelman 01 agosto, 2011 07:07  

Edith,

si usas Excel 2010 puedes hacerlo con la función DIAS.LAB.INTL. Si usas una versión anterior de Excel, te sugiero leer esta nota.

Anónimo,  18 agosto, 2011 23:43  

Hola.. Tengo la siguiente pregunta. debo realizar un calculo de proceso donde tengo que si el dia es Lunes 22 agosto mi fecha de ejecucion debe ser el marte 23 de agosto. pero si mi dia es sabado o domigo la formula me debe dar para el siguiente lunes

Jorge L. Dunkelman 19 agosto, 2011 19:05  

Tienes que usar DIA.LAB. Si estás usando Excel 2010, fijate en esta nota.

Anónimo,  11 noviembre, 2011 06:19  

Hola.. me gustaria hacerte una pregunta. tengo que calcular cuantos días de licencia corresponden a un empleado introduciendo el día de comienzo de la licencia y el número de dias que le corresponden teniendo en cuenta que la semana se calcula de lunes a sabados y los domingos no se trabaja. por ejemplo comienza el 11 de noviembre y se le dan 20 días,el resultado tendría que ser el 3 de diciembre

Jorge L. Dunkelman 11 noviembre, 2011 07:00  

Si tienes Excel 2010 puedes usar las nuevas funciones DIA.LAB.INTL o DIAS.LAB.INTL como muestro en esta nota.
Para versiones anteriores de Excel te sugiero que veas la técnica que muestro en esta nota.

Anónimo,  11 noviembre, 2011 17:40  

Gracias por contestar tan rapido, pero lamentablemente no he podido solucionar el calculo, puede ser por que tengo instalado office 2002?, pues la UDF del linck me da error y no funciona.

mi pregunta era...
"Hola.. me gustaria hacerte una pregunta. tengo que calcular cuantos días de licencia corresponden a un empleado introduciendo el día de comienzo de la licencia y el número de dias que le corresponden teniendo en cuenta que la semana se calcula de lunes a sabados y los domingos no se trabaja. por ejemplo comienza el 11 de noviembre y se le dan 20 días,el resultado tendría que ser el 3 de diciembre"

Jorge L. Dunkelman 12 noviembre, 2011 17:22  

No creo que tenga que ver con la versión de Excel (aunque te sugiero que te muevas a alguna de los nuevas versiones).
Puedes enviarme el archivo para ver donde está el problema.

Anónimo,  14 noviembre, 2011 16:15  

Gracias Joege, ya solucioné el problema. yo estaba haciendo las cosas mal.
SOS UN GENIOOOO.... Mil gracias

yax 26 enero, 2012 21:34  

Hola realmente estas muy heavy, como diria el Anonimo "Sos un Genio", bueno después de los cebollazos, quiero pedir tu ayuda tengo un problema. De un rango de fechas de todo un año quiero obtener el conteo de esos datos pero por mes y obtener el total de fechas repeditas en ese rango, esta es la formula que he estado trabajando pero como resultado me trae todo lo anterior a la fecha inicial.

=CONTAR.SI(Hoja1!F6:Hoja1!F6004,"=>01/01/2012")+CONTAR.SI(Hoja1!F6:Hoja1!F6004,"<=31/1/2012")

Esta es la primera formula y no funciona, la segunda es

=CONTAR.SI(Hoja1!F$2:Hoja1!F$6000,"=>"&$B19)+CONTAR.SI(Hoja1!F$2:Hoja1!F$6000,"<="&$B20)

Aquí asigne un valor a la celda B19 y B20 es decir le puse el valor del 01/01/2012 y a la siguiente 31/01/2012

Ya por ultimo utilice esta

=CONTAR.SI(Hoja1!F4:Hoja1!F6002,"=>"&FECHA(2012,3,1))+CONTAR.SI(Hoja1!F4:Hoja1!F6002,"<="&FECHA(2012,3,31))

Para evitar poner el valor a las celdas.

No se si es un problema de signos o de función.

Jorge L. Dunkelman 27 enero, 2012 10:21  

No me queda claro que es lo que quieres hacer, pero para contar cuantos valores hay entre dos fechas tendrías que usar SUMAPRODUCTO de esta manera
=SUMAPRODUCTO((rango=fecha inicial)*(rango=fecha final))

yax 01 febrero, 2012 21:13  

Ah lo que pasa es que ya MUCHAS GRACIAS la revisare te agradezco.

Anónimo,  21 enero, 2013 19:32  

Quien me puede ayudar, necesito calcular el número de días de un día particular por ejemplo: "Martes" que tiene un mes cualquiera del año a partir de una fecha cualquiera

Jorge L. Dunkelman 21 enero, 2013 19:55  

Fijate en mi respuesta al primer comentario en esta nota. En esta nota puedes ver la solucion que propongo.

Anónimo,  21 enero, 2013 21:50  

Jorge:

Excel no me reconoce las llaves { }
={SUMA(SI(DIASEM(A2-1+FILA(INDIRECTO("1:"&TRUNCAR(B2-A2)+1));2)=C2;1;0))} ¿Que podría estar haciendo mal?

Jorge L. Dunkelman 22 enero, 2013 03:32  

Se trata de una fórmula matricial; las llaves no hay que escribirlas sino que la se introduce la fórmula apretando simultáneamente Ctrl, Mayúsculas y Enter.

JORGE ROJAS 07 agosto, 2013 01:22  

buenas tardes; que formula podria utilizar para contar 30 dias apartir de una fecha que yo inserte?graciaS

Jorge Dunkelman 07 agosto, 2013 06:38  

Si no necesitas tomar en cuenta los feriados/fines de semana, sencillamente =A1+30, donde A1 es la celda que contiene la fecha.
Si hay que tomar en cuenta feriados usamos la función DIAS.LAB o DIAS.LAB.INTL.
En el blog hay varias notas sobre el tema.

Edwin Andres Gaviria 25 enero, 2016 15:40  

QUIERO RESTAR DOS FECHAS SIN TENER EN CUENTA LOS FINES DE SEMANA?? TENIENDO EL CUENTA EL TIEMPO TRASCURRIDO EN LOS DIAS HABILES

Fɑвiοlɑ Sοtο 18 julio, 2016 17:07  

Hola Jorge, primeramente te quiero felicitar por tu excelente blog. Ahora bien, necesito ayuda para realizar lo siguiente:
Tengo en una hoja de excel todos los cheques con las fechas que deben ser entregados y en otra celda los montos de cada uno. me piden poder ver consultar cuantos cheques salen en alguna fecha determinada y cual es el monto de los mismos. espero puedas ayudarme

Jorge Dunkelman 24 julio, 2016 22:04  

Hola Fabiola, lamento la demora en responder.
La más sencillo y eficiente sería usar una tabla dinámica.

arturott 22 diciembre, 2016 17:54  

Wow... Es genial el tipo de dominio que tienes en Excel, muchas felicidades. Me salvaste la vida de verdad, mil gracias por compartir tu conocimiento y tu tiempo !!!.

Aileem 15 febrero, 2017 21:48  

Hola, me gustaria saber que formula puedo utilizar para calcular los domingos y feriados que existen entre dos fechas. Gracias!

Jorge Dunkelman 17 febrero, 2017 15:53  

Tendras que usar la función DIAS.LAB.INTL para calcular la diferencia entre dos fechas excluyendo sábados, domingos y feriados. Luego calcular la diferencia de días en forma sencilla (Fechas final - fecha inicial) y restar este resultado del obtenido con la función DIAS.LAB.INTL.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP