EXCEL straordinari

[massimo311273]

Tribuno adesso ho un altro aiuto da chiederti per calcolare i giorni di presenza vorrei fare cosi creare un elenco in dati dove ci sono tutti i giorni non lavorativi per esempio malattia ferie sciopero ecc. aggiornabile in modo da avere in L una formula che cerchi in B le parole in elenco e che le trova risultato 0 e che se non ne trova 1 chiaramente se la cella e vuota deve restituire 0 anticipatamente grazie

[Tribuno]

Ciao Massimo,
prova in questo modo:

1) nel foglio "Dati" selezioni il range L1:L30 e lo chiami "Assenze"
2) la cella L1 la lasci vuota, nelle altre inserisci le varie tipologie di assenze (Malattia, Ferie, Riposo, Permesso, ecc.), immagino che 29 tipologie di assenze siano sufficienti
3) nella macro Crea_Riposi_Permessi sostituisci "Perm." con "Permesso" e "Rip." con "Riposo", in quanto devono coincidere con l'elenco del range "Assenze"
4) nella colonna B dei mesi, selezioni B4, Dati--->Convalida, nella finestra che si apre, in scheda impostazioni, nella casella a discesa "Consenti" selezione "Elenco", nella casella "Origine" scrivi =Assenze, spunta "Ignora celle vuote", "Elenco nella cella" e premi OK, poi trascini la selezione fino alla fine del mese. In questo modo puoi scegliere dall'elenco a discesa la tipologia di assenza per evitare errori nell'istruzione per il calcolo dei giorni lavorativi
5) seleziona la cella L4 e scrivi la seguente istruzione:

Codice: Seleziona tutto

=SE(B4="";0;SE(VAL.ERRORE(CERCA.VERT(B4;Assenze;1;FALSO));1;0))

trascinandola fino alla fine del mese

P.S.1 per quanto riguarda l'istruzione per il calcolo degli straordinari festivi (mi ero fermato alla verifica, in sabato, dell'orario oltre la mezzanotte, non avevo verificato che nell'orario prima della mezzanotte la considerava come se fosse domenica, la tua correzione ho visto che funziona egregiamente), una mia considerazione personale: quando si iniziano a mettere troppe istruzioni, specialmente con tanti SE() nidificati, l'istruzione risulta poco leggibile e trovare l'errore o l'inghippo risulta faticoso. Personalmente preferisco, in questo caso usare delle funzioni personalizzate.

P.S.2 sempre per il calcolo degli straordinari festivi, non si dovrebbe verificare anche per quelle giornate festive che cadono negli altri giorni, esclusa la domenica, il calcolo degli straordinari festivi come fosse domenica? In caso affermativo bisogna rivedere il codice e, a questo punto, se troppo, passami il termine, incasinato, passerei ad una funzione personalizzata.

Ciao
Tribuno

[massimo311273]

Ciao Tribuno io ho provato a fare come mi dici ma non devo aver capito gran ché però funziona lo stesso, mi spiego meglio:
4) nella colonna B dei mesi, selezioni B4, Dati--->Convalida, nella finestra che si apre, in scheda impostazioni, nella casella a discesa "Consenti" selezione "Elenco", nella casella "Origine" scrivi =Assenze, spunta "Ignora celle vuote", "Elenco nella cella" e premi OK, poi trascini la selezione fino alla fine del mese. In questo modo puoi scegliere dall'elenco a discesa la tipologia di assenza per evitare errori nell'istruzione per il calcolo dei giorni lavorativi
Per dare il nome al Range L1-L30 eseguo la stessa procedura adoperata per le Festivita dopo di che passo alla colonna B auando faccio la procedura da te descritta mi si crea un menù a tendina con dentro la parola assenze e quando inserisco una delle perole malattia ecc. mi da errore che la parole non è definita dall’ utente o una cosa molto simile comunque va lo stesso significa che inserendo in L4 la formula da te creata anche se non effettuo la procedura su descritta mi calcola comunque quello che desideravo.
Ora ti chido la procedura di convalida è indispensabile a tuo parere? Se si doresti essere un pò più paziente e spiegarmi più semplicemente come effettuarla
Ciao grazie.

