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 |
G |
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 |
|
|
|
|
|
|
|
|
|
9 |
|
|
|
In VBA avremo: |
|
|
|
|
|
10 |
|
|
|
|
If OraInizio > OraFine Then |
|
||
|
11 |
|
|
|
|
Range("D18") = 1 + OraFine - OraInizio |
|||
|
12 |
|
|
|
|
Else |
|
|
|
|
13 |
|
|
|
|
Range("D18") = OraFine - OraInizio |
|||
|
14 |
|
|
|
|
End If |
|
|
|
|
15 |
|
|
|
|
|
|
|
|
|
16 |
|
|
|
|
|
|
|
|
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 il seguente fumetto:
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("D4") = (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 1904) 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.
Soluzione
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
Eseguiamo gli stessi calcoli per valutare l'intervallo di tempo trascorso per il secondo orario dell'esempio:
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 |
Come usare la Funzione
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 indicato qui sotto.
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 usiamo:
In VBA useremo:
Range("B15").NumberFormat = "h:mm;@"
Conclusioni
E' tutto per oggi. Spero con questo di aver dato un contributo a quanti cercano soluzioni alternative e funzionali sul calcolo delle ore.
prelevato sul sito www.ennius.altervista.org