Usare calendario perpetuo con formule condizionali per turni di lavoro

Buon pomeriggio,
vorrei usare un calendario perpetuo al fine di poter gestire i turni dei dipendenti diversificando le gionate festive, feriali e super festive.
Il file:
1) in bianco le giornate feriali;
2) in arancio i sabati (che possono essere considerati festivi), evidenziati con formattazione condizionale con la seguente formula:

---

=O(GIORNO.SETTIMANA(G6;2)=6)

---

;
3) in rosso le domeniche (festivi) evidenziate con formattazione condizionale con la seguente formula:

---

=O(GIORNO.SETTIMANA(G6;2)=7)

---

;
4) in blu è evidenziato il santo patrono della città (foglio "Calendario" riga R27 - festivo) evidenziato con formattazione condizionale con la seguente formula:

---

=G6=Celendario!$R$27

---

;
5) in giallo i super festivi (foglio "Calendario" dalla riga R11 alla riga R26) evidenziati con formattazione condizionale con la seguente formula:

---

=CONFRONTA(G$6;Celendario!$R$11:$R$26;0)

---

.

Il problema:
Vorrei assegnare un determinato punteggio a seconda se il turno di servizio venga svolto in giornata feriale, festiva o super festiva con relative formule condizionali.
Esempio:
- se feriale punti: 1;
- se festivo punti: 2;
- se super festivo punti: 3.
Ovviamente se un lunedì è super festivo ha la priorità il punteggio 3 e non il punteggio 1, stessa cosa dicasi per con le giornate festive/super festive.

Allego il file che sto utilizzando con la speranza di essermi spiegato.
Potete darmi una mano?

Grazie

Mauro

Ciao MrFama
inserisci le seguenti formule nel Foglio Master Prospetto

in J9 =SE(E(G9>0;L9<=0;K9<=0;GIORNO.SETTIMANA(G$6)>1;GIORNO.SETTIMANA(G$6)<7);1;0)

in K9 =SE(E(G9>0;L9<=0;O(GIORNO.SETTIMANA(G$6)=1;GIORNO.SETTIMANA(G$6)=7));2;0)

in L9 =SE(G9>0;SE(SE.ERRORE(CONFRONTA(G$6;Celendario!$R$11:$R$26;0);0);3;0))

dette formule vanno copiate in tutti i giorni prestando molta attenzione ai rirerimenti di cella.
Purtroppo la struttura del tuo file non consente una formula da trascinare.

Prova e fai sapere. Ciao,
Mario

Ciao Marius44,
ti ringrazio per l'aiuto.
Ho inserito le formule che mi hai indicato come hai detto e a prima vista sembrano funzionare, mi manca però la giornata del santo patrono da inserire nelle giornate festive (quindi nella formula da mettere in K9), indicata nel foglio calendario alla colonna R riga 27.

Infatti inserendo le formule al giorno 02 maggio il servizio viene calcolato come feriale e non come festivo.

Mauro

Ciao Mauro
però un piccolo sforzo potresti farlo anche tu

volevo lasciarti il piacere di studiarci un po' su. Comunque la formula in K9 va implementata così:

=SE(E(G9>0;L9<=0;O(GIORNO.SETTIMANA(G$6)=1;GIORNO.SETTIMANA(G$6)=7;G$6=Celendario!$R$27));2;0)

dove il passo G$6=Celendario!$R$27 si riferisce proprio al giorno del Santo Patrono.

Ciao,
Mario

Mario, grazie.
Credi che non ci stia lavorando?
Sono 3 giorni che combatto con queste formule girandole e rigirandole e ci stavo girando ancora adesso....

Ciao Mauro
ovviamente scherzavo.
Stando sempre sulle formule o sui codici può venire, e spesso viene, il mal di testa.

Ciao e se hai bisogno ... chiama,
Mario

Buongiorno Marius44,
avevo capito stavi scherzando ^_*

Ti ringrazio per il tuo celere ed importante aiuto.
Ho apportato ancora una piccola modifica alle tue formule in quanto, se guardi il foglio calendario, noterai che le festività indicate non sono tutti super festivi.

