viernes, julio 25, 2008

Cálculo de fecha de pago con Excel.

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:

27 comentarios:

  1. 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

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

    ResponderBorrar
  3. 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.

    ResponderBorrar
  4. 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.

    ResponderBorrar
  5. 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))

    ResponderBorrar
  6. 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)

    ResponderBorrar
  7. 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.

    ResponderBorrar
  8. 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.

    ResponderBorrar
  9. 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

    ResponderBorrar
  10. 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))

    ResponderBorrar
  11. 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!!!

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

    ResponderBorrar
  13. 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).

    ResponderBorrar
  14. 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

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

    ResponderBorrar
  16. Y por ejemplo si los pagos se hacen únicamente un día en la semana, por ejemplo solamente los jueves cómo tendría que modificaría esta fórmula ???

    ResponderBorrar
  17. Eli, la respuesta va en un post que publicaré en breve.

    ResponderBorrar
  18. Jorge,

    Y si en vez de un día al mes pagan 2 o más?

    ResponderBorrar
  19. Publicaré un post ampliando sobre el tema.

    ResponderBorrar
  20. BUENOS DIAS, NECESITO ALGO MUY BASICO PERO NO PUEDO RESOLVER. TENGO DOS COLUMNAS, UNA CON MONTOS Y LA PARALELA CON FECHAS, INTENTO QUE: UNA VEZ RELLENADO LA SELDA DE MONTO, AUTOMATICAMENTE SE UBIQUE EN LA DE AL LADO , LA FECHA DEL DIA, SIN QUE SE MODIFIQUE DIARIAMENTE.

    ResponderBorrar
  21. Muy bueno Jorge.
    Mis Saludos.
    Hugo

    ResponderBorrar
  22. Y si en vez de un día al mes pagan 2 o más?

    ResponderBorrar
  23. ¿Cuál sería el criterio para determinar cuando se paga? Es decir, si la empresa paga todos los 10 y 20 de cada mes, ¿cómo se decide qué se paga el 10 y qué el 20?

    ResponderBorrar
  24. Tengo una duda estoy calculando los montos facturados de 1 a 30 dias, ya tengo parametrizado mi excel con los dias que tomaron para pagarnos cada factura. Lo que necesito hacer es saber cuanto se cobro en el rango de 1-30 dias, el otro criterio es de 31-60 dias, el siguiente es de 61-90, el otro es de 91-120 dias y el ultimo de 121 en adelante

    ResponderBorrar
  25. Hay varias formas de hacerlo, dependiendo de como estén organizados tus datos. Suponiendo que parametrizado quiere decir que hay una columna con el número de días, La más práctica sería usando una tabla dinámica agrupando por esa columna (podés ver este post sobre el tema).

    ResponderBorrar
  26. Buenos días quiero calcular las quincenas transcurridas de esta forma

    Fecha Emi. Fecha Ven. Plazo
    11/07/2017 11/03/2018 8

    ResponderBorrar
    Respuestas
    1. Podrías sencillamente calcular la diferencia de días (restando) y dividirla por 15.

      Borrar

Nota: sólo los miembros de este blog pueden publicar comentarios.