Formule sul tempo in Excel: calcolare, formattare e manipolare l’ora in Excel

Excel rende facile lavorare con i numeri, ma gestire l’ora (secondo me) è tutta un’altra storia. La parte che confonde (di cui parlerò sotto) è che Excel memorizza ed elabora i valori temporali come frazioni di giorno, il che può portare a risultati inaspettati se non lo sai.

In questo tutorial, ti guiderò da zero: dalla creazione e formattazione dei valori orari alla risoluzione di problemi comuni come durate negative e overflow del tempo. Alla fine, saprai creare fogli più intelligenti e affidabili per pianificazione progetti, buste paga o qualsiasi altra attività su cui stai lavorando.

Capire il sistema delle date di Excel

Dobbiamo partire dal sistema delle date di Excel. Excel memorizza le date come numeri seriali sequenziali, a partire dal 1° gennaio 1900, rappresentato dal valore 1. Ogni giorno successivo incrementa il numero seriale di 1. Per esempio:

• 1 gennaio 1900 = 1

• 2 gennaio 1900 = 2

• 31 dicembre 2023 = 45205

I valori orari sono memorizzati come frazioni di un giorno. Per esempio:

• 06:00 = 0,25 (6 ore sono il 25% di un giorno di 24 ore)

• 12:00 = 0,5

• 18:00 = 0,75

Combinare data e ora è semplice come sommare i due valori. Ad esempio, la formula sotto combina una data e un’ora:

=A2 + TIME(10, 30, 0)

Se A2 contiene 45205 (31 dicembre 2023), il risultato sarà 45205,4375, che rappresenta le 10:30 del 31 dicembre 2023.

Come creare valori orari in Excel

Vediamo i due modi più comuni per creare valori orari validi in Excel: usando la funzione TIME() e convertendo testo con TIMEVALUE().

Usa la funzione TIME()

L’ho già citata. La funzione TIME() ti permette di costruire un valore orario da componenti separati: ore, minuti e secondi. Ecco la sintassi:

=TIME(hour, minute, second)

Per esempio, se vuoi rappresentare le 10:30, puoi scrivere:

=TIME(10, 30, 0)

Questo crea un valore orario corretto per le 10:30, memorizzato internamente come 0,4375 (dato che 10 ore e 30 minuti sono il 43,75% di un giorno di 24 ore).

Excel è flessibile con i valori inseriti. Se superi i limiti normali del tempo, Excel fa il rollover del tempo in eccesso. Per esempio:

=TIME(27, 0, 0)

Questo restituisce le 3:00 perché 27 ore equivalgono a un giorno intero (24 ore) più 3 ore. Allo stesso modo:

=TIME(10, 75, 0)

Questo viene interpretato come 11:15, poiché 75 minuti diventano 1 ora e 15 minuti.

Questi casi limite sono importanti, soprattutto se generi valori orari dinamicamente con formule.

Usa la funzione TIMEVALUE()

Puoi convertire testo in ora con TIMEVALUE(). Quindi, se il tuo foglio include orari scritti come testo, tipo “2:30 PM” o “14:45”, Excel potrebbe non trattarli automaticamente come valori orari. Qui entra in gioco TIMEVALUE().

=TIMEVALUE("2:30 PM")

Questo converte la stringa di testo in un valore orario di Excel. Ora puoi usarlo nei calcoli come qualsiasi altro orario.

Vale la pena notare la differenza tra TIME() e TIMEVALUE():

• TIME() costruisce un orario da numeri separati (ore, minuti, secondi)

• TIMEVALUE() converte una stringa di testo in un orario

Entrambe restituiscono valori che Excel riconosce come orari validi, ovvero ciò che ti serve per calcoli e formattazione accurati.

Come calcolare le differenze di tempo

Una volta che hai valori orari validi in Excel, uno dei compiti più comuni è calcolare quanto tempo è passato tra due momenti. Che tu stia misurando la durata di un turno o il tempo necessario per completare un’attività, Excel ti offre più modi per ottenere la risposta.

Esploriamo gli approcci più utili, dalla semplice sottrazione a trucchi più avanzati.

Sottrai direttamente due orari

Il modo più diretto per calcolare una differenza di tempo è sottrarre l’ora di inizio da quella di fine. 

Per esempio, se la cella A1 contiene 10:00 e B1 contiene 13:30, la formula è così:

=B1 - A1

Il risultato sarà 0,145833. Potrebbe sembrare strano all’inizio, ma ricorda che Excel rappresenta il tempo come frazione di un giorno. In questo caso, 0,145833 equivale a 3 ore e 30 minuti.

Per rendere il risultato più utile, di solito vorrai convertirlo in un formato leggibile.

Converti le differenze di tempo in ore, minuti o secondi

Puoi moltiplicare il risultato di una sottrazione temporale per esprimere la differenza in ore, minuti o secondi. Ecco come convertire una differenza di tempo in ore:

