Cálculo de fecha de pago con Excel.

viernes, julio 25, 2008

Un caso particular del cálculo de fechas es el de fechas de pago. Si las condiciones de pago de una factura, o cualquier otra obligación, son, por ejemplo, 30 días de la fecha de la factura, el cálculo con Excel es muy sencillo. Para calcular la fecha de pago de una factura cuya fecha es el 25/07/2008 y las condiciones de pago son fecha factura + 30 días, usamos



Pero si la intención es que la fecha de pago sea el mismo día de la fecha de la factura pero un mes más tarde, usamos la función FECHA.MES (en algunos sistemas tendremos que usar la versión inglesa: EDATE)



Para poder usar esta función necesitamos que esté instalado el complemento Analysis ToolPak.
Como puede verse, los resultados son distintos. En el primer caso, 24/08/2008 es exactamente 30 días; el segundo resultado es el 25 del mes siguiente.

Ciertas empresas e instituciones tienen días de pago determinados. Por ejemplo, supongamos una empresa que paga sólo los 15 de cada mes. Si la condición de pago es "fecha de la factura + 30 días", las facturas con fecha posterior al 15 del mes serán pagadas de hecho dos meses más tarde.
Veamos este caso



La fecha de pago de la segunda factura será un mes más tarde que el de la primera. Cómo hacemos para calcular estas fechas con Excel? Obviamente creando un fórmula condicional con la función SI

=SI(DIA(A3+30)<=15,FECHA(AÑO(A3+30),MES(A3+30),15),FECHA(AÑO(A3+60),MES(A3+60),15))



Usamos la función FECHA para "armar" la fecha de pago basándonos en la fecha de la factura.
En caso que queramos establecer un mes después de la fecha de la factura (o dos, o cualquier otro número de meses) usaremos la función FECHA.MES




Technorati Tags:

15 comments:

Cesar Mera 26 julio, 2008 19:20  

Hola Jorge, antes que nada felicitarte por el blog, muy constructivo.

