[Tutorial Formule] Calcoli con le ore

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
18.123
736
113
Como
2011MAC 2016WIN
316
CALCOLI CON LE ORE

Uno degli argomenti più ostici in Excel è fare calcoli con le ore.

Le funzioni per le ore sono:

ORA Converte un numero seriale in un'ora
MINUTO Converte un numeros eriale in un minuto
ADESSO Restituisce il numero seriale della data e dell'ora correnti
SECONDO Converte il numeros eriale in un secondo
ORARIO Restituisce il numero seriale di un orario
ORARIO.VALORE Converte un orario in forma di stesto in un numero seriale

Per ottenere l'orario attuale si potrebbe utilizzare la segente funzione:

=ADESSO()

ottenendo la DATA di oggi e l'orario e quindi formattare la cella interessata come "hh:mm"

oppure, in maniere più semplice senza fare alcuna formattazione si può usare la seguente formula:

=ADESSO()-OGGI()

Per calcolare la differenza tra due ore si potrebbe semplicemente sottrarre;
esempio:
in A1 -> 8:00
in B1 -> 12:00
=B1-A1 risultato 4:00 (la cella in cui si vuole ottenere il risultato restituisce un numero seriale e va formattata in hh:mm

Il problema però si presneta subito quando si cerca di sottrarre un'ora maggiore di una minore, esempio:
in A1 -> 12:00
in B1 -> 8:00
in questo caso il risultato sarà ######

Si potrebbe risolvere usando la funzione ASS

=ASS(B1-A1)

e funziona correttamente se gli orari interessati sono nella stessa giornata, se però il periodo supera la mezzanotte il risultato sarà sarà errato
esempio
A1 -> 22:00
B1-> 4:00
Usando ASS(B1-B2) darà 18:00 (errato) e con B1-B2 darà un risultato negativo

Il problema si risolve con la seguente funzione, ben nota a chi conosce excel:

=RESTO(B1-A1;1)

e restituirà correttamente che dalle 22:00 alle 4:00 sono passate 6 ore.

La funzione RESTO ci viene in aiuto in quanto lascia i valori positivi come sono mentre per i negativi calcola la differrenza tra questi e -1

Possiamo risolvere anche con una formula logica che ottiene lo stesso risultato:

=B1-A1+SE(A1>B1;1)

in questo caso si fa la differenza tra i due orari ma se l'orario sottratto è maggiore di quello da sottrarre si aggiunge 1

Un altro metodo che sfrutta una peculiarità delle variabili booleane si ottiene con la seguente formula:

=B1-A1+(A1>B1)

In pratica alla sottrazione di B1-A1 si somma A1>B1 che può essere VERO (se A1 è maggiore di B1) oppure FALSO (se A1 è minore o uguale a B1), se è VERO darà 1.

In ogni caso per tagliare la testa al toro si può mettere direttamente il +1, così:

=B1-A1+1

Un modo alternativo per ottenere lo stesso risultato è con questa formula:

=SE(A1>B1;1-(A1-B1);B1-A1)

Ci sono però delle situazioni in cui bisogna calcolare l'Intera giornata, cioè da mezzanotte a mezzanotte del giorno dopo:
A1 -> 0:00
B1-> 0:00
in questo caso le formule citate sopa falliscono poiché daranno come risultato 0:00

Ci soccore in aiuto questa formula, più complessa:

=SE(RESTO(B1-A1;1)=0;RESTO(B1-A1;1)+1;RESTO(B1-A1;1))

con questa formula avremo come risultato 24:00.
Quest'ultima è una formula universale poiché può essere usata non solo per tale citata occasione ma anche in tutte le altre precedenti.

Un rislutato simile viene dato anche con queste altre due formule:

=SE(B1-A1=0;1;SE(B1<A1;B1+1;B1)-A1)

=SE(RESTO(B1-A1;1)=0;(B1+(RESTO(ORARIO(0;0;0)-B1;1)));RESTO(B1-A1;1))

Può essere che occorra calcolare il tempo trascorso dopo un certo numero di ore (es. pausa, straordinario, ecc).
Cioè mettiamo che una persona abbia come orario di lavoro ordinario n. 8 ore giornaliere e nel caso in cui superi tale orario la parte eccedente sia imputabile a lavoro straordinario, per orrenere ques&#8217;ultimo valore si può ultilizzare la seguente formula.
A1 -> inizio: 8:00
B1-> fine: 18:00
C1-> ore effettuate: 10
C2-> starordinario (superiore a 8 ore): 2

La formula che da la soluzione (proposta più volte da Gerardo) è la seguente:

=SE(RESTO(B1-A1;1)>8/24;RESTO(B1-A1;1)-8/24;0)

La formula che lavora per tale risultato è veramente questa:

=RESTO(B1-A1;1)-8/24

con la quale si effettua la nota differenza oraria e dal risultato si sottraggono le 8 ore, però se il risultato è minore di 8 sottranedo 8 ore ci restituirà il classico errore #######; per ovviare si prevede la condizione che SE la formua darà un risultato maggiore di 8 (cioè se è vero) sia valido il risultato della formula, altrimenti (cioè SE è falso) dia come risultato 0.

Una formula alternativa alla predente per calcolare l'orario eccedente è la seguente:

=MAX(0;B1-A1+(A1>B1)-8/24)

in questo caso si sfrutta la funzione MAX prevedendo come primo argomento lo 0


L'argomento ovviamente non è esaustivo, spero che non vi siano errori, in ogni caso sono ben accetti suggerimenti ed integrazioni.
 

Allegati

Ultima modifica di un moderatore:

ggratis

VBA Expert
Expert
27 Settembre 2015
1.178
80
48
Lecce - Pisa
Excel 2010
30
Calcoli con le ore - approssimazione orari ad intervalli prefissati di tempo

Cogliendo l'invito di Gerardo,
credo possano essere una continuazione di questa discussione alcune considerazioni in merito all'approssimazione dei valori temporali, già fatte in risposta ad una domanda fatta nel formum.

La problematica si riscontra spesso rielaborando i dati forniti dalle varie apparecchiature di timbratura cartellini in ingresso ed uscita dei dipendenti, dove gli orari registrati possono avere una precisione che non è adeguata rispetto alle esigenze di fatturazione, per le quali risulta opportuno effettuare delle approssimazioni dell'orario di lavoro rispetto alla mezz'ora, ai 10-15 minuti o altro da precisare.

Le soluzioni al problema possono essere diverse, tra queste le più semplici quelle che sfruttano le funzioni di arrotondamento pesato di excel:
ARROTONDA.DIFETTO(Num; Peso)
ARROTONDA.DIFETTO.PRECISA(num; [peso])
ARROTONDA.ECCESSO(Num; Peso)
ARROTONDA.ECCESSO.PRECISA(num; [peso])
ISO.ARROTONDA.ECCESSO(Num; [Peso])
ARROTONDA.MULTIPLO(Num; Multiplo)
ecc...

Tali funzioni trovano applicazione perché ogni valore temporale di Excel è rappresentato da un valore seriale indicante il numero di giorni rispetto ad una data di riferimento; le ore, i minuti i secondi, i decimi, ecc. corrispondono alla parte decimale di tale numero seriale secondo una frazione predeterminata, tali frazioni possono essere così rappresentate:
1 ora =1/24 giorni = 1/24 giorni
1 min = 1/(24*60) giorni = 1/1440 giorni
1 secondo = 1/(24*60*60) giorni = 1/86400 giorni
1 decimo (di secondo) = 1/(24*60*60*10) giorni = 1/864000 giorni
ecc.

Quindi nel caso si voglia arrotondare il numero seriale rappresentativo del valore temporale, per esempio ai 5 min, 10 min, 15 min, 30 min, 1 ora o altro è sufficiente utilizzare nelle funzioni di arrotondamento i corrispondenti pesi pari a:
5 min = 5/(24*60) giorni = 5/1440 giorni -> peso = 1/288
10 min = 10/(24*60) giorni = 10/1440 giorni -> peso = 1/144
15 min = 15/(24*60) giorni = 15/1440 giorni -> peso = 1/96
30 min = 30/(24*60) giorni = 30/1440 giorni -> peso = 1/48
1 ora = 1/24 giorni -> peso = 1/24

la cella contenente il valore seriale risultato va formattata con il formato data voluto.

nella risposta fornita nel forum, è stata utilizzata la funzione ARROTONDA.MULTIPLO(Num; Multiplo)

saluti
GG
 
  • Like
Reactions: Andrea

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
18.123
736
113
Como
2011MAC 2016WIN
316
Re: Calcoli con le ore - approssimazione orari ad intervalli prefissati di tempo

Un esempio di calcolo.
Turni di lavoro di sei ore distribuiti su orari diurni (6:22) e notturni(22:6) e relativo straordinario in caso di superamento delle 6 ore.
 

Allegati

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
18.123
736
113
Como
2011MAC 2016WIN
316
Ciao a tutti,
ho sottoposto l'esercizio di cui sopra a Giovanni Velardita - docente di informatica che ha gentilmente rielaborato con formule semplificate il calcolo delle ore.


Giovanni Velardita

Ipotizzando due turni lavorativi a cavallo tra diversi giorni, considerato che il superamento di sei ore produce lavoro straordinario e il lavoro effettuato tra le 22:00 e le 6:00 è da considerare lavoro notturno ha proposto le seguenti formule:


Per semplificare ulteriormente ha dato dei nomi a delle formule, precisamente:


ore_22 --> =ORARIO(22;;)

ore_6--> =ORARIO(6;;)

ore_8 --> =ORARIO(8;;)



Il foglio Excel è impostato con i seguenti dati:

Colonna B -> Entrata primo turno

Colonna C -> Uscita primo turno

Colonna D -> Entrata secondo turno

Colonna E-> Uscita secondo turno


Considerando la riga 2, per avere le "ORE TOTALI" in F2:

=RESTO(C2-B2+E2-D2;1)


Per avere lo straordinario superiore alle sei ore in G2:

=SE(F2>ore_6;F2-ore_6;0)


In H2 straordinario notturno:

=F2-I2


In I2 ore diurne:

=SE(C2=B2;0;SE(C2>B2;MIN(C2;ore_22)-MAX(B2;ore_6);MAX(C2;ore_6)-ore_6+ore_22-MIN(B2;ore_22)))


In J2 straordinario diurno

=G2-K2


In K2 straordinario notturno

=SE(H2>ore_6;H2-ore_6;0)


Grazie Giovanni SmileFace
 

Allegati

Ultima modifica:
  • Like
Reactions: Andrea and Powerwin

Powerwin

VBA Expert
Expert
17 Marzo 2016
3.484
139
63
Milano
2016/365
42
Ottimo ges @ges e in particolar modo utile perché vedo diverse richieste su questo argomento
 

Sostieni ForumExcel

Aiutaci a sostenere le spese e a mantenere online la community attraverso una libera donazione!