Immissione di tempi negativi (Microsoft Excel)

Guido ha bisogno di inserire tempi negativi nelle celle. Se inserisce un tempo positivo (2:00), Excel lo riconosce perfettamente. Se prova a digitare -2: 00, Excel gli dice che c’è un errore nella sua formula. Guido si chiede il modo più semplice per inserire un valore temporale negativo in una cella e farlo riconoscere a Excel come valore temporale.

Prima di fornire un paio di possibili risposte alla domanda, è bene comprendere il modo in cui Excel visualizza le date e gli orari. Internamente, le date e le ore vengono memorizzate come numeri di serie, che non sono altro che numeri effettivi. Supponiamo che tu immetta una data nella cella B4, ad esempio “17 marzo 2020” (senza virgolette. Excel capisce che stai inserendo una data e la converte in un numero di serie. Non puoi dire che si tratta di un numero di serie, però , perché Excel mostra utilmente la data rappresentata dal numero di serie. (Vedi Figura 1.)

Figura 1. Inserimento di una data in Excel.

Nota che la data viene visualizzata nella cella come 17-Mar-20 e nella barra della formula come 3/17/2020. (Esattamente come Excel visualizza la data dipende da come l’hai inserita e da quali sono le tue impostazioni regionali Windows.) La data mostrata nella cella (17-mar-20) è il risultato di come la cella è formattata. Quando hai inserito la data nella cella e poiché la cella aveva in precedenza un formato “Generale”, Excel ha modificato la formattazione della cella per visualizzare la data come ci si sente meglio.

A causa della data mostrata nella barra della formula (17/3/2020) potresti pensare che sia t il modo in cui Excel memorizza le date internamente. Non così; puoi vederlo inserendo una formula in una cella adiacente (= B4) e poi formattando quella cella come “Generale”. (Vedi figura 2.)

Figura 2. Visualizzazione di come Excel memorizza una data.

La barra della formula mostra la formula (= B4), come dovrebbe, ma la cella mostra il valore 43907. Se hai formattato la data nella cella B4 come generale, mostrerebbe anche lo stesso valore. Questo è il numero di serie; è il modo in cui Excel memorizza le date internamente.

Ma cosa significa il numero di serie? È un “conteggio” del numero di giorni trascorsi da una data di base. Di solito questa data di base è il 1 gennaio 1900, mentre quel giorno è il giorno 1. Il 2 gennaio 1900 sarebbe il giorno 2 e così via. Quindi, 43907 è il giorno 43,907 che inizia con il 1 gennaio 1900.

Tuttavia, è possibile che la data di base di una cartella di lavoro sia diversa. Excel, ai fini storici, comprende due possibili date di base. L’altro è indicato come il sistema di data 1904. Inizia il conteggio con il 1 gennaio 1904. A differenza del sistema di data 1900, il sistema di data 1904 inizia a contare con 0. Pertanto, il 1 gennaio 1904 ha un numero di serie 0, il 2 gennaio 1904 ha un numero di serie 1, ecc.

Puoi vedere quale sistema di data è utilizzato in una cartella di lavoro seguendo questi passaggi:

(Vedi figura 3.)

Se la casella di controllo è selezionata nel passaggio 4, la cartella di lavoro utilizza il sistema di data 1904. Se non è selezionato, stai usando il sistema di data 1900.

Cosa c’entra questo con la domanda originale di Guido sull’ingresso in tempi negativi? In abbondanza, effettivamente. Vedi, un valore temporale, per Excel, non è altro che una parte di un giorno. Notare che gli esempi forniti finora riguardano date memorizzate come interi numeri di serie, in questo caso 43907.

Se inserisci una data con un’ora in una cella, ad esempio “3/17/20 6:00 pm”

(di nuovo senza virgolette, quindi viene memorizzato internamente come la parte del numero di serie a destra del punto decimale. (Vedi Figura 4.)

Figura 4 . Vedere come Excel memorizza una data e un’ora.

La parte frazionaria memorizzata con il numero di serie (.75) significa che l’ora che hai inserito (18:00) è del 75% durante il giorno . I tempi, la parte decimale, possono variare da .0 (12:00:00 am) a .999988426 (11:59:59 pm).

A causa di questo schema di memorizzazione delle date e volte internamente come numeri di serie, non esiste nel mondo di Excel un “tempo negativo”.

Non c’è modo di analizzare e memorizzare tale valore, quindi Excel fornisce un messaggio di errore quando ci provi. può fare “matematica della data” e sottrarre un’ora da un valore di ora già memorizzato in una cella, ma tutto ciò che stai facendo è regolare il numero di serie di data e ora. Ad esempio, se hai il numero di serie 43907.75 memorizzato in una cella S Per trarre un “tempo” da esso che darebbe un risultato negativo, tutto ciò che hai fatto è passare al giorno precedente. (Vedi figura 5.)

Figura 5. Sottrazione da una data e un’ora.

Notare che nella cella B6 è stata inserita un’ora senza data di accompagnamento (22:00). Questo è stato analizzato da Excel come un numero seriale con una “data zero” come mostrato nella cella C6. Se provi a sottrarre un’ora da quel numero di serie che produce un risultato negativo, Excel non può visualizzare il risultato come ora. (Vedi figura 6.)

Figura 6. Non puoi sottrarre troppo con i tempi.

Se passi il puntatore del mouse sui segni di cancelletto mostrati nella cella B9, vedrai il messaggio di errore: “Date e ore che sono negative o sono visualizzate troppo grandi come “. (L’esatta formulazione del messaggio di errore può variare da versione a versione di Excel.)

Otterrai lo stesso risultato se provi ad arrivare a momenti negativi anche in altri modi. Ad esempio, se inserisci un valore temporale in una cella e poi, in una cella diversa, utilizza una formula per moltiplicare il tempo per -1.

Quando provi a formattare il risultato come volta, ottieni di nuovo i segni di cancelletto. Excel semplicemente non può visualizzare tempi negativi perché non possono esistere per quanto riguarda il programma.

Allora cosa deve fare Guido? Se ha effettivamente bisogno di lavorare con tempi negativi, c’è ben poco che possa fare per ragioni già spiegate.

(Maggiori informazioni su questo in un momento, però.) Se vuole semplicemente sottrarre i tempi, questa è una storia diversa. Il fatto che Guido stia cercando di inserire qualcosa come -02: 00 indica che sta lavorando con la durata degli eventi, non con i “tempi di clock” effettivi. (Ricorda che Excel funziona con l’ora dell’orologio per eseguire la sua analisi e -02: 00 non è l’ora dell’orologio: non puoi dire a qualcuno che lo incontrerai a -02: 00.)

Per gestire le durate degli eventi potresti semplicemente lasciare che Excel faccia i conti per te. Ad esempio, un orario di inizio potrebbe essere inserito nella cella B4 e un numero di ore che Guido vuole sottrarre da esso (2) potrebbe essere inserito nella cella C4. Nella cella D4 potrebbe quindi utilizzare una formula per calcolare il risultato:

Dividendo il numero di ore per 24 si ottiene il valore decimale corretto utilizzato internamente da Excel nei suoi numeri di serie. Ovviamente, se il risultato della formula restituisce un numero di serie negativo, otterrai i segni cancelletto quando proverai a formattare il risultato come tempo. L’unica soluzione a questo è assicurarsi che ciò che è memorizzato nella cella B4 includa una data. Puoi formattare la cella B4 per mostrare solo l’ora, ma l’inclusione della data impedirà al risultato di essere un errore. Puoi saperne di più su come lavorare con i tempi trascorsi negativi dando un’occhiata a questo ExcelTip:

Dopo tutto è stato detto e fatto, c’è un modo in cui puoi lavorare con tempi negativi reali: passare al sistema di data 1904, come discusso in precedenza.

Ad esempio, guarda i risultati delle sottrazioni quando usi il sistema di data 1904. (Vedi figura 7.)

Figura 7. Il sistema di data 1904 gestirà i tempi negativi.

Questo approccio funzionerà alla grande per singole cartelle di lavoro sul tuo sistema, ma potrebbe non essere così eccezionale quando altri usano la tua cartella di lavoro. Basandosi su cartelle di lavoro che utilizzano diversi sistemi di data (alcuni 1900 e altri 1904)

può essere causa di potenziali problemi quando tu (o altri) copi le informazioni dalla cartella di lavoro alla cartella di lavoro. Questo problema è stato discusso in un diverso ExcelTip:

Quando utilizzi il sistema di data 1904, ti imbatterai comunque in intoppi quando inserisci tempi negativi, ma i risultati delle formule possono visualizzare tempi negativi senza problemi.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (13356) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 e Excel in Office 365.