Formula Excle Date-Ora

[yoghi87]

Buona sera, il problema è questo:
-ho una fascia oraria standard 7:30-12:30 e 14:00-17:00;
-decido un'ora di inizio con rispettiva data e un'ora di fine con rispettiva data che può essere anche il giorno successivo o il giorno stesso.

Ora vorrei creare una formula che mi restituisce la somma di ore secondo la data di inizio e di fine, però sommando solo le ore che rientrano nella fascia oraria stabilita....

[wallace&gromit]

ciao yoghi,
io sono quello delle soluzioni complicate, che talvolta funzionano.
Magari Anthony e Flash riusciranno a semplificare il procedimento.
Comunque il tuo problema l'ho risolto così:
in una colonna d'appoggio inserisco questa formula, che mi riporta tutte le date a giorno + un decimale che descrive il numero di ore "filate" nel giorno.

Codice: Seleziona tutto

=SE(ORA(A1)>14;A1-(7.5/24)-1.5/24;A1-(7.5/24))

In un'altra colonna eseguo il calcolo con la seguente formula:

Codice: Seleziona tutto

=ARROTONDA(B2-B1;0)*8+((B2-B1)-ARROTONDA(B2-B1;0))*24

Questa si può sicuramente semplificare (ma non so come) in sostanza moltiplica le differenze di giorni * 8 (no massimo di ore al giorno) e somma la parte di decimale che descrive le ore rimanenti.

Prova e facci sapere

[Anthony47]

Forse non ho capito, ma se l' orario standard finisce alle 17:00 come e' possibile che tu lavori non restribuito fino a un orario magari del giorno dopo?

Comunque potresti usare questo codice:

Codice: Seleziona tutto

Function WorkH(ByRef InOut As Range, ByRef StdH As Range) As Double
'InOut=Orari di Ingresso e Uscita; StdH=4 celle con In/Out, In/Out standard
Dim myMin As Integer, Steps As Double, I As Integer, StrMin As Double
'
If InOut.Range("A1").Value = 0 Or InOut.Range("B1").Value = 0 Then Exit Function
'
Steps = InOut.Range("B1").Value - Int(InOut.Range("B1").Value) - _
    InOut.Range("A1").Value + Int(InOut.Range("A1").Value)
StrMin = InOut.Range("A1").Value - Int(InOut.Range("A1").Value)
If Steps < 0 Then Steps = Round((Steps + 1) * 1440, 0) Else Steps = Round(Steps * 1440, 0)
For I = 0 To Steps - 1
    If (StrMin + I / 1440 - Int(StrMin + I / 1440)) >= StdH.Range("A1").Value And _
        (StrMin + I / 1440 - Int(StrMin + I / 1440)) < StdH.Range("B1").Value Then
        myMin = myMin + 1
    ElseIf (StrMin + I / 1440 - Int(StrMin + I / 1440)) >= StdH.Range("C1").Value And _
        (StrMin + I / 1440 - Int(StrMin + I / 1440)) < StdH.Range("D1").Value Then
        myMin = myMin + 1
    End If
Next I
WorkH = myMin / 1440
End Function


Da excel, Alt-F11 per aprire l' editor delle macro; Menu /Inserisci /Modulo; copi il codice e lo incolli nel frame di destra.
Poi torni in excel e usi una formula del tipo

Codice: Seleziona tutto

=Workh(A2:B2;$J$1:$M$1)

Il risultato e' in formato orario; la formula puo' essere copiata poi verso il basso se hai un elenco di orari di ingresso /uscita da valorizzare.
Presuppone che l' ora di inizio sia in A2, quella di fine in B2, e le quattro celle con l' orario standard in J1:K1, L1:M1; se hai un layout diverso basta adattare la formula.
Se l' orario di termine e' inferiore all' orario di inizio si assume che si tratti del giorno dopo.

La proposta di W&G (vedi sopra) non l' ho provata perche' non ho capito la relazione tra la prima e la seconda formula e quale il tracciato dati su cui provare.

Ciao

[wallace&gromit]

Ciao Anthony,
penso che abbiamo interpretato in modo diverso la richiesta di yoghi, a lui di dirci quale è la soluzione giusta.
Posti come orari di inizio e fine:
22.02.2012 09:00:00
23.02.2012 17:00:00
la tua formula restituisce 6 h e 30.
La mia restituisce invece 14 h e 30.
Il mio ragionamento è che serve determinare il tempo di lavoro totale intercorso tra le due date (si presuppone che per compiere una determinata attività l'impiegato abbia fatto una pausa a mezzogiorno, smesso alle 17 e ripreso il giorno successivo fino alle 17).

P.S. la seconda formula la si può semplificare così:

Codice: Seleziona tutto

=INT(B9-B8)*8+((B9-B8)-INT(B9-B8))*24

[Anthony47]

Ciao w&g, credo che la tua lettura del quesito sia quella giusta, cioe' si tratta di calcolare la durata contrattuale di un servizio (o disservizio) e non di un turno di lavoro (come avevo interpretato io ).

Ciao!