En cuanto al post de las fechas tengo dos anotaciones, la primera es que en el condicional no es necesario sumarle 30 al día, es decir que la fórmula quedaría así (sólo el condicional, el resto queda igual): =SI(DIA(A3)<=15.....

Eso por supuesto no aporta nada sino que reduce unos caracteres.

El segundo comentario, a mi modo de ver, es más interesante, se trata de utilizar la fórmula FIN.MES que arroja el último día de un mes con base en una fecha y un número de meses hacia adelante o hacia atrás, una vez obtenido el último día del mes indicado (con el condicional de arriba) le sumamos 15 días, quedaría así:

=FIN.MES(A3;SI(DIA(A3)<=15;0;1))+15

Creo que es más simple que la que tu propones, que por supuesto funciona muy bien como siempre.

Cordialmente,


CESAR MERA

Jorge L. Dunkelman 26 julio, 2008 22:14  

Muy buen aporte. Se me había pasado por alto la posibilidad de usar FIN.MES (EOMONTH).

Nalda,  07 agosto, 2008 13:11  

Hola Jorge, necesito un SOS!!!! urgente.No sé como solucionar un problema en mi hoja excel. Te cuento lo que me hace falta calcular.

Teng una columna con muchas fechas y otra con cantidades, así:

FECHA EUROS
01/05/2008 85
05/06/2008 25
21/05/2008 45
30/09/2008 12
22/12/2008 12
01/05/2008 45

Necesito que me sume en una celda todo lo que se ha pagado en el mes de mayo, en otra celda todo lo del mes de junio y así sucesivamente.

He intentado con la fórmula sumar.si pero cuando debo definir el criterio no sé como se hace para indicarle el rango de fechas(p.e. del 01/05/08 al 31/05/08).

Si me puedes ayudar con esto, o si sabes de otra fórmula mejor para calcular estos casos.

Gracias.

Guille 08 agosto, 2008 00:40  

Jorge, te hago una consulta, como pasaríamos a un día hábil, si el 15 (día de pago) fuese sábado o domigo? Muchas gracias, muy bueno el blog.

Jorge L. Dunkelman 08 agosto, 2008 12:43  

Hola
primero tenemos que determinar el día de semana de la fecha de pago y luego, en base a este resultado, recalcular la fecha de pago.
Podemos hacerlo usando una columna auxiliar o en una única fórmula.
Digamos que la fecha de la factura esta en la celda A3 (por ejemplo, 16/01/2008). En la celda B3 calculamos el día de pago usando al fórmula proúesta por César Mera

=FIN.MES(A3,SI(DIA(A3)<=15,0,1))+15

Luego en la celda C3 ponemos esta fórmula condicional

=SI(DIASEM(B3,2)<6,B3,SI(DIASEM(B3,2)=6,B3+2,B3+1))

En este ejemplo en la celda B3 obtenemos 15/03/2008. Esta fecha cae en sábado, por lo que en C3 la fórmula dá como resultado 17/03/2008.

Si tuviéramos 17/04/2008 en A3, la fecha de pago caería el 15/06/2008, que es domingo. Así que la fórmula en C3 calcularía 16/06/2008.
Si que remos evitar usar columnas auxiliares podemos incluir todo en una única (y monstruosa)fórmula

=SI(DIASEM(FIN.MES(A3,SI(DIA(A3)<=15,0,1))+15,2)<6,FIN.MES(A3,SI(DIA(A3)<=15,0,1))+15,SI(DIASEM(FIN.MES(A3,SI(DIA(A3)<=15,0,1))+15,2)=6,FIN.MES(A3,SI(DIA(A3)<=15,0,1))+15+2,FIN.MES(A3,SI(DIA(A3)<=15,0,1))+15+1))

Jorge L. Dunkelman 08 agosto, 2008 18:05  

Nalda

te sería más fácil usando como criterio el resultado de la función MES. Por ejemplo, si las fechas de tu ejemplo están en el rango A1:A6 y los valores en B1:B6,puedes crear una columna auxiliar en C1:C6 con la fórmula =MES(A1:A6)Para sumar mayo usarías =SUMAR.SI(C1:C6,5,B1:B6).
Si quieres evitar el uso de una columna auxiliar tienes que usar esta fórmula:
=SUMAPRODUCTO((MES(A1:A6)=5)*B1:B6)

Nalda,  11 agosto, 2008 10:45  

Mil gracias Jorge, he tenido otras dudas pero leyendo el blog se me han aclarado.

Tengo una última consulta (creo), cómo hago para que esta fórmula se actualice, que conforme vaya aumentado filas la fórmula aumente el área de cálculo sin necesidad de hacerla manualmente y sin contar con la primera fila donde estan los encabezados.

Lo leí en alguna respuesta, pero no logro ubicarla.

Gracias una vez más y felicitaciones por el excelente blog.

Jorge L. Dunkelman 11 agosto, 2008 19:25  

Nalda

he tocado el tema en esta nota.
Dado que se trata de una consulta que se repite con frecuencia, estaré publicando una nota más extensiva sobre el tema.

Anónimo,  01 julio, 2010 18:58  

hola, tengo una duda como puedo hacer para que Excel redondeé una fecha según en el día del mes en la que me encuentre. Me explico: Necesito que si estoy en los primeros 15 días del mes en otra celda se devuelva al día primero de ese mes, y si estoy en un día del 16 al 31, en otra celda este se vaya al dia primero pero del mes siguiente.

Si tengo en una celda una fecha entre el 01-01-2010 al 15-01-2010, necesito que en otra celda se devuelva la fecha al 01-01-2010. Y si estoy en una fecha del 16-01-2010 al 31-01-2010, entonces se vaya al 01-02-2010.

Gracias

Jorge L. Dunkelman 01 julio, 2010 20:00  

Suponiendo que la fecha a evaluar está en la celda A1, podrías usar esta fórmula

=SI(DIA(A1)<=15;FIN.MES(A1;-1)+1;FIN.MES(A1;0))

Anónimo,  26 julio, 2011 18:47  

hola porfa ayudemen necesito calcular la fecha de ultimo pago aplico algunas de las formulas que estan aqui pero me sale error espero me puedan ayudar!!!

Jorge L. Dunkelman 26 julio, 2011 18:57  

Porfa!!!! Lee lo que aparece en el enlace Ayuda (en la parte superior del blog).

Jorge L. Dunkelman 18 octubre, 2012 18:23  

No puedes hacerlo con con autorellenar ya que las fechas son una serie de números enteros sucesivos. Si bien en pantalla ves la fecha 15/01/2012, lo que Excel ve es el número 40923 (la cantidad de días transcurridos desde el 01/01/1900).

Martin Alonso Bojanovich 27 febrero, 2014 16:39  

Hola Jorge, como puedo hacer para calcular en un cuadro de ingresos, cuando se tienen diferentes enganches iniciales y periodos diferentes a partir de una fecha determinada, para devolver los meses en que se pagarán las cuotas. Ej: Enero/14 Enganche 1.000, a partir de el mes anterior calcular los meses (en forma consecutiva en que deberán pagarse cada una de las cuotas. Cuando ingrese el mes inicial y la cantidad de cuotas posteriores que calcule el horizonte de cada mes de pagos. Gracias

Jorge Dunkelman 04 marzo, 2014 20:24  

Por favor, lee lo que pongo en el enlace Ayuda, en la parte superior de la plantilla.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP