Come visualizzare le ore negative in Excel? Se hai provato a sottrarre due celle formattate come orario di cui la prima riporta un valore più piccolo della seconda ti sarai accorto che Microsoft Excel restituisce come risultato una serie di cancelletti ########.
Immaginiamo di avere la seguente situazione:
A1: 05:30
A2: 08:15
Il calcolo che ci serve è A1-A2, quindi -02:45 come risultato ma essendo un’ora negativa otteniamo ##########
Vediamo alcune possibilità di soluzione
Formula
Una possibilità è utilizzare una formula che trasformi in testo i valori assoluti delle celle orario e visualizzi l’eventuale risultato negativo della differenza tra i due orari; nell’esempio sopra la formula sarebbe la seguente:
=SE(A1-A2<0;"-" & TESTO(ASS(A1-A2);"hh:mm"); A1-A2)
Funzione VBA
Altra possibilità è quella di creare una funzione VBA che permetta di svolgere il calcolo tra due celle orario e visualizzare le eventuali ore negative.
Dovendo lavorare in ambiente VBA apriamo la relativa finestra.
In Excel XP o 2003
Menù Strumenti – Macro – Visual Basic Editor
In Excel 2007
Bottone di Office – Pulsante Opzioni di Excel – Impostazioni generali – Opzione Mostra scheda Sviluppo sulla barra multifunzione
A questo punto ritornando nella finestra di Excel verrà visualizzata anche la Scheda Sviluppo nella quale troviamo il pulsante Visual Basic
In Excel 2010/2013/2016
Scheda File – Opzioni – Personalizzazione barra multifunzione – Selezionare Sviluppo nella sezione Schede principali
A questo punto ritornando nella finestra di Excel verrà visualizzata anche la Scheda Sviluppo nella quale troviamo il pulsante Visual Basic
Nella finestra Visual Basic inserire un nuovo modulo (menù Inserisci – Modulo) quindi copiare il codice riportato qui sotto:
Public Function OreNegative(Dato)
If Dato < 0 Then
OreNegative = Format(Dato, "-hh:mm")
Else
OreNegative = Format(Dato, "hh:mm")
End If
End Function
Chiudere la finestra di Visual Basic.
Posizionarsi nella cella in cui si vuole venga calcolata la differenza tra le due celle orario e inserire la seguente funzione:
=OreNegative(Dato)
Dove Dato rappresenta il calcolo tra le celle orario, nel nostro esempio corrispondenti a A1-A2, ovvero =OreNegative(A1-A2)
Modifica opzioni Excel
Infine si potrebbe anche cambiare l’opzione di Excel per la gestione delle ore e delle date; l’impostazione di base è il sistema data 1900 in cui il numero 1 di riferimento corrisponde all’1 gennaio 1900 e non permette la visualizzazione delle ore negative. L’altra impostazione è il sistema data 1904 in cui il numero 1 di riferimento corrisponde al 2 gennaio 1904 e in questo caso le ore negative vengono visualizzate.
Per cambiare l’impostazione del sistema data i passaggi sono i seguenti:
Scheda File ->Opzioni
Nella finestra che appare selezionare Impostazioni avanzate quindi nella porzione di destra scendere fino a incontrare la sezione Durante il calcolo della cartella di lavoro e selezionare Usa sistema data 1904
Tieni però ben presente che quest’ultimo metodo potrebbe essere pericoloso nel senso che cambiare il sistema data di Excel con delle date già presenti nel foglio potrebbe causare la modifica delle date stesse quindi potrebbe generare problemi nelle celle contenenti date o ai relativi calcoli.
Informatics, ski racer, loves travels, Finland, Norway, mountains, squash, photography, Drones and RC Helicopter
Utilizzando il formato ora della funzione VBA creata “hh.mm” non conteggia risultati maggiori di 23:59. Modificando il formato come previsto da “personalizza formato celle” “[h].mm” la funzione “OreNegative” non da nessun risultato.
Grazie
Saluti
Grazie Andrea, ho trovato utilissimo il tuo articolo.