Funzione per calcolo ore straordinario

[Anthony47]

Con “un po’ di ritardo” rispondo a un paio di quesiti che avevano come oggetto lo split dello straordinario pesandolo in modo diverso a seconda dell’ orario e del giorno di lavoro.
Vedere ad esempio
viewtopic.php?f=26&t=66808
viewtopic.php?f=26&t=67341

Allo scopo ho creato la “funzione” XSTRA che calcola quanto richiesto, anche se in modo piu’ articolato.

Non conoscendo la normativa che c’ e’ dietro, e anche per dare una maggiore flessibilita’ al lavoro, il calcolo viene fatto secondo una definizione a matrice di tipo gg della settimana / fascia oraria, dove all’ intersezione tra questi 2 dati c’ e’ un codice numerico (arbitrario, compreso tra 1 e 16) attribuito a quello straordinario.
La tabella va creata dall’ utente, e ha questo aspetto:

Codice: Seleziona tutto

DefOrari OrarioStd  start1  start2   22:00    24:00   30:00 . . . (vuoto)
 1        8:00        N2       N3       1       2       2  . . .
 2        8:00        N..      N..      N..     N..  . . .
 3        8:00        N..      N..      N..     N..  . . .
 4
 5
 6        00:00       1        1        2       2       3
 7        00:00       3        3        4       4       2  . . .

I numeri in verticale da 1 a 7 rappresentano dal Lunedi’ alla Domenica. La colonna “Orario Std” indica le ore “standard” per quel giorno (le ore aggiuntive sono straordinario); ho arbitrariamente scritto 8h dal Lu in poi e 0h per Sa e Dom, ma questi sono paramentri che potete mettere a piacere. Gli Nx o N.. sono numeri da 1 a 16 usati arbitrariamente dall’ utente per raggruppare stessi tipi di straordinario. Start1,2,3,.. sono limiti di orario, e si intende che “fino a quel limite la classe assegnata e’ quella sottostante”; nell’ esempio che ho fatto, lo straordinario tra start2 e le 22:00 e’ classificato di tipo 1 il lunedi’ ma di tipo 4 la domenica.
Si possono definire quante fasce orario si vuole, purche’ non ci siano piu’ di 16 tipi diversi (da 1 a 16); ma questo penso che sia un limite ben superiore al bisogno. Nell’ esempio ho inserito anche le ore 30:00; cosi’ indico in realta’ le ore 6:00 del giorno dopo, giacche’ un turno cominciato oggi puo’ benissimo concludersi domani.

Alla destra della tabella e’ necessario lasciare una colonna libera, che sara’ interpretata come fine della tabella. Come pure l’ angolo in alto a sx deve contenere la stringa DefOrari

La macro richiede 4 celle in orizzontale contigue con le timbrature E/U mattina, E/U pomeriggio; questi dati devono essere introdotti in formato “orario”; se l’ orario e’ continuato si possono solo compilare E/U mattina o E matt + U pom.
Anche il concetto di Matt e Pom e' “largo”, e si intende come “prima parte” e “seconda parte”; ad esempio la seguente sequenza di timbrature e’ gestita correttamente:
21:30 -> 00:30 ; 1:00 -> 4:00 (tot 6 ore).
Per compilare correttamente la tabella del tipo straordinari, si tenga presente che questa timbratura viene trasformata nella macro in questa sequenza:
21:30 24:30 25:00 28:00 (ovviamente 25:00 significa 1:00 del giorno dopo e 28:00=4:00)
Questo e' fatto sia per il calcolo corretto dei delta ore che per consentire di gestire il passaggio da un giorno a un altro; es. dal venerdi’ al Sabato o dal Sabato alla Domenica, come gia’ accennato parlando degli “orari limite” (l’ intestazione della tabella DefOrari.

Uso del codice:
Aprire il vba editor con Alt-F11; Menu /Inserisci /Modulo; copiare il codice e incollarlo nel frame di destra.

Il codice:

Codice: Seleziona tutto

Function Xstra(Data, InOuTable, DefOrario, TipoXstra, Optional TotH) As Single
'Data=cella contenente la data; InOuTable=prima delle 4 timbrature (E-U, E-U)
'DefOrario=indirizzo tabella con la matrice gg/hh/tipo di straordinario
'TipoXstra= valore del "tipo" richiesto; oppure 0=Ore lavorate
'
Dim GSett As Integer
Dim In1 As Single: Dim In2 As Single: Dim CTy As Single
Dim Out1 As Single: Dim Out2 As Single
Dim WHours As Single
Dim DefCols As Integer: Dim I As Integer: Dim CT As Integer
Dim TabTy(16) As Single: Dim TabTy0 As Single: Dim TabTyOld As Single

If Tipoxsta > 16 Then Exit Function
If DefOrario <> "DefOrari" Then Exit Function
Application.Volatile
GSett = Weekday(Data, vbMonday)
In1 = InOuTable
Out1 = InOuTable.Offset(0, 1).Value + (InOuTable.Offset(0, 1) < In1) * -1
'In2 = InOuTable.Offset(0, 2).Value + (InOuTable.Offset(0, 2) < InOuTable.Offset(0, 1)) * -1
In2 = InOuTable.Offset(0, 2).Value + (InOuTable.Offset(0, 2) < Out1) * -1

