Excel: Applicare il formato numerico ora ed effettuare operazioni di calcolo

Lavorare con il formato ora | Calcolare ore e minuti in Excel

Se in un foglio Excel sono presenti valori formattati come orario (Formato ORA), potrebbe essere necessario eseguire operazioni di calcolo, come ad esempio la somma delle ore lavorative, la differenza tra l'orario di uscita e quello di entrata, o moltiplicare un numero di ore per una retribuzione oraria, espressa ovviamente in formato valuta.

Questo sezione, mostra come inserire correttamente il formato ora in Excel, come formattarlo e convertirlo in un formato numerico decimale.
Vengono inoltre spiegate le principali operazioni di calcolo applicabili alle ore.
Alla fine della sezione, i contenuti trattati vengono riassunti in un breve video dedicato al formato ora in Excel.

Contenuti tutorial: il formato numerico ora

[01] - Il formato ORA di Excel
[02] - Imputare correttamente il Formato ORA
[03] - Convertire il tempo, in un formato numerico decimale attraverso una operazione aritmetica
[04] - Convertire il tempo in formato numerico decimale, con la Funzione CONVERTI
[05] - Come sommare ore e minuti in Excel
[06] - Come sottrarre le ORE (Esempio: calcolo entrata uscita lavoro)
[07] - Come moltiplicare ORE per un numero (Esempio: il calcolo di un compenso)
[08] - Esempio di Moltiplicazione di un totale ore per un compenso orario
[09] - Combinare il formato ORA con il formato DATA
[10] - Il formato ora spiegato in un video

Il Formato ORA di Excel

La caratteristiche del Formato ora non rendono particolarmente semplici le operazioni di calcolo in Excel.
Per poter supportare le operazioni di calcolo sul formato ORA, Excel trasforma e salva gli orari in un numero che va da 0 a 1 per l'arco delle 24 ore. Questo numero rappresenta il risultato della divisone dell'orario stesso per il totale delle ore della giornata ovvero 24. Il valore numerico di un'ora è pertanto rappresentato dalla frazione di un numero intero che equivale ad un ventiquattresimo del giorno ossia 1/24 = 0,041666

Il numero "seriale" del Formato ORA di Excel

In realtà non si tratta di un vero numero seriale come per le date.
In Excel il Formato ora viene memorizzato attraverso un numero, che varia da 0 a 1 per l’arco delle 24 ore di una giornata.
Come anticipato nel precedente punto il numero rappresenta il risultato della divisone dell'orario stesso per il totale delle ore della giornata ovvero 24.
Ad esempio le ore 9:00 vengono memorizzata con il numero 0,375 e l'orario delle 21:00 come 0,875.
Nella sottostante tabella sono riportati alcuni esempi.

 

Imputare correttamente il Formato ORA

Per digitare direttamente un'informazione numerica in Formato ORA vanno utilizzati i due punti “:” per separare ore, minuti e secondi.
Se si digita 6:30:00 le informazioni immesse verranno automaticamente riconosciute da Excel con formattazione ora.

Per immettere un'ora in formato 12 ore anziché con il formato 24 ore, digitare uno spazio e quindi una "a" oppure una "p" dopo l'ora, ad esempio 7:00 p
Se non si aggiunge la "a" oppure la "p", l'ora verrà immessa nel classico formato 24.
Se non si desidera tenere traccia dei secondi, è sufficiente digitare 1:30
Se nell'informazione ora, vi fosse la necessità di inserire i centesimi di secondo, questi vanno separati dai secondi con una virgola e non con i due punti.
Esempio: si dovesse inserire il valore di Ventotto minuti venticinque secondi e dieci centesimi andrebbe imputato nel seguente modo: 28:25,10

Convertire il tempo, in un formato numerico decimale attraverso una operazione aritmetica

Il modo più semplice per convertire un valore espresso in formato ora in un numero equivalente su base decimale è quello di moltiplicare il valore per il numero di ore, minuti o secondi presenti in un giorno.
Ore = 24,  Minuti = 1440, Secondi = 86400
Nell'immagine seguente nella colonna A del Foglio di lavoro sono elencate una serie di ore che nelle successive colonne B, C e D sono trasformate nei rispettivi numeri in formato decimale.