=(B2 - A2) * 24

Ecco come convertire una differenza di tempo in minuti:

=(B2 - A2) * 1440

Ecco come convertire una differenza di tempo in secondi:

=(B2 - A2) * 86400

Queste formule funzionano perché in un giorno ci sono 24 ore, 1.440 minuti e 86.400 secondi. Se la differenza è di tre ore e mezza, otterrai 3,5 dalla formula per le ore, 210 da quella per i minuti e 12.600 da quella per i secondi.

Estrai unità temporali specifiche

Se vuoi estrarre solo una parte del tempo, ad esempio solo i minuti o le ore, puoi usare HOUR(). HOUR() restituisce la parte delle ore di un orario.

=HOUR(B2 - A2)

MINUTE() restituisce la parte dei minuti.

=MINUTE(B2 - A2)

SECOND() restituisce i secondi.

=SECOND(B2 - A2)

Queste funzioni sono utili, ma si azzerano dopo 23 ore, 59 minuti o 59 secondi. Quindi, se la durata supera un giorno intero, non rifletteranno il tempo totale trascorso. In quei casi, è meglio moltiplicare la differenza o usare formati personalizzati, che vedremo più avanti.

Somma valori orari oltre le 24 ore

Se devi aggiungere ore che superano le 24, l’uso diretto di TIME() non funzionerà come previsto. Invece, puoi fare calcoli usando frazioni di giorno.

Ecco come aggiungere ore oltre 24.

=A2 + (28/24)  ' Adds 28 hours

Ecco come aggiungere minuti oltre 60.

=A2 + (90/1440)  ' Adds 90 minutes

Ecco come aggiungere secondi oltre 60.

=A2 + (3600/86400)  ' Adds 3600 seconds

Excel memorizza l’ora come decimale, dove 1 giorno equivale a 1, quindi dividere per 24, 1440 o 86400 adatta il valore a rappresentare una porzione di giorno.

Gestisci i valori orari negativi

Per impostazione predefinita, se sottrai un orario più grande da uno più piccolo (es., 10:00 - 14:00), Excel restituirà un errore o visualizzerà una serie di ###.

Per gestire in sicurezza i valori orari negativi, usa la funzione MOD(). Questa funzione di fatto impedisce risultati negativi riportandoli a un orario valido.

Per esempio, per sottrarre 3 ore in sicurezza:

=MOD(A2 - TIME(3, 0, 0), 1)

Questa formula assicura che il risultato sia sempre un valore orario positivo, evitando errori o problemi di visualizzazione.

Come formattare correttamente i risultati temporali

I calcoli sul tempo in Excel spesso producono valori decimali confusi o risultati inaspettati, soprattutto quando si trattano durate oltre le 24 ore o valori negativi. Una formattazione corretta può trasformare output grezzi in formati leggibili e facili da interpretare e presentare.

Usare la formattazione oraria di base

Se vuoi visualizzare una differenza di tempo come “3:30” invece di un decimale, usa la funzione TEXT().

=TEXT(B2 - A2, "h:mm")

Questo formatta il risultato come ore e minuti. Tieni solo presente che l’output è testo: ha un bell’aspetto ma non potrai usarlo in ulteriori calcoli matematici a meno di riconvertirlo.

Applicare formati orari personalizzati

I formati orari predefiniti di Excel gestiscono bene i valori standard, ma i formati personalizzati offrono maggiore flessibilità per visualizzare durate, ore prolungate o componenti temporali specifici.

Per applicare un formato orario personalizzato, segui questi passaggi:

1. Seleziona le celle che contengono i valori orari.
2. Clic destro e scegli Formato celle.
3. Nella scheda Numero, seleziona Personalizzato.
4. Inserisci il codice di formato desiderato.

Ecco alcuni formati orari personalizzati comuni:

• [h]:mm: visualizza ore e minuti totali, anche oltre 24 ore. Esempio: 36:45 (36 ore e 45 minuti)

• d "days" h:mm:ss: visualizza giorni, ore, minuti e secondi. Esempio: 1 days 12:30:00

• h:mm AM/PM: visualizza l’ora con notazione AM/PM. Esempio: 10:30 PM

• mm:ss: visualizza solo minuti e secondi. Ecco un esempio: 45:15

I formati personalizzati ti permettono di presentare i dati temporali in modi più intuitivi per casi d’uso specifici, come timesheet o registri di lavoro.

Visualizzare differenze di tempo leggibili

Se vuoi mostrare una differenza di tempo come stringa leggibile, ad esempio “2 giorni, 4 ore e 15 minuti”, puoi combinare più funzioni in un’unica formula.

Ecco un esempio pratico:

=INT(B2 - A2) & " days, " & HOUR(B2 - A2) & " hours, " & MINUTE(B2 - A2) & " minutes"

