Calcular días por años entre dos fechas

martes, marzo 04, 2014

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:

tabla de fechas


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))

tabla de datos

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"

formulario auditoría de fórmulas

En este video comento el proceso


4 comments:

Gonzalo,  05 marzo, 2014 21:04  

Muchisimas gracias, un gran trabajo y muy bien explicado.

Un saludo

José Manuel Agundis 07 marzo, 2014 03:20  

Maestro ¡Que buen ejercicio pero sobre todo la evaluación de la fórmula!

Emilio Carmona 14 mayo, 2014 17:03  

Una formula simplemente elegante. Me salta una idea: puede emplearse el principio de la formula para "particionar en el aire" una cantidad cualquiera (gramos, horas, metros)?

Jorge Dunkelman 16 mayo, 2014 14:12  

Bien, no me queda claro qué es "particionar en el aire" (¿al vuelo?) pero supongo que si. Te sugiero que me mandes un ejemplo por mail privado (ver el enlace Ayuda, en la parte superior del blog).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP