miércoles, 20 de enero de 2010

Cálculo de diferencias de días.

Aplicaremos hoy algunas funciones generales para poder calcular diferencias entre fechas, de acuerdo a la consulta de un lector:
...estoy tratando de crear una tabla que me permita calcular lo intereses de mora causados por una deuda desde un rango de fechas. Las tasas son variables mes a mes, por tanto la liquidacion debe hacerse por días.
Quisiera saber si hay una fórmula que me permita, dado el rango de fechas, saber cuantos días han pasado por cada mes; es decir, si le doy un rango del 01-nov-09 hasta el 1-ene-10 me deberia reflejar 30 dias en nov, 31 en diciembre y 1 en enero...

Este lector nos propone el cálculo de unos intereses de demora de una deuda sabiendo su fecha inicial y su fecha de vencimiento, así como los diferentes tipos aplicables a la deuda en cada uno de los meses que transcurren entre ambas fechas.
Por comodidad, ya que no lo especifica, entenderé que los intereses de cada mes no son acumulables para el cálculo del mes siguiente.
Suponemos el siguiente ejemplo:


Observamos que tenemos una deuda de 1.000 eur, con unas fechas inicial de 01/11/2009 y de vencimiento a 01/12/2010, en la tabla vemos los distintos tipos de interés aplicables a cada uno de los meses.
Nuestro trabajo consistirá en determinar cuántos días corresponde atribuir a cada mes dentro del intervalo de fechas 01/11/2009 y 01/01/2010.
He asignado un nombre a cada una de las fechas:
Fini ='Int demora'!$B$2
Fvcto ='Int demora'!$B$3
Sabemos que el número de días naturales (no comerciales!!) transcurridos es
=Fvcto-Fini
es decir, 61 días naturales.
En primer lugar voy a discriminar aquellos meses del listado que no aplican al rango de fechas dado; para ello aplicaré la siguiente función:
=SI(A8=Fini;A8<=Fvcto);B8;0))
valor que emplearé para el cálculo diario de intereses en una entrada posterior.



con esto conseguimos que sólo los meses que comprendan días entre la fecha inicial y la de vencimiento tengan un valor para el tipo de interés de mora.
Llegamos entonces a la última parte de la consulta, calcular los días asociados a cada mes dentro del rango de fechas.
Sabiendo que las fechas del listado, aunque tengan un formato 'mmm-aa' corresponden a fechas '01/mm/aaa', podremos aprovecharlas para situar nuestra 'Fecha inicial' y 'Fecha de vencimiento' temporalmente vinculado a éstas. Es decir, cada Fini y Fvcto es mayor o menor que cada fecha del mes.
La fórmula a ejecutar será:
=SI(Y(A8>=Fini;A8<=Fvcto);MIN(FIN.MES(A8;0);Fvcto)-MAX(Fini;FIN.MES(A8;-1));0)
la pregunta o prueba lógica consiste en aclarar si la fecha que tenemos del mes, i.e., 01/mm/aa se encuentra dentro del intervalo (Fini, Fvcto), ya que si esto es cierto corresponderá aplicar un interés de demora por algunos días; días que calculamos con el argumento verdadero de la función SI:
MIN(FIN.MES(A8;0);Fvcto)-MAX(Fini;FIN.MES(A8;-1))
hay que prestar algo de atención al uso de la función FIN.MES, ya que al combinarla con las funciones MIN y MAX obtendremos el correcto cálculo de días trasncurridos en cada mes; la primera parte de la fórmula
MIN(FIN.MES(A8;0);Fvcto)
devuelve la menor entre la fecha final del mes analizado o la 'Fecha de vencimiento' de la deuda; con la segunda parte
MAX(Fini;FIN.MES(A8;-1))
obtengo la mayor entre la 'Fecha inicial' y la fecha final del mes anterior al estudiado.

>

