Cálculo de días específicos con Excel

jueves, abril 19, 2007

El cálculo de fechas con Excel es muy flexible y ofrece muchas posibilidades. Para ejemplificar esto, responderé en esta nota a tres preguntas que me hace un lector del Perú:

1.Como podría hacer para calcular cuantos sábados y/o domingos hay en un mes dado el primer y ultimo día.

2.Para saber que día cae un segundo o tercer domingo de un mes X, por ejemplo el día del padre o de la madre.

3.Calcular el día de Pascua (Domingo Santo)


Calcular la ocurrencia de un día determinado.

Para calcular cuantas ocurrencias tiene un día determinado de la semana en un mes (o cualquier otro período) usamos esta fórmula

={SUMA(SI(DIASEM(A2-1+FILA(INDIRECTO("1:"&TRUNCAR(B2-A2)+1));2)=C2;1;0))}

Esta es una fórmula matricial y se pone en la celda apretando simultáneamente Ctrl + Mayúscula + Enter




En la celda A2 ponemos la fecha del primer día del mes/período, en la celda B2 el último día del mes/período, en C2 el número de orden del día de la semana (1 = Lunes, 2= Martes, etc). Es decir si queremos saber cuántos domingos hay en un período, ponemos 7 en la celda C2.

Calcular la fecha de un día determinado, dado el mes y el año.
Si queremos calcular, por ejemplo, la fecha del tercer viernes de abril de este año, usamos esta fórmula

=FECHA(E2;D2;1+((C2-(B2>=DIASEM(FECHA(E2;D2;1);2)))*7)+(B2-DIASEM(FECHA(E2;D2;1);2)))

donde B2 contiene el día buscado (1 = Lunes, 2 = Martes, etc); C2 contiene el número de orden dentro del mes; D2 contiene el mes y E2 el año



También aquí usamos DIASEM con el argumento Tipo = 2.

Calcular la fecha de Pascua con Excel.

Este cálculo es más complicado y mostraré aquí tres posibilidades

1 – una función definida por el usuario (UDF), citada por Chip Pearson


Public Function EasterDate(Yr As Integer) As Date

Dim d As Integer
d = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
EasterDate = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 - ((Yr + Yr \ 4 + _
d + (d > 48) + 1) Mod 7)

End Function


Esta función hay que copiarla a un módulo del editor de Vba, y luego a activa con el asistente de fórmula (categoría: definidas por el usuario)



2 – con la fórmula =MULTIPLO.INFERIOR(FECHA(A3;5;DIA(MINUTO(A3/38)/2+56));7)-34

donde A3 contiene el año

3 – con esta fórmula, citada por John Walkenbach, sobre la cual ni él, y mucho menos yo, tenemos la más mínima idea de cómo funciona

=MONEDA(("4/"&A3)/7+RESIDUO(19*RESIDUO(A3;19)-7;30)*14%;)*7-6

Nuevamente A3 contiene el año.






Technorati Tags:

32 comments:

Petit Comite 25 octubre, 2007 18:51  

Por favor, podrías explicarme el desarrollo de esta fórmula:

={SUMA(SI(DIASEM(A2-1+FILA(INDIRECTO("1:"&TRUNCAR(B2-A2)+1));2)=C2;1;


No logro descifrarlo.

Mil gracias

Jorge L. Dunkelman 25 octubre, 2007 19:33  

No puedo. La fórmulas está incompleta y además es difícil interpretarla sin ver el contexto.
Si te puedo decir que se trata de una fórmula matricial. Sobre la función DIASEM puedes en mi nota sobre Contar condicional de fechas en Excel .

Petit Comite 26 octubre, 2007 10:24  

Gracias por tu respuesta.
He obtenido esta formula de un post anterior que trataba de resolver el nº de sábados y domingos entre dos fechas.

Entiendo que es una fórmula matricial, pero no logro descifrar el motivo por el cual se transforma el número de días entre dos fechas por una cadena de texto, y ésta a su vez por un referencia con la fórmula Indirecto.
Aquí está mi gran duda.

Jorge L. Dunkelman 27 octubre, 2007 17:05  

Envíame el cuaderno con el problema a jorgedun@gmail.com

Anónimo,  04 julio, 2008 20:30  

Jorge, necesitaría ayuda con lo siguiente:
Necesito obtener una formula que me cuente la cantidad de días entre 2 fechas, a eso le sume el clearing bancario de 48 hs. pero, si la fecha final cae jueves, viernes o sabado entonces en lugar de 2 días, le sume 4 días y si cae domingo que le sume 3 días. Es para calcular los dias reales de acreditacion de cheques.

Espero que puedas ayudarme, muchas gracias!

Martin Markowicz
euromartin@hotmail.com

Jorge L. Dunkelman 05 julio, 2008 00:35  

Hola Martín

la respuesta va por mail

Roger 13 agosto, 2008 21:18  

Hola, mira no encuentro por internet o no se como buscar, me podrias ayudar, necesito calcular la fecha promedio de pago cuando recibo un pago con muchos cheques de distitas fechas pero estos son de distinto importe.
Por ejemplo yo le digo que me pague a 30 dias pero me paga algo a 10 dias y algo a 45 bueno con distinto importe. Como lo puedo implementar en excel?
Gracias de Antemano
Roger

Jorge L. Dunkelman 14 agosto, 2008 17:34  

Hola Roger
más que la fecha promedio, supongo que quieres calcular la cantidad de días (plazo de pago) promedio. Mandame un archivo con el ejemplo que quieres desarrollar.

Anónimo,  01 octubre, 2008 23:43  

Hola jorge, necesito saber como poder calcular un contador diario que apartir de una fecha puesta en A1 me pueda decir diariamente cuantos dias han pasado y almacenarla en A2.

Gracias

Jorge L. Dunkelman 03 octubre, 2008 18:31  

En A2 usas la fórmula =HOY()-A1 o mejor aún

=SIFECHA(A1,HOY(),"d")

Anónimo,  01 diciembre, 2008 17:35  

La formula que das para saber cuantas ocurrencias tiene un dia determinado no me sirve...
le quito los corchetes {} y me dice que tiene un error la formula.
aparte de copiar y pegar la formula que mas tengo que hacer??

Rene Gonzalez
rene_gerardo@hotmail.com

Jorge L. Dunkelman 01 diciembre, 2008 20:58  

Hola

como pongo en la nota, ésta es una fórmula matricial

Esta es una fórmula matricial y se pone en la celda apretando simultáneamente Ctrl + Mayúscula + Enter

Los corchetes aparecen al introducir la fórmula como está explicado arriba. No los tienes que oner manualmente.
Si los quitas, la fórmula deja de ser matricial y eso produce un error.

Anónimo 17 marzo, 2009 16:37  

Hola Jorge,

Necesito determinar el número de días transcurridos entre dos fechas, omitiendo sábados y domingos. Existe alguna manera de hacerlo?

Gracias por tu ayuda

Anónimo,  19 marzo, 2010 02:01  

Buenas noches,

Quisiera saber como contabilizar en Excel 2007 el número de dias entre dos fechas no teniendo en cuenta los domingos.

Gracias,

Mi correo: abap79@hotmail.com

Jorge L. Dunkelman 19 marzo, 2010 06:49  

Una posibilidad es crear una columna auxiliar con la función DIASEM para identificar los domingos y luego usar CONTAR.SI
Una segunda posibilidad es usar alguna de las técnicas que muestro en esta nota.

KAREN 21 septiembre, 2011 03:35  

Buenas Noches,

Tengo excel 2010 estoy utilizando la formula para Calcular la ocurrencia de un día determinado y me dice que la formula esta mala.

espero me pueda ayudar.

saludos,

kA

Jorge L. Dunkelman 21 septiembre, 2011 07:25  

Karen,

no puedo ayudarte si no eres un poco más clara en tu explicación. Por favor, fijate en lo que pongo en el enlace "Ayuda".

Anónimo,  07 noviembre, 2011 15:27  

Hola alguien me puede ayudar... Necesito realizar en excel una formula que me cuente los días de forma diaria a partir de una fecha.

De antemano gracias,
Slds

Jorge L. Dunkelman 07 noviembre, 2011 16:32  

=SIFECHA(fecha inicial,HOY(),"d")

Anónimo,  19 enero, 2012 19:25  

Necesito una formula que me cuente los días transcurridos entre una fecha inicial y otra final, sin tener en cuenta los domingos y feriados.

Jorge L. Dunkelman 19 enero, 2012 20:28  

Con las funciones DIAS.LAB o en Excel 2010 DIAS.LAB.INT, como muestro en esta nota

Anónimo,  13 junio, 2012 23:17  

como puedo obtener los dias de la semana que mas consultas de usuario se han realizado?
tengo campo fechas y consultas...

alguien podria orientarme o explicar algun ejemplo similar.

Jorge L. Dunkelman 17 junio, 2012 15:17  

Lo más eficiente sería con una tabla dinámica resumiendo los valores con Recuento.
También podrías hacerlo creando una tabla con una columna para los ´dias de la semana (1 para lunes, dos para martes, etc.), luego agregar en tu base de datos una columna para el día de semana (con la función DIASEM) y usar CONTAR.SI para obtener la frecuencia de cada día.

Anónimo,  30 junio, 2012 04:02  

Hola!

Necesito calcular cuantos lunes hay entre dos fechas, cuando sigo el procedimiento que se describe en el artículo me sale "Error en la Formula". Tengo Microsoft Excel 2010. Si es posible explica un poco cada parte de la formula para entenderla bien por si necesito hacerle alguna modificación. No hay una forma más sencilla y directa de hacerlo con alguna función predeterminada de Excel?

Muchas gracias de antemano.

Jorge L. Dunkelman 30 junio, 2012 11:00  

Excel no tiene una función específica para calcular la frecuencia de un día en un rango de fechas. En relación al error, asegurate de introducir la fórmula en forma matrcial (Ctrl+Mayúsc.+Enter) y de usar los separadores de argumentos adecuados (dependiendo del sistema puede ser como o punto y coma).
También puedes descargar el ejemplo y analizar la fórmula.

David Ramos 16 abril, 2014 19:27  

Muchas gracias!!!
Me podria dar una pista de como llego a lógica para desarrollar la segunda formula del post "Calcular la fecha de un día determinado, dado el mes y el año", se lo agradeceré mucho.
Saludos,

Jorge Dunkelman 17 abril, 2014 08:14  

La fórmula se basa en la función FECHA que calcula un día en base a tres argumentos: año, mes y día. El año y el mes los obtenemos de las celdas E2 y D2. Para calcular el día usamos los valores de las celdas B2 (día de la semana, viernes=5) y C2 (ocurrencia= 3).
La expresión DIASEM(FECHA(E2;D2;1);2)) calcula en que día de la semana cae el primero del mes (en nuestro ejemplo= 7, domingo). Esto nos permite saber si el primer viernes (5) cae en la primer semana o no usando la comparación B2>=DIASEM(FECHA(E2;D2;1);2))
En nuestro ejemplo es FALSO por lo que la expresión
1+(C2-(B2>=DIASEM(FECHA(E2;D2;1);2)))*7
se convierte en el número 22, que es el cuarto domingo del mes. Ahora tenemos que corregir este resultado para encontrar el tercer viernes, que ocurre antes que el cuarto domingo. Lo hacemos con la expresión (B2-DIASEM(FECHA(E2;D2;1);2), que da -2, dando el resultado final 20/04/2007.

Anónimo,  24 junio, 2014 21:14  

Buenas tardes me podrias ayudar, necesito saber como sacar cuantos días hábiles están entre una fecha y otra pero en este caso solo descartando los domingos
Saludos y gracias

xmariox@msn.com

Jorge Dunkelman 25 junio, 2014 07:30  

Fijate en esta nota.
Además te sugiero que nunca pongas tu dirección de correo electrónico en un comentario (a menos que te guste ver tu casilla de correo invadida con spam).

Anónimo,  02 diciembre, 2014 21:42  

buenas tardes Jorge, podrias ayudarme a calcular la cantidad de días (plazo de pago) promedio? Seria para aplicar en un pago recibido de varios cheques que tienen distintas fechas y distintos importes, ya que no puedo hacer el calculo lineal porque el porcentaje del total del pago contra cada cheque varia mucho. Desde ya muchas gracias por tu ayuda y tu tiempo.. Ezequiel

Jorge Dunkelman 03 diciembre, 2014 15:26  

Hola Ezequiel, por favor fijate en lo que pongo en el enlace Ayuda (en la parte superior del blog) y enviame el archivo con el ejemplo.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP