Funzioni Ore / minuti

Ultima modifica: 28-05-2016

A volte abbiamo la necessità di calcolare quanto tempo intercorre tra due orari:

Molti diranno: "Elementare! che problema può essere mai questo. VBA ha un esercito di funzioni per manipolare il tempo".

È vero: ma che succederebbe se in un foglio Excel invece di trovare 10.05 trovassi 10,05?

Una catastrofe!!!

Ecco un esempio di ciò che ho dovuto scrivere in Excel per calcolare il tempo tra l'ora di entrata ed l'ora di uscita dal lavoro:

=INT((INT(F8)*60+(F8-INT(F8))*100-(INT(E8)*60+(E8-INT(E8))*100))/60)+((INT(F8)*60+(F8-INT(F8))*100-(INT(E8)*60+(E8-
INT(E8))*100))-INT((INT(F8)*60+(F8-INT(F8))*100-(INT(E8)*60+(E8-INT(E8))*100))/60)*60)/100

non è mostruoso?

Purtroppo nelle varie guide e manuali che riusciamo a trovare un po' ovunque, troviamo come trattare gli orari scritti nella normale sintassi (10.05), col punto separatore, ma è difficile riuscire a trovare qualcosa su come comportarsi su orari scritti con una differente sintassi (10,05), cioè con la virgola separatrice, cioè in formato decimale.

Finalmente una intuizione: perché non creare una funzione che possa trattare indifferentemente sia l'uno che l'altro formato liberando così il popolo di Excel dalla sudditanza del formato cella e/o della virgola/punto?

 

ATTENZIONE!

prima di passare al nocciolo del discorso è importante capire come vengono visti gli orari scritti nelle due diverse sintassi sopra menzionate:

in una cella (la A15) digitiamo 10.05 usando il punto che troviamo nella tastiera alfanumerica in un'altra cella (la B15) digitiamo 10,05 usando la virgola oppure il punto del tastierino numerico.

Questi saranno i risultati:

  1. ad occhio notiamo la evidente presenza del punto o della virgola la qual cosa già ci fa notare la differenza tra i due valori appena inseriti
  2. facciamo clic destro sulla prima cella e andando a vederne la proprietà notiamo che è impostata a personalizzata h.mm o qualcosa del genere il che indica che il dato è già considerato come orario
  3. facciamo clic destro anche sulla seconda cella e notiamo che la proprietà è impostata a generale o a numero con due decimali e questa cosa ci indica che il dato è considerato come numero decimale

Queste sono già le prime grandi differenze.
A questo punto andiamo ad indagare anche in VBA (io vi invito a fare questa esperienza anche se può risultare banale in quanto vi farà toccare con mano quello che a fatica si va a leggere)

In un modulo qualsiasi scriviamo queste semplici istruzioni:

Sub Prova()
A = Range("A15").Value
B = Range("B15").Value
Range("A16").Value = A
Range("B16").Value = B
End Sub

vedendo i risultati dopo l'esecuzione delle istruzioni noteremo che in A16 è stato scritto 0,420138888888889 ed in B16 è stato scritto 10,05.

Come mai la presenza di quel numero così mostruoso invece del semplice orario scritto col punto?

Per rispondere a questo vi rimando al tasto destro del mouse su quella cella (la A16) per controllarne il formato cella.

Per ora basta sapere che è così che l'orario o, in ogni caso, il tempo in genere, è visto dal sistema e quindi anche dal VBA.

Facciamo un'altra prova: aggiungiamo le seguenti istruzioni alle istruzioni di sopra in modo da vedere il formato dei due valori:

Range("A17").Value = TypeName(A)
Range("B17").Value = TypeName(B)

i risultati saranno Double e Double quindi i due valori sono visti da VBA alla stessa maniera

Vogliamo fare una ulteriore verifica?

Andiamo a controllare la posizione della virgola o del punto nei valori inseriti nelle celle. Modifichiamo sempre le ultime due righe. Questa volta facciamo un'altra aggiunta:

andiamo a cercare la presenza del punto e della virgola tra i due dati

Range("A18").Value = InStr(1, A, ".") ' tra gli apici mettiamo una volta il punto
Range("B18").Value = InStr(1, B, ".")
Range("A19").Value = InStr(1, A, ",") ' tra gli apici mettiamo una volta la virgola
Range("B19").Value = InStr(1, B, ",")