Por ejemplo, para el caso de cálculo de días del mes de enero 2010; al cumplir la prueba lógica de la función SI definida, aplica el valor de verdadero, es decir, calcula la fecha menor entre 31/01/2010 y 'Fecha vencimiento' (=01/01/2010) y la resta por la mayor entre la 'Fecha inicial' y 31/12/2009 (=31/12/2009); por tanto ejecuta la diferencia entre las fechas 01/01/2010 y 31/12/2009, esto es un día.
Con este ejercicio contestaríamos la consulta del lector. La parte final de cálculo de los intereses por días lo explicaré en una entrada posterior.

13 comentarios:

  1. Hola,
    a ver si me puedes ayudar con mi problema, yo parto de un intervalo de fechas, q pueden iniciarse o finalar en cualquier día del mes, por estas tres linéas de datos en columnas A1-B1
    05/12/1965-14/04/1995
    10/05/1995-26/02/1996
    22/03/1996-26/10/2010

    Lo q necesito es saber los días por mes de cada año en esas tres fechas. Hasta ahora he conseguido descomponer esas tres líneas por año, desde el 65 al 2010, pero saber los días por mes, nada...

    He visto q es un problema común en Access:
    http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/17814/converting-single-record-with-date-range-into-multiple

    También me han dicho q una vez descompuesto en años, se puede calcular por medio de una tabla dinámica...

    Gracias, a ver si me puedes ayudar.

    ResponderEliminar
  2. Hola Salvaje,
    he navegado por la durl que indicas, pero no acabo de ver qué pretendes de esos intervalos de fechas.
    No se si quieres saber cuántos días, meses y años hay entre cada una de esas fechas u otra cosa.
    Echa un vistazo a esta entrada:
    http://excelforo.blogspot.com/2010/05/calcular-la-edad-con-excel.html
    En esta se define un conteo de días, meses y años transcurridos entre dos fechas...
    Saludos

    ResponderEliminar
  3. Hola,
    bueno, lo q hacen ahí es descomponer un intervalo de fecha (05/12/1965-14/04/1995) en varios "registros" de año en año.

    Yo esto lo consegui, pero lo q necesito es saber, los días q hay en cada mes de este intervalo de fechas (05/12/1965-14/04/1995).

    Ya lo tengo descompueso por años, pero no sé si aplicando una tabla dinámica se puede sacar los días en cada mes de cada año.

    Para el intervalo q te pongo, ahora mismo tengo lo siguiente (ya descompuesto):

    FECHA1 FECHA2 AÑO2 DIAS1
    05/12/1965 31/12/1965 1965 27
    01/01/1966 31/12/1966 1966 365
    01/01/1967 31/12/1967 1967 365
    01/01/1968 31/12/1968 1968 366
    01/01/1969 31/12/1969 1969 365
    01/01/1970 31/12/1970 1970 365
    01/01/1971 31/12/1971 1971 365
    01/01/1972 31/12/1972 1972 366
    01/01/1973 31/12/1973 1973 365
    01/01/1974 31/12/1974 1974 365
    01/01/1975 31/12/1975 1975 365
    01/01/1976 31/12/1976 1976 366
    01/01/1977 31/12/1977 1977 365
    01/01/1978 31/12/1978 1978 365
    01/01/1979 31/12/1979 1979 365
    01/01/1980 31/12/1980 1980 366
    01/01/1981 31/12/1981 1981 365
    01/01/1982 31/12/1982 1982 365
    01/01/1983 31/12/1983 1983 365
    01/01/1984 31/12/1984 1984 366
    01/01/1985 31/12/1985 1985 365
    01/01/1986 31/12/1986 1986 365
    01/01/1987 31/12/1987 1987 365
    01/01/1988 31/12/1988 1988 366
    01/01/1989 31/12/1989 1989 365
    01/01/1990 31/12/1990 1990 365
    01/01/1991 31/12/1991 1991 365
    01/01/1992 31/12/1992 1992 366
    01/01/1993 31/12/1993 1993 365
    01/01/1994 31/12/1994 1994 365
    01/01/1995 14/04/1995 1995 104

    ResponderEliminar
  4. Bueno, entonces usaríamos la función FIN.MES y FECHA.MES para determinar por diferencias esos días.
    Claro que si quieres automatizar ese cálculo deberás programar una macro.
    En próximos días subiré un ejemplo explicando ambos procesos.
    Slds

    ResponderEliminar
  5. Hola!! Estoy intentando aplicar las formulas de este ejemplo para el caso de semestres en lugar de meses pero no se que cambios realizar en las formulas para ese caso, me lo podrías explicar?
    Saludoss

    ResponderEliminar
    Respuestas
    1. Hola!,
      a ver, la clave del asunto es emplear la función FIN.MES correctamente. Supondre que tu disposición de fechas es similar a la del ejemplo, pero en lugar de intervalos mensuales los tienes semestrales como indicas; esto es importante, ya que el cálculo del ejemplo realmente opera sobre dichas fechas.
      La modifación pasará entonces por sustituir la función FIN.MES(fecha;0) por FIN.MES(fecha;6), el resto quedaría tal cual.
      Pruébalo y me comentas si te surge alguna cuestión.
      Un saludo

      Eliminar
  6. Hola. Tu solucion es muy buena, de todo lo que he mirado es la mejor con diferencia, pero hay una cosa que no me funciona. Si en vez de poner la fecha de inicio 01/11/2009 pones (por ejemplo) 16/11/2009 no te calcula los dias que transcurren desde el 16/11 hasta el 30/11. No se si estoy haciendo algo mal...
    Muchísimas gracias por anticipado.

    ResponderEliminar
    Respuestas
    1. Hola, muchas gracias por tus palabras... Es normal que no funcione, el informe está diseñado para operar sin tener en cuenta los días iniciales, esto es siempre considerando un inicio como 01/mm/aaaa. Para determinar también los días transcurridos en el mes, e incorporarlos al cálculo habría que reconstruir la fórmula.
      Para incorporar en el cálculo esta nueva condición, se me ocurre algo sencillo.La fórmula para el rango D8:D14 sería:
      =SI(MIN(FIN.MES(A8;0);Fvcto)-MAX(Fini;FIN.MES(A8;-1))>=0;MIN(FIN.MES(A8;0);Fvcto)-MAX(Fini;FIN.MES(A8;-1));0)

      y para el rango C8:C14:
      =SI(Y(FECHA(AÑO(Fini);MES(Fini);1)<=A8;A8<=Fvcto);B8;0)

      Un saludo

      Eliminar
  7. Muchas gracias Ismael, es justo lo que necesitaba. Gran blog, espero que sigas así.

    Saludos!

    ResponderEliminar
  8. Cuantos Viernes han transcurrido desde el primero de abril de 1997 al 14 de Febrero de 2014 ? Se preguntaran ¿Para que se requiere dicha información?.Cuando mi niño entró ha estudiar a los tres años, todavia no se ubicaba bien en el tiempo, debía aprender que era lunes o jueves, que eran las semanas, los meses. De una manera practica y agradable comenzó a reconocer los Viernes y como estaba ubicado dicho dia en la semana. Desde la fecha arriba escrita mi niño recibe TODOS LOS VIERNES, sin faltar uno solo su "Baul de las sorpresas" que es un baulito en el cual coloco dulces, dinero y regalitos sencillos. Quiero saber si hay una formula para saberlo o simplemente debo tomar almanaque tras almanaque y contar los viernes de cada año.Por eso quiero saber cuantos Baules he podido brindarle a mi niño, y si es una cifra especial 890 o 1000 o 888 hacerlo muy especial ! Gracias

    ResponderEliminar
    Respuestas
    1. Hola Javier,
      prueba con la matricial (validando la fórmula presionando Ctrl+Mayusc+Enter en lugar de sólo Enter)
      =SUMA(SI(DIASEM(D3+FILA(INDIRECTO("1:"&D4-D3));2)=5;1;0);1)
      suponiendo en D3: 1/4/1997
      y en D4: 14/2/2014

      salen un total de 882 viernes..

      Saludos

      Eliminar
    2. Gracias Ismael ! El "Baúl de las sorpresas" 888 esta próximo. Lo voy ha llenar especialmente para mi niño...................mi niño de 20 años !

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.