Out2 = InOuTable.Offset(0, 3).Value + (InOuTable.Offset(0, 3).Value < In2) * -1
DefCols = DefOrario.End(xlToRight).Column - DefOrario.Column
WHours = Out2 - In2 + Out1 - In1
If TipoXstra = 0 Then
Xstra = WHours: Exit Function
End If
Xstra = WHours - DefOrario.Offset(GSett, 1)
If Xstra < 0 Then
Xstra = 0: Exit Function
End If
For I = DefCols To 2 Step -1
If Out2 > DefOrario.Offset(0, I) Then Exit For
Next I
CalcTy:
CT = DefOrario.Offset(GSett, I + 1)
TabTy0 = Out2 - DefOrario.Offset(0, I)
If In2 >= DefOrario.Offset(0, I) Then TabTy0 = TabTy0 + DefOrario.Offset(0, I) - In2
If Out1 >= DefOrario.Offset(0, I) Then TabTy0 = TabTy0 + Out1 - DefOrario.Offset(0, I)
If In1 >= DefOrario.Offset(0, I) Then TabTy0 = TabTy0 + DefOrario.Offset(0, I) - In1
TabTy0 = TabTy0 - TabTyOld
TabTy(CT) = TabTy0 + TabTy(CT)
TabTyOld = TabTyOld + TabTy0
Xstra = Xstra - TabTy0
If Xstra <= 0 Then
TabTy(CT) = TabTy(CT) + Xstra
Xstra = TabTy(TipoXstra)
Exit Function
End If
I = I - 1
If I > 1 Then GoTo CalcTy
Xstra = "XXX"    'Errore, I=<2 e non ancora completato il calcolo
End Function

Tornare sul foglio excel.
Uso della “funzione” Xstra:
usare Xsta in una formula del tipo

Codice: Seleziona tutto

=XSTRA(data;timbrature;tabella;tipo)

dove
-Data e’ l’ indirizzo della cella che contiene la data di lavoro, da cui si desume il gg della settimana.
-Timbratura e l’ indirizzo della prima cella con le timbrature;
-Tabella e’ l’ indirizzo della cella con la scritta DefOrari e che punta alla tabella di definizione.
-Tipo e’ il tipo di straordinario che si vuole calcolare; probabilmente userete tante formule in tante colonne diverse quanti sono i “tipi” che avete definito.

