Fare calcoli in base ad un cambio di data

Ciao
devo calcolare delle commissioni che variano in base al periodo.
Nello specifico si tratta di un hotel che dovrà riconoscere commissioni in base ai soggiorni nei vari periodi.
Per esempio per soggiorni in giugno 15%
in luglio 10%
in agosto 5%
in settembre 15%

il problema si presenta quando i soggiorni sono a "cavallo" di due mesi
Allego un file di esempio dove vorrei che nella colonna H (H8:H20) venissero calcolate le commissioni.
Naturalmente l'elenco dei clienti potrebbe essere più lungo di quello in esempio

Buongiorno

in H8 da trascinare in basso

=SOMMA(MATR.SOMMA.PRODOTTO((MESE(RIF.RIGA(INDIRETTO(B8&":"&C8)))={6\9})*(F8/(C8-B8)))*15%;MATR.SOMMA.PRODOTTO((MESE(RIF.RIGA(INDIRETTO(B8&":"&C8)))=7)*(F8/(C8-B8)))*10%;MATR.SOMMA.PRODOTTO((MESE(RIF.RIGA(INDIRETTO(B8&":"&C8)))=8)*(F8/(C8-B8)))*5%)

bella formula Danilo....

forse c'è da escludere uno dei due giorni (arrivo o partenza) dal calcolo o almeno io farei così nel calcolo, in quanto sono normalmente considerati mezze giornate.

Es: ultimo cliente (Bluetti) la formula moltiplica x 8 considerando sia il 23 che il 30 agosto....Ma ci sta che mi sbagli.

cari saluti

Ciao Dodo
in mancanza dei risultati attesi io ho considerato tutti i giorni dei periodi da prendere in esame....eventualmente si può modificare

Attendiamo lumi dall'utente

Saluti

dodo47, 31/05/2020 11:04:

---

bella formula Danilo....

forse c'è da escludere uno dei due giorni (arrivo o partenza) dal calcolo o almeno io farei così nel calcolo, in quanto sono normalmente considerati mezze giornate.

Es: ultimo cliente (Bluetti) la formula moltiplica x 8 considerando sia il 23 che il 30 agosto....Ma ci sta che mi sbagli.

cari saluti

---

Le convenzioni alberghiere prevedono che si calcoli il giorno di arrivo e non quello di partenza
quindi nell'esempio del signor Bianchi (arrivo 28/06 e partenza 05/07) si devono calcolare i giorni 28-29-30/06 al 15% e i giorni 1-2-3-4/07 al 10% il totale dei giorni soggiornati è 7

Sarebbe possibile anche avere una spiegazione della formula?😊

ciao

solo per curiosità e senza voler entrare nella discussione per la quale sei in "mani preziose"....ma la tua attività per il riconoscimento delle provvigioni riguarda solo quei quattro mesi ?

Ciao
faccio le somme delle percentuali da calcolare per i giorni di soggiorno quindi prendendo in esame la prima parte (riferito a riga 8)

MATR.SOMMA.PRODOTTO((MESE(RIF.RIGA(INDIRETTO(B8&":"&C8)))={6\9})*(F8/(C8-B8)))*15%

dove calcolo i giorni di soggiorno di giugno e settembre

con questa parte

MESE(RIF.RIGA(INDIRETTO(B8&":"&C8)))

estraggo i numeri del mese tra le due date se evidenzi e fai F9 ottieni

{6.6.6.6.6.6.6.6.6.6.7.7.7.7.7}

poi gli dico di verificare se appartengono a giuno (6) o settembre (9) ={6\9} in quanto hanno la stessa percentuale ottenendo

{VERO\FALSO.VERO\FALSO.VERO\FALSO.VERO\FALSO.VERO\FALSO.VERO\FALSO.VERO\FALSO.VERO\FALSO.VERO\FALSO.VERO\FALSO.FALSO\FALSO.FALSO\FALSO.FALSO\FALSO.FALSO\FALSO.FALSO\FALSO}

ora moltiplico per

(F8/(C8-B8))