[massimo311273]

Tribuno come credi che sia meglio risolvere il problema del calcolo degli straordinari festivi?
Proviamo con la personalizzazione?
Rettifico quello che hai scritto nel punto P.S.1 le ore lavorate non sono straordinarie festive ma maggiorazioni sulle ore ordinarie domenicali quindi se un’ ora dal lun al sab mi pagano 1€ la domenica mi pagano sempre 1€ es. ma a parte percepisco una indennità di 1 Cent es. per aver lavorato di domenica e idem funziona per la festività nazionali che poi vedremo se non ti dispiace.
Fammi sapere come risolveresti il problema
Grazie ciao
P.S. ti sei fatto un quadro dal file che ti ho inviato di come ho impostato le varie formule?
Che ne pensi, modificheresti qualcosa? Ad esempio nel calcolo della malattia fino a sospensione

[Tribuno]

Dal mio punto di vista, per come ho pensato la formula, la convalida è necessaria, in quanto faccio un controllo della tipologia di assenza e se la parola inserita è uguale ad una inserita nell'elenco allora nei giorni lavorati mette 0, altrimenti mette 1 (tranne, ovviamente, se la casella è vuota allora mette 0). Se non utilizzo la convalida, nel caso in cui inserisci, per errore, un tipo di assenza che non corrisponde ad una voce dell'elenco L1:L30, allora ti considera presente (ad esempio Malattia con Malattie ). Per quello che hai scritto ho l'impressione che non hai seguito attentamente il mio punto 2

2) la cella L1 la lasci vuota, nelle altre inserisci le varie tipologie di assenze (Malattia, Ferie, Riposo, Permesso, ecc.), immagino che 29 tipologie di assenze siano sufficienti

e punto 4

...spunta "Ignora celle vuote"...

Nel Range L1:L30, che abbiamo chiamato Assenze, nella cella L1 non devi aver nessun valore, da cella L2 a cella L30 devi scrivere solo le tipologie di assenze, ad es. in L2=Malattia, L3=Riposo, L4=Permesso, L5=Infortunio e così via.
Ora posizionati nella cella B4 di gennaio (dove immagino hai inserito la convalida dei dati), verifica che ci sia la spunta in "Ignora celle vuote" (sempre con Dati--->Convalida), altrimenti, se non c'è la spunta, sei costretto ad inserire solo i valori dell'elenco a discesa, prelevati dal range Assenze L1:L30.
Non riesco a capire come, poi, ti funziona il calcolo dei giorni di presenza. Secondo me dovresti avere sempre 0. Infatti se non ti permette di inserire nessun dato all'infuori dell'elenco, non potrai inserire il luogo di servizio che ti corrisponde alla presenza e di conseguenza il valore 1 non ci dovrebbe mai essere.
Mi raccomando, se non l'hai ancora fatto, nella macro Crea_Riposi_Permessi sostituisci "Perm." con "Permesso" e "Rip." con "Riposo".

Per quanto riguarda il discorso degli straordinari festivi, a questo punto, se dobbiamo verificare anche le fetività nazionali (immagino sia da inserire anche quella patronale), dobbiamo passare ad una funzione personalizzata, in quanto nella formula è stato raggiunto il massimo di 7 nidificazioni di SE() ed altri ne dobbiamo aggiungere.
Vedo di mettere giù qualcosa e ti faccio sapere.

Per l'ultimo quesito, se il calcolo delle assenze si ferma nelle 7 che hai usato (da Malattia a Sospensione), che, a questo punto, con la convalida dati devono corrispondere all'elenco Assenze, le formule che hai messo vanno bene, a meno che tu hai altre esigenze.