Se come Tipo viene usato 0 la funzione restituisce le ore lavorate.
La funzione ovviamente puo’ essere combinata con altre funzioni per eseguire calcoli piu’ complessi, e i suoi parametri possono essere definiti in modo assoluto (globale, su riga, su colonna; tramite l' uso di $) che relativo.
Se la tabella di definizione la mettete su un altro foglio, suggerisco di assegnare un nome alla cella DefOrari e usare quel nome al posto dell’ indirizzo.

Ho fatto un debug decoroso, gli errori piu’ clamorosi penso di averli eliminati; sono rimasti solo i piu’ subdoli che lascio al vostro collaudo...


Se avete problemi con la funzione, per favore scrivete su questo topic.

Ciao.

[Anthony47]

Nessuno ha il coraggio di dire "non ho capito questo e non ho caputo quello" oppure solo "ho provato ma non funziona"?

Ciao, non lasciatemi solo.

[Rosario77]

In effetti!!! , ho preferito chiedere a lovange se, per caso, Lui fosse stato più bravo di me, non ho avuto risposta, quindi, forse non lo è stato.
La tabella non mi è molto chiara, forse riuscendo a capirci qualcosa potrei tentare di proseguire da solo ma, a causa del VB, senza ulteriori aiuti, sono sicuro che, senza un aiuto più consistente , dovrò rinunciare a completare il foglio di calcolo .

Ciao

Rosario77

[Anthony47]

Intanto ho scoperto un errore nella macro postata:
l’ istruzione If Tipoxsta > 16 Then Exit Function deve essere modificata in

Codice: Seleziona tutto

If TipoXstra etc etc!

La tua richiesta era stata:
devo calcolare in automatico solo le ore effettuate oltre le 36 ore, considerando che effettuate di domenica sono festive, efettuate solo il sabato, feriali e dalle 22 alle 24 di domenica sono notturne festive...
Diciamo che 1= straord semplice, 2=di sabato, 3=festivo, 4=nott+festivo

Quindi potresti prepare una tabella piu’ o meno cosi’:

Codice: Seleziona tutto

DefOrari   Std   0:00   6:00   22:00   24:00   30:00   46:00   48:00
1   06:00   1   1   1   1   1   1   1
2   06:00   1   1   1   1   1   1   1
3   06:00   1   1   1   1   1   1   1
4   06:00   1   1   1   1   1   1   1
5   06:00   1   1   1   1   2   2   2
6   00:00   2   2   2   2   3   3   3
7   00:00   3   3   3   4   1   1   1

Come orario standard ho messo 6h * 6 gg; se non e’ cosi’ cambia la seconda colonna.

Poi giorno per giorno ti calcoli su 4 colonne i possibili 4 tipi di straordinario e li sommi a livello settimanale.

Ora tu hai il problema di voler compensare lavoro in piu’ fatto il lunedi’ (e conteggiato tra lo straordinario) con lavoro in meno fatti il martedi’ (per esempio). Per questo, in una ulteriore colonna che chiamiamo “Debiti” calcoli il lavoro in meno rispetto alle 6h (o all’ orario teorico effettivo); userai una formula tipo

Codice: Seleziona tutto

=SE(TotOreLavorate<ORARIO(6;0;0);ORARIO(6;0;0)- TotOreLavorate;"")

dove TotOreLavorate e’ la cella con il calcolo del totale lavorato del giorno.
Sommi i debiti a livello settimanale e otteniamo una cifra di ore che dovranno essere sottratte alle ore di straordinario calcolate; immagino che si sottrarranno prima le ore di tipo 1, se non bastano a colmare i debiti si passa al tipo 2, e cosi’ via.
Supponiamo che i totali settimanali (4 tipi di Xstra e debiti) siano da cella H10-I10-J10-K10 (Xstra) ed L10 (debito), allora nella 4 colonne successive calcoli gli straordinari detratti i debiti con le formule:

Codice: Seleziona tutto

M10 (xstra tipo 1) =SE((H10-L10)<0;0;H10-L10)
N10 (tipo 2) =SE(SE(M10<=0;I10-L10+H10;I10)<0;0;SE(M10<=0;I10-L10+H10;I10))
O10 (tipo 3) =SE(SE(N10<=0;J10-L10+H10+I10;J10)<0;0;SE(N10<=0;J10-L10+H10+I10;J10))
P10 (tipo 4) =SE(SE(O10<=0;K10-L10+H10+I10+J10;K10)<0;0;SE(O10<=0;K10-L10+H10+I10+J10;K10))

Spero che ti quadri..
Ciao, fai sapere.

[lovange]

Grazie Anthony e scusami per il ritardo, in questi giorni, mi sto cimentando con la tua risoluzione nella speranza di portare a termine l'incolonnamento di questo benedetto straordinario. Per Rosario77, sono io che devo chiederti delucidazioni, ti ho già inviato il mio indirizzo di posta eletttronica.

[lovange]

Ciao Anhony, putroppo le tue delucidazioni riportate nel messaggio datato 04/01/2008; 22:41, dopo vari tentativi, per me rimangono traguardi irraggiungibili. Ci sono tante caselle dove vengono riportati esempi esplicativi, personalmente provando e riprovando, mi ritrovo sempre al punto di partenza.Sarei infinitamente grato se, qualcun'altro ha già risolto il problema e possa darmi un aiuto, considerando la mia scarsa preparazione, un file allegato, probabilmente mi aiuterebbe a capire i vari passaggi, riportati nel succitato messaggio. Nell'attesa, di un'eventuale risposta,colgo l'occasione per ringraziare Anthony, per l'impegno profuso e la SUA GRANDE PROFESSIOALTA'. Lovange.

[Anthony47]

Non mi sembra gia’ il momento di rinunciare..

Tu avevi chiesto di calcolare lo straordinario diurno (fatto tra le 6 e le 22 non di festivo), il notturno (tra le 22 e le 24), il festivo (dalle 0 alle 22?) e il festivo+notturno (22-24); mi resta il dubbio di come andrebbero calcolati straordinari fatti tra le 24:01 e le 6:00, soprattutto il lunedi’ mattina (quando probabilmente sono la continuazione di un lavoro cominciato la domenica..). La tabella era nata proprio per lasciare a voi catalogare tutte le fasce orarie.

Nella mia interpretazione, la tabella che corrisponde alle definizioni e’ la seguente:

Codice: Seleziona tutto

DefOrari   Std    00:00  06:00  22:00  00:00  06:00  22:00  00:00 
  1        07:00    1      1      1      2      1      1      2     
  2        07:00    1      1      1      2      1      1      2     
  3        07:00    1      1      1      2      1      1      2     
  4        07:00    1      1      1      2      1      1      2     
  5        07:00    1      1      1      2      1      1      2     
  6        00:00    1      1      1      2      3      3      3     
  7        00:00    3      3      3      4      1      1      1     

1=diurno; 2=notturno; 3=festivo; 4=Fest+Nott

Se la data e’ in col A (da A2) e la prima timbratura e’ in col B (da B2), allora le formule per gli straordinari saranno:

Codice: Seleziona tutto

Diurno  =xstra(A2;B2;TabTime;1)
Nottur  =xstra(A2;B2;TabTime;2)
Festivo  =xstra(A2;B2;TabTime;3)
Nott+F  =xstra(A2;B2;TabTime;4)

Nella cella dove vuoi calcolare la somma tra Nott e Fest inserirai

Codice: Seleziona tutto

=xstra($A2;$B2;TabTime;2)+ xstra($A2;$B2;TabTime;3)

Ricorda che le 4 timbrature devono essere in formato “orario” (nel foglio che mi avevi inviato usavi invece una notazione “decimale”; per gestire l’ eventuale lavoro a cavallo della mezzanotte, per il totale lavorato puoi usare la formula

Codice: Seleziona tutto

=E2-D2+(D2>E2)+C2-B2+(B2>C2)

(se le timbrature sono in B2/C2, D2/E2).

Le istruzioni per inserire il codice al posto giusto le avevo gia’ postate; se hai problemi comunque prova a chiedere.

Ciao.

[Anthony47]

A seguito di segnalazione inviatami da Rosario77 ho introdotto una modifica alla macro

da cosi':
Xstra = WHours - DefOrario.Offset(GSett, 1)
If Xstra < 0 Then
Xstra = 0: Exit Function

a cosi':

Codice: Seleziona tutto

Xstra = WHours - DefOrario.Offset(GSett, 1)
If Xstra <= 0 Then            '<<< Istruzione Modificata
Xstra = 0: Exit Function

(notare la correzione sull' istruzione intermedia)
Questo corregge la possibilita' di avere errore (#VALORE) nei giorni non lavorativi.

Ciao.

[Rosario77]

E come disse qualcuno “ ora sembra funzionare uguale “, però se funzionasse bene sarebbe meglio.
Credo che l’errore sia da riferirsi alla tabella che codifica lo straordinario ma, nonostante i diversi tentativi, non sono riuscito a risolvere il problema quindi, mi affido, come al solito, ad Anthony e al forum che è stato superlativo.
Ecco ciò che ho pasticciato:

Colonne nome col Formule nelle celle contenuto o risultato delle celle
A2 = data sab. 01 dic. 2007
B2 = assenze vuoto
C2 = entr matt 5.00
D2 = usc matt 10.00
E2 = entr pom 18.00
F2 = usc pom 23.00
G2 = tot ore giornata =F2-E2+(E2>F2)+D2-C2+(C2>D2) 10.00 OK
H2 = ore da ccnl =SE(VAL.ERRORE(CERCA.VERT(B2;
codifiche!A3:A15;1;0));codifiche!A32;0) 7.00 OK
I2 = tot str giornata =SE(SOMMA(G2>H2);G2-H2;"") 3.00 OK
J2 = str diurno =xstra(A2;C2;codicistr!A1;1) 2.00 OK
K2 = str nott =xstra(A2;C2;codicistr!A1;2) 1.00 OK
L2 = str festivo =xstra(A2;C2;codicistr!A1;3) 0.00 OK
M2 = str nott/fest =xstra($A2;$C2;codicistr!A1;2)+ xstra($A2;$C2;codicistr!A1;3) 1.00 DOVREBBE DARE 0
N2 = ore da recup. =SE(VAL.ERRORE(CERCA.VERT(B2;codifiche!A3:A15;1;0));
SE(G2<ORARIO(7;0;0);ORARIO(7;0;0)-G2;"");"") 0.00 OK
Come si può vedere, la cella M2, straordinario notturno e festivo, contiene un errore, dovrebbe essere 0 e non 1 perché le ore lavorate terminano alla ore 23,00 del sabato, quindi è un’ora di str notturna non festiva, come nella cella K2.
Le ore di lavoro previste dal ccnl nella col H, sono nel foglio codifiche. Questo perché le ore potrebbero essere anche inferiori a 7, però ho un dubbio: la tabella sottostante condiziona le suddette ore?
Questa è la tabella codici str che utilizzo
DefOrari Orario norm 0.00 6.00 22.00 24.00 30.00 36.00 42.00 vuoto
1 7.00 1 1 1 2 1 1 2
2 7.00 1 1 1 2 1 1 2
3 7.00 1 1 1 2 1 1 2
4 7.00 1 1 1 2 1 1 2
5 7.00 1 1 1 2 1 1 2
6 7.00 1 1 1 2 3 3 2
7 7.00 3 3 3 4 1 1 1
N.B. Il sabato e la domenica non sono sempre da considerare come riposo.
Ultimo chiarimento; come faccio, nella formattazione condizionale, a far leggere le festività che si trovano in un foglio diverso?

[Anthony47]

Rispondo a Rosario e ne approfitto per dare qualche ulteriore informazione sull’ uso della Funzione XSTRA.
Rosario, con la formula =xstra($A2;$C2;codicistr!A1;2)+ xstra($A2;$C2;codicistr!A1;3) che hai messo in M2 tu ottieni la somma tra lo straordinario di tipo 2 e quello di tipo 3; cioe’, con le definizioni che hai nella tabella DefOrari, “quello notturno” + “quello festivo”, NON quello di tipo “notturno e contemporaneamente festivo”, che nella tabella ha il codice 4.
Quindi la formula da usare e’

Codice: Seleziona tutto

=XSTRA($A2;$C2;codicistr!A1;4)

La domanda sulla formattazione condizionale: sposti l’ elenco delle festivita’ in un altro foglio (ad esempio “Codifiche”) e poi usi la notazione Codifiche!A:A (se l’ elenco e’ in col A) per puntare all’ elenco; oppure dai un nome all’ intervallo che “contiene” l’ elenco (“contiene” implica che puoi definire un intervallo piu’ lungo dell’ elenco attuale, in modo da poter aggiungere altre festivita’ man mano che se ne presenta il bisogno) e poi usi il nome dell’ intervalo nelle formule.
Per assegnare un nome a un intervallo: selezioni l’ intervallo, poi Menu /Inserisci /Nome /definisci; qui scrivi il nome che si vuole assegnare e si completa con Ok.
(come gia' detto in un messaggio privato...)

Anticipo una possibile osservazione sulla lentezza di esecuzione, che potrebbe essere un problema se il foglio venisse usato per elaborazioni manuali di fine mese (cioe’ non input giorno dopo giorno, ma digitazione manuale dei dati mensili; nessun problema se i dati vengono copiati a blocchi nelle celle delle timbrature).
La funzione riparte ogni volta che il foglio viene calcolato, tante volte quante sono le celle che contengono la funzione; questo puo’ tradursi in un ritardo tra l’ Enter di un valore e lo spostamento del cursore nella cella successiva.

Possibili soluzioni.
1-Si imposta il foglio per calcolo manuale (Menu /Strumenti /Opzioni; tab Calcolo, si spunta Manuale); si inputano tutti i dati, si preme F9 per eseguire il ricalcolo.

2-Si modifica la macro e l' uso della funzione
a) Si usa nelle formule una opzione “non documentata”, cioe’ si usa la formula nella sintassi
=XSTRA(data;timbrature;tabella;tipo;OreComplessive)
dove “OreComplessive” e’ l’ indirizzo della cella in cui viene calcolato “con funzioni base excel” il totale delle ore lavorate; ribadisco “con funzioni base excel”, per escludere l’ uso della funzione XSTRA con “tipo 0” che, come detto nel mio post del 4-1-2008, restituirebbe le ore lavorate.
b) poi dal codice della funzione si cancella l’ istruzione Application.Volatile (che e’ quella che fa partire l’ istruzione a ogni ricalcolo, qualsiasi ne sia la causa).
In questo modo il codice si attiva solo per rielaborare quelle celle in cui cambia il totale ore lavorate, cioe’ quelle della riga oggetto di digitazione.


Qualcuno mi ha chiesto tramite messaggio personale chiarimenti sulla tabella DefOrari.
La tabella serve a definire la classificazione degli straordinari a seconda della giornata e dell’ orario di effettuazione; se conoscessi la normativa di legge e se volessi fare dei soldi vi venderei una macro ad hoc, e ve la rivenderei a ogni cambio di normativa.
Prendo la tabella di esempio postata in data 14-1 perche’ abbastanza leggibile, con l’ avvertenza che l’ intestazione e’ ERRATA, in quanto la sequenza dovrebbe essere
DefOrari Std 00:00 06:00 22:00 24:00 30:00 46:00 48:00
Quela tabella dice (ad esempio) che uno straordinario effettuato di Sabato (giorno “6”) fino alle ore 22 e’ classificato di Tipo 1; se l’ orario di lavoro si prolungasse fino alle 23:50 ci sarebbe una ulteriore straordinario classificato di Tipo 2 per la durata di 1h50m; se l’ orario di lavoro si prolungasse ulteriormente fino alle 00:30 (di domenica) ci sarebbe un ulteriore straordinario classificato di Tipo 3 per la durata di 0h30m. Notate che questo e’ quello che fa la tabella, ma non so se e’ proprio quello che prevede la normativa; a voi il compito di mappare la normativa dentro la tabella.
Mi accorgo ora di un ulteriore errore nella tabella postata il 14-1: l’ ultimo numero della riga “6” dovrebbe essere un 4 (e non un 3). Lo dico nonostante che la probabilita’ che l’ errata definizione generi un errato risultato nei calcoli sia praticamente nulla; quel numerino viene infatti preso in considerazione per un lavoro cominciato di sabato che pero’ si conclude dopo le 22 della domenica, roba da vecchi stacanovisti.
Nella vostra tabella potete usare fino a 16 definizioni (numerati da 1 a 16), il cui significato lo date voi; nella tabella di esempio, il 4 corrisponde a uno straordinario fatto di Domenica dopo le 22:00, cosi’ quando voglio sapere lo straordinario “notturno e festivo” usero’ la formula =XSTRA(data;timbrature;tabella;4) [oppure =XSTRA(data;timbrature;tabella;4;OreComplessive))

Spero di aver chiarito qualcosa in piu’.....

[Rosario77]

Voglio puBBBBlicamente ringraziare Anthony47 per aver risolto i niei problemi circa il foglio per il calcolo dello straordinario, è stato fantastico . Adesso funziona bene, se sarà il caso aggiungerò qualche fronzolo per personalizzarlo ma, va già bene così.
L'ultima cosa che vorrei sapere, circa la tabella DefOrari riguarda la nuova riga " Prefestivo ": cosa intendi?.
Grazie ancora
Rosario77

[Anthony47]

Il messaggio di Rosario fa seguito ad alcuni messaggi personali in cui sono state fatte delle modifiche alla soluzione, che qui spiego per tutti.
Problemi
1. La funzione XSTRA talvolta va in crisi in presenza di 2 sole timbrature
2. Era gestita la domenica ma non gli altri giorni festivi infrasettimanali

Le modifiche riguardano
A. La tabella DefOrario deve avere altre 3 righe ulteriori come segue:
N. 8, "prefestivo"
N. 9, "Festivo infrasettimanale"
N. 10, "Festivo seguito da Festivo"

Per prefestivo si intendono le definizioni che saranno utilizzate quando il giorno successivo a quello in esame e' "festivo infrasettimanale".
In proposito vorrei ricordare che nella compilazione della tabella va tenuto sempre conto che le ore successive alle 24 vanno intese come ore del giorno "successivo"; infatti la macro prevede che un turno cominciato "oggi" possa finire "domani".
Questo spiega anche l' ulteriore riga "Festivo seguito da festivo", che ha una gestione diversa dalla domenica.
Un ESEMPIO di tabella DefOrari e' la seguente

Codice: Seleziona tutto

DefOrari   Std    00:00  06:00  22:00  24:00  30:00  46:00  48:00 
  1        07:00    1      1      1      2      1      1      2     
  2        07:00    1      1      1      2      1      1      2     
  3        07:00    1      1      1      2      1      1      2     
  4        07:00    1      1      1      2      1      1      2     
  5        07:00    1      1      1      2      1      1      2     
  6        00:00    1      1      1      2      3      3      3     
  7        00:00    3      3      3      4      1      1      1     
  8        07:00    1      1      1      2      4      3      4     
  9        00:00    4      4      3      4      2      1      2     
  10       00:00    4      4      3      4      4      3      4     

1=Straord normale; 2=Notturno; 3=Festivo; 4= Festivo & notturno

B. L' inserimento di una colonna intestata "Festività" alla immediata destra della colonna con la data, da usare per indicare le giornate festive infrasettimanali.
Allo scopo vi create in un foglio di appoggio l' elenco delle giornate festive,e poi a questo intervallo di celle date il nome "FESTIVI" (Selezionate l' elenco, magari prevedendo celle libere a piacere per le festivita' future; Menu /Inserisci /Nome /Definisci; qui inserite il nome e chiudete con Ok); infine nelle celle delle festivita' usate la formula =SE(VAL.ERRORE(CERCA.VERT(A2;festivi;1;0));0;1) per indicare se quel giorno e' un festivo; questa colonna viene sondata dalla macro, quindi e' necessario che essa sia adiacente alla colonna della data.

C. La modifica del codice della funzione, che riporto integralmente:

Codice: Seleziona tutto

Function Xstra(Data, InOuTable, DefOrario, TipoXstra, Optional TotH) As Single
'Data=cella contenente la data; InOuTable=prima delle 4 timbrature (E-U, E-U)
'DefOrario=indirizzo tabella con la matrice gg/hh/tipo di straordinario
'TipoXstra= valore del "tipo" richiesto; oppure 0=Ore lavorate
'
Dim GSett As Integer
Dim In1 As Single: Dim In2 As Single: Dim CTy As Single
Dim Out1 As Single: Dim Out2 As Single
Dim WHours As Single
Dim DefCols As Integer: Dim I As Integer: Dim CT As Integer
Dim TabTy(16) As Single: Dim TabTy0 As Single: Dim TabTyOld As Single

If TipoXstra > 16 Then Exit Function
If DefOrario <> "DefOrari" Then Exit Function
Application.Volatile
'aaaa = Data
GSett = Weekday(Data, vbMonday)
'MODIF --->
If GSett < 6 Then
If Data.Offset(0, 1) = 1 Then GSett = (9 + Data.Offset(1, 1)) Else If Data.Offset(1, 1) = 1 Then GSett = 8
End If
If Weekday(Data, vbMonday) = 6 Then
If Data.Offset(0, 1) = 1 Then GSett = 10 Else GSett = 6
End If
If Weekday(Data, vbMonday) = 7 Then
If Data.Offset(1, 1) = 1 Then GSett = 10
End If
'<--MODIF

In1 = InOuTable
Out1 = InOuTable.Offset(0, 1).Value + (InOuTable.Offset(0, 1) < In1) * -1
'In2 = InOuTable.Offset(0, 2).Value + (InOuTable.Offset(0, 2) < InOuTable.Offset(0, 1)) * -1
'MODIF:
If InOuTable.Offset(0, 2).Value = 0 Then In2 = Out1 Else: In2 = InOuTable.Offset(0, 2).Value + (InOuTable.Offset(0, 2) < Out1) * -1

'MODIF:
If InOuTable.Offset(0, 3).Value = 0 Then Out2 = In2 Else Out2 = InOuTable.Offset(0, 3).Value + (InOuTable.Offset(0, 3).Value < In2) * -1

DefCols = DefOrario.End(xlToRight).Column - DefOrario.Column
WHours = Out2 - In2 + Out1 - In1
If TipoXstra = 0 Then
Xstra = WHours: Exit Function
End If
Xstra = WHours - DefOrario.Offset(GSett, 1)
If Xstra <= 0 Then
Xstra = 0: Exit Function
End If
For I = DefCols To 2 Step -1
If Out2 > DefOrario.Offset(0, I) Then Exit For
Next I
CalcTy:
CT = DefOrario.Offset(GSett, I + 1)
TabTy0 = Out2 - DefOrario.Offset(0, I)
If In2 >= DefOrario.Offset(0, I) Then TabTy0 = TabTy0 + DefOrario.Offset(0, I) - In2
If Out1 >= DefOrario.Offset(0, I) Then TabTy0 = TabTy0 + Out1 - DefOrario.Offset(0, I)
If In1 >= DefOrario.Offset(0, I) Then TabTy0 = TabTy0 + DefOrario.Offset(0, I) - In1
TabTy0 = TabTy0 - TabTyOld
TabTy(CT) = TabTy0 + TabTy(CT)
TabTyOld = TabTyOld + TabTy0
Xstra = Xstra - TabTy0
If Xstra <= 0 Then
TabTy(CT) = TabTy(CT) + Xstra
Xstra = TabTy(TipoXstra)
Exit Function
End If
I = I - 1
If I > 1 Then GoTo CalcTy
Xstra = "XXX"    'Errore, I=<2 e non ancora completato il calcolo
End Function

Rimane valido il modo per risolvere l' eventuale lentezza di esecuzione, eliminando l' istruzione Application.Volatile e usando la sintassi =XSTRA(data;timbrature;tabella;tipo;OreComplessive); vi rimando per i dettagli al mio post del 26/01/08 ore 15:54 .

Se avete domande, sono qua; abbiamo fatto 30, faremo anche 31...

Ciao.

[Anthony47]

Avendo io stesso detto "In proposito vorrei ricordare che nella compilazione della tabella va tenuto sempre conto che le ore successive alle 24 vanno intese come ore del giorno successivo", e' clamoroso da perte mia la seguente sequenza errata in DefOrari:
DefOrari Std 00:00 06:00 22:00 24:00 30:00 46:00 48:00
6 00:00 1 1 1 2 3 3 3
7 00:00 3 3 3 4 1 1 1

La sequenza del 6 (sabato) dovrebbe essere questa:

Codice: Seleziona tutto

 6        00:00    1      1      1      2      3      3      4

Infatti "le 48:00" del sabato sono "le 24" della domenica, quindi i due codici corrispondenti devono essere uguali. O no?

Ciao.

[lovange]

Anthony, scusami per questa lunga assenza, dovuta tra l'altro ai vari tentativi inerenti la risoluzione ""Calcoli orario di lavoro su foglio "excel"", quest'ultimi, purtroppo inefficaci. Questo, dovuto alla mia scarsa attiudine a recepire e mettere in essere i tuoi suggerimenti. Confidando e approfittando della tua immensa pazienza, allegandoti il FILE in questione, dopo una TUA VISIONE, concentrando l'attenzione sulle celle M 25; N 25 - L 29; M29, dove sono immesse le formule inviatemi con messaggi datati 24 e 26 u.s., torno a richiederti, DOVE HO SBAGLIATO? P.S.. Ho dovuto optare su altro indirizzo, perchè su questo ogni qualvolta che allego il file, mi evidenzia una dicitura di diniego.Grazie lovange

[Anthony47]

Ho guardato il file trasmesso via messaggio privato.

Prendiamo la formula in M25: =xstra(B25;E25;"TabTime";2)+xstra(B25;E25;"TabTime";3), ricordando che XSTRA va richiamata con i seguenti parametri, in sequenza: Data, InOuTable, DefOrario, TipoXstra
Data=cella contenente la data; InOuTable=prima delle 4 timbrature (E-U, E-U)
DefOrario=indirizzo tabella con la matrice gg/hh/tipo di straordinario
TipoXstra= valore del "tipo" richiesto; oppure 0=Ore lavorate


Cio' detto ho rilevato i seguenti errori:
1) in col B non hai una data, ma solo il nome del giorno della settimana (Lun-Dom). Quindi: inserisci la data in B25 (la prima riga con timbrature, sul tuo foglio) e =B25+1 nelle celle sottostanti; nota che siccome hai formattato la cella "Personalizzato"/"ggg" vedrai solo il nome del giorno (lun-Dom) pur contenendo la cella la data completa.
2) la tabella "con la matrice gg/hh/tipo di straordinario" non ha il nome TabTime, ma si chiama sia Tab_Time che DefOrari; decidi quale nome vuoi usare e inseriscilo col suo nome corretto nella formula. Tuttavia ...
3)...quando hai scelto quale nome usare tra Tab_Time e DefOrari, ricorda che NON DEVI scrivere questo nome tra virgolette, perche' e' il nome di un intervallo e non una costante stringa.
4) nelle istruzioni avevo detto che "l’ angolo in alto a sx [della tabella di definizione] deve contenere la stringa DefOrari"; mentre nel tuo esempio la stringa (cella A1 di foglio CodiciXstra) e' TabTime; quindi inserisci in A1 la stringa DefOrari, che e' un valore che serve alla macro per essere certa di puntare alla tabella giusta.
5) poi l' intestazione della tabella, dove hai trascurato una delle istruzioni chiave: "In proposito vorrei ricordare che nella compilazione della tabella va tenuto sempre conto che le ore successive alle 24 vanno intese come ore del giorno successivo"; quindi e' errata la tua sequenza 0:0/6:0/22:0/0:0/6:0/22:0/24:, che deve essere invece 0:0/6:0/22:0/24:0/30:0/46:0/$8:0
6) infine la timbratura in colonna H, che viene riportata erroneamente come stringa "24.00" e non come orario 24:00. Nota che io uso il "duepunti" per separare le ore perche' cosi' ho settato il computer; e' possibile che il tuo pc usi invece il "punto", dipende da come hai settato il Pc; certamente non devi inserire l' apice prima della scritta 24:00 o 24.00, mentre nel tuo foglio anteponi appunto un' apice alla scritta di cella H25 e altre.

