Scopri come sottrarre il tempo e calcolare la differenza di tempo tra orari, date e la differenza di tempo negativa in Excel.
La sottrazione del tempo da un altro orario è utile se lo desideri trova la differenza di tempo tra due tempi. Ad esempio, se vuoi trovare il tempo impiegato per completare un’attività, puoi inserire sia l’ora di inizio che di fine dell’attività e sottrarle per trovare il tempo trascorso totale.
Poiché Excel memorizza date e ore come numeri, puoi facilmente sottrarre utilizzando semplici formule aritmetiche, la funzione ORA, la funzione TESTO o la funzione TIMEVALUE per calcolare la differenza di tempo o il tempo trascorso e altro ancora. In questo articolo vedremo diversi metodi per sottrarre il tempo e calcolare la differenza tra date e orari in Excel.
Calcola la differenza di tempo tra due tempi
Più che spesso, dobbiamo calcolare la differenza di tempo tra due tempi dati per trovare il tempo trascorso. Puoi calcolare il tempo tra due volte o due celle con una semplice sottrazione.
Il modo più semplice per trovare la differenza di orario è sottraendo l’ora di inizio dall’ora di fine:
=Ora di fine-Ora di inizio
Ad esempio, per sottrarre l’ora in A3 dall’ora in B3, possiamo utilizzare la formula seguente:
=B3-A3
Dove A3 è l’ora di inizio e B3 è l’ora di fine. Come puoi vedere, ci sono 5 ore di differenza tra A3 e B3.
Tuttavia, se il formato di la cella del risultato è impostata su Generale, otterresti i risultati in frazioni/decimali. È perché i valori temporali sono rappresentati come frazioni di un numero in Excel.
Nel sistema di data di Excel, l’ora è memorizzata in questo modo:
Nel sistema interno di Excel:
00:00:00 è memorizzata come 0.0 06:00 è salvata come 0.25 12:00 PM viene memorizzata come 0.5 18:00 PM viene salvata come 0.75 23:59:59 viene memorizzata come 0.99999
Quando si inseriscono data e ora in una cella, verranno memorizzate come un numero di serie composto di un numero intero e di un decimale. Mentre l’intero rappresenta giorno/data, il numero dopo il punto decimale rappresenta l’ora. Ad esempio, quando digiti”15 agosto 2001, 6:00:00″in una cella di Excel, verrà archiviato come”37118.25″.
Quindi, le 5 ore verranno visualizzate come”0.208333333’come mostrato di seguito.
Per visualizzare i decimali in Time, selezionare la cella di output. Quindi, fai clic sul menu a discesa”Formato numero”dal gruppo”Numero”e seleziona l’opzione”Ora”.
Per applicare il formato orario personalizzato, fai clic sul pulsante freccia (Formato numero) nell’angolo del gruppo Numero sulla barra multifunzione o premi Ctrl+1 per aprire la finestra di dialogo Formato celle.
Nella scheda Numero, vai alla scheda’Ora’e scegli uno dei formati dell’ora e fai clic su’OK’.
Oppure puoi andare alla scheda’Personalizzato’e digitare uno dei seguenti codici e fare clic su’OK’per applicare il rispettivoformattazione dell’ora:
Se l’output è un errore #####, significa che l’output è la cella non è sufficientemente ampia o il risultato è un numero negativo. Se il risultato è negativo, vedremo come gestire i tempi negativi in Excel in una delle seguenti sezioni.
Conta ore, minuti o secondi tra due tempi
A volte, vogliamo solo calcolare la differenza di tempo in singole unità di tempo (ore, minuti o secondi). Quando sottrai i tempi, Excel restituirà sempre numeri decimali. Nel valore risultante, ogni numero intero rappresenta un giorno e i numeri decimali possono essere convertiti in ore, minuti o secondi. Per farlo, usa le seguenti formule:
Trova le ore tra due tempi
Per calcolare la differenza di tempo tra due volte in ore, usa questa formula:
=(Ora di fine-Ora di inizio) * 24
Esempio:
=(B3-A3) * 24
Qui l’ora di inizio è A3 e la fine il tempo è B3. Puoi sottrarre B3-A3 per calcolare la differenza tra le due volte, quindi moltiplicarla per 24 (che è il numero di ore in un giorno). Questo ci dà 10,17 come risultato.
Puoi utilizzare la funzione INT per ottenere il numero di ore complete:
=INT((B3-A3) * 24)
La funzione INT arrotonda il risultato per dare numeri interi.
Trova i minuti totali tra due tempi
Per calcolare la differenza di tempo tra due tempi in minuti, usa questa formula:
=(Ora di fine-Ora di inizio) * 1440
o
=(Ora di fine-Ora di inizio) * 24 * 60
Esempio:
=(B3-A3) * 1440
Dove inizia il tempo è A3 e il tempo finale è B3. Puoi usare la semplice sottrazione B3-A3 per trovare la differenza tra i due tempi, quindi moltiplicarla per 1440 che è il numero di minuti in un giorno. L’output finale sarà 610, ovvero i minuti totali tra A3 e B3.
Trova i secondi totali tra due tempi
Utilizza questa formula per ottenere la differenza tra due tempi in secondi:
=(Ora di fine-Ora di inizio) * 86400
oppure
=(Ora di fine-Ora di inizio) * 24 * 60 * 60
Esempio:
=(B3-A3) * 86400
Dove l’ora di inizio è A3 e l’ora di fine è B3. Puoi usare la semplice sottrazione B3-A3 per trovare la differenza tra i due tempi, quindi moltiplicarla per 86400 che è il numero di secondi in un giorno. L’output finale sarà 36600, che è il numero totale di secondi tra A3 e B3.
Calcola il tempo trascorso da un’ora di inizio ad ora utilizzando la funzione Now()
Quando si utilizza la funzione ADESSO in Excel, verranno visualizzate la data e l’ora correnti dal computer. Se vuoi trovare il tempo trascorso da un’ora di inizio fino ad ora, ovvero l’ora corrente, puoi utilizzare la funzione Now() invece dell’ora di fine nella formula.
Ecco una formula che puoi utilizzare per trovare il tempo trascorso:
=Ora-Ora di inizio
Nell’esempio seguente, l’ora di inizio è nella colonna A. Per calcolare il tempo trascorso dall’ora di inizio data fino ad ora, puoi utilizzare la formula:
=NOW()-A1
Il valore risultante per il tempo trascorso fino ad ora viene restituito nella colonna B. Nel caso in cui i tuoi orari di inizio abbiano solo unità di tempo senza date, la formula potrebbe restituire risultati errati come mostrato di seguito. È perché Excel considererà la data come 1 gennaio 1990 per impostazione predefinita.
Per risolvere questo problema, puoi aggiungere la parte della data all’ora di inizio o usa la funzione INT() che taglierà il valore del giorno restituito dalla funzione NOW().
In primo luogo, inserisci la data e l’ora di inizio in una cella(e), quindi, inserisci la stessa formula Assicurati di applicare il formato dell’ora appropriato alla colonna C (colonna dei risultati), h:mm.
Se il il tempo trascorso supera le 24 ore, è necessario applicare il formato dell’ora per visualizzare i giorni e l’ora nel risultato. Puoi utilizzare uno dei seguenti formati per includere i giorni:
Nel sotto l’esempio, abbiamo applicato il formato d”day”h:mm:ss alla colonna C. Come puoi vedere, il tempo trascorso dall’ora di inizio (29/07/2022) fino ad ora è di 1 giorno 15 ore, 57 minuti e 29 secondi.
Calcola la differenza di tempo tra orari specificati
Puoi calcolare direttamente la differenza di tempo tra due orari dati utilizzando la funzione TIMEVALUE(). Con la funzione TIMEVALUE, non è necessario inserire l’ora di inizio e di fine in due celle diverse, puoi passare i valori direttamente alla formula.
La sintassi:
=TIMEVALUE(“Ora di inizio”)-TIMEVALUE(“Ora di fine”)
Ad esempio, per calcolare la differenza di orario tra le 6:10 e le 3:00, utilizzare la formula seguente:
=TIMEVALUE (“18:10”)-TIMEVALUE (“15:00”)
Calcola la differenza in una sola unità di tempo
Puoi usare le funzioni ORA(), MINUTO() e SECONDO() per calcolare la differenza di tempo tra due tempi in una sola unità ignorando le altre.
Per calcolare il differenza di orario in ore ignorando minuti e secondi, utilizza questa formula di esempio:
=HOUR(A2-B2)
Per calcolare la differenza di tempo in minuti ignorando minuti e secondi, usa questa formula di esempio:
=MINUTE (A2-B2)
Per calcolare la differenza di tempo in secondi ignorando minuti e secondi, utilizza questa formula di esempio:
=SECOND(A2-B2)
Tuttavia, se l’ora di fine è inferiore all’ora di inizio, il risultato sarà un numero negativo. Quindi, otterrai un NUM! errore di conseguenza.
Assicurati che il risultato della funzione HOUR() non esca 24 ore e il risultato delle funzioni MINUTE() e SECOND() non esce rispettivamente di 60 minuti e 60 secondi.
Sottrai il tempo per trovare la differenza di tempo usando la funzione di testo
Se non lo fai Per modificare il formato delle celle, è possibile utilizzare la funzione TESTO per calcolare e restituire la differenza di orario come stringa di testo. Con la funzione TESTO, non è necessario applicare la formattazione alle celle, puoi semplicemente specificare il formato dell’output direttamente all’interno della formula.
La sintassi per il calcolo della differenza di orario:
strong>
=TEXT(Ora di fine-Ora di inizio,”format”)
Differenza di tempo in ore tra due tempi
Per ottenere solo le ore trascorse tra le due volte:
=TEXT(B2-A2,”hh”)
In quanto sopra, hh rappresenta l’unità dell’ora. Come puoi vedere, la formula sottrae il tempo in B2 da A2 e quindi restituisce solo il numero di ore trascorse tra l’ora di inizio e quella di fine.
Differenza di tempo in minuti tra due tempi
Per ottenere solo i minuti trascorsi tra i due tempi, prova questa formula:
=TESTO (B2-A2,”[mm]”)
Dove[mm] rappresenta i minuti totali. Come puoi vedere, la formula sottrae il tempo in B2 da A2 e quindi restituisce solo il numero di minuti trascorsi tra due volte.
Differenza di tempo in secondi tra due tempi
Per ottenere solo i secondi trascorsi tra i due tempi, usa questa formula:
=TEXT(B2-A2,”[ss]”)
Dove [ss] rappresenta il numero totale di secondi. La formula sottrae l’ora di fine dall’ora di inizio e restituisce solo il numero totale di secondi.
Probabilmente l’hai notato i formati’mm’e’ss’sono racchiusi tra parentesi quadre, è perché vogliamo ottenere il numero totale di minuti o secondi tra i due tempi e non solo i minuti o secondi rimanenti oltre alle ore. Tuttavia, se il numero di ore è superiore a 24 ore, puoi utilizzare il formato'[hh]'(racchiuso tra parentesi quadre) nella formula.
Per trovare i minuti rimanenti tra due tempi oltre alle ore , usa invece la formula seguente
=TEXT(B2-A2,”mm”)
Per trovare i secondi rimanenti tra due volte oltre alle ore, usa invece la formula sottostante
=TEXT(B2-A2 ,”ss”)
Per restituire tutte e tre le ore, minuti e secondi tra due volte, utilizza questa formula:
=TEXT(B2-A2,”hh:mm:ss”)
Nota: il risultato restituito dalla funzione TESTO è sempre un TESTO. Pertanto, l’output non può essere utilizzato in altri calcoli. Se si intende utilizzare l’ora calcolata in altri calcoli, utilizzare uno degli altri metodi per calcolare la differenza di orario.
Se il valore restituito dalla funzione TESTO è un numero negativo, otterresti #VALORE! errore.
Calcola la differenza di tempo tra due date
Prima di iniziare, dovresti sapere che Excel memorizza i dati e l’ora come numeri di serie. Quando inserisci una data in una cella di Excel, assegna un numero di serie e se inserisci un’ora, assegna una frazione di un numero.
Excel ha un sistema di numeri di serie con date che vanno dal 1 gennaio 1900 ad oggi e oltre. Ciò significa che 1 gennaio 1900 è 1, 2 gennaio 1900 è 2, 3 gennaio 1900 è 3 e così via. Ad esempio, il numero di serie della data 01/08/2022 è 44774 e la frazione o il numero decimale per le 6 del mattino è 0,25 (trimestre di giorno).
Tuttavia, alcune versioni precedenti di Excel per Mac utilizza il sistema della data del 1904. Hai anche la possibilità di abilitare il sistema della data 1904. In questi casi, il numero di serie inizierà il 1 gennaio 1904 (0). E il 2 gennaio 1904 è il 2, il 3 gennaio 1904 e così via.
Calcola la differenza di orario tra due date in ore
Per calcolare la differenza di orario tra due date e orari, puoi usa la formula seguente:
=INT((B2 – A2) * 24) &”ore”
Dove B2 è la data di fine e A2 è la data di inizio. La Formula (B2-A2) produrrà una frazione. Dobbiamo sommare il numero di giorni + la frazione per ottenere la differenza in ore.
Vediamo come funziona questa formula:
Come puoi vedere la differenza totale tra le due date (quando hai applicato la formattazione dell’ora’d h:mm:ss’alla cella di output) è 2 giorni, 5 ore, 59 minuti e 40 secondi.
Puoi applicare questa formattazione personalizzata’d h:mm:ss’nella finestra di dialogo Formatta celle.
Excel calcolerà in termini di giorni e ci mostra che ci sono 2 giorni di differenza tra le date, ovvero 48 ore. Questo ci lascia con 5 ore, 59 minuti e 40 secondi. Ora, dobbiamo anche aggiungere questo nell’output.
Per farlo, dobbiamo convertire le 5 ore, 59 minuti e 40 secondi in termini di giorni. Poiché il nostro tempo termina con i secondi, calcoleremo le 5 ore, 59 minuti e 40 secondi in termini di secondi come mostrato di seguito:
5 ore * 60 minuti * 60 secondi=18.000 secondi 59 minuti * 60 secondi=3.540 40 secondi 5 ore, 59 minuti e 40 secondi=18.000 secondi + 3.540 secondi + 40 secondi=21.580 secondi.
Per calcolare il decimale del giorno, dobbiamo dividere il totale di 21.580 secondi sopra per 86.400 secondi (ovvero il numero totale di secondi in 1 giorno).
21.580/86.400=0,249769 giorni
Come di conseguenza, la frazione che otterremo è 0,249769 giorni.
Ora, la differenza finale tra le due date è 2 giorni + 0,249769=2,2497685.
In alternativa,
Il numero di serie per Start DateTime (01/08/2022 14:15:50) è 44774.59433. Il numero di serie per End DateTime (03/08/2022 20:15:30) è 44776.8441. Data e ora di fine (B2) – Data e ora di inizio (A2)=44774,59433 – 44776,8441=2,2497685.
Per convertire l’output sopra in ore, moltiplicalo per 24:
2,2497685 * 24=53,994444 ore.
Infine, la funzione INT taglia i decimali alla fine e l’operatore’&’aggiunge la stringa di testo (ore) alla fine per fornire il numero totale di ore tra i due giorni, ovvero’53 ore’.
Calcola la differenza di orario tra due date in minuti
Calcolare la differenza di orario tra due date in minuti è simile al calcolo delle ore, l’unica differenza è che devi moltiplicare la differenza di orario per 1440 (numero di minuti al giorno) nella formula. Quindi, aggiungi la stringa di testo”minuti”alla fine invece di”ore”.
Per calcolare la differenza di tempo tra due date in termini di minuti:
=INT((B2 – A2) * 1440) &” minuti”
Dove B2 è EndDate e A2 è il Data d’inizio. Nella formula sopra, la differenza di tempo di output di sottrazione tra due date viene moltiplicata per 1440 (il numero di minuti in 1 giorno). E poi la stringa’minuti’viene aggiunta al risultato.
Calcola la differenza di tempo tra due date in secondi
Calcolare i secondi equivale a calcolare ore e minuti, l’unica differenza è che moltiplichi l’output di sottrazione per 86400 (numero di secondi in un giorno).
Per fare ciò, puoi utilizzare la formula seguente:
=INT((B2-A2) * 86400) &”secondi”
La formula sottrae DateTime in B2 da A2 e quindi moltiplica il risultato per 86400 (numero di secondi in un solo giorno) perché vogliamo il risultato in termini di secondi. Questo produrrà 194379 come risultato. Poiché non ci sono valori decimali, la funzione INT restituirà lo stesso output (numero totale di secondi tra due date).
Calcola tutti i giorni, le ore, i minuti e i secondi tra due date
Se desideri ottenere la differenza di orario tra due date in termini di giorni, ore, minuti e secondi, devi usa la funzione TESTO con la funzione INT.
Per calcolare giorni, ore, minuti e secondi tra due date, prova la formula seguente:
=INT(B2-A2) &”giorni,”& TEXT(B2-A2,”h””ore,””m””minuti e””s””secondi”””)
Qui, la prima parte della formula funziona allo stesso modo degli esempi precedenti. La funzione INT restituisce un valore intero generato sottraendo le date specificate (2.2497685) che è 2 giorni.
Nella seconda parte della formula, la funzione TESTO sottrae entrambe le date e quindi applica la formattazione dei numeri personalizzata al risultato (5:59:40). Il formato”h”restituisce il numero di ore,”m”restituisce il numero di minuti e”s”restituisce il numero di secondi. La funzione TESTO aggiunge anche le stringhe tra virgolette ore, minuti e secondi dopo ogni unità di tempo.
Infine, entrambi gli output delle funzioni INT e TESTO vengono combinati insieme utilizzando l’operatore di concatenazione (&) per fornire questo risultato: 2 giorni, 5 ore, 59 minuti e 40 secondi.
Come avrai notato, abbiamo aggiunto uno spazio dopo ogni stringa di testo nella formula. Assicurati di aggiungere quegli spazi per evitare che i risultati vengano confusi.
In alternativa, puoi anche utilizzare la funzione INT con funzioni ORA, MINUTO e SECONDO separate per ottenere gli stessi risultati:
=INT(B2-A2) &”giorni,”& ORA(B2-A2) &”ore,”& MINUTI(B2-A2) &”minuti e”& SECONDO(B2-A2) &”secondi”
Sottrazione di ore, minuti o secondi da un tempo specifico
A volte, potremmo voler sottrarre determinati intervalli di tempo ( Ore, Minuti, Secondi) da un’ora specifica o DateTime. Puoi ottenere ciò con o senza la funzione ORA in Excel. Se le ore che vuoi sottrarre sono minori o uguali a 23, puoi utilizzare la formula TIME. Ma c’è un’altra formula che può essere utilizzata per sottrarre qualsiasi numero di ore.
Sottrazione di qualsiasi ora da un’ora senza la funzione ORA
Ad esempio, se hai una data particolare e tu vuoi calcolare un tempo che era un certo numero di ore prima, puoi dividere le ore che vuoi sottrarre per il numero totale di ore (al giorno) e quindi sottrarre il risultato dal tempo dato.
=Given Time/DateTime-(Ore da sottrarre/24)
Esempio:
=A2-(18/24)
Ecco come funziona la formula:
Excel calcola la differenza in termini di giorni, quindi dobbiamo fornire a Excel il numero di giorni (o frazione) di giorni che desideriamo sottrarre dalla data o dall’ora specificata (A2).
Per calcolare i giorni, devi dividere le ore che vuoi sottrarre per 24 (numero totale di ore al giorno) – 18/24. Questo ci darà 0,75. Se vuoi sottrarre più di 24 ore, il numero prima del punto decimale rappresenta i giorni e il valore dopo il decimale rappresenta l’ora.
Ora Excel sottrarrà 0,75 da (44774.59433) che è il numero di serie per 01/08/2022 14:15:50 (A2) perché Excel pensa in termini di frazioni e decimali.
Questo restituirà 44773.84433 che è il numero di serie per’7/31/22 8:15 PM’. Quindi, l’output è 7/31/22 20:15.
Esempio 2:
Supponiamo che tu voglia tornare indietro di 42 ore nel tempo dal 1 agosto 2022, 18:00. Quindi dobbiamo fornire a Excel il numero di giorni che vogliamo sottrarre dall’ora indicata.
Per convertire le ore in giorni, dobbiamo dividere 42 per 24 e poi sottrarlo da A2:
=A2-(42/24)
Dividendo 42 per 24, otterremo 1,75 giorni. Come accennato in precedenza, il numero prima del decimale rappresenta i giorni (1) e la frazione dopo il decimale rappresenta le ore (0,75).
Moltiplicando 0,75 per 24 (0,75*24), otterremo 18 ore. Quindi, Excel sottrarrà 1 giorno 18 ore da A2 (1 agosto 2022, 18:00). L’output finale è 7/30/22 20:15.
Mentre la semplice formula aritmetica sopra può essere utilizzata per qualsiasi numero di ore dall’ora indicata, puoi utilizzare la formula TIME se le ore da sottrarre sono minori o uguali a 23.
Sottrarre le ore da un orario utilizzando la funzione TIME
La funzione TIME consente di convertire un singolo valore di tempo nella singola ora, minuto, e seconde unità. Può anche essere utilizzato per unire singole unità di tempo in un unico valore temporale. Tuttavia, la funzione TIME può gestire solo fino a un massimo di 23 ore, 59 minuti e 59 secondi.
Sintassi:
=Date Time/DateTime-TIME (Ore da sottrarre, 0, 0)
Esempio:
Per tornare indietro di 18 ore dalla sera del 15 agosto 2022 (18:00), utilizzare quanto segue Formula TIME:
=A2 – TIME (18, 0, 0)
La funzione TIME ha tre argomenti: ora, minuti e secondi. Vogliamo sottrarre 18 ore, quindi il primo argomento è’18’e gli altri due argomenti, minuto e secondo, sono specificati come’0′.
Di solito, la funzione TIME restituisce un output compreso tra 0 (0:00:00 o 00:00:00) e 0,99988426 (23:59:59). Nella formula sopra, restituisce 0,75 che viene sottratto da A2.
Sottrazione di minuti da un DateTime
Possiamo utilizzare le stesse formule precedenti per sottrarre minuti e l’unica differenza è che possiamo è necessario dividere i minuti da sottrarre per 1440.
=A2-(4500/1440)
Nella formula sopra, il numero di minuti che vogliamo sottrarre (4500) è diviso per il numero totale di minuti in 1 giorno (1440) per convertire i minuti in giorni. La divisione restituirà 3.125 giorni che, se sottratti da A2, otterremo l’output di’7/29/22 3:00 PM’.
Sottrazione di minuti da un’ora specifica utilizzando la funzione TIME
Puoi anche utilizzare la funzione TIME per sottrarre minuti da un’ora specifica purché il tempo da sottrarre sia inferiore maggiore o uguale a 1439 a causa della limitazione della funzione TIME. Può gestire solo fino a 23 ore, 59 minuti e 59 secondi, per un totale di 1439 minuti. Se vuoi sottrarre più di 1439 minuti, devi usare la formula aritmetica di cui sopra.
Per sottrarre 900 minuti dal tempo dato usando la funzione TIME:
=A2-TIME(0,900, 0)
Nella formula sopra, i minuti (900) che vogliamo sottrarre sono specificati nel secondo argomento della funzione TIME. Questo ci dà il 01 agosto 2022, 03:00.
Sottrai secondi da un DateTime
Possiamo utilizzare le stesse formule sopra per sottrarre i secondi e l’unica differenza è che il denominatore tra parentesi è 86400.
Sintassi:
=A2-(secondi da sottrarre/86400)
Nella formula sopra, il numero di secondi che vogliamo sottrarre (10000) è diviso per il numero totale di secondi in 1 giorno (24 ore * 60 minuti * 60 secondi=86400) per convertire i secondi in giorni. La divisione restituirà 0,115740741 giorni che, sottratti da A2, otterremo’8/1/22 15:13′.
Sottrazione di secondi da un’ora specifica utilizzando la funzione TIME
Ancora una volta, puoi anche utilizzare la funzione TIME per sottrarre secondi da un’ora specifica, purché il tempo da sottrarre sia inferiore a o uguale a 86399 a causa della limitazione della funzione TIME. Può gestire solo fino a 23 ore, 59 minuti e 59 secondi, per un totale di 86399 minuti. Se vuoi sottrarre più di 86399 secondi, devi usare la formula aritmetica di cui sopra.
Per sottrarre 9000 secondi dal tempo dato usando la funzione TIME:
=A2-TIME(0, 9000,0)
Nella formula sopra, i secondi (9000) che vogliamo sottrarre sono specificati nel secondo argomento della funzione TIME. Questo ci dà il 01 agosto 2022, 15:30.
Sottrazione di ore, minuti e secondi da un Ora specifica utilizzando la funzione TIME
Se desideri sottrarre ore, minuti e secondi da una determinata data, la funzione TIME può aiutarti.
Ad esempio, per sottrarre 2 ore, 15 minuti e 30 secondi, prova la formula seguente:
=A2-TIME(2,15,30)
Nella formula sopra, abbiamo specificato tutte e tre le unità temporali: ore, minuti e secondi negli argomenti della funzione ORA. Quindi, la formula sottrae 2 ore, 15 minuti e 30 secondi da A2 (01/08/22 18:00:00). Questo ci darà 8/1/22 15:44.
Calcolo dei tempi negativi in Excel
Quando sottrai l’ora se l’ora di fine è inferiore all’ora di inizio, otterrai un numero negativo nella differenza di orario. The formula will result in negative numbers if the time difference is less than 0. This usually happens, when you input only times into a spreadsheet without dates.
Also, if you usually work at nightshift, the end time and date will always be earlier than the start date and time of your work. For example, if you start your work at 6 PM and then finish your work and log out the next morning at 6 AM, the regular subtraction method won’t work.
As you can see below, not only the formula won’t, Excel will throw you the ###### error when you have a negative time.
However, if you want to display the negative anyway, you can just switch to the 1904 date system. Most modern Excel version defaults to the 1900 date system but if you change the date system to 1900, you can display the negative time values.
Here’s how you can switch to the 1904 date system in MS Excel:
To do this, Click the ‘File’ menu on the Excel window and then select ‘Options’ in the backstage view.
In the Excel Options window, go to the ‘Advanced’ tab on the left, and then scroll down to the ‘When calculating this workbook section’. Then, check the box for ‘Use 1904 date system’ and click ‘OK’.
Now, Excel will display the negative date and time values as shown below:
If you want to display time in negative values, you can use the IF function to force negative times to display properly.
You can use either of the following formulas to display negative times properly when times cross midnight:
=IF((B3-A3)<0,1-(A3-B3),(B3-A3))
or
=IF(B3>A3,B3-A3,1-A3+B3)
The above formula will calculate the negative time difference and display it properly as shown below.
However, the above formula will work in most cases. If the time difference is more than 24 hours, the above formulas won’t work either. In such cases, make sure to include dates with the times.
Subtracting the Dates to Count Days in Between
Similar to time, you can also subtract two cells with dates to find the number of days in between two dates.
For example, cell A3 and B3 contains two dates. To find the difference in days, use the below formula:
=B3-A3
The formula returns the difference between two dates A3 and B3 as 132 days.
Count Days in Between Two Dates using the DAYS function
Besides the simple subtraction of cells, you can also use the DAYS function to calculate the difference between two dates.
The syntax
=DAYS(End date, Start Date)
Example:
=DAYS(B3,A3)
Where B3 is the End Date and A3 is the Start Date.
That’s the end. Now you know everything you need to know about substracting time in Excel and then some. Hopefully, knowledge of these functions will increase your productivity in Excel.