Ciao
Tribuno

[Anthony47]

Mi sono assentato da questa discussione perche' procedeva abbastanza rapidamente seguendo lo schema proposto da Tribuno; adesso mi rendo conto che forse potrebbe aiutare questa discussione, dal titolo "Funzione per calcolo ore straordinario", che trovate qui: viewtopic.php?f=26&t=69705
In particolare guardate il messaggio di Anthony47 del 31/01/08 23:54.

Ciao.

[Tribuno]

Ciao massimo311278,
prova la seguente funzione personalizzata

Codice: Seleziona tutto

Function Indennità_Festivi(Giorno, Entrata1, Uscita1, Entrata2, Uscita2)
Check = 0
RigFest = Sheets("Dati").Range("L65536").End(xlUp).Row
I = 1
    Do 'verifico se giorno è festivo
        If Giorno = Sheets("Dati").Cells(I, 10) Then
            Check = 1
            Exit Do
        End If
        I = I + 1
    Loop While I <= RigFest
    If Check = 1 Then 'se Check = 1 allora giorno festivo
        If Uscita1 < Entrata1 Then
            Indennità_Festivi = TimeValue("00:00:00") - Entrata1
        Else
            If Uscita2 < Entrata2 Then
                Indennità_Festivi = (Uscita1 - Entrata1) + (TimeSerial(24, 0, 0) - Entrata2)
            Else
                Indennità_Festivi = (Uscita1 - Entrata1) - (Uscita2 - Entrata2)
            End If
        End If
    Else 'altrimenti Check = 0 quindi verifico se giorno è Sabato o Domenica
    Select Case Weekday(Giorno)
        Case 1 'Giorno = Domenica
            If Uscita1 < Entrata1 Then
                Indennità_Festivi = TimeValue("00:00:00") - Entrata1
            Else
                If Uscita2 < Entrata2 Then
                    Indennità_Festivi = (Uscita1 - Entrata1) + (TimeSerial(24, 0, 0) - Entrata2)
                Else
                    Indennità_Festivi = (Uscita1 - Entrata1) - (Uscita2 - Entrata2)
                End If
            End If
        Case 7 'Giorno = Sabato
            If Uscita1 < Entrata1 Then
                Indennità_Festivi = Uscita1 - TimeValue("00:00:00")
            Else
                If Uscita2 < Entrata2 Then
                    Indennità_Festivi = Uscita2 - TimeValue("00:00:00")
                End If
            End If
        End Select
    End If
End Function


In VBA inserisci nuovo modulo ed incolla la funzione, poi in excel ti posizioni sulla cella M4, Inserisci--->Funzione e scegli Funzione definita dall'utente. Ti troverai la funzione Indennità_Festivi, selezionala, nella succesiva maschera la corrispondenza dei campi è la seguente:
Giorno = A4
Entrata1=C4
Uscita1=D4
Entrata2=E4
Uscita2=F4
poi trascini la formula nelle altre celle della colonna M.

Verifica se va bene.

Ciao
Tribuno

[massimo311273]

Ciao Tribuno il problema del menu a tendina lo risolto solo che ho preferito creare un altra lista che contiene sia i giorni di assenza che lavorativi cosi quando inserisco il tipo di servizio o assenza faccio prima mi e piaciuto tanto che ho creato lo stesso menu anche per gli orari, approposito di questo ti volevo chiedere un modo automatico tipo come per la creazione del calendario es. (=A4+1) per scrivere la lista degli orari automaticamente altrimenti devo scriverli uno ad uno li sto scrivendo di quarti d' ora in quarti d' ora es. 01:00 01:15 01:30 01:45 02:00 ecc.
Per quanto riguarda la funzione festivita ho un problemino, anziche darmi uno come risultato mi da -1 meno uno, ed inoltre mi calcola festivita anche le domeniche e non deve essere cosi poi ho visto che nella formula c'e "dati I" che penso sia l' indicazione della colonna dove sono in elenco i giorni festivi solo che sono in "dati J" se fosse cosi ti ho indicato dove li ho elencati ti rigrazio della tua pazienza e disponibilita ciao