Sto verificando che il tutto funzioni come deve.
Ti ringrazio ancora per il tuo aiuto.

Mauro

Buongiorno,
sono ancora qui a sottoporvi il mio lavoro.
Finchè inserisco il servizio (1) nelle colonne "Servizio" tutto sembra funzionare correttamente (le colonne "Fer." - "Fes." - "S. Fes."), calcolano il valore assegnato che viene sommato (con matrici) nelle colonne in coda (verdine e azzurre).
Quando, invece, vado ad inserire anche una particolare dicitura nelle colonne "Posizione" o "Motivazione" le matrici in coda (colonne verdine e azzurre) mi restituiscono #VALORE.
Qualcuno riesce a vedere l'errore che IO non vedo?

Grazie ancora a tutti.

Mauro

Ciao
non ho seguito la discussione, pertanto posso sbagliare; prova in IV9 da trascinare:

=MATR.SOMMA.PRODOTTO(($H$7:$IN$7>0)*(GIORNO.SETTIMANA($H$7:$IN$7;2)<6)*(CONTA.SE(Calendario!$R$5:$R$21;$H$7:$IN$7)<>1)*($K9:$IQ9>0))

comunque sei sicuro che funzioni anche per il giorno 31 ?

saluti

Ciao Mauro
prova a ragionare in maniera diversa.
Dopo aver completato la formula relativa ai SuperFestivi così

---

=SE(H9>0;SE(SE.ERRORE(CONFRONTA(H$6;Calendario!$R$5:$R$14;0);0);Calendario!$B$26;0);0)

---

cioè aggiungendo quello in rosso per evitare quel fastidiosissimo FALSO, devi fare la SOMMA delle rispettive colonne e cioè al posto della formula MATR.SOMMA.PRODOTTO (che, francamente, mi viene difficile sistemare) metti:

---

in IV9 =SOMMA(K9+S9+AA9+AI9+AQ9+AY9+BG9+BO9+BW9+CE9+CM9+CU9+DC9+DK9+DS9+EA9+EI9+EQ9+EY9+FG9+FO9+FW9+GE9+GM9+GU9+HC9+HK9+HS9+IA9+II9+IQ9)

---

che poi trascini per le prossime due celle a destra. Quindi selezioni le tre celle IV9:IX9 e copi fin dove serve. Vedrai che si sistemano anche quelle appresso.

Molto artigianale ma a me funziona.
Fai sapere. Ciao,
Mario

Dodo47,
ti ringrazio per l'aiuto....e proverò anche la tua soluzione ma ad occhio mi sembra ci sia qualcosa che stride, ma potrei sbagliarmi.

Marius44,
hai ragione, quel "FALSO" è inguardabile ma era un problema che mi ero ripromesso di aggiustare alla fine.

Per quanto concerne la somma, beh......la buona vecchia somma delle sole celle interessate non guasta mai ma....credo che si ponga il problema quando la giornata passa da feriale a festiva o a super festiva (bisogna rimettere mano alle formule), mentre con la matrce....il problema si dovrebbe poter ovviare.

Ma mi son preso 2 giorni di pausa, almeno questi me li son "rubati", pertanto se ne riparlerà da dopodomani....

Intanto vi ringrazio ancora per l'aiuto offertomi e porgo a Voi e a tutta la community un augurio di Buon Anno 2017

Mauro

Ciao Mauro
Credo che sia un falso problema. Le formule si riferiscono sempre alla giornata (Feriale, Festiva o SuperFestiva).

Quando ... rientri se ne riparla.

Ciao,
Mario

Buongiorno,
allora come promesso stamane, di buon mattino, mi sono rimesso al lavoro ed ho provato prima la soluzione proposta da dodo47 che sembra funzionare.
Non so cosa intendi con:

dodo47, 31/12/2016 12.28:

---

comunque sei sicuro che funzioni anche per il giorno 31 ?

---

Ho fatto diversi tentativi, sia di feriale che di festivo che di super festivo e il dato viene sempre calcolato nelle somme finali, hai avuto modo di verificare il contrario?

