Aggiornamento:
Su prezioso suggerimento di Federico Giacanelli che qui pubblicamente ringrazio, ho modificato la formula originale in modo che funzioni correttamente anche per periodi superiori ai 24 mesi, e ho corretto il post di conseguenza.
Paola lavora per una associazione professionale e si occupa delle iscrizioni, che iniziano in periodi diversi e valgono per periodi diversi. Anche l’ammontare della quota varia a seconda del tipo di socio.
Paola ha bisogno di imputare a ogni anno la parte di quota di iscrizione proporzionale ai mesi di validità di quell’anno. I dati rilevanti che estrae dal gestionale in un foglio di Excel sono la data di inizio, la data di fine e l’ammontare della quota:
Ti faccio un esempio: se un socio in data 01/06/2012 ha pagato $240 per 24 mesi, Paola vuole imputare $70 nel 2012, $120 nel 2013 e $50 nel 2014. Il primo passo è trovare il numero di mesi
- del primo anno fino a fine anno se finisce in un anno diverso altrimenti dalla data di inizio alla data di fine:
E2==SE(ANNO(C2)>ANNO(B2);13-MESE(B2);MESE(C2)-MESE(B2)+1) - dell’ultimo anno se maggiore del primo:
F2=SE(ANNO($C2)>ANNO($B2);MESE($C2);0) - degli anni intermedi se maggiori del primo e minori dell’ultimo:
=SE(ANNO($C2)>ANNO($B2);(ANNO(C2)-ANNO(B2)-1)*12;0)
Questa è la tabella risultante:
A questo punto bisogna aggiungere una colonna per ogni anno in cui calcolare la parte di quota da imputare, controllando se l’anno nella riga 1 è iniziale, finale o intermedio e moltiplicando la quota mensile (Amount / Total months) per il relativo numero di mesi. Se invece l’anno in questione è esterno all’intervallo, la cella viene lasciata vuota. La formula in I2, copiabile a destra con gli opportuni $, è la seguente:
=SE(ANNO($B2)=I$1;$D2/$H2*$E2;
SE(ANNO($C2)=I$1;$D2/$H2*$F2;
SE(E(I$1>ANNO($B2);I$1<ANNO($C2));$D2/$H2*12;””)))
Questa è la tabella risultante:
A questo punto è possibile nascondere le colonne da E a H, oppure eliminarle dopo aver copiato in I2 (con santa pazienza) le loro formule al posto dei relativi riferimenti di cella, ottenendo questa bella lenzuolata:
=SE(ANNO($B2)=E$1;$D2/(13-MESE($B2)+SE(ANNO($C2)>ANNO($B2);
MESE($C2);0)+SE(ANNO($C2)>ANNO($B2);
(ANNO($C2)-ANNO($B2)-1)*12;0))*(13-MESE($B2));
SE(ANNO($C2)=E$1;$D2/(13-MESE($B2)+SE(ANNO($C2)>ANNO($B2);
MESE($C2);0)+SE(ANNO($C2)>ANNO($B2);
(ANNO($C2)-ANNO($B2)-1)*12;0))*(SE(ANNO($C2)>ANNO($B2);
MESE($C2);0));SE(E(E$1>ANNO($B2);E$1<ANNO($C2));
$D2/(13-MESE($B2)+SE(ANNO($C2)>ANNO($B2);
MESE($C2);0)+SE(ANNO($C2)>ANNO($B2);
(ANNO($C2)-ANNO($B2)-1)*12;0))*12;””)))
E questa è la situazione finale:
Se ti serve, il file è questo: Esempio – mesi e anni tra due date. Se hai qualche altra soluzione più elegante, come ad esempio quella di Federico, fatti sotto.
