Un lector preguntaba por una fórmula para obtener la fecha inmediata anterior a otra dada, según una condición:
Tal y como se muestra en la imagen:
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())
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).!!
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:
| [...] necesito hallar la fecha de compra de unos consumos, tiene que ser la próxima anterior[...] |
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())
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).!!
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:
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
ResponderEliminarHola,
Eliminarsupongamos 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