[massimo311273]

ah! dimenticavo la colonna dove deve essere svolto il colcolo e U e non M li c'e la malattia
Ciao

[Tribuno]

Ciao massimo311273,

per quanto riguarda l'orario, puoi utilizzare una cella a tuo piacimento e, dopo la formattazione come data, scrivere il valore 0.15, poi alla cella le dai un nome, ad esempio Quindici, nella colonna dove vuoi gli orari a distanza di un quarto d'ora scrivi nella prima cella, ad es. A1, 01:00, in A2 =A1+Quindici, poi trascini A2 in basso fino a quando vuoi tu. Ho dato un nome alla cella dove c'è 0.15 perchè puoi far riferimento a quella cella da qualsiasi foglio senza dover scrivere FoglioX!cella.

Per gli altri due problemi appena ho un attimo di tempo ci dò un'occhiata.

Ciao
Tribuno

[Tribuno]

per quanto riguarda l'orario, puoi utilizzare una cella a tuo piacimento e, dopo la formattazione come data, scrivere il valore 0.15, poi alla cella le dai un nome, ad esempio Quindici, nella colonna dove vuoi gli orari a distanza di un quarto d'ora scrivi nella prima cella, ad es. A1, 01:00, in A2 =A1+Quindici, poi trascini A2 in basso fino a quando vuoi tu. Ho dato un nome alla cella dove c'è 0.15 perchè puoi far riferimento a quella cella da qualsiasi foglio senza dover scrivere FoglioX!cella.

Altre due soluzioni:

- invece di dar un nome alla cella dove hai inserito 0.15 (es. B1), nello stesso foglio, in A2, puoi scrivere =A1+$B$1

- puoi scrivere in A2 =A1+ORARIO(0;15;0)

ovviamente in tutti e due i casi devi trascinare in basso la formula in A2.

Ciao
Tribuno

[Tribuno]

Per quanto riguarda la funzione festivita ho un problemino, anziche darmi uno come risultato mi da -1 meno uno, ed inoltre mi calcola festivita anche le domeniche e non deve essere cosi poi ho visto che nella formula c'e "dati I" che penso sia l' indicazione della colonna dove sono in elenco i giorni festivi solo che sono in "dati J" se fosse cosi ti ho indicato dove li ho elencati ti rigrazio della tua pazienza e disponibilita ciao

Ciao,
sinceramente non ho capito bene il problema che hai riscontrato, potresti fare un esempio con i dati che hai inserito? Anche perché il valore -1 mi sembra un risultato strano.
Inoltre quando parli di funzione festività intendi l'ultima funzione
Function Indennità_Festivi
che serve per il calcolo della maggiorazione oraria in caso di ore lavorative effettuate nelle giornate festive (nazionali e domeniche)?
In questo caso quando mi parli della colonna I in Dati, in realtà

Codice: Seleziona tutto

If Giorno = Sheets("Dati").[b]Cells(I, 10)[/b] Then


la I è una variabile e non corrisponde alla colonna I, ma alla riga avente valore di I, mentre la colonna è la numero 10 [Cells(riga,colonna)], che corrisponde alla colonna J (A=1, B=2......I=9, J=10).

Nel tuo post del 21/09/2008 h. 21:31 avevi scritto

Rettifico quello che hai scritto nel punto P.S.1 le ore lavorate non sono straordinarie festive ma maggiorazioni sulle ore ordinarie domenicali quindi se un’ ora dal lun al sab mi pagano 1€ la domenica mi pagano sempre 1€ es. ma a parte percepisco una indennità di 1 Cent es. per aver lavorato di domenica e idem funziona per la festività nazionali che poi vedremo se non ti dispiace.

