Calcular días por años entre dos fechas

Supongamos una lista de fechas de inicio y finalización de tareas (o contratos o cualquier otra actividad) que se extienden a más de una año, como en este ejemplo:

Podemos ver, por ejemplo, que la Tarea 1 empieza en el 2013 y termina en el 2015. ¿Cómo hacemos para calcular cuántos días caen en cada año?

Para resolver este problema vamos a usar una técnica no tan conocida: crear dentro de la fórmula un vector implícito (o "virtual"). El concepto quedará más claro cuando expliquemos la fórmula.

La columna "Total de días" muestra el total entre ambas fechas (incluida el día del comienzo) usando la fórmula =SIFECHA(C4,D4,"d")+1

Para poder calcular cuantos días caen dentro de cada año (2013,2014,2015), vamos a agregar tres columnas en nuestra tablam una para cada año, y en la celda F4 ponemos esta fórmula (que luego copiamos al resto de la tabla):

=SUMAPRODUCTO(--(AÑO($C4+FILA(INDIRECTO("1:"&($D4-$C4+1))))=F$3))

Vamos a "disecar" la fórmula de adentro hacia afuera:

• La expresión "($D4-$C4+1)" calcula el total de días transcurridos entre el principio y el fin de la tarea. Es equivalente al cálculo en la celda E4.
• La expresión INDIRECTO("1:"&($D4-$C4+1)) crea una referencia al rango "1:750". Al usar esta expresión como argumento de la función FILA en forma matricial (al estar dentro de la función SUMAPRODUCTO), obtenemos un vector con los valores 1,2,3,...,750
• Al sumar a $C4 este vector en ($C4+FILA(INDIRECTO("1:"&($D4-$C4+1))), obtenemos un vector de fechas día por día que comienza el 06/07/2013 y termina con el 25/07/2015
• La función AÑO extrae de cada fecha el año correspondiente, por lo que obtenemos un vector de años con 750 puntos.
• Este vector los comparamos con el año que aparece en la celda F$3, por lo que obtenemos un vector de valores VERDADERO o FALSO (si se cumple o no la condición).
• La doble negación a la izquierda de la función AÑO convierta los valores VERDADERO en 1 y los FALSO en 0. Igualmente podríamos haber multiplicado la expresión por 1.
• Finalmente SUMAPRODUCTO suma todos los valores del vector que cumplen con la condición. Recordemos, como puse más arriba, que SUMAPRODUCTO se comporta en forma matricial.

Podemos visualizar el proceso del cálculo usando la herramienta "Evaluar fórmula" en "Fórmulas-Auditoría de fórmulas"

En este video comento el proceso