Osservazioni. Come precedentemente citato In Excel il Formato ora viene memorizzato attraverso un numero, che varia da 0 a 1 per l’arco delle 24 ore di una giornata.
La trasformazione delle ore 3:30 nel numero 3,5 ha per altro trasformato da sessagesimale a centesimale il formato dei minuti 30/60 corrispondono a 50/100.
Non va trascurato che le ore 3:30 corrispondo per Excel al numero 0,14583. Mentre il risultato finale 3,5 ci restituisce un numero in formato decimale.

ATTENZIONE. Se a seguito dell'operazione di calcolo il risultato fosse restituito in formato ora, è sufficiente modificare la formattazione in Generale oppure in Numero.

Convertire il tempo in formato numerico decimale, con la Funzione CONVERTI

Per convertire il tempo in un formato numerico decimale, in alternativa all'operazione aritmetica è possibile applicare la Funzione CONVERTI.
CONVERTI è una Funzione di Excel che appartiene al gruppo delle ingegneristiche, e pur essendo una Funzione molto interessante è poco conosciuta e conseguentemente poco applicata nei Fogli di lavoro.
:: La Funzione CONVERTI di Excel permette di convertire un numero da un sistema di unità di misura a un altro (non solo il tempo, ma anche per le distanze, i pesi, e altre misurazioni fisiche).
Nello specifico la Funzione ha tre argomenti: =CONVERTI(num;da_misura;a_misura)
Nella sottostane immagine viene applicata la Funzione CONVERTI.
Il primo argomento fa riferimento al valore contenuto nella cella A, i due successivi argomenti sono la trasformazione da giorno "day" in ore "hr", oppure minuti "mn" a seconda dell'obiettivo del calcolo.
Per prendere visione dei codici che la Funzione CONVERTI utilizza come argomenti relativi a Tempo, nell'immagine è illustrata una tabella riepilogativa.

:: Per un approfondimento sulla Funzioni CONVERTI si rimanda a: Convertire un numero da un sistema di unità di misura all'altro

 

Come sommare ore e minuti in Excel

Per il conteggio delle ore è possibile usare la Funzione SOMMA. Va però posta MOLTA ATTENZIONE nell'applicare al risultato il formato numerico corretto !!

Esempio su come sommare il formato ora in Excel.
Nel foglio di lavoro successivamente raffigurato, si vuole effettuare il calcolo dell'ammontare complessivo delle ore di lavoro settimanale.
Nella cella C10 è stata impostata la seguente Funzione: =SOMMA(C3:C8)

 

Il risultato che inizialmente Excel restituisce dalla sommatoria presente nella cella C10 è di: 15:15  e ovviamente non corrisponde al conteggio corretto!
Il motivo è che il formato ORA prevede un conteggio massimo fino al numero 24, e totalizzate le prime 24 ore Excel riparte da 0 (non riesce pertanto a rendere visibile un calcolo cumulativo oltre il numero 24).

Per rendere visibile il risultato corretto della totalizzazione delle ore di lavoro è necessario aprire la scheda Formato Celle e nella categoria Ora selezionare il formato 37:30:55.
Questa particolare formattazione [37:30:55] permette ad Excel di applicare un conteggio del tempo svincolato dal valore massimo di 24 tipico di questo formato numerico.
Nella finestra di dialogo Formato celle, è possibile considerare tutte le formattazioni che Excel prevede per il Formato numerico ORA.

:: Consiglio. Per aprire velocemente la scheda Formato celle è possibile usare la combinazione CTRL + 1

:: Per un approfondimento sul Formato ora si rimanda a: Formattazione numerica, impostazione e personalizzazione del Formato data e ora

Come sottrarre le ORE (Esempio: calcolo entrata uscita lavoro)