Con queste piccole ma significative correzioni a me sembra funzionare. La formula che ho usato in M25 e'

Codice: Seleziona tutto

=xstra(B25;E25;DefOrari;2)+xstra(B25;E25;DefOrari;3)

E in N25:

Codice: Seleziona tutto

=xstra(B25;E25;DefOrari;4)

Tuttavia ho ancora delle osservazioni:
-perche' in L25 non hai inserito la formula per il calcolo dello straordinario Diurno?
-vedo che quando fai meno ore del dovuto vuoi inserire nella colonna "straordinario Diurno" il "debito" di ore, calcolato nella colonna K; vedo pero' che fai questa operazione "a mano", cioe' inserendo la formula =Kxx invece della formula di calcolo con Xstra. Per evitare cio' basta usare una formula che usi anche SE, ad esempio in L25 puoi usare la formula

Codice: Seleziona tutto

=SE(K25>0;xstra(B25;E25;DefOrari;1);K25)

che poi copi verso il basso: in questo modo in L25 visualizzi 1:00 di straordinario e in L26 visualizzerai invece -1:00.

Spero che sia tutto chiaro ...
Ciao.

[lovange]

Grazie Anthony, con i tuoi suggerimenti riportati nel messaggio 24/02/08 – 02:26 qualcosa si è mosso in senso positivo. Credo di aver omesso qualche correzione per rendere efficientissimo il prospetto inviatoti tramite messaggio privato.