andiamo a leggere i risultati ma vi avviso già da ora che troveremo delle sorprese:

se tra gli apici abbiamo messo una virgola i risultati saranno 2 e 3,

ma se abbiamo messo un punto il risultato sarà 0 (zero) e 0.

Ma come mai?

La risposta la troviamo nella prova che abbiamo fatto quando abbiamo trascritto nelle celle sottostanti i due valori:

Range("A16").Value = A
Range("B16").Value = B

 

Dopo queste considerazioni e questi test sono riuscito a creare una, anzi due funzioni:

  1. una che calcola i minuti, che è la principale e la più impegnativa,
  2. l'altra, a cui faccio fare riferimento alla prima per ottenere i minuti, che mi restituisce le ore trascorse tra due eventi.

Le due funzioni, inoltre, sono in grado di calcolare anche orari a cavallo della mezzanotte.

 

Prima funzione: Calcolo dei minuti:

Public Function CalcMinTot(Ora1, Ora2)
Dim A, B, A1, A2, A3
A = Len(Ora1)
B = Len(Ora2)
' Ora1 ed Ora2 rappresentano rispettivamente l'ora di inizio l'ora di fine di un evento
' A e B sono le discriminanti per determinare la sintassi usata per scrivere le ore e cioè:
' se l'ora inserita nel foglio usa la sintassi col punto il numero di cifre con cui è vista
' dal VBA saranno sicuramente superiori a 5
' altrimenti saranno minori di 6

If A > 5 Then
' siamo di fronte ad un orario riconosciuto come tale e per estrarre da questo le ore ed
' i minuti possiamo usare tranquillamente le funzioni che VBA ci mette a disposizione
' quindi in A1 vengono immessi i minuti dell'ora di ingresso tramite questa formula

A1 = (Hour(Ora1) * 60 + Minute(Ora1))
ElseIf A < 6 Then
' in caso contrario ci troviamo di fronte ad un numero normale e per calcolare i minuti
' da immettere in A1 usiamo questa semplice formula matematica

A1 = Int(Ora1) * 60 + (Format(Ora1 - Int(Ora1), "0.00") * 100)
End If
If B > 5 Then
' stesso discorso appena fatto per immettere in A2 i minuti dell'ora di uscita
A2 = (Hour(Ora2) * 60 + Minute(Ora2))
ElseIf B < 6 Then
A2 = Int(Ora2) * 60 + (Format(Ora2 - Int(Ora2), "0.00") * 100)
End If
' a questo punto abbiamo in A1 ed A2 l'ora di entrata e l'ora di uscita espressi in minuti ed
' in formato Double per cui con la semplice operazione matematica possiamo
' determinano i minuti trascorsi tra le due ore

CalcMinTot = A2 - A1
' ma...
' che succede se l'ora di inizio e l'ora di fine attività sono a cavallo della mezzanotte?
' Il valore in CalcMinTot sarà negativo e, di conseguenza, sbagliato per cui non basta
' convertirlo semplicemente in positivo ma visto che oramai stiamo lavorando con normali
' numeri basta un piccolo artificio:
' mettiamo in A3 il valore in minuti che per noi mortali è l'ora 24 e ci comportiamo come di seguito:

If CalcMinTot < 0 Then
A3 = 24 * 60
CalcMinTot = A3 - A1 + A2
End If
End Function

 

Seconda funzione: Calcolo delle ore:

Arrivati a questo punto il discorso si semplifica di molto.

Abbiamo appena costruito una funzione che ci calcola i minuti intercorsi tra due eventi (entrata ed uscita, partenza ed arrivo, ecc....).

Per calcolare le ore intercorse tra i due eventi ci è sufficiente prelevare i minuti dalla funzione CalcMinTot e, visto che a questo punto siamo in pieno regime numerico - matematico e non più temporale, è sufficiente applicare al valore che ci ritorna quelle elementari operazioni di matematica che studiamo anche a scuola.

Public Function CalcOreTot(Inizio, Fine)
Dim Minuti, A1, A2
Minuti = CalcMinTot(Inizio, Fine)
A1 = Int(Minuti / 60)
A2 = Minuti - (A1 * 60)
CalcOreTot = (A1 + A2 / 100)
End Function

