Poiché le date e le ore sono memorizzate come numeri nel back-end in Excel, puoi facilmente utilizzare semplici operazioni e formule aritmetiche sui valori di data e ora.
Ad esempio, è possibile aggiungere due valori di ora o data diversi oppure è possibile calcolare la differenza di ora tra due date/orari specificati.
In questo tutorial, ti mostrerò un paio di modi per eseguire calcoli usando il tempo in Excel (come calcolare la differenza di tempo, aggiungere o sottrarre tempo, mostrare il tempo in diversi formati e fare una somma di valori temporali)
In che modo Excel gestisce data e ora?
Come ho detto, le date e le ore sono memorizzate come numeri in una cella in Excel. Un numero intero rappresenta un giorno completo e la parte decimale di un numero rappresenterebbe la parte del giorno (che può essere convertita in valori di ore, minuti e secondi)
Ad esempio, il valore 1 rappresenta il 01 gennaio 1900 in Excel, che è il punto di partenza da cui Excel inizia a considerare le date.
Quindi, 2 significherebbe il 2 gennaio 1990, 3 significherebbe il 3 gennaio 1900 e così via, e 44197 significherebbe il 1 gennaio 2021.
Nota: Excel per Windows ed Excel per Mac seguono date di inizio diverse. 1 in Excel per Windows significherebbe 1 gennaio 1900 e 1 in Excel per Mac significherebbe 1 gennaio 1904
Se ci sono cifre dopo un punto decimale in questi numeri, Excel le considera come parte del giorno e può essere convertito in ore, minuti e secondi.
Ad esempio, 44197.5 significherebbe 01 gennaio 2021 12:00:00.
Quindi, se stai lavorando con i valori temporali in Excel, essenzialmente lavorerai con la parte decimale di un numero.
Ed Excel ti offre la flessibilità di convertire quella parte decimale in diversi formati come solo ore, solo minuti, solo secondi o una combinazione di ore, minuti e secondi
Ora che hai capito come viene memorizzato il tempo in Excel, diamo un'occhiata ad alcuni esempi su come calcolare la differenza di tempo tra due date o orari diversi in Excel
Formule per calcolare la differenza di tempo tra due tempi
In molti casi, tutto ciò che vuoi fare è scoprire il tempo totale che è trascorso tra i due valori di tempo (come nel caso di una scheda attività che ha l'In-time e l'Out-time).
Il metodo che scegli dipende da come viene menzionata l'ora in una cella e dal formato in cui desideri il risultato.
Diamo un'occhiata a un paio di esempi
Sottrazione semplice di Calcola differenza di tempo in Excel
Poiché l'ora è memorizzata come numero in Excel, trova la differenza tra i valori dell'ora 2, puoi facilmente sottrarre l'ora di inizio dall'ora di fine.
Ora di fine - Ora di inizio
Il risultato della sottrazione sarebbe anche un valore decimale che rappresenterebbe il tempo trascorso tra i due valori temporali.
Di seguito è riportato un esempio in cui ho l'ora di inizio e di fine e ho calcolato la differenza di orario con una semplice sottrazione.
È possibile che i risultati vengano visualizzati nel formato dell'ora (invece dei decimali o in valori di ore/minuti). Nel nostro esempio sopra, il risultato nella cella C2 mostra 09:30 invece di 9,5.
Va perfettamente bene poiché Excel tenta di copiare il formato dalla colonna adiacente.
Per convertirlo in un decimale, cambia il formato delle celle in Generale (l'opzione si trova nella scheda Home nel gruppo Numeri)
Una volta ottenuto il risultato, puoi formattarlo in diversi modi. Ad esempio, puoi mostrare il valore solo in ore o solo minuti o una combinazione di ore, minuti e secondi.
Di seguito sono riportati i diversi formati che è possibile utilizzare:
| Formato | Cosa fa |
| h | Mostra solo le ore trascorse tra le due date |
| hh | Mostra le ore a due cifre (come 04 o 12) |
| hh:mm | Mostra le ore e i minuti trascorsi tra le due date, ad esempio 10:20 |
| hh:mm:ss | Mostra ore, minuti e secondi trascorsi tra le due date, ad esempio 10:20:36 |
E se ti stai chiedendo dove e come applicare questi formati di data personalizzati, segui i passaggi seguenti:
- Seleziona le celle in cui desideri applicare il formato della data
- Tieni premuto il tasto Ctrl e premi il tasto 1 (o Comando + 1 se utilizzi Mac)
- Nella finestra di dialogo Formato celle che si apre, fai clic sulla scheda Numero (se non è già selezionata)
- Nel riquadro di sinistra, fai clic su Personalizza
- Inserisci uno dei codici di formato desiderati nel campo Tipo (in questo esempio, sto usando hh:mm:ss)
- Fare clic su OK
I passaggi precedenti cambierebbero la formattazione e ti mostrerebbero il valore in base al formato.
Tieni presente che la formattazione dei numeri personalizzati non modifica il valore nella cella. Cambia solo il modo in cui viene visualizzato un valore. Quindi, posso scegliere di mostrare solo il valore dell'ora in una cella, mentre avrebbe ancora il valore originale.
Suggerimento per professionisti: se il numero totale di ore supera le 24 ore, utilizza invece il seguente formato numerico personalizzato: [hh]:mm:ss
Calcola la differenza di orario in ore, minuti o secondi
Quando si sottraggono i valori dell'ora, Excel restituisce un numero decimale che rappresenta la differenza di orario risultante.
Poiché ogni numero intero rappresenta un giorno, la parte decimale del numero rappresenterebbe quella parte del giorno che può essere facilmente convertita in ore, minuti o secondi.
Calcolo della differenza di orario in ore
Supponiamo di avere il set di dati come mostrato di seguito e di voler calcolare il numero di ore tra i due valori
Di seguito è la formula che ti darà la differenza di fuso orario in ore:
=(Si2-LA2)*24
La formula sopra ti darà il numero totale di ore trascorse tra i due valori.
A volte, Excel cerca di essere utile e ti darà anche il risultato in formato orario (come mostrato di seguito).
Puoi facilmente convertirlo in formato numero facendo clic sulla scheda Home e nel gruppo Numero, selezionando Numero come formato.
Se vuoi solo restituire il numero totale di ore trascorse tra i due tempi (senza alcuna parte decimale), usa la formula seguente:
=INT((LA2-LA2)*24)
Nota: questa formula funziona solo quando entrambi i valori dell'ora sono dello stesso giorno. Se il giorno cambia (dove uno dei valori dell'ora è di un'altra data e il secondo di un'altra data), questa formula darà risultati errati. Dai un'occhiata alla sezione in cui tratterò la formula per calcolare la differenza di orario quando la data cambia più avanti in questo tutorial.
Calcolo della differenza di tempo in minuti
Per calcolare la differenza di tempo in minuti, devi moltiplicare il valore risultante per il numero totale di minuti in un giorno (che è 1440 o 24*60).
Supponiamo di avere un set di dati come mostrato di seguito e di voler calcolare il numero totale di minuti trascorsi tra la data di inizio e quella di fine.
Di seguito è la formula che lo farà:
=(Si2-LA2)*24*60
Calcolo della differenza di tempo in secondi
Per calcolare la differenza di tempo in secondi, è necessario moltiplicare il valore risultante per il numero totale di secondi in un giorno (ovvero 24*60*60 o 86400).
Supponiamo di avere un set di dati come mostrato di seguito e di voler calcolare il numero totale di secondi trascorsi tra la data di inizio e quella di fine.
Di seguito è la formula che lo farà:
=(Si2-LA2)*24*60*60
Calcolo della differenza di orario con la funzione TESTO
Un altro modo semplice per ottenere rapidamente la differenza di orario senza preoccuparsi di cambiare il formato è utilizzare la funzione TESTO.
La funzione TESTO consente di specificare il formato direttamente all'interno della formula.
=TESTO(Data di fine - Data di inizio, Formato)
Il primo argomento è il calcolo che si desidera eseguire e il secondo argomento è il formato in cui si desidera visualizzare il risultato del calcolo.
Supponiamo di avere un set di dati come mostrato di seguito e di voler calcolare la differenza di tempo tra i due orari.
Ecco alcune formule che ti daranno il risultato con diversi formati
Mostra solo il numero di ore:
=TESTO(LA2-LA2,"hh")
La formula sopra ti darà solo il risultato che mostra il numero di ore trascorse tra i due valori. Se il tuo risultato è di 9 ore e 30 minuti, te ne mostrerà comunque solo 9.
Mostra il numero di minuti totali
=TESTO(B2-A2,"[mm]")
Mostra il numero di secondi totali
=TESTO(B2-LA2,"[ss]")
Mostra ore e minuti
=TESTO(LA2-LA2,"[hh]:mm")
Mostra ore, minuti e secondi
=TESTO(B2-A2,"hh:mm:ss")
Se ti stai chiedendo qual è la differenza tra hh e [hh] nel formato (o mm e [mm]), quando usi le parentesi quadre, ti darebbe il numero totale di ore tra le due date, anche se il il valore dell'ora è maggiore di 24. Quindi, se sottrai due valori di data in cui la differenza è maggiore di 24 ore, l'uso di [hh] ti darà il numero totale di ore e hh ti darà solo le ore trascorse il giorno della data di fine .
Ottieni la differenza di tempo in un'unità (ore/minuti) e ignora gli altri
Se si desidera calcolare la differenza di tempo tra i due valori temporali solo nel numero di ore o minuti o secondi, è possibile utilizzare la funzione dedicata ORA, MINUTO o SECONDO.
Ognuna di queste funzioni accetta un singolo argomento, che è il valore dell'ora, e restituisce l'unità di tempo specificata.
Supponiamo di avere un set di dati come mostrato di seguito e di voler calcolare il numero totale di ore, minuti e secondi che sono trascorsi tra questi due tempi.
Di seguito sono riportate le formule per farlo:
Calcolo delle ore trascorse tra due volte
=ORA(B2-A2)
Calcolo dei minuti dal risultato del valore temporale (escluse le ore completate)
=MINUTO(B2-A2)
Calcolo dei secondi dal risultato del valore dell'ora (escludendo le ore e i minuti completati)
=SECONDO(B2-A2)
Un paio di cose che devi sapere quando lavori con queste formule ORE, MINUTI e SECONDI:
- La differenza tra l'ora di fine e l'ora di inizio non può essere negativa (come spesso accade quando la data cambia). In tali casi, queste formule restituirebbero un #NUM! errore
- Queste formule utilizzano solo la parte dell'ora del valore dell'ora risultante (e ignorano la parte del giorno). Quindi, se la differenza tra l'ora di fine e l'ora di inizio è 2 giorni, 10 ore, 32 minuti e 44 secondi, la formula ORA darà 10, la formula MINUTI darà 32 e la formula SECONDO darà 44
Calcola il tempo trascorso Fino ad ora (dall'ora di inizio)
Se si desidera calcolare il tempo totale trascorso tra l'ora di inizio e l'ora corrente, è possibile utilizzare la formula ADESSO anziché l'Ora di fine.
La funzione ADESSO restituisce la data e l'ora correnti nella cella in cui viene utilizzata. È una di quelle funzioni che non accetta alcun argomento di input.
Quindi, se si desidera calcolare il tempo totale trascorso tra l'ora di inizio e l'ora corrente, è possibile utilizzare la formula seguente:
=ORA() - Ora di inizio
Di seguito è riportato un esempio in cui ho gli orari di inizio nella colonna A e il tempo trascorso fino ad ora nella colonna B.
Se la differenza di tempo tra la data e l'ora di inizio e l'ora corrente è superiore a 24 ore, è possibile formattare il risultato per mostrare il giorno e la parte dell'ora.
Puoi farlo usando la seguente formula TEXT:
=TESTO(ORA()-LA2,"gg hh:ss:mm")
Puoi anche ottenere la stessa cosa modificando la formattazione personalizzata della cella (come spiegato in precedenza in questo tutorial) in modo che mostri il giorno e la parte dell'ora.
Nel caso in cui l'ora di inizio abbia solo la parte dell'ora, Excel la considererà come l'ora del 1 gennaio 1990.
In questo caso, se usi la funzione ADESSO per calcolare il tempo trascorso fino ad ora, ti darà il risultato sbagliato (poiché il valore risultante avrebbe anche i giorni totali trascorsi dal 1 gennaio 1990).
In tal caso, puoi utilizzare la formula seguente:
=ORA()- INT(ORA())-LA2
La formula sopra utilizza la funzione INT per rimuovere la parte del giorno dal valore restituito dalla funzione now, e questa viene quindi utilizzata per calcolare la differenza di orario.
Nota che ORA è una funzione volatile che si aggiorna ogni volta che c'è una modifica nel foglio di lavoro, ma non si aggiorna in tempo reale
Calcola l'ora in cui la data cambia (calcola e visualizza i tempi negativi in Excel)
I metodi descritti finora funzionano bene se l'ora di fine è successiva all'ora di inizio.
Ma il problema sorge quando l'ora di fine è inferiore all'ora di inizio. Questo accade spesso quando si compilano schede attività in cui si inserisce solo l'ora e non l'intera data e ora.
In questi casi, se lavori in un turno di una notte e la data cambia, è possibile che l'ora di fine sia precedente all'ora di inizio.
Ad esempio, se inizi il tuo lavoro alle 18:00 di sera e completi il lavoro e il time out alle 9:00 del mattino.
Se lavori solo con i valori temporali, sottraendo l'ora di inizio dall'ora di fine otterrai un valore negativo di 9 ore (9 - 18).
Ed Excel non può gestire valori temporali negativi (e del resto nemmeno gli umani, a meno che tu non possa viaggiare nel tempo)
In questi casi, hai bisogno di un modo per capire che il giorno è cambiato e il calcolo dovrebbe essere fatto di conseguenza.
Per fortuna, c'è una soluzione davvero semplice per questo.
Supponiamo di avere un set di dati come mostrato di seguito, dove ho l'ora di inizio e l'ora di fine.
Come noteresti che a volte l'ora di inizio è la sera e l'ora di fine è la mattina (il che indica che si trattava di un turno di notte e il giorno è cambiato).
Se utilizzo la formula seguente per calcolare la differenza di fuso orario, mi mostrerà i segni di cancelletto nelle celle in cui il risultato è un valore negativo (evidenziato in giallo nell'immagine sottostante).
=LA2-LA2
Ecco una formula IF che se il valore della differenza temporale è negativo o meno, e nel caso in cui sia negativo, restituisce il risultato corretto
=SE((B2-A2)<0,1-(A2-B2),(B2-A2))
Anche se questo funziona bene nella maggior parte dei casi, è comunque insufficiente nel caso in cui l'ora di inizio e l'ora di fine siano distanti più di 24 ore. Ad esempio, qualcuno accede alle 9:00 del giorno 1 e si disconnette alle 11:00 del giorno 2.
Poiché sono più di 24 ore, non è possibile sapere se la persona si è disconnessa dopo 2 ore o dopo 26 ore.
Sebbene il modo migliore per affrontare questo problema sarebbe assicurarsi che le voci includano la data e l'ora, ma se è solo l'ora con cui stai lavorando, la formula sopra dovrebbe occuparsi della maggior parte dei problemi ( considerando che è improbabile che qualcuno lavori per più di 24 ore)
Aggiungere/Sottrarre tempo in Excel
Finora, abbiamo visto esempi in cui avevamo l'ora di inizio e quella di fine e dovevamo trovare la differenza di fuso orario.
Excel consente inoltre di aggiungere o sottrarre facilmente un valore di tempo fisso dalla data e dal valore di ora esistenti.
Ad esempio, supponiamo che tu abbia un elenco di attività in coda in cui ogni attività richiede il tempo specificato e desideri sapere quando terminerà ciascuna attività.
In tal caso, puoi facilmente aggiungere l'ora che ogni attività impiegherà all'ora di inizio per sapere a che ora dovrebbe essere completata l'attività.
Poiché Excel memorizza i valori di data e ora come numeri, è necessario assicurarsi che l'ora che si sta tentando di aggiungere rispetti il formato già seguito da Excel.
Ad esempio, se aggiungi 1 a una data in Excel, ti darà la data successiva. Questo perché 1 rappresenta un'intera giornata in Excel (che equivale a 24 ore).
Quindi, se vuoi aggiungere 1 ora a un valore di tempo esistente, non puoi andare avanti e semplicemente aggiungere 1 ad esso. devi assicurarti di convertire quel valore dell'ora nella parte decimale che rappresenta un'ora. e lo stesso vale per l'aggiunta di minuti e secondi.
Utilizzo della funzione TIME
La funzione Tempo in Excel prende il valore dell'ora, il valore dei minuti e il valore dei secondi e lo converte in un numero decimale che rappresenta questa volta.
Ad esempio, se voglio aggiungere 4 ore a un'ora esistente, posso utilizzare la formula seguente:
=Ora di inizio + ORA(4,0,0)
Questo è utile se sai quante ore, minuti e secondi vuoi aggiungere a un'ora esistente e usa semplicemente la funzione ORA senza preoccuparti della corretta conversione dell'ora in un valore decimale.
Inoltre, tieni presente che la funzione TIME prenderà in considerazione solo la parte intera del valore di ore, minuti e secondi che hai inserito. Ad esempio, se utilizzo 5,5 ore nella funzione TIME, aggiungerebbe solo cinque ore e ignorerebbe la parte decimale.
Inoltre, tieni presente che la funzione TIME può aggiungere solo valori inferiori a 24 ore. Se il valore dell'ora è superiore a 24, questo darebbe un risultato errato.
E lo stesso vale per i minuti e la parte dei secondi dove la funzione considererà solo i valori inferiori a 60 minuti e 60 secondi
Proprio come ho aggiunto il tempo usando la funzione TIME, puoi anche sottrarre il tempo. Basta cambiare il segno + in un segno negativo nelle formule sopra
Utilizzo dell'aritmetica di base
Quando la funzione del tempo è facile e comoda da usare, presenta alcune restrizioni (come spiegato sopra).
Se vuoi un maggiore controllo, puoi usare il metodo aritmetico che tratterò qui.
Il concetto è semplice: converti il valore dell'ora in un valore decimale che rappresenta la parte del giorno, quindi puoi aggiungerlo a qualsiasi valore dell'ora in Excel.
Ad esempio, se desideri aggiungere 24 ore a un valore temporale esistente, puoi utilizzare la formula seguente:
=Ora_inizio + 24/24
Questo significa solo che sto aggiungendo un giorno al valore temporale esistente.
Ora, portando avanti lo stesso concetto, supponiamo che tu voglia aggiungere 30 ore a un valore temporale, puoi utilizzare la formula seguente:
=Ora_inizio + 30/24
La formula sopra fa la stessa cosa, dove la parte intera del (30/24) rappresenterebbe il numero totale di giorni nel tempo che vuoi aggiungere e la parte decimale rappresenterebbe le ore/minuti/secondi
Allo stesso modo, se hai un numero specifico di minuti che desideri aggiungere a un valore temporale, puoi utilizzare la formula seguente:
=Start_time + (Minuti da aggiungere)/24*60
E se hai il numero di secondi che desideri aggiungere, puoi utilizzare la formula seguente:
=Start_time + (Minuti da aggiungere)/24*60*60
Sebbene questo metodo non sia facile come usare la funzione del tempo, lo trovo molto migliore perché funziona in tutte le situazioni e segue lo stesso concetto. a differenza della funzione ora, non devi preoccuparti se l'ora che vuoi aggiungere è inferiore a 24 ore o superiore a 24 ore
Puoi seguire lo stesso concetto sottraendo anche il tempo. Basta cambiare il + con un segno negativo nelle formule sopra
Come SOMMA il tempo in Excel
A volte potresti voler sommare rapidamente tutti i valori temporali in Excel. L'aggiunta di più valori temporali in Excel è abbastanza semplice (tutto ciò che serve è una semplice formula SUM)
Ma ci sono alcune cose che devi sapere quando aggiungi tempo in Excel, in particolare il formato della cella che ti mostrerà il risultato.
Diamo un'occhiata a un esempio.
Di seguito ho un elenco di attività insieme al tempo che ciascuna attività impiegherà nella colonna B e voglio aggiungere rapidamente questi tempi e conoscere il tempo totale che impiegherà per tutte queste attività.
Nella cella B9, ho usato una semplice formula SUM per calcolare il tempo totale che tutte queste attività impiegheranno e mi dà il valore di 18:30 (il che significa che ci vorranno 18 ore e 20 minuti per completare tutti questi compiti)
Tutto bene finora!
Come SOMMA su 24 ore in Excel
Ora guarda cosa succede quando cambio nel momento in cui ci vorrà l'attività 2 per completare da 1 ora a 10 ore.
Il risultato ora dice 03:20, il che significa che dovrebbero essere necessarie 3 ore e 20 minuti per completare tutte queste attività.
Non è corretto (ovviamente)
Il problema qui non è che Excel si incasina. Il problema qui è che la cella è formattata in modo tale da mostrare solo la parte temporale del valore risultante.
E poiché il valore risultante qui è più di 24 ore, Excel ha deciso di convertire la parte 24 ore in un giorno rimuoverla dal valore mostrato all'utente e visualizzare solo le ore, i minuti e i secondi rimanenti.
Questo, per fortuna, ha una soluzione semplice.
Tutto quello che devi fare è cambiare il formato della cella per forzare la visualizzazione delle ore anche se supera le 24 ore.
Di seguito sono riportati alcuni formati che è possibile utilizzare:
| Formato | Risultato atteso |
| [Hmm | 28:30 |
| [m]:ss | 1710:00 |
| d “D” hh:mm | 1 G 04:30 |
| d “D” hh “Min” ss “Sec” | 1 D 04 Min 00 Sec |
| d “Giorno” hh “Minuti” ss “Secondi” | 1 giorno 04 minuti 00 secondi |
Puoi cambiare il formato andando nella finestra di dialogo Formato celle e applicando il formato personalizzato, oppure usa la funzione TESTO e usa uno dei formati sopra nella formula stessa
È possibile utilizzare la formula sottostante TEXT per mostrare l'ora, anche se supera le 24 ore:
=TESTO(SOMMA(B2:B7);"[h]:mm:ss")
oppure la formula sottostante se si desidera convertire in giorni le ore eccedenti le 24 ore:
=TESTO(SOMMA(B2:B7);"gg hh:mm:ss")
Risultati che mostrano hash (###) invece di data/ora (motivi + correzione)
In alcuni casi, potresti scoprire che invece di mostrarti il valore temporale, Excel visualizza i simboli hash nella cella.
Ecco alcuni possibili motivi e i modi per risolverli:
La colonna non è abbastanza larga
Quando una cella non ha spazio sufficiente per mostrare la data completa, potrebbe mostrare i simboli di cancelletto.
Ha una soluzione semplice: cambia la larghezza della colonna e allargala.
Valore data negativo
Un valore di data o ora non può essere negativo in Excel. nel caso in cui stai calcolando la differenza di orario e risulta essere negativo, Excel ti mostrerà i simboli hash.
Il modo per correggere la modifica della formula per darti il risultato giusto. Ad esempio, se si calcola la differenza di orario tra i due orari e la data cambia, è necessario modificare la formula per tenerne conto.
In altri casi, puoi utilizzare la funzione ABS per convertire il valore del tempo negativo in un numero positivo in modo che venga visualizzato correttamente. In alternativa, puoi anche utilizzare una formula SE per verificare se il risultato è un valore negativo e restituire qualcosa di più significativo.
In questo tutorial, ho trattato argomenti sul calcolo del tempo in Excel (dove puoi calcolare la differenza di tempo, aggiungere o sottrarre tempo, mostrare il tempo in diversi formati e sommare i valori del tempo)
Spero che tu abbia trovato utile questo tutorial.