A ritroso ti rappresento le anomalie riscontrate:

La formula =SE(K25>0;xstra(B25;E25;DefOrari;1);K25) inserita nella cella L25,
visualizza il valore corretto, tranne e soltanto, quando nelle timbrature E25 – F25 riporto il seguente orario17:00 -24:00, la cella su sottolineata, in questo caso, evidenzia il valore con un segno negativo (-0:00), quando, al contrario deve evidenziarmi un valore positivo. C’è qualcosa da modificare affinché non si verificano queste anomalie?

La formula =xstra(B25;E25;DefOrari;4) inserita nella cella N25 attualmente rispecchia correttamente quanto chiesto con messaggi precedenti.

Mentre la formula;
=xstra(B25;E25;DefOrari;2)+xstra(B25;E25;DefOrari;3)inserita nella cella [M25],purtroppo, evidenzia valori inesatti.
Considerando che la succitata cella racchiusa nelle parentesi quadre, deve riportarmi lo straordinario FESTIVO DIURNO + quello NOTTURNO, al contrario, la cella nominata, mi riporta il totale delle ore lavorate, evidenziate nella cella I25. Sempre nella succitata cella M25;
riportando la formula Festivo “”=xstra(B25;E25;DefOrari;3)”” – con l’inserimento di qualsiasi turno lavorativo, nelle quattro timbrature, orari che danno un valore eccedente quello standard, mi riporta il valore 00:00. Al contrario, la formula evidenziata con le doppie virgolette, riportata in una cella sottostante a quella circoscritta con le parentesi quadre[M25]; esempio M26; giornata non festiva; immessa in quest’ultima cella, con qualsiasi orario riportato nelle quattro timbrature, evidenzia sempre valori corretti.