quindi avevo interpretato che la maggiorazione oraria doveva essere calcolata sia per le ore lavorate domenica sia per quelle lavorate nelle giornate festive indicate nella colonna J del foglio Dati, allora perchè poi mi dici che "mi calcola festivita anche le domeniche e non deve essere cosi "?
Ah, ho corretto la colonna, è la U e non la M come giustamente mi hai fatto notare.

Ciao, attendo notizie per poter risolvere i problemi che hai riscontrato.

Tribuno

[massimo311273]

Ciao a tutti e da un bel po che manco, sono felice di aver ritrovato il mio account. un saluto particolare a tribuno che ho lasciato in sospeso con il mio quesito.
Avrei ancora bisogno del vostro aiuto: devo calcolare le ore diurne e notturne le ore notturne sono quelle che vanno dalle 22:00 alle 06:00,
ho questo tipo di schema:
C4=Dalle,D4=Alle,E4=Dalle,F4=Alle,G4=Totale,W4=Ore Diurne,X4=Ore Notturne
Al moneto ho in W4 ho la seguente formula: =SOMMA(D4-C4+(C4>D4)+F4-E4+(E4>F4))*SOMMA(SE(D4>C4+(F4-E4);1;0))
e in X4 quest'altra: =SOMMA(D4-C4+(C4>D4)+F4-E4+(E4>F4))*SOMMA(SE(D4>C4+(F4-E4);0;1))
Ma entrambe non soddisfano le mie necessità perché specie in notturno non mi tiene in considerazione il parametro 22:00 - 06:00
Io ho trovato questo tipo di soluzione in W4 per le ore notturne ho: =(RESTO(D4-C4;1)*24-(D4<C4)*(22-6)+MEDIANA(6;22;C4*24)-MEDIANA(6;22;D4*24))/24
mentre in X4 per le ore diurne ho: =G5-X5

il mio problema è che come avrete notato io ho 4 caselle per l'inserimento delle ore (C4=Dalle,D4=Alle,E4=Dalle,F4=Alle) e devono essere calcolate tutte, come posso aggiungere le altre due celle delle ore nella formula RESTO?

Grazie a tutti per il vostro supporto

[Anthony47]

Per questi calcoli avevo tempo fa sviluppato la funzione modulare XSTRA, vedi qui:
http://mail.pc-facile.com/forum/viewtop ... 51#p562761
E' programmabile e penso faccia quello che chiedi, tipo calcolo di festivi, notturni, prefestivi, con qualsiasi calendario.
Prova a usarla...

[massimo311273]

Grazie per la rapida risposta, appena proverò ti farò sapere se è andato tutto bene, grazie

[massimo311273]

Ciao ho dato un occhio ai tuoi link e mi sono stati utili per mettere in moto la fantasia, così ho tirato fuori questa formula:
=SE(G4>ORARIO(7;15;0);G4-ORARIO(7;15;0);0)*SE(E(VAL.ERRORE(CERCA.VERT(A4;Festivi;1;0));VAL.ERRORE(CERCA.VERT(A4;Festivi;1;0)));1;0)
Che vale a dire di trovare gli straordinari solo nei giorni che non sono Festivi, nel mio caso le domeniche sono feriali,
quindi deve trovare solo le festività nazionali.
Lo stesso criterio l'ho adoperato per trovarmi gli straordinari Festivi solo invertendo i dati, in questo modo:
=SE(G4>ORARIO(7;15;0);G4-ORARIO(7;15;0);0)*SE(E(VAL.ERRORE(CERCA.VERT(A4;Festivi;1;0));VAL.ERRORE(CERCA.VERT(A4;Festivi;1;0)));0;1)
Il tutto pare funzionare.

Ma adesso ho un' altro problema, estrarre le ordinarie e ordinarie festive, cioè ho le totali ho le straordinarie ma mi mancano le ordinarie, il prospetto è questo:

A4 Data
B4 Luogo di servizio
C4 Dalle
D4 Alle
E4 Dalle
F4 Alle
G4 Totale ore lavorate
I4 Ore Ordinarie Feriali - da calcolare
J4 Straordinario Feriali
K4 Ore Ordinarie festive - da calcolare
L4 Straordinari festivi
M4 Festivo da recuperare - da calcolare

Come puoi vedere in M3 c'è un'altra voce che é Festivo da recuperare che significa? significa che se io a Natale sono a casa non mi viene pagata la festività ma viene convertita in un giorno di riposo quindi la logica é questa:
se A4 (dove sono i Giorni è un festivo) e si abbina a B4 (dove vi è riposo) in M4 deve restituirmi "1"
se A4 (dove sono i Giorni non è un festivo) si abbina a B4 (dove vi è riposo o un luogo di servizio) in M4 deve restituirmi "0"
Quindi solo se i dati di festività e riposo coincidono io devo ottenere "1"

In più nel file vi è una scheda denominata "Dati" dove ci sono le liste dei luoghi di servizio
"la colonna B si chiama Attività" e delle festività "la colonna H si chiama Festivi"

Spero che sia stato chiaro nella spiegazione del problema, e ti ringrazio anticipatamente per le tue sempre pronte e risolutive risposte, grazie.

[Anthony47]

Tutto quello che chiedi sul calcolo degli orari puo' essere ottenuto usando la funzione XSTRA.
Quanto alla colonna M, il tracciato per l'uso di xstra presuppone che accanto alla data ci sia una colonna che indica Festivo (1) non festivo (0), eventualmente derivandolo da un elenco di festivita'.
Il contenuto quindi di N4 (supponendo che per l'inserzione di questa nuova colonna la tua vecchia B sia diventata C e tutte le altre shiftate a destra, quindi M --> N) lo calcolerai con la formula

Codice: Seleziona tutto

=Se(E(B4=1;C4<>"");1;0)


Ciao

[massimo311273]

Il calcolo dei festivi è nella colonna A facendo riferimento alla lista che si trova nel foglio dati alla lettera H, questa funzione mi cambia il colore della cella inerente alla giornata di festività, la funzione extra nel tuo link è una macro? scusami ma ciò provato più volte ma ciò capito poco per questo insisto con nuove domande, in più utilizzo office 2007 dovrei usare già la macro per i riposi come vedi nei post precedenti ma non riesco ad attivarla, non riesco a sbloccare la macro in 2007 prima usavo Office 2003 e li tutto ok, grazie per la tua pazienza

[massimo311273]

Per quanto riguarda la colonna I4 per calcolare le ore ordinarie feriali e festive ho elaborato questa formula che pare funzionare se non fosse che quando inserisco un totale ore inferiore alle 7:15 mi da come risultato 00:00, avrei bisogno che restituisce sempre 7:15.
La formula è questa: =CONTA.VALORI(G4)*ORARIO(7;15;0)*SE(E(VAL.ERRORE(CERCA.VERT(A4;Festivi;1;0));VAL.ERRORE(CERCA.VERT(A4;Festivi;1;0)));1;0)

PS. C'è modo di inviarti il file? Così puoi avere una visione migliore, grazie

[massimo311273]

Invece per la colonna M recupero riposi, ne ho elaborata un'altra, anch'essi funziona ma unico cruccio e che se nella colonna A non c'è un festivo mi restituisce N/D,
Se in A ho la festività e in B il riposo funziona mi da 1, se in A ho la festività e in B non ho il riposo funziona mi da 0, il problema nasce se in A non ho la festività, la formula è questa:
=SE(E(A4=(CERCA.VERT(A4;Festivi;1;0));VAL.ERRORE(CERCA.VERT(A4;Festivi;1;0)))=SE(CONTA.SE(B4;"Riposo");0;);0;1)