www.valterborsato.it

Calcolare le ore di lavoro con Excel

Creare un Foglio di Excel che permette di calcolare le ore di lavoro settimanali e lo stipendio incluse eventuali ore straordinarie.
Per risolvere l'esercizio vengono utilizzate le seguenti Funzioni: SE; SOMMA; e applicate formattazioni numeriche personalizzate per il formato data e ora.

Impostazione tabella calcolo orario di lavoro - Registro presenza

A titolo di esempio viene ipotizzato un calcolo su base settimanale, ma l'esercizio può facilmente essere declinato su un periodo di durata mensile.
Nella colonna B della tabella vanno inseriscono le date delle giornate lavorative. Nella cella B2 è sufficiente imputare la sola data del lunedì, poichè nella sottostante cella (che successivamente sarà possibile trascinare) è stata inserita la formula =B3+1 
Per ragioni di spazio è stata sostituita la data estesa (Lunedì 9 febbraio 2015) trasformandola nel formato numerico personalizzato ggg gg/mm  dove le prime tre lettere "g" rappresentano il giorno abbreviato e i due successivi valori "gg" e "mm" il giorno e il mese in formato numerico. Il risultato di questa formattazione personalizzata visualizza la data nel seguente formato: lun 09/02
Nelle colonne C,D,E,F vanno inseriti gli orari di entrata e uscita dal lavoro.

:: ATTENZIONE! Per digitare direttamente un'informazione numerica in formato ora vanno utilizzati i due punti “:” per separare ore, minuti e secondi. Ad esempio digitando 6:30:00 i valori immessi verranno automaticamente riconosciute da Excel come formato numerico ora.

Excel: calcolare le ore di lavoro

Impostazione formule per il calcolo del totale ore giornaliere e settimanali

Nell'esempio viene ipotizzato un orario di lavoro spezzato mattina - pomeriggio, nel totalizzare le ore della giornata sarà necessario improntare una formula che preveda di sommare la differenza fra l'orario di uscita e di entrata del mattino e del pomeriggio.
Nel caso dell'orario del lunedì imputato nella riga 3 nella cella va inserita la formula =D3-C3+F3-E2:E3
Ovviamente è possibile trascinare con il pulsante di riempimento la formula nelle celle sottostanti per ottenere le successive giornate.

Nella cella H11 è inserita un funzione di sommatoria =SOMMA(H3:H9)

Excel: sommare  le ore di lavoro

ATTENZIONE: il risultato che viene restituito nella cella H11 risulta apparentemente sbagliato. In realtà il formato numerico ORA quando arriva al valore 24 si azzera. Di conseguenza al totale delle ore 17:20 mancano la somma delle prime 24 ore.
Per formattare correttamente il risultato, nella finestra di dialogo Formato celle cliccare sulla scheda Numero. All'interno della scheda Numero, nella colonna a sinistra selezionare il formato Ora e nella colonna a destra scegliere il formato 37:50:55, che serve appositamente per il conteggio delle ore. Applicando questa formattazione nella cella H11 si visualizza il corretto risultato 41:20

Per un approfondimento sul formato ora: Excel: formato ora. Funzioni di base per calcolare e sommare ore minuti

:: Per aprire la finestra di dialogo formato celle, nella scheda HOME nel gruppo pulsanti Numeri, cliccare in basso destra sul pulsante di apertura della finestra di dialogo 

Excel: accedere alla finestra di dialogo Formato celle

Excel: formato ora 37:50:55

Trasformare il formato ora in numero

Una volta totalizzato il monte ore è necessario trasformare il formato ora in un formato numerico che permetta di poterlo moltiplicare per il valore del compenso orario.
Excel salva il formato ora in un numero seriale che va da 0 a 1 per l'arco delle 24 ore. Non a caso 1 è in Excel il valore numerico seriale dell'unità tempo giorno.
Dal punto di vista pratico si deve semplicemente moltiplicare il totale del monte ore per 24, in questo modo la suddivisione sessantesimale dei minuti e secondi del formato ora viene trasformata nell'equivalente numerico centesimale.
Il valore 41:20 moltiplicato per 24 restituisce il numero 41,33, questo numero può essere successivamente moltiplicato per la retribuzione oraria.
Per un approfondimento si rimanda a: Excel: formato ora, funzioni di base, calcolare e sommare ore minuti

Calcolare il compenso e applicazione dello straordinario

Come da esempio, si ipotizza che le prime 36 ore settimanali siano retribuite a € 17,50, mentre le eventuali ore successive ad € 19,00
Dal punto di vista del calcolo, nel caso di un monte ore settimanale di 41:20 le prime 36 ore saranno moltiplicate per 17,50 e la differenza (41:20 meno 36:00) sarà moltiplicata per 19,00
nel caso però che il totale delle ore non superasse le 36 il calcolo dello stipendio sarà dato dalla sola operazione di moltiplicare il totale ore per la retribuzione ordinaria.
Essendoci due condizioni diverse è necessario impostare un formula SE il cui test logico verifichi se il totale delle ore settimanali ha superato il limite delle 36 ore. nella successiva espressione del vero e falso vera rispettivamente formulato un calcolo che ipotizzi il conteggio dello straordinario oppure la sola retribuzione ordinaria.

La funzione SE sarà espressa come segue: =SE(H12>36;C12*36+(H12-36)*C13;H12*C12)

Excel: calcolare il lavoro straordinario con una funzione SE

Per un approfondimento sulla Funzione SE: Funzione SE Excel (introduzione alle funzioni logiche)

Indice argomenti correlati: DATA e ORA Giorni mesi anni e orari

01 - Formato data: Le Funzioni GIORNO; MESE; ANNO; FRAZIONE.ANNO - Le date in Excel prima parte
02 - Formato data: Giorni lavorativi, le Funzioni GIORNI.LAVORATIVI.TOT e GIORNO.LAVORATIVO - Le date in Excel seconda parte
03 - Formato data: Calcolare numero giorni mesi e anni con la Funzione DATA.DIFF - Le date in Excel terza parte
04 - Formato data: Le Funzioni GIORNO.SETTIMANA; NUM.SETTIMANA; DATA.MESE e FINE.MESE - Le date in Excel quarta parte
05 - Formato data: Le Funzioni OGGI; ADESSO e la Funzione DATA.VALORE - Le date in Excel quinta parte
06 - Formato ora: Funzioni di base - calcolare e sommare ore minuti
07 - Formato ora: Arrotondare ore e minuti con le Funzioni ARROTONDA e ARROTONDA.MULTIPLO
08 - Formattazione numerica: Personalizzazione del formato data e ora

Approfondimenti & Esercitazioni
01 - Estrarre la data da una stringa di testo con le funzioni DATA, SINISTRA, DESTRA e STRINGA.ESTRAI
02 - Estrarre la data di nascita dal Codice Fiscale
03 - Conteggio ore di lavoro settimanali ordinarie e straordinarie, Formule e Funzioni con il formato numerico ora
04 - Conteggiare Giorni, Mesi ed Anni fra due date
05 - Inserire la data corrente (data oggi) in una cella: Funzioni e comandi da tastiera

Indice Generale Argomenti: EXCEL - WORD & POWERPOINT