dove F8 è il totale del soggiorno diviso i giorni di soggiorno ottengo il costo/giorno

ora dove c'è un VERO ti restituisce l'importo... il MATR.SOMMA.PRODOTTO somma questi importi e infine moltiplicando per la percentuale relativa al/ai mesi otteniamo il dato da passare al SOMMA

per quanto riguarda il giorno in meno da calcolare una ipotesi è


=SOMMA(MATR.SOMMA.PRODOTTO((MESE(RIF.RIGA(INDIRETTO(B8&":"&C8-1)))={6\9})*(F8/((C8-1)-B8)))*15%;MATR.SOMMA.PRODOTTO((MESE(RIF.RIGA(INDIRETTO(B8&":"&C8-1)))=7)*(F8/((C8-1)-B8)))*10%;MATR.SOMMA.PRODOTTO((MESE(RIF.RIGA(INDIRETTO(B8&":"&C8-1)))=8)*(F8/((C8-1)-B8)))*5%)


se non dovesse riportare i risultati attesi allega il file con i risultati da ottenere inseriti a mano

Ecco qui in allegato il file con i risultati attesi

puoi dire come esce fuori 82,40 di Bluetti (riga 20) ?
grazie

Ciao
modifica cosi

=SOMMA(MATR.SOMMA.PRODOTTO((MESE(RIF.RIGA(INDIRETTO(B8&":"&C8-1)))={6\9})*(F8/(C8-B8)))*15%;MATR.SOMMA.PRODOTTO((MESE(RIF.RIGA(INDIRETTO(B8&":"&C8-1)))=7)*(F8/(C8-B8)))*10%;MATR.SOMMA.PRODOTTO((MESE(RIF.RIGA(INDIRETTO(B8&":"&C8-1)))=8)*(F8/(C8-B8)))*5%)


Credo che in riga 20 hai sbagliato a fare il calcolo a mano

allego il file su alcune celle c'è una differenza di 2 CENT.....

Perfetto! Si nella riga 20 ho sbagliato io a fare il calcolo.

Chiedo:
essendo che le commissioni possono variare negli anni
ho eliminato il testo in H2-I2-J2-K2 e ho sostituito nelle formule la percentuale con il riferimento delle celle noto però che giugno e settembre non paiono essere calcolati separatamente potrebbe accadere in futuro che le commissioni siano differenti tra questi due mesi

Chiedo in ultimo
essendo che a volte anche le date nelle quali vengono applicate le commissioni sono variabili e non sempre sono riferite alla fine del mese sarebbe possibile modificare le celle H1-I1-J1-K1 mettendo di anno in anno la data?
Per me è indifferente se funziona meglio o è più semplice come formula considerare come un "fino al 27/06" oppure "dal 28/06 in poi"

Ciao
da H3 a K3 metti le date di riferimento (no "fino al")

quindi la formula diventa


=SOMMA((MATR.SOMMA.PRODOTTO(--(RIF.RIGA(INDIRETTO(B8&":"&C8-1))<=$H$3))*(F8/(C8-B8)))*$H$2;(MATR.SOMMA.PRODOTTO((RIF.RIGA(INDIRETTO(B8&":"&C8-1))>$H$3)*(RIF.RIGA(INDIRETTO(B8&":"&C8-1))<=$I$3))*(F8/(C8-B8)))*$I$2;(MATR.SOMMA.PRODOTTO((RIF.RIGA(INDIRETTO(B8&":"&C8-1))>$I$3)*(RIF.RIGA(INDIRETTO(B8&":"&C8-1))<=$J$3))*(F8/(C8-B8)))*$J$2;(MATR.SOMMA.PRODOTTO((RIF.RIGA(INDIRETTO(B8&":"&C8-1))>$J$3)*(RIF.RIGA(INDIRETTO(B8&":"&C8-1))<=$K$3))*(F8/(C8-B8)))*$K$2)

Ciao a tutti
Complimenti a DANILOFIORINI per la formula.

Ho ampliato il file da 4 a 6 date differenti (anche se non servono, bisogna mettere lo stesso le ultime due date).