Al nr.2 del succitato messaggio vengono riportate le correzioni da inserire nel foglio codicixstra, Anthony, posso garantirti che la procedura eseguita per l’inserimento dei tuoi parametri viene riportata integralmente come la descrivi, attenendomi scrupolosamente alle delucidazioni del tuo messaggio privato datato 24/01/2008 – 22:32:54 trovo inspiegabile che cambi automaticamente la sua posizione e tra l’altro si aggiunge nella 2^ finestra di dialogo DefOrari e TabTime, mentre lascia la 1^ cella completamente vuota, riportando nella 3^ cella la seguente dicitura =codicixstra!$A$;1. Procedimento attuato per l’inserimento del TabTime; (Menu/Inserisci/Nome/Definisci)nella 1^ cella vuota della finestra di dialogo, inserisco TabTime, clicco OK e mi ritrovo il foglio codicexstra con le mappature inviatemi. In questa procedura c’è qualcosa di sbagliato? Spero di essere stato chiaro e comprensibile nell'esprimere le anomalie verificatesi. Antony, INFINITAMENTE grato per tutto il tempo dedicatomi e per eventuali suggerimenti, se lo riterrai ancora opportuno inviarmi. Lovange.

[Anthony47]

Andiamo con ordine:
>>L25: -00:00 (invece di 00:00) non mi preoccupa, e’ probabilmente un effetto degli arrotondamenti delle operazioni eseguite che restituisce un valore minimo da diverso da Zero, che comunque nel calcolo rimane impercettibile.

