Calcoli con le ore

Il problema

Prendo lo spunto di questo articolo da una lettera che ho ricevuto recentemente e che mi ha spinto ad approfondire l'argomento di cui vorrei farvi partecipi.

Non ci sono problemi per calcolare le ore trascorse tra un'ora di partenza ed un'ora di arrivo. Sia in ambiente Excel che in ambiente VBA tutti sappiamo che, anche se ad un neofita sembra strano, è sufficiente eseguire una normale sottrazione:

OreTot = OraFine - OraInizio.

La soluzione del problema diventa difficile quando dobbiamo eseguire dei calcoli su orari che stanno a cavallo della mezzanotte.

Per esempio:

inizio h 22.00 --------> fine h 6.00.

Noi sappiamo che la differenza tra i due orari é di 8 ore. Il difficile è dire ad Excel o al VBA di eseguire tale calcolo.

Vediamo l'esempio che segue:

  A B C D E F D H
1                
2                
3 Inizio Fine            
4 22.00 6.00   ######## =B3-A3     Es 1
5       16.00 =A3-B3     Es. 2
6       8.00 =1+B3-A3     Es. 3
7       8.00 =SE(B3>A3;B3-A3;1+B3-A3)     Es. 4
8                

In VBA avremo:

If OraInizio > OraFine Then
Range("D18") = 1 + OraFine - OraInizio
Else
Range("D18") = OraFine - OraInizio
End If

Come possiamo notare, prima di arrivare alla soluzione finale, proviamo ad eseguire alcune prove:

La prima ci restituisce una serie di antiestetici ed incomprensibili ######## ad indicare che abbiamo ottenuto un risultato negativo, inconcepibile nel calcolo delle ore in questo ambiente. Infatti se passiamo sulla cella con il mouse ci viene mostrato un fumetto come visibile in questa immagine

La seconda prova nella quale tentiamo di sottrarre il valore minore dal maggiore ci restituisce un risultato palesemente errato.

La terza prova comincia a restituirci un risultato veritiero. Tuttavia ci costringe di prendere delle decisioni diverse ogni qualvolta dobbiamo eseguire il calcolo.

Giungiamo finalmente all'ultima prova che sembra soddisfacente. Infatti si adatta a calcolare la differenza tra qualsiasi tipo di orario: sia esso a cavallo della mezzanotte, che durante la giornata.

Nella formula di Excel leggiamo la formula: =SE(B3>A3;B3-A3;1+B3-A3)

Con questa diciamo:

se l'orario finale è superiore all'orario d'inizio (B3>A3) - siamo di fronte ad un orario che cade nella giornata -

calcola la differenza eseguendo la normale sottrazione (B3 - A3)

altrimenti - l'orario da valutare è a cavallo della mezzanotte -

calcola la differenza aggiungendo 1 al calcolo (1 + B3 - A3).

In VBA avremo:

If OraInizio > OraFine Then
Range("B6") = 1 + OraFine - OraInizio
Else
Range("B6") = OraFine - OraInizio
End If

Le righe di codice appena viste potrebbero essere modificate in questa unica, ma meno comprensibile, riga:

Range("B6") = (OraFine - (OraInizio + (OraInizio > OraFine)))

In questa seconda istruzione l'aggiunta di OraInizio > OraFine restituisce 1 - Vero - se l'ora di inizio è maggiore dell'ora finale, restituisce 0 (zero) - Falso - se l'ora di inizio non è maggiore dell'ora finale.

Se la condizione e la sintassi sono chiare, non altrettanto chiara può apparire l'aggiunta dell'unità al calcolo.

Per capire il motivo di questo correttivo occorre sapere che le date e gli orari sono gestiti dai cosiddetti numeri seriali che altro non sono che dei numeri decimali dove la parte intera rappresenta i giorni, mentre la parte decimale rappresenta le ore:

Il 10/05/1988 viene rappresentato dal numero seriale 32273

Le 10.55 viene rappresentato dal numero seriale 0,454861111

Le ore 10.55 del 10/05/1988 vengono rappresentate dal numero seriale 32273,45486

Le ore 10.55 del 10/11/2005 vengono rappresentate dal numero seriale 38666,45486

Questo ci fa comprendere che il correttivo usato nel calcolo sopra illustrato aggiunge un giorno (24 ore) al calcolo che si va ad eseguire.

Prendiamo il calcolo che andremo ad eseguire per calcolare il tempo trascorso tra le ore 22.00 e le 6.00 e tra le ore 6.00 e le 14.00 eseguendo il calcolo:

Range("D4") = (OraFine - (OraInizio + (OraInizio > OraFine)))
6 - 22 =-16   -16 + (1 * 24) = 8  
14 - 6 = 8   8 + (0 * 24)) = 8  

Fin qui tutto sembra chiaro e, soprattutto, funzionale. Ma non è sempre così. A volte può capitare, nonostante l'esattezza della sintassi usata nei calcoli, di vedere quegli antiestetici ########. Questo può accadere per delle particolari formattazioni che hanno le celle che ospitano il risultato del calcolo, o magari perchè, stiamo eseguendo calcoli su date antecedenti il 1900 o il 1904 (a seconda del SO in uso sul nostro PC) o per altri motivi.

Per vedere con quale sistema di data stiamo lavorando osserviamo nella scheda Calcolo della finestra Opzioni (Strumenti / Opzioni) quale sistema è impostato. Normalmente in tale finestra non è selezionata la voce Sistema data 1904.

