Sommare ore negative con Excel ~ mini-Howto

Augusto Scatolini (webmaster@comunecampagnano.it)

V1.0, febbraio 2008

Abstract

1. Introduzione

2. Convertire le ore in minuti

3. Sommare le ore espresse in minuti
4. Che cosa succede se le ore sono negative?
5. Conclusioni

6. Download

Abstract

Sommare le ore  (es. 1,34  0,02  5,00) con Excel non è una cosa banale, sommare le ore negative (es. -1,20  -0,30  -2,00) è impossibile. Si potrebbe obiettare che è sufficiente formattare le celle come ora e il gioco è fatto :

Ma il primo problema sorge quando la somma supera le 24 ore, nel caso mostrato in figura la somma dovrebbe essere pari a 25.34:

In realtà a questo problema si può ovviare formattando la cella del risultato non come ora ma come personalizzato (vedi figura):

Formattando la cela del risultato come mostrato il risultato si aggiusta:

Cosa succede se si prova a sommare un vale negativo come capita sommando gli scarti orari dei cartellini di presenza giornalieri o mensili?

Nella cella B3 ho tentato di scrivere -2.30 al posto di 2.30

Semplicemente non è possibile.

 

1. Introduzione

Dopo aver fatto lunghe ed estenuanti ricerche con Google sono arrivato alla conclusione che bisogna trattare le ore con il sistema decimale invece che sessagesimale e quindi ho pensato che sarebbe sufficiente convertire le ore in minuti, sommare i minuti e riconvertire il risultato in ore.

2. Convertire le ore in minuti

Supponiamo di aver l'ora 2,20 - La via più semplice è quella di estrapolare l'intero 2, moltiplicarlo per 60 e somare il risultato alla parte decimale di 2,20 ovvero 20.

INT(2,20) = 2 ore
2 X 60 = 120 minuti (2 ore)
parte decimale di 2,20 = 20 minuti
totale dei minuti = 120 + 20 = 140 minuti

Supponiamo di voler sommare l'ora 0,10 - Ripetendo la stessa procedura con la stessa logica si avrà:

INT(0,10) = 0 ore
0 X 60 = 0 minuti (0 ore)
parte decimale di 0,10 = 10 minuti
totale dei minuti = 0 + 10 = 10 minuti

3. Sommare le ore espresse in minuti

A questo punto si può sommare 140 e 10 e si ottiene 150 (minuti)

Per riconvertire in ore questo valore espresso in minuti è sufficiente dividere il risultato per 60 e si ottengono le ore intere mentre la parte decimale rappresenta il numero di minuti nel sistema decimale.

Per convertire i minuti dal sistema decimale a quello sessagesimale si risolve una semplice equazione:

150 / 60 = 2,50 (2 ore e 50 centesimi di ora)
X = 50 X 60 / 100
X = 30 minuti
Risultato finale sarà 2,30 (2 ore e 30 minuti)

4. Cosa succede se le ore sono negative?

Tutto quanto scritto presuppone che Excel sia capace di calcolare l'intero di un numero qualunque, sia esso intero che decimale, sia positivo che negativo. Purtroppo un'altra scoperta (baco?) è che l'intero di un numero decimale negativo per Excel sembra essere un concetto sconosciuto.

Come si può notare in figura, l'intero di -1,24 risulta -2 e ancora l'intero di -2,36 risulta -3

Io non so che aritmetica usino nella Silicon Valley ma questo problema si risolve solamente con una formula di questo tipo
=SE(C4>=0;INT(C4);SE(C4=INT(C4);C4;INT(C4)+1))
ovvero con una doppia condizione (SE) una nidificata nell'altra

la formula è abbastanza complessa perché il problema è abbastanza complesso

è come se non ci fossero problemi nella parte positiva (a destra dello zero), non ci fossero problemi nella parte negativa (a sinistra dello zero) limitatamente ai numeri interi (-1,00  -2,00  -3,00 ......) MA ci sono problemi per i numeri negativi decimali (diversi da -1,00  -2,00  -3,00 ......)
Praticamente un problema intermittente ma solo nella parte negativa.

-----2,50-----2,00-----1,50-----1,00-----0,50-----0,00-----0,50-----1,00-----1,50-----2,00-----
                      OK                        OK                      OKOKOKOKOKOKOKOKOKOKOKOK     

Ricordando la funzione SE per la quale SE una cosa è vera il valore è il primo altrimenti il valore è il secondo, la funzione doppio SE nidificata si legge:

SE l'ora è uguale o maggiore a zero, il valore intero è l'intero dell'ora, altrimenti SE l'ora (negativa) è intera  il valore intero è l'ora, altrimenti il valore intero è l'intero dell'ora + 1

5. Conclusioni

Per costruire un foglio elettronico capace di sommare tutti gli scarti orari dei cartellini di un anno, positivi o negativi, interi o decimali si può procedere come mostrato nella figura che segue:

6. Download

Scarica il file di Excel costruito come descritto. Il file, una volta decompresso, presenterà due schede. Nella scheda chiamata Istruzioni c'è l'esempio dettagliato riportato in figura, nella scheda chiamata Risultato c'è lo stesso esempio ripulito, (con colonne e celle nascoste) pronto per essere usato.

FINE

questo documento è rilasciato con licenza CopyLeft - (tutti i rovesci sono riservati) - ovvero fatene quello che vi pare!

altre miniguide