È chiaro? Spero di sì, per cui questo è tutto per quanto riguarda i commenti al codice delle due funzioni.

In caso contrario io sto qui per qualsiasi delucidazione.

 

Uso delle funzioni

Fin qui la scrittura ed alcune semplici spiegazioni sulle due funzioni da me pensate.

Ma perché queste funzionino occorre in qualche modo usarle.

Allora possiamo agire in due modi, come con delle normali funzioni native di Excel.

Ora vediamo come:

dal foglio di Excel

  1. nella cella dove vogliamo il risultato cominciamo a digitare il carattere "="
  2. digitiamo la funzione che vogliamo evocare (CalcMinTot oppure CalcOreTot)
  3. apriamo una parentesi tonda
  4. digitiamo o clicchiamo la cella che contiene l'ora di partenza
  5. digitiamo il punto e virgola (;)
  6. digitiamo o clicchiamo la cella che contiene l'ora di arrivo
  7. chiudiamo la parentesi tonda per ottenere una formula del genere
  8. premiamo INVIO

oppure

  1. facciamo clic su inserisci funzione per aprire la finestra di dialogo che ci fa scegliere la funzione
  2. in questa finestra scegliamo Definite dall'utente dalla casella Oppure selezionare una categoria
  3. ci vengono mostrate tutte le funzioni Definite dall'utente
  4. ne scegliamo una (per esempio la nostra funzione CalcOreTot) che ci mostra quest'altra finestra
  5. ci posizioniamo sulla prima casella di teso
  6. facciamo clic sulla cella che contiene l'ora di partenza
  7. ci posizioniamo sulla seconda casella di teso
  8. facciamo clic sulla cella che contiene l'ora di arrivo
  9. facciamo clic su OK

il risultato sarà una cosa del genere:

=CalcOreTot(A4;B4)

 

Nel codice VBA

Assegniamo a due variabili il valore contenuto nelle due celle interessate: A = Range("A4").Value: B = Range("B4").Value

poniamo le due variabili come argomenti della funzione che preferiamo: CalcOreTot(A, B)

al ritorno dalle funzioni:

  1. o poniamo i risultati delle funzioni in altrettante variabili: D = CalcOreTot(A, B)
  2. o li depositiamo direttamente nelle celle opportune: Range("M4").Value = CalcOreTot(A, B)
  3. o depositiamo nelle celle la formula appropriata usando la notazione R1C1 in modo assoluto:
    Range("M6").FormulaR1C1 = "=CalcOreTot(R4C1,R4C2)"
    o in modo relativo:
    Range("M5").FormulaR1C1 = "=CalcOreTot(R[-1]C[-12],R[-1]C[-11])"

Attenzione:

mentre per testare la maggior parte delle istruzioni che trovate in questa ed in molte pagine è sufficiente un semplice copia ed incolla, le formule che trovate con il metodo relativo per le Proprietà FormulaR1C1 occorre adattarle

 

Ecco di seguito le tre versioni:

'  cattura del risultato delle funzione tramite variabili

Sub prova()
A = Range("A4").Value
B = Range("B4").Value
C = CalcMinTot(A, B)
D = CalcOreTot(A, B)
End Sub


'  inserimento diretto nelle celle del risultato

Sub prova()
A = Range("A4").Value
B = Range("B4").Value
Range("D15").Value = CalcMinTot(A, B)
Range("E15").Value = CalcOreTot(A, B)
End Sub


'  uso della notazione R1C1

Sub prova3()
' con riferimenti relativi alla posizione corrente
Range("L5").FormulaR1C1 = "=CalcMinTot(R[-1]C[-11],R[-1]C[-10])"
Range("M5").FormulaR1C1 = "=CalcOreTot(R[-1]C[-12],R[-1]C[-11])"
' oppure con riferimenti assoluti
Range("L6").FormulaR1C1 = "=CalcMinTot(R4C1,R4C2)"
Range("M6").FormulaR1C1 = "=CalcOreTot(R4C1,R4C2)"
End Sub

 

Visti i commenti inseriti nel listato, penso sia inutile aggiungerne altri. Infatti se riusciamo a leggere con calma il listato, le istruzioni sono abbastanza chiare.

 

Spero sia tutto chiaro. Buon lavoro