In questa formula, la funzione INT() estrae il numero di giorni interi, mentre HOUR() e MINUTE() estraggono le ore e i minuti rimanenti.

Per evitare che i valori zero compaiano nell’output, puoi annidare questi calcoli in IF(). Per esempio:

=IF(INT(B2 - A2) > 0, INT(B2 - A2) & " days, ", "") &
 IF(HOUR(B2 - A2) > 0, HOUR(B2 - A2) & " hours, ", "") &
 MINUTE(B2 - A2) & " minutes"

Questa formula mostra solo le unità rilevanti, rendendo l’output più pulito e leggibile.

Funzioni di Excel sul tempo da conoscere

Come hai visto finora, Excel include un set potente di funzioni temporali. Per essere più completi, ecco una rapida carrellata delle funzioni più importanti.

Usare la funzione TIME() per creare valori orari

TIME() costruisce un valore orario valido da tre componenti: ore, minuti e secondi. È ideale quando devi creare un orario in modo programmatico.

Ecco un buon esempio:

=TIME(14, 30, 0)  

Quanto sopra restituirà le 14:30, in base a questa sintassi.

=TIME(hour, minute, second)

Convertire testo in ora con TIMEVALUE()

Se hai un orario scritto come stringa di testo (es., "2:45 PM"), usa TIMEVALUE() per convertirlo in un valore orario corretto di Excel utilizzabile nei calcoli.

=TIMEVALUE("2:45 PM")

Per esempio, questo restituirà 0,614583 che è una frazione di giorno:

=TIMEVALUE("14:45")

Ottenere data e ora correnti con NOW()

NOW() restituisce data e ora correnti come un unico valore. Si aggiorna automaticamente ogni volta che il foglio ricalcola.

=NOW()

Ecco un buon esempio. Se l’ora corrente è 15:15, NOW() potrebbe restituire 44710,63542, dove 44710 è la data e ,63542 rappresenta l’ora.

Recuperare la data corrente con TODAY()

Se ti serve solo la data corrente senza l’ora, usa TODAY(). È particolarmente utile per calcolare scadenze o l’anzianità dei dati.

=TODAY()

Estrarre componenti temporali con HOUR(), MINUTE() e SECOND()

Queste funzioni ti permettono di isolare parti specifiche di un valore orario, utili per reportistica e logiche condizionali.

HOUR() restituisce la componente delle ore di un valore orario (0-23).

=HOUR(A2)

MINUTE() restituisce la componente dei minuti (0-59).

=MINUTE(A2)

SECOND() restituisce la componente dei secondi (0-59).

=SECOND(A2)

Formattare i valori orari con TEXT()

TEXT() converte un valore orario in un formato specifico, ideale per visualizzare durate o creare stringhe orarie personalizzate. Sintassi:

=TEXT(A2, "h:mm:ss AM/PM")

Gestire valori orari negativi con TEXT()

Quando sottrai valori orari, MOD() assicura che il risultato sia sempre positivo, evitando errori causati da orari negativi.

=MOD(A2 - B2, 1)

Creare valori di data con DATE()

DATE() ti permette di creare una data da componenti separati: anno, mese e giorno. È utile per combinare date e orari in un unico valore.

=DATE(year, month, day)

Ecco un buon esempio:

=DATE(2023, 12, 31) + TIME(14, 30, 0)

Che restituisce 31/12/2023 14:30.

Creare giorni lavorativi con NETWORKDAYS()

NETWORKDAYS() calcola il numero di giorni lavorativi tra due date, escludendo i weekend e, facoltativamente, le festività specificate. È ideale per calcolare durate di progetto o periodi di paga.

=NETWORKDAYS(start_date, end_date, [holidays])

Per esempio:

=NETWORKDAYS(A2, B2, C2:C5)

Se A2 contiene 12/1/2023 e B2 contiene 12/31/2023, il risultato sarà il numero di giorni lavorativi di dicembre, escludendo le festività elencate nell’intervallo C2:C5.

Creare giorni lavorativi e weekend con WORKDAY.INTL()

WORKDAY.INTL() è una versione avanzata di WORKDAY() che ti consente di personalizzare quali giorni sono considerati weekend.

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

Per esempio:

=WORKDAY.INTL(A2, 10, "0110000")

Qui, 0110000 specifica che martedì (0) e mercoledì (1) sono weekend. La formula calcola la data 10 giorni lavorativi dopo A2, escludendo martedì e mercoledì.

Conclusione

Gestire l’ora in Excel può sembrare complicato, ma con le funzioni giuste diventa molto più gestibile.

Se vuoi rafforzare ulteriormente le tue competenze in Excel, dai un’occhiata al nostro Introduction to Excel per le basi o al corso Data Analysis in Excel per esercitarti con dati reali.

Per applicazioni più avanzate, il percorso di competenze Excel Fundamentals offre progetti guidati ed esercizi per aiutarti a padroneggiare i calcoli sul tempo e non solo.