Altro motivo di confusione è l'impostazione data in Opzioni internazionali del Pannello di controllo di Windows.

Allora ho pensato di rispolverare alcune vecchie reminescenze del vecchio buon Basic dove per ottenere questo risultato occorreva usare la matematica imparata a scuola ed ecco quel che ne è venuto fuori.

Questa è la soluzione che propongo se, usando le funzioni di Excel o del suo VBA, non siamo soddisfatti. In questo esempio lavoriamo sui due seguenti intervalli di tempo:

Inizio   Fine  
21.05   7.00  
6.50   14.10  

Per il primo orario

Convertiamo in minuti l'ora iniziale:
O1 = Hour(oraIniz) * 60 + Minute(oraIniz) 1.265 minuti
convertiamo in minuti anche l'ora finale ma ,siccome questa e quella iniziale c'è di mezzo la mezzanotte, aggiungiamo nel computo una intera giornata:
O2 = Hour(oraFinal) * 60 + 24 * 60 + Minute(oraFinal) 1.860 minuti
eseguiamo la differenza tra i due valori trovati:
O3 = O2 - O1 595 minuti
convertiamo i minuti in ore:
O4 = Int(O3 / 60) 9 ore
calcoliamo gli eventuali minuti eccedenti:
M4 = O3 - O4 * 60 55 minuti
Ora che abbiamo l'intervallo di tempo trascorso in ore e minuti li trasformiamo in orario comprensibile ad Excel usando una sua funzione:
Tot = TimeSerial(O4, M4, 0) 9.55.00

Per il secondo orario dell'esempio eseguiamo gli stessi calcoli per valutare l'intervallo di tempo trascorso :

O1 = Hour(oraIniz) * 60 + Minute(oraIniz) 410 minuti
O2 = Hour(oraFinal) * 60 + Minute(oraFinal) 850 minuti
O3 = O2 - O1 440 minuti
O4 = Int(O3 / 60) 7 ore
M4 = O3 - O4 * 60 20 minuti
Tot = TimeSerial(O4, M4, 0) 7.20.00

Arrivati a questo punto ci rendiamo conto che, per rendere questi calcoli validi per qualsiasi intervallo di orario che incontriamo nel foglio di calcolo, li dobbiamo trasformare in una routine, o meglio in una funzione, che tenga conto delle due tipologie di intervalli.

Io ho scelto di costruire una funzione che possa essere richiamata anche direttamente dal foglio di calcolo o, se preferiamo da una routine VBA. In questo caso la Funzione viene chiamata Matematico1:

Function Matematico1(oraIniz As Date, oraFinal As Date) As Date
Dim O1, O2, O3, O4, M4
O1 = Hour(oraIniz) * 60 + Minute(oraIniz)
If oraIniz > oraFinal Then 'orario a cavallo della mezzanotte
O2 = Hour(oraFinal) * 60 + 24 * 60 + Minute(oraFinal)
Else 'orario nella stessa giornata
O2 = Hour(oraFinal) * 60 + Minute(oraFinal)
End If
O3 = O2 - O1
O4 = Int(O3 / 60)
M4 = O3 - O4 * 60
Matematico1 = TimeSerial(O4, M4, 0)
End Function

Sul foglio di calcolo

Sul foglio di calcolo possiamo usare la funzione usando la sintassi:

=Matematico1(A14;B14)

e magari possiamo farci aiutare anche da una finestra dalla finestra di dialogo Argomenti Funzione proprio come per le altre funzioni native di Excel.

Per fare questo:

Selezioniamo il pulsante Inserisci Funzione

nella finestra che si apre scegliamo tra le categorie esposte quelle Definite dall'utente che ci mostrano le nostre funzioni personalizzate:

Scelta la funzione che vogliamo applicare ci viene mostrata la finestra che ci aiuta a scegliere gli argomenti della funzione

In VBA

In alternativa possiamo usare la funzione in un qualsiasi modulo del VBA ed anche in questo ambiente, come è mostrato dall'immagine qui sotto, riceviamo gli stessi aiuti (suggerimenti) offerti dalle funzioni native.

In questa istruzione scriviamo il nome della funzione e, tra parentesi indichiamo gli argomenti abbinati a questa funzione come in questa immagine che viene mostrata scrivendo il seguente codice:

Sub test()
Dim OraInizio As Date, OraFine As Date
OraInizio = Range("E3")
OraFine = Range("F3")
Range("G3").NumberFormat = "h:mm;@"
Range("G3") = Matematico1(OraInizio, OraFine)
End Sub

NB

In entrambi i casi occorre che la cella che ospita la formula o il valore restituito dal VBA deve essere formattata in uno dei formati Ore.

Se lavoriamo in Excel eseguiamo le operazioni evidenziate in questa immagine

In VBA useremo:

Range("G3").NumberFormat = "h:mm;@"
Se non eseguiamo una di queste operazioni potremmo imbatterci in situazioni inattese (una delle quali sarebbe la visualizzazione di un numero, che altro non sarebbe se non il numero seriale relativo all'orario che intendiamo visualizzare).

Conclusioni

E' tutto per oggi. Spero con questo di aver dato un contributo a quanti cercano soluzioni alternative e funzionali sul calcolo delle ore.