>>M25: dici che ti riporta il totale delle ore lavorate; questo succede semplicemente perche’ nella tabella DefOrari, alla riga 7, colonna Standard, e’ inserito 00:00, che significa che la domenica si dovrebbe lavorare 0 ore. E’ evidente quindi che tutte le ore lavorate saranno considerate straordinario, spalmate sulle colonne L, M, N.
Quindi devi aggiornare la colonna “Standard” affinche’ rifletta l’ orario di lavoro teorico per le singole giornate in tabella:1=Lun, 2=Mart, . . 7=Dom, 8=Prefestivo, 9=Festivo infrasett; 10=Festivo
Poi se ti interessa portare l’ orario standard sulla colonna J lo porterai con la formula

Codice: Seleziona tutto

=CERCA.VERT(GIORNO.SETTIMANA(B25;2);codicixstra!A:B;2;0)

>>La formula =xstra(B25;E25;DefOrari;3): a me sembra che restituisca valori corretti, fammi un esempio di data e timbrature che ti restituiscono un risultato errato (quale dato ti restituisce, quale dato ti aspettavi).

>>L’ ultima parte del messaggio: non capisco se fai riferimento a un errore superato (in questo caso non starei a chiedermi che cosa mai possa essere successo) o non ancora superato.

Ciao.

