Come visualizzare / mostrare correttamente il tempo negativo in Excel?
Lavorare con i calcoli di tempo in Excel può essere impegnativo quando si sottrae un orario successivo da uno precedente. Ad esempio, se si sottrae 12:20 da 10:15, Excel restituisce un errore ###### invece di un valore di tempo negativo, come illustrato negli screenshot sottostanti. Questo problema si verifica comunemente perché Excel, per impostazione predefinita, supporta solo valori di tempo positivi nel suo sistema di date standard. Risolvere questo problema è importante per una pianificazione accurata, la gestione delle schede orarie e qualsiasi situazione in cui sia necessario riflettere durate temporali negative o calcoli degli straordinari.
➤ Visualizza correttamente il tempo negativo cambiando il sistema di date predefinito di Excel
➤ Visualizza correttamente il tempo negativo con le Formule
➤ Visualizza il tempo negativo utilizzando il codice VBA (formato testo personalizzato)
Visualizza correttamente il tempo negativo cambiando il sistema di date predefinito di Excel
Quando si lavora con calcoli di tempo, un modo semplice per visualizzare valori di tempo negativi è passare la cartella di lavoro al sistema di date del 1904. Questo consente di mostrare correttamente risultati negativi (ad esempio, “-2:05”) invece di errori. È utile quando è necessario mostrare frequentemente durate negative, come nel monitoraggio dei progetti o nella pianificazione dei turni.
Note:
- Passare al sistema di date del 1904 sposta tutte le date esistenti nella cartella di lavoro di circa 1.462 giorni (~4 anni). Rivedere gli altri campi di data dopo il cambiamento.
- Questa impostazione è specifica per la cartella di lavoro (non influisce su altri file).
- Per la collaborazione, tenere presente che gli utenti sul sistema di date del 1900 potrebbero vedere risultati diversi se le cartelle di lavoro vengono mescolate.
- Non è possibile utilizzare contemporaneamente i sistemi di date del 1900 e del 1904 nella stessa cartella di lavoro.
1. Aprire la finestra di dialogo Opzioni di Excel. In Excel 2010/2013, andare su File > Opzioni. In Excel 2007, fare clic sul pulsante Office e scegliere Opzioni di Excel.
2. In Opzioni di Excel, fare clic su Avanzate. Sotto Quando si calcola questa cartella di lavoro, selezionare Usa sistema di date del 1904. Vedere lo screenshot sotto:
3. Fare clic su OK. Le differenze di tempo negative verranno ora visualizzate correttamente, come mostrato qui:
Consigli:
- Se la conservazione dell'accuratezza delle date storiche è critica, considerare approcci alternativi (ad esempio, output testuale personalizzato tramite formula o VBA) invece di cambiare il sistema di date.
- Documentare il cambiamento del sistema di date nella cartella di lavoro (ad esempio, su un foglio “Leggimi”) per evitare confusione per altri utenti.
Sblocca la Magia di Excel con Kutools AI
- Esecuzione Intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici — tutto guidato da semplici comandi.
- Formule Personalizzate: Genera formule su misura per ottimizzare i tuoi flussi di lavoro.
- Codifica VBA: Scrivi e implementa codice VBA senza sforzo.
- Interpretazione delle Formule: Comprendi facilmente formule complesse.
- Traduzione del Testo: Superare le barriere linguistiche all'interno dei tuoi fogli di calcolo.
Visualizza correttamente il tempo negativo con le Formule
Se il flusso di lavoro non consente di cambiare il sistema di date (per motivi di compatibilità o report), è possibile ottenere una corretta visualizzazione del tempo negativo utilizzando formule. Questo metodo mostra le durate negative come testo, consentendo risultati visivi chiari senza attivare l'errore di Excel. Questo approccio viene ampiamente utilizzato in scenari come calcoli di deficit di ore lavorative, monitoraggio dei superamenti nei progetti o confronti di tempo trascorso.
1. Immettere gli orari che si desidera calcolare in due celle separate (ad esempio, A1 e A2). In una cella vuota, digitare la seguente formula per calcolare e formattare la differenza negativa:
=TEXT(MAX($A$1:$A$2)-MIN($A$1:$A$2),"-H::MM")Qui, A1 e A2 rappresentano i due valori di tempo. Modificare i riferimenti alle celle secondo necessità per i propri dati. Questa formula fornisce un risultato come testo, quindi è possibile vedere segni negativi se richiesto, anche se normalmente Excel restituirebbe un errore nel calcolo.
2. Premere Invio. Il risultato apparirà come una stringa di tempo formattata con un segno meno se è negativo, come mostrato di seguito:
Formula alternativa:
È possibile utilizzare anche questa formula, che visualizza condizionalmente un segno meno quando il risultato è negativo:
=IF(A2-A1<0, "-" & TEXT(ABS(A2-A1),"hh:mm"), A2-A1)In questa configurazione, A2 è l'ora più piccola o precedente, e A1 è l'ora successiva, ma si dovrebbero regolare queste a seconda della direzione del calcolo. Questo metodo produce un risultato testuale per i valori negativi, rendendolo più facile per la segnalazione, ma notare anche che i valori risultanti non possono essere totalizzati o sommati come tempi senza ulteriore conversione.
Note:
- I risultati sono testo, non valori di tempo veri, quindi ulteriori calcoli di tempo non funzioneranno a meno che non si converta nuovamente l'output in un formato di tempo.
- L'approccio con le formule è indipendente dalla cartella di lavoro, utile per la condivisione e evita conflitti di impostazioni globali.
- Prestare attenzione ai riferimenti di celle corretti per evitare errori logici (ad esempio, sottrarre la fine dall'inizio per ottenere un valore negativo).
Visualizza il tempo negativo utilizzando il codice VBA (formato testo personalizzato)
Un altro metodo efficace per gestire il tempo negativo in Excel è utilizzare una macro VBA. Questo è particolarmente utile quando si desidera un modo fluido per rilevare automaticamente e visualizzare calcoli di tempo negativi nel foglio di lavoro – anche per grandi quantità di dati – senza modificare le impostazioni di sistema o fare affidamento su formule per ogni cella. Questo metodo è adatto per utenti avanzati che gestiscono l'immissione continua di dati temporali, come registri di turni dei dipendenti, e previene l'errore ###### convertendo i risultati di tempo negativi in stringhe di testo personalizzate (ad esempio, con un segno meno prefisso).
Vantaggi: Consente l'elaborazione in batch delle celle selezionate, preserva i dati originali, può formattare i risultati secondo necessità e non influisce sulle impostazioni del sistema di date di Excel.
Svantaggi: Richiede l'abilitazione delle macro, non adatto se si desidera mantenere i valori di tempo nativi di Excel per calcoli oltre la visualizzazione.
Attenzione e suggerimenti:
- Salvare sempre una copia di backup del file prima di eseguire script VBA.
- Questa soluzione trasforma i risultati di tempo negativi in testo. Se sono necessari calcoli futuri, utilizzare i dati numerici originali, non il risultato di visualizzazione generato dalla macro.
- Le macro potrebbero essere disabilitate dalle politiche IT. Abilitare le macro in Excel per utilizzare questa soluzione.
1. In Excel, fare clic su Sviluppatore > Visual Basic. Nella finestra dell'editor VBA, fare clic su Inserisci > Modulo, quindi incollare il seguente codice nella finestra del modulo:
Sub DisplayNegativeTimeAsText()
Dim WorkRng As Range
Dim Cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select time calculation result cells", xTitleId, WorkRng.Address, Type:=8)
For Each Cell In WorkRng
If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
If InStr(Cell.NumberFormat, ":") > 0 Then
If Cell.Value < 0 Then
Cell.NumberFormat = "@"
Cell.Value = "-" & Format(Abs(Cell.Value), "h:mm")
End If
End If
End If
Next
End Sub2. Per eseguire la macro, fare clic sul pulsante 
Esegui nella finestra dell'editor VBA o premere F5. Apparirà una finestra di dialogo che permette di selezionare l'intervallo di celle contenenti i risultati delle differenze di tempo (anche quelle che mostrano l'errore ###### ).
La macro scansionerà automaticamente l'intervallo selezionato. Per ogni cella contenente un valore di tempo negativo (che normalmente mostrerebbe ######), cambia la cella in formato testo e converte il valore in un formato leggibile, come "-2:05". I risultati di tempo positivi rimangono invariati. Se si desidera tornare al formato normale del tempo, è necessario ripristinare i valori originali delle celle o utilizzare un'azione di annulla/ripristino.
Risoluzione dei problemi e ulteriori suggerimenti:
- Se si seleziona un intervallo senza differenze di tempo (ad esempio, celle vuote o valori non temporali), la macro li ignorerà in sicurezza.
- Se non vengono selezionate celle, la macro chiederà un intervallo e non influenzerà altri dati.
- Rivedere sempre la formattazione dopo l'esecuzione; le celle interessate sono ora testo, quindi i calcoli di tempo originali non funzioneranno fino al ripristino.
- Per espandere o adattare la macro per altri formati di visualizzazione o strutture più lunghe di ore-minuti-secondi, modificare la sezione
Format(Abs(Cell.Value), "h:mm")secondo necessità.
I migliori strumenti per la produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un'efficienza mai vista prima. Kutools per Excel offre oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in