Oltre alle consuete funzioni che consentono la realizzazione di strumenti di analisi finanziaria ed economica, Excel mette a disposizione tutto il necessario per la “gestione del tempo” in azienda. Attraverso la realizzazione di un foglio per la rilevazione presenze, vediamo un ulteriore approfondimento sulle operazioni con date e orari. Uno degli elementi principali (e variabili) da considerare per quantificare le retribuzioni di dipendenti e collaboratori riguarda la rilevazione e determinazione delle giornate lavorate in azienda, ma anche degli orari di lavoro, assenze per malattia, ferie, permessi e per altre cause che, mediamente, sono contemplate nei contratti di lavoro stipulati tra imprese e dipendenti.
=> Scarica l’esempio
Gli elementi comuni di un foglio per il controllo delle presenze saranno: elementi anagrafici di azienda e lavoratore (Cognome e Nome); indicazione sintetica di mese ed cui si riferisce la rilevazione; orari di ingresso e uscita previsti (mattino e/o pomeriggio); scheda mensile in cui per ogni singolo giorno del mese considerato vengono annotati presenza, orario di lavoro svolto, assenze, permessi ecc.
=> Come leggere la busta paga: guida alle retribuzioni
Nel nostro modello, nella sezione anagrafica andranno compilate le celle relative al nome Azienda e Cognome e Nome del collaboratore. L’inserimento anche degli orari di ingresso e uscita (dalle/alle) consentirà la compilazione automatica e condizionata (vedremo tra breve come) di tutte le celle (mattino:entrata/uscita – pomeriggio: entrata/uscita) della scheda di rilevazione giornaliera.
Il nostro obiettivo è creare un prospetto che, mese per mese, riporti tutte le date (es.1-30 novembre) e per ciascuna il corrispondente giorno della settimana (es. lunedì – domenica). In questo esempio i giorni di sabato e domenica vengono considerati non lavorativi. Ora, partendo dalla cella B12 precedentemente predisposta nel formato “data” (tipo: gg/mm/aaaa) a cui abbiamo assegnato il nome “Data_Iniziale“, inseriamo la data di inizio mese: nel nostro esempio 1 novembre 2016 (digitiamo 01/11/2016). Tornando alla sezione anagrafica, verranno indicati mese ed anno di riferimento: nella cella C6 (Mese) verrà indicato Novembre. Il risultato è dato dalla applicazione combinata della funzione =MESE(Data_Iniziale) e della funzione =SCEGLI che ci consente di utilizzare il valore ottenuto (11) come indice dei mesi che compongono l’anno (gennaio – dicembre).
=> Un prospetto per le ore lavorate con Excel 2007
La formula applicata sarà quindi: =SCEGLI(MESE(Data_Iniziale); “Gennaio”;”Febbraio”;”Marzo”;”Aprile”;”Maggio”;”Giugno”;
“Luglio”;”Agosto”;”Settembre”;”Ottobre”;”Novembre”;”Dicembre”).
Per ottenere il valore della cella E6 (2016) applicheremo semplicemente la funzione =ANNO(Data_Iniziale)
Nella scheda sottostante verrà riprodotto un calendario mensile con indicazione per ciascuna data dei corrispondenti giorni della settimana; i giorni di sabato e domenica, mediante la applicazione della formattazione condizionale, saranno evidenziati con un colore delle celle diverso e l’utilizzo del carattere in grassetto.
L’intervallo di celle A13:A43 sarà predisposto nel formato data e tipo gg/m e la prima formula che andremo ad inserire nella cella A13 sarà =DATA(ANNO(Data_Iniziale);MESE(Data_Iniziale);GIORNO(Data_Iniziale)).
In sostanza estrapoliamo dalla Data iniziale i valori relativi ad anno, mese e giorno. Nella cella A14 aumenteremo di 1 il valore contenuto nella cella A13. Dalla cella A15 fino alla cella A43 aumenteremo di un’unità questo valore ottenendo i giorni del mese.
=> Come creare una busta paga in Excel
Se il mese è composto di 30 giorni (o 28/29 per febbraio) le celle A41:A43 assumeranno il valore della data del mese successivo e non saranno prese in considerazione. Per quanto riguarda la indicazione del corrispondente giorno della settimana nell’intervallo B13:B43 valga ad esempio la formula inserita nella cella B13: =SE(GIORNO.SETTIMANA(A13;2)=1;”Lunedì”;SE(GIORNO.SETTIMANA(A13;2)=2;”Martedì”;SE(GIORNO.SETTIMANA(A13;2)=3;”Mercoledì”;SE(GIORNO.SETTIMANA(A13;2)=4;”Giovedì”;SE(GIORNO.SETTIMANA(A13;2)=5;”Venerdì”;SE(GIORNO.SETTIMANA(A13;2)=6;”Sabato”;SE(GIORNO.SETTIMANA(A13;2)=7;”Domenica”))))))).
Abbiamo scelto una formula piuttosto lunga che utilizza la funzione SE abbinata alla funzione GIORNO.SETTIMANA per correlare al valore ottenuto i nomi dei giorni della settimana. C’è un modo più semplice (e leggibile) per ottenere lo stesso risultato? La risposta è affermativa utilizzando ad esempio l’abbinamento tra le funzioni SCEGLI e GIORNO.SETTIMANA: =SCEGLI(GIORNO.SETTIMANA(A13;2);”Lunedì”;”Martedì”;”Mercoledì”;”Giovedì”;”Venerdì”;”Sabato”;”Domenica”) .
=> Scopri come creare un business plan con strumenti gratuiti
Possiamo quindi inserire gli orari normalmente applicati alle giornate lavorative.
Le celle F8:F9 e H8:H9 sono state predisposte nel formato Ora (tipo hh:mm9 e i valori dovranno essere inseriti dividendo con il punto i numeri relativi alle ore da quelli relativi ai minuti anche quando sono pari a zero (es: 0.30 – 12.45 – 12.00).
L’inserimento dei valori in questi quattro campi consentirà la compilazione automatica dell’intervallo di celle C13:F43. In ogni cella di questo intervallo la visualizzazione dei valori è subordinata alle seguenti condizioni: giorni della settimana diversi da Sabato e Domenica; nessuna annotazione di assenza per Malattia (M), Ferie (F) o Festività (Fe).
In definitiva questo intervallo di celle si compilerà da solo evitando la ripetizione della indicazione degli orari per ogni singola giornata lavorativa.
Valga ad esempio la formula contenuta nella cella C13: =SE(E(B13<>”Sabato”;B13<>”Domenica”;I13<>”M”;I13<>”F”;I13<>”Fe”;MESE(A13)=MESE(Data_Iniziale));F$8;””).
Il conteggio delle ore lavorate in ogni giornata viene effettuato nell’intervallo K13:K43 (Ore ordinarie) dove per ogni cella viene calcolata la differenza tra orari di uscita e di entrata subordinatamente alla verifica di determinate condizioni. Nella fattispecie: che si tratti di una giornata lavorativa (la casella relativa all’entrata del mattino non deve essere vuota).Vale per esempio la formula inserita in K13: =SE(C13<>””;SOMMA(D13-C13)+(F13-E13);””).
Riepilogando, nella sezione finale della scheda di rilevazione è stata predisposta una sezione riepilogativa in cui vengono conteggiati i valori inseriti nella scheda relativamente a: giorni di presenza, ritardi (in ore), permessi (in ore), giorni di assenza per malattia, giorni di assenza per ferie, giorni in cui cadono festività, ore straordinarie e notturne lavorate.
Per determinare le giornate di presenza abbiamo utilizzato la funzione CONTA.NUMERI applicata all’intervallo di celle A13:A43. La presenza di valori in questo intervallo ci da infatti l’indicazione esatta se la giornata è stata lavorata (non si tratta di sabato/domenica, non si sono registrate assenze per Malattie, Ferie, Festività). Per la rilevazione delle assenze per Malattia e Ferie o per le Festività viene utilizzata la colonna I13:I43 e pertanto i risultati di riepilogo saranno ottenuti con l’utilizzo della funzione CONTA.SE con indicazione nei criteri delle condizioni (M, F, Fe) utili a ottenere il conteggio dei valori per le tre tipologie di assenza.