Una situazioni frequente di calcolo con il formato ora, è la differenza fra orari di entrata e uscita, per calcolare le ore di lavoro.
Si tratta di una operazione di tipo aritmetico, come la precedente, senza ausilio di Funzioni ed esemplificata nella successiva immagine.
Nella tabella è stato ipotizzato un orario di lavoro spezzato mattina pomeriggio.
Nella cella G3 è stata inserita la seguente Formula =D3-C3+F3-E3 ovvero la differenza fra l'uscita e l'entrata del mattino sommata all'uscita meno l'entrata del pomeriggio.
Qualora i valori presenti nella colonna G dovessero successivamente essere sommati, come descritto nel precedente punto, è fondamentale modificare la formattazione del calcolo totale applicando il formato ora: 37:30:55

:: Per un approfondimento sulla costruzione di una tabella con un calcolo settimanale delle ore di lavoro si rimanda a: Calcolare le ore di lavoro ordinarie e straordinarie (Operazioni con il formato numerico ora)

Come moltiplicare ORE per un numero (Esempio: il calcolo di un compenso)

Se si vuole moltiplicare il numero totale delle ore per una retribuzione oraria, ci si trova nella difficoltà di dover trasformare i minuti (espressi in formato sessagesimale) in un corrispondente formato centesimale.
::Attenzione. Se dovessimo moltiplicare 22 ore e 50 minuti per una retribuzione oraria non è possibile considerare l'ora come 22,5 (i 50 minuti sono 50/60 non 50/100).
Oltretutto, come spiegato all'inizio di questo tutorial, ricordiamo che Excel ha in salvato l'orario delle 22:50 nel numero 0,951333 e pertanto l'eventuale moltiplicazione sarebbe per 0,951333 !

Come precedentemente spiegato, per trasformare i minuti espressi in sessantesimi nell'equivalente numerico espresso in centesimi, bisogna moltiplicare per 24. (vedi esempio e immagine sottostante)
Il risultato di questa operazione rende possibile la successiva moltiplicazione per una retribuzione oraria.

Esempio di Moltiplicazione di un totale ore per un compenso orario

Consideriamo l'esempio di 12:50 ore, se questo numero viene moltiplicato per 24, si ottiene 12,83.
Il decimale 83 rappresenta la trasformazione dei 50 sessantesimi del formato ora nell'equivalente rapporto su base centesimale.
Effettuata questa operazione è possibile moltiplicare il valore ottenuto per una cifra numerica ad esempio in formato valuta.

 

Calcolare un compenso moltiplicando un totale ore per una retribuzione oraria, può essere sviluppato in un'unica Formula. Come illustrato nell'immagina sottostante è possibile inserire nella cella F3 la seguente operazione =B3*24*D3  

 

Riassumendo. La difficoltà nel trattare le operazione sulle ore e minuti è dovuta al fatto che l'ora è divisa in 60 minuti, pertanto, qualora fosse necessario moltiplicare un valore ora per un numero, bisogna trasformare il valore dell'ora espresso su base sessagesimale nei corrispondenti centesimi.

Combinare il formato ORA con il formato DATA

Excel permette di combinare questi due formati numerici per consentire operazioni di calcolo più complesse, ovvero quante ore sono trascorse a partire da una preciso orario di una data.
L'elemento che supporta questo calcolo è legato al numero seriale progressivo delle date a cui si aggiunge una parte decimale che  va da 0 a 1 relativa al formato ora.
Esempio. Nell'immagine sottostante è possibile verificare l'abbinamento del numero seriale della data del 22/12/2020 con il numero 0,5 corrispondente alla ore 12:00.
Il numero seriale 44186 rappresenta la data del 21/12/2020, il numero 44186,5 rappresenta invece una precisa ora (le 12:00) sempre del 21/12/2020

:: ATTENZIONE. Le date vengono memorizzate in Excel attraverso una numerazione seriale.
Excel utilizza il sistema data 1900 ovvero il numero 1 corrisponde per Excel alla data del 1/1/1900 e così a seguire per tutte le date successive, pertanto il numero 2 corrisponde al 2/1/1900 fino arrivare ai nostri giorni nei quali il numero 44.186 corrisponde al 21 dicembre 2020.

Per un approfondimento sul formato ora, è disponibile questo video presente sul canale Youtube

Indice Generale Argomenti: EXCEL

[Valter Borsato - Febbraio 2014 | aggiornato il 27/04/2024]