Marius44, avendo provato e testato la soluzione proposta da dodo47 per quando riguarda la matrice son passato ad aggiustare quel fastidioso "#FALSO".
Allego il file con le modifiche suggerite.

Buonasera a tutti,
sto lavorando ancora sul file e dopo essere andato un pochetto avanti ho iniziato a lavorare sul "Registro servizi" (per la maggior trasparenza possibile con il personale).
Ho prima di tutto creato un foglio "Master registro servizi", per semplificare le operazioni, foglio che verrà successivamente nascosto.
Da qui vorrei ricavare il "famoso" registro, ho provato con la funzione CERCA ma mi son reso conto che non da il risultato sperato (zona verdina del foglio "Registro servizi"). Ho poi provato con INDICE ma anche qui....la cosa risulta incompleta (zona rossastra del foglio "Registro servizi").

In pratica, dalla verifica del foglio “Master registro servizi”,quello che vorrei ottenere con la formula in argomento è questo:
- la formula controlla la riga corrispondente a ciascun dipendente e, pertanto, a ciascun turno svolto (indicato dal numero "1");
- nel foglio “Registro servizi” il numero "1" viene trasformato nella data corrispondente ma, anche se a distanza di diverso tempo le date devono risultare consecutive (insomma non devono esserci spazi vuoti come nalla zona verdina nè, tantomeno, #VALORE come nella zona rossastra).
Spero di essermi spiegato, come al solito allego il file in attesa di un vostro gentile aiuto.

Ciao Mauro
per quanto riguarda la zona in basso, prendiamo, ad esempio, Del Piero, la formula (ma anche tutte le altre) puoi aggiustarle così:
=SE.ERRORE(INDICE(SE('Master registro servizi'!$D13:$AH13=1;'Master registro servizi'!$D$10:$AH$10;"");0);"")
Con le aggiunte in rosso fai ... sparire tutto.

Per quanto riguarda la prima parte del quesito non capisco: cosa vuol dire "senza spazi"? Puoi spiegarmi. Grazie.
Ciao,
Mario

Ciao Mario ,
immaginavo di non essere stato troppo chiaro.
Quel che voglio dire è che se guardi il foglio "Registro servizi", nell'intestazione delle varie colonne, si parla di "Turno 1"; "Turno 2", "Turno 3", ecc. ecc., pertanto quello che dovrebbe accadere grazie alla funzione dovrebbe essere questo:

Qualifica	COGNOME	Nome	Turno 1	Turno 2	Turno 3	Turno 4
Mr.	DEL PIERO	Alessandro	02/01/2017	12/01/2017	23/01/2017	28/01/2017

EXCELFORUM........EUREKAAAAAAAAAAAA

Ciao Mauro

dalle prime emoticon e dallo scritto "sembra" che tu abbia risolto.
Poi, dopo, vi è anche il pianto.

Ma allora, hai risolto oppure no? e se si, come spero, puoi condividere la soluzione?

Ciao,
Mario

Ciao Mario,
in effetti si....sono riuscito a trovare la soluzione al mio problema.
Il pianto è semplicemente dovuto alla "fatica" che ho fatto ed ai milioni di tentativi testati per arrivare alla soluzione.

Non ho postato prima la soluzione perchè convocato dalle MIE donne di casa che si son lamentate del mio continuo "lavorare"

In pratica, rispetto a quanto postato in precedenza, ho modificato il "punto di vista" per arrivare al risultato.
In pratica in "Master registro servizi" non compaiono più gli (1) ma direttamente le date in cui il personale svolge il suo turno di servizio.
Dopodichè, con questa bella funzione infilata nelle celle del "Registro servizi":

---

=SE.ERRORE(INDICE('Master registro servizi'!$D12:$AH12;AGGREGA(15;6;(RIF.COLONNA('Master registro servizi'!$D$12:$AH$12)-RIF.COLONNA($D$12)+1)/('Master registro servizi'!$D12:$AH12<>"");COLONNE($D$12:D$12)));"")

---

sono arrivato alla soluzione del problema.

Allego il file modificato, così se dovesse essere utile a qualcuno....anche come semplice riferimento...

Grazie a Mario e a Dodo47 per l'aiuto datomi.