martes, 20 de septiembre de 2016

Fecha Inmediata Anterior a Otra

Un lector preguntaba por una fórmula para obtener la fecha inmediata anterior a otra dada, según una condición:
[...] necesito hallar la fecha de compra de unos consumos, tiene que ser la próxima anterior[...]
Tal y como se muestra en la imagen:

Fecha Inmediata Anterior a Otra



El objetivo es claro, para cada fecha y artículo del segundo rango D3:E8 debemos recuperar la fecha correspondiente anterior más próxima del primer rango A3:B8.

Para ello construimos la siguiente matricial que insertamos en F3:
=E3-MIN(SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())))
(recuerda validarla presionado Ctrl+Mayuscula+Enter!!)

A continuación copiaremos y pegaremos al resto de celdas F4:F8.


¿Cómo funciona esta fórmula matricial?.
Nos basamos en el cálculo de diferencias entre fechas para llegar a la más próxima anterior; esto es, con la parte más profunda de la fórmula, llegamos a conseguir unas fechas para aquellos casos en los que coincide el artículo.. así discriminamos las fechas que no nos interesan:
SI($A$3:$A$8=D3;$B$3:$B$8;HOY())

Fecha Inmediata Anterior a Otra


Sobre esta fecha obtenida aplicamos un nuevo condicional para calcular la diferencia con la fecha de referencia:
=SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY()))

discriminando además aquellas diferencias negativas (solo nos quedamos con diferencias positivas); esto es así por que buscamos sólo fechas anteriores (una diferencia negativa indicaría que la fecha es posterior).!!

Fecha Inmediata Anterior a Otra



Si de esas diferencias obtenidas nos quedamos con el valor mínimo:
MIN(SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())))
y ese valor mínimo lo restamos a la fecha de referencia, obtenemos la Fecha buscada:
=E3-MIN(SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())))

Es decir, sobre el ejemplo de la imagen (fecha de referencia 15/07/2016), desde la fecha de referencia nos desplazamos 10 días antes... lo que nos lleva a la fecha de origen: 05/07/2016

Tal como se muestra en la imagen:

Fecha Inmediata Anterior a Otra

2 comentarios:

  1. Hola, tengo un problema que no logro resolver. Mi idea es crear una formula para poder obtener, a partir del ingreso de una fecha en el formato xx/xx/xx, la fiscalia que se encontraba de turno en ese día. En efecto, las fiscalias penales se organizan por turnos: por ejemplo, del 01/01/2014 al 10/03/2014 la fiscalia primera estuvo de turno, del 11/03/2014 al 25/05/2014 la fiscalia segunda... y así el resto del año, todos los años de forma diferente pero continuada. gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      supongamos tienes en la columna A las fechas iniciales de cada intervalo (por ejemplo 1/1/2014), en la columna B las fechas finales (por ejem, 10/3/2014) y en la columna C en texto la fiscalía (por ejemplo Fiscalía I).
      En una celda cualquiera, E2 introduces la fecha dd/mm/aaaa que desees y en la celda F2:
      =BUSCARV(E2;A:C;3;1)

      OJO, necesario que las fechas sean correlativas, como indicas, y además estén ORDENADAS!!!

      Slds

      Eliminar