Non sono certo di interpretare correttamente la tua tabella.
Supponiamo che le righe "straordinaria" siano legate alle ore OLTRE le Ordinarie mentre le righe "ordinaria" siano legate alle ore Ordinarie.
Poiche' le maggiorazioni sono diverse, per la stessa fascia oraria (es Notturno) a seconda che si tratti di orario Ordinario o ore Straordinarie dobbiamo avere una colonna che spiega se l'orario del lavoratore e' Diurna o Notturna: io ho usato la colonna G del foglio Timbrature, compilandola con D oppure N.
Bisogna inoltre avere due tabelle DefOrari diverse.
Supponiamo che l'orario notturno (ordinario o straordinario) sia quello compreso tra le 22:00 e le 6:00
Allora, prendiamo la prima parte della tua tabella ("feriale") e vediamo come si possono segmentare le ore e come gestire i risultati nel caso di orario Diurno
Ho detto che bisogna creare due DefOrari; dalla mia interpretazione di quanto detto finora, queste tabelle avranno questi due aspetti:
Fare click sull'immagine per vederla completa
La parte compilata (forse appropriatamente) e' solo quella in Verde; mappare l'area in grogio lo lascio a te.
La prima tabelle e' riferita all'orario di lavoro Diurno; la seconda all'orario Notturno.
Come vedi, l'orario giornaliero standard viene dichiarato 0h0m, per cui le tabelle splittano tutte le ore lavorate (non solo quelle "straordinarie").
Avendi due tabelle DefOrari, dobbiamo modificare la formula usata per calcolare i risultati; avendo usato la colonna G per indicare se l'orario e' Diurno o Notturno, la formula che useremo (ad esempio in H3:O3 del foglio Timbrature) sara':
- Codice: Seleziona tutto
=SE(G3="D";Xstra3($A3;$C3:$F3;Foglio1!$A$1:$I$11);SE(G3="N";Xstra3($A3;$C3:$F3;Foglio1!$A$31:$I$41);NON.DISP()))
Ricordo che bisogna selezionare tutte le celle in cui va inserita la formula (H3:O3, nell'esempio); scrivere la formula nella barra della formula; confermare con Contr-Maiusc-Enter, non il solo Enter.
Poi l'intera riga con formula va copiata verso il basso.
Ad esempio, con alcune timbrature di esempio, potremmo ottenere i seguenti risultati:
Fare click sull'immagine per vederla completa
Come si leggono i risultati:
MERC 2 genn: totale 13:30 ore lavorate; di cui 13:00 di "fascia 1" (6-22); e 0:30 di fascia 2 (notturno straordinario)
GIO 3 genn: totale 11:00 ore lavorate; di cui 1:00 in fascia 1 (dalle 6 alle 7 di Ven mattina); 3:00 in fascia 3 (18-22); 8:00 in fascia 4 (notturno 22-6)
VEN 4 genn: totale 13:00 ore lavorate; di cui 1 in fascia 3 (18-22); 8:00 in fascia 4 (notturno 22-6) e 4:00 in fascia 5 (diurno Sabato; infatti l'orario ipotizzato, 21:00 - 10:00, spazia fino a sabato mattina)
Come si "lavorano" i risultati.
Gli orari cosi' calcolati vanno lavorati con le maggiorazioni che corrispondono alla tua tabella.
Vedo che nella tabella ci sono piu' colonne, di cui non conosco il significato; usero' quindi quel che leggo nella prima colonna.
Ad esempio, alle ore del codice 1 (diurno 6-22), se relative a una giornata con orario Diurno, vanno sottratte le ore di lavoro standard, e alle rimanenti va applicata la maggiorazione 25% (prime 2 ore) e 35% le rimanenti.
Se invece relative a un orario Notturno allora al risultato non va sottratto 8:00 e si applicano direttamente le maggiorazioni del 25% o 35%
Una formula che applica dette "maggiorazioni" alle ore di tipo 1 contenute in I4 dovrebbe essere
- Codice: Seleziona tutto
=SE((I4-8/24*(G4="D"))>0;8/24*(G4="D")+SE((I4-8/24*(G4="D"))>(2/24);((I4-8/24*(G4="D"))-2/24)*1,35+2/24*1,25;(I4-8/24*(G4="D"))*1,25);I4)
Per l'interpretazione della formula, tieni presente che le varie componenti del tipo 8/24 serve per trasformare in formato orario Excel quel numero di ore (es 2,5/24 indicherebbe 2h30m)
Per il Tipo 2, se l'orario e' Diurno si tratta di straordinario notturno, cui va applicata direttamente la maggiorazione del 55%; per il contenuto di J4 bastera' la formula
- Codice: Seleziona tutto
=SE(G4="D";J4*1,55;0)
(il SE non sarebbe nemmeno necessario, visto che la tabella DefOrari per orario Notturno non utilizza il tipo 2)
Lascio a te individuare le formule che inseriscono le maggiorazioni agli altri "tipi" di ore lavorate
Le "ore maggiorate" cosi' calcolate (per "tipo") vanno poi sommate per calcolare il nuovo orario convenzionale, sulla cui base immagino dovrai calcolare poi delle retribuzioni.
Concludo pero' con una considerazione: si tratta palesemente di attivita' commerciale non di piccolo cabotaggio; ha senso affidarsi a una soluzione "amatoriale" o non avrebbe piu' senso affidarsi a una societa' di consulenza, che potra' preparare una soluzione probabilmente piu' solida e certamente su misura, e quindi piu' facile da applicare?
Ciao
Registrazione
di Simo1 » 03/06/19 09:58 
