fijar criterios de acuerdo a un rango de fechas

Recibí una consulta mas que interesante: mi lector debe asignar en una celda determinados porcentajes (o valores, para el caso es lo mismo), de acuerdo a la diferencia existente entre dos fechas. Por ejemplo:

Pongamos una fecha cualquiera en C2: si dicha fecha está entre el 01/01/2011 y el 15/01/2011 una celda de Excel tendrá que devolver 10%. Si la misma se encuentra entre el 16/01/2011 y el 31/01/2011, la celda devolverá 20%. A simple vista y luego de un rápido análisis, nos vemos en la tentación de utilizar la funcion =SI(), colocando dentro de ella las distintas condicionales. Pero... ¿cuantos SI() deberíamos anidar? Cuando hablamos de pocos argumentos la cuestión se torna sencilla, aunque si necesitamos comparar muchas fechas, SI() se tornará por demás de engorroso y difícil de manejar. Solo mencioné a Enero/2011 y lo arreglamos fácil.... ¿pero si esta comparación se extiende por varios meses?

en C2 tengo la fecha 12/01/11  ¿Como hacemos para determinar que se encuentra entre el 01/01 y el 15/01 y, en base a ello, que nos arroje un determinado valor?

[+/-] Ver el resto / Ocultar

Una de las posibles soluciones, sin hacer uso de VBA y sus macros, viene dada por la función =COINCIDIR(valor, matriz, [tipo de coincidencia]) y su poco conocido tercer ([opcional]) argumento. Hagamos memoria: COINCIDIR() busca un valor (el primer argumento) dentro de una matriz (segundo argumento) y me devuelve su posicion:

busqué a "victoria" en el rango (matriz) A1:A6 y la función me devolvió 3, ya que ocupa el tercer lugar dentro de la matriz.

El tercer argumento, opcional y que no utilicé en el ejemplo, puede ser cualquiera de estos: 0, 1, -1 y dependerá de nuestras necesidades. Con 0 le indicaremos que debe buscar el valor exacto. Con 1 "el mayor valor que es igual o menor al valor buscado" y con -1 "el menor valor que es mayor o igual al valor buscado". Para 1 los datos deben estar ordenados de forma ascendente y para -1 descendente: ojo con este punto, ya que de lo contrario obtendremos errores no deseados.

Habrán visto que no fuí muy claro en la explicación.... pero en la práctica será todo mas fácil, no olvidando (obviamente) lo esencial que resulta llevar a cabo ejemplos de este tipo.

Para resolver el problema planteado por mi lector colocaremos en A1 la fecha 01/01/2011 y completaremos la serie, hacia abajo, hasta donde necesitemos. Para el ejemplo lo hice hasta el 04/02/2011, pero bien podría haberlo hecho finalizar el 31/12/2011, es decir, abarcar todo el año. Y allí es donde resultaría imposible usar la función =SI(), ya que deberíamos anidar decenas de ellas. 

la tabla llega hasta el 04/02/2011, pero muestro menos para no ocupar tanto espacio. en el archivo que les dejo la tendrán completa.

Ahora armo una tabla auxiliar, que contendrá las fechas entre las cuales deseo evaluar y los porcentajes que aplicaré:

La tabla de arriba tiene la siguiente interpretación: si la fecha ingresada en C2 se encuentra entre el 01/01 y el 15/01, se aplicará un 10%, si está entre el 16/01 y el 31/01 el 20% y así sucesivamente. Y todo lo lograremos con dos funciones.

En C4 ponemos: =COINCIDIR(C2;E1:E6;1) para obtener este resultado:

Aquí estoy diciendo: buscame el valor de C2 (15/01/11) en el rango E1:E4. Si observan detenidamente dicho rango, ese valor no existe en la matriz. Y si no existe ¿por que me dice que está en la posición 1?
Cambiemos la fecha en C2 y observemos:

Y sigue... el 25/01 no se encuentra en la matriz E1:E4, pero mi función me está diciendo que ocupa la segunda posición ¿por qué? Por el tercer argumento, nada mas ni nada menos. Recordemos que si ese tercer argumento es 1 le estoy diciendo "dame el  mayor valor que es menor o igual al valor buscado". En este caso, el mayor valor que es menor al 25/01 corresponde al 16/01 (que sí está en el rango/matriz) y me devuelve su posición dentro de ella, o sea, 2. ¿Se entiende la lógica? Probemos con otra fecha:

El 04/02 no existe en el rango E1:E4, entonces COINCIDIR() me está dando la posición del 31/01, ya que es el "mayor valor, menor al valor buscado". Y así podemos seguir con todo el año (o años), que obtendremos el mismo maravilloso y útil resultado.
Al lado de cada fecha puse un porcentaje, que es el que deberemos aplicar según en que rango de fechas se encuentre "valor buscado". Debo reflejar ese porcentaje en C4 y, habiendo muchas formas de hacerlo, utilizaré la mas sencilla: la función INDICE(matriz, número de fila, [numero de columna]). Para que sea mas fácil de comprender, primero lo haré en otra celda (utilizando como nro de fila el resultado de C4) y luego les muestro lo mismo, pero con ambas funciones anidadas: =INDICE(F1:F4;C4)

Y así obtengo que: el 04/02 está entre el 31/01 y el 15/02. Como el 04/02 no está en la matriz, me devuelve la posición correspondiente al 31/01, 3er posición (en C4), resultado que utilizo para la función INDICE() y así lograr (al fin) colocar el respectivo porcentaje de descuento o aumento solicitado por mi lector.
Si quisiéramos omitir el uso de una celda auxiliar, hay que anidar:
=INDICE(F1:F4;COINCIDIR(C2;E1:E4;1))

Desde ya que quedará a criterio y necesidad de cada usuario la modificación del rango E1:E4 y los porcentajes de acuerdo al trabajo que deban realizar en su proyecto.
Ahora eliminamos la columna A, completa (ya que era para una mejor visualización del problema, pero carece de cualquier otra utilidad) y el libro queda listo para ser utilizado.
Aquí está el archivo. Vimos otra forma de trabajar con fechas, sin recurrir a las macros.
Gracias a todos mis lectores y especialmente a aquellos que a diario me hacen llegar palabras de agradecimiento, que a la vez son muy reconfortantes.