[Anthony47]

Mi accorgo ora di un' altro errore nel tuo foglio: la colonna accanto alla data dovrebbe essere usata per le le festivita' infrasettimanali (vedere mio post del 31/01/08 22:54); quindi: seleziona tutta la colonna C (la data e' in colonna B), fai Inserisci /Colonna; se non devi gestire festivita' infrasettimanali puoi lasciare tutte le celle vuote, eventualmente la restringi o la nascondi.
Non dovrebbe avere impatto sui calcoli (la tua col. B oggi contiene testi, mentre il festivo e' indicato con il numero 1), ma e' meglio essere allineati.

Ciao.

[lovange]

Anthony, ho combinato un gran pasticcio, dopo aver messo in atto le tue ultime delucidazioni, citate nel messaggio del 26/02/08 –17:28, tutto procedeva per il verso giusto, nella colonna M25 ed N25, si evidenziavano i valori corretti, qualsiasi orario inserivo nelle timbrature E – F – G – H25. Con le identiche condizioni, purtroppo, nella celle L – M – N33, trattandosi questo rigo di una giornata festiva infrasettimanale, non ritrovavo quanto previsto e cioè; “due ore” di straordinario festivo diurno nella cella [M33] ed un’ora nella cella [N33]; al contrario, i valori su evidenziati, venivano riportati nelle celle M33 ed N33, come una giornata NORMALE. Considerando che di seguito nel tuo messaggio vi erano ulteriori nozioni esplicative, inerenti la formule da inserire, nella colonna J; *CERCA.VERT(GIORNO.SETTIMANA(B25;2);codicixstra!A:B;2;0),
ho dedotto che la risoluzione al problema su esposto, poteva risolversi inserendo la formula riportata con asterisco.
Putroppo, questo, non si verificava, nella cella interessata e di conseguenza a tutte le altre, evidenziavano, questo simbolo #####?.
Convinto che cliccando i simboli “annulla o ripristina” il tutto,
ritornava alla normalità, quella inizio apertura del file, continuavo
ad inserire l’altra formula citata nell’altro messaggio datato 26/02/08 18:52 e riportata integralmente nel messaggio del 31/01/08 22:54, stralcio di quel messaggio, racchiuso da asterischi. ***B. L' inserimento di una colonna intestata "Festività" alla immediata destra della colonna con la data, da usare per indicare le giornate festive infrasettimanali.
Allo scopo vi create in un foglio di appoggio l' elenco delle giornate festive,e poi a questo intervallo di celle date il nome "FESTIVI" (Selezionate l' elenco, magari prevedendo celle libere a piacere per le festivita' future; Menu /Inserisci /Nome /Definisci; qui inserite il nome e chiudete con Ok); infine nelle celle delle festivita' usate la formula =SE(VAL.ERRORE(CERCA.VERT(A2;festivi;1;0));0;1) per indicare se quel giorno e' un festivo; questa colonna viene sondata dalla macro, quindi e' necessario che essa sia adiacente alla colonna della data.*** Attenendomi
a quest’ultime nozioni, dopo tanti tentativi, quello che ricreavo; nelle celle interessate, si evidenziavano, #NOME? Chiedo scusa, in PRIMIS AD ANTHONY ed a pari merito a tutto lo Staff per la mia manifestata incapacità,ed umilmente, ritorno a chiederti, “”AIUTO Anthony””. Lovange

[Anthony47]

1) Riga 33: riconfermo il bisogno di avere dati analitici per capire il problema: data, timbrature; quale dato ti viene restito per ognuna delle classi di straordinario, quali dati invece ti aspettavi.
In assenza posso solo prendere atto che il foglio non ti funziona, nulla di piu’.

2) La colonna J: la formula suggerita (=CERCA.VERT(GIORNO.SETTIMANA(B25;2);codicixstra!A:B;2;0)) serve ad assicurare la coerenza tra l’ orario standard che usi nel foglio di calcolo e l’ orario standard inserito nella tabella DefOrari, quello usato da XSTRA per calcolare gli straordinari.
Ma che cosa non funzionava nell’ applicazione della formula? Quale era la formula e quale era il risultato in colonna J?

3) Codici di errore: non conosco la segnalazione #####?, quindi non so immaginare cosa sia successo.

4) Le festivita’ infrasettimanali: sono da gestire come indicato nel messaggio che hai racchiuso tra “***”, quindi nella colonna accanto alla data, nel tuo foglio dovrebbe andare in col. C. Ho capito che le formule inserite in col. C danno #NOME?; l’ ipotesi che posso fare e’ che non hai creato un intervallo denominato “festivi”.

Quindi: invia i dettagli richiesti al punto 1 e al punto 2; verifica quale e’ il simbolo di errore riportato e quale formula lo genera (punto 3); verifica che l’ intervallo “festivi” esiste.

Una ulteriore informazione: per il corretto funzionamento del foglio, devono essere introdotte tutte le modifiche segnalate; solo alla fine potrai quindi vedere il funzionamento corretto. Cioe’ se inserisci una modifica, hai degli errori e rimuovi tutto, allora non arriveremo mai in fondo.

Ciao.