Condividi:        

Excel -Formula per Calcolo SLA

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Excel -Formula per Calcolo SLA

Postdi roberto.g » 14/01/09 10:48

Ciao, sono nuovo e ho trovato su questo forum spunti molto interessanti. Nn riesco a creare una formula excel (senza quindi l'utilizzo di macro e vba) per il calcolo dello SLA. Spiego meglio, io ho questi dati

Data/Ora apertura lavoro
Data/ora sospensione lavoro
Data/ora riattivazione del lavoro (chiusura sospensione)
Data/ora chiusura definitiva lavoro

Mi servirebbe calcolare il tempo impiegato (hh,mm,ss) per l'esecuzione del lavoro, nell'orario lavorativo 8,30/18.30, solo dal lun al ven (escludendo quindi i sab e le dom)

Grazie a tutti per un vs aiuto
Roberto
roberto.g
Utente Junior
 
Post: 32
Iscritto il: 14/01/09 09:54

Sponsor
 

Re: Excel -Formula per Calcolo SLA

Postdi Anthony47 » 14/01/09 15:05

Ciao Roberto e benvenuto nel forum.
Se hai inizio lavoro (data/ora) in A2 e fine lavoro in B2, puoi calcolare le ore intercorse (in formato decimale, es 8,90 ore) con la formula
Codice: Seleziona tutto
=GIORNI.LAVORATIVI.TOT(A2;A3)*10-(RESTO(A2;1)-ORARIO(8;30;0))*24-(ORARIO(18;30;0)-RESTO(A3;1))*24

Tu ha due periodi, da inizio a inizio sospensione e da fine sospensione a fine), quindi userai la stessa formula sui due blocchi di dati e poi sommerai i due valori; ovviamente puoi fare anche tutto nella stessa formula senza bisogno del calcolo intermedio.
GIORNI.LAVORATIVI.TOT e' una funzione del componente aggiuntivo "Strumento di analisi", sa hai #NOME significa che questo componente va ancora installato: Menu /Strumenti /Componenti aggiuntivi; spunta Strumenti di Analisi, Ok.

Ciao.
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel -Formula per Calcolo SLA

Postdi roberto.g » 14/01/09 15:24

CIao antonhy, e grazie perla veloce e ottima risposta. Io mi stavo scervellando con formule kilometriche , invece in pochi caratteri hai trovato questa bella formula.
Solo una cosa. Ho notato che quando le ore di inizio o fine stanno al di fuori dell'orario di lavoro, la formula fa dei calcoli errati. Ad esempio se metto come ora inizio 2.30 AM e come ora fine 10.30 AM di una stessa data , mi dice 8 ore, invece dovrebbe dare 2 come risultato.
Forse ho sbagliato io qualche cosa?

Grazie in anticipo
ROberto
roberto.g
Utente Junior
 
Post: 32
Iscritto il: 14/01/09 09:54

Re: Excel -Formula per Calcolo SLA

Postdi pietrol » 14/01/09 15:36

Ciao roberto.g
immagino dalla tua domanda che il tempo in più per entrate prima delle 8.30 e uscite dopo le 18.30 non debba mai venire considerato
se ipotizziamo che i dati in siano nelle celle A1:A4 la formula in A5 dovrebbe essere questa:

=(B1-(SE(A1<=ORARIO.VALORE("8.30");ORARIO.VALORE("8.30");A1)))+(SE(D1>ORARIO.VALORE("18.30");ORARIO.VALORE("18.30");D1)-C1)
in questo caso la formula restituirà al massimo 10 ore se non viene registrata una pausa.
Verifica e fammi sapere se è quello che volevi.

Ciao
il lupo ululà, il castello ululì
pietrol
Utente Senior
 
Post: 270
Iscritto il: 07/01/09 14:34

Re: Excel -Formula per Calcolo SLA

Postdi roberto.g » 14/01/09 15:56

Grazie per la risposta. La formula funziona se pero' applicata alla stessa data. A me serve che la formula funzioni su date di inizio e fine che possono anche eessere diverse, dove la sospensione ci puo' essere o meno, sempre nell'orario di lavoro, esclusi sabati e domeniche.

Grazie ancora
ROberto
roberto.g
Utente Junior
 
Post: 32
Iscritto il: 14/01/09 09:54

Re: Excel -Formula per Calcolo SLA

Postdi pietrol » 14/01/09 16:31

Ciao roberto.g
ero sicuro che sarebbe stato più complicato di come sembrava.

Potresti fare un esempio di situazione in cui la formula fallisce il calcolo, per favore.

Se per esempio l'orario inizia alle 17.00 di giovedi, la pausa inizia alle 10.00 di venerdi, riprendi alle 11.00 e termini alle 16.00 quale dovrebbe essere il risultato corretto?

Ciao
il lupo ululà, il castello ululì
pietrol
Utente Senior
 
Post: 270
Iscritto il: 07/01/09 14:34

Re: Excel -Formula per Calcolo SLA

Postdi roberto.g » 14/01/09 16:51

Si in effetti sono stato un po' fumoso nella descrizione. Seguendo il tuo esempio, il lavoro inizia alle h17 di giovedi , quindi dato che lo SLA finisce alle 18,30,per giovedi conto solo 1h 3 30 min. Poi il ven lo SLA parte alle 8.30 esi blocca alle 10 per la sosp attivita, conto ancora 1h 30min. Poi lo SLA riparte alle 11 e si ferma alle 16 alla fine del lavoro, quindi altre 5h. Totale tempo trascorso 8H. Questo dovrebbe essere il risultato.Sempre che con una formula excel si possa fare

Grazie ancora
Roberto
roberto.g
Utente Junior
 
Post: 32
Iscritto il: 14/01/09 09:54

Re: Excel -Formula per Calcolo SLA

Postdi pietrol » 14/01/09 19:58

Ciao roberto.g
credo, grazie al suggerimento di Anthony che mi ha messo sulla strada giusta, di avere trovato la soluzione,
(i dati sono in A2:A5) :
Codice: Seleziona tutto
=(GIORNI.LAVORATIVI.TOT(A2;A5)-1)*10+24-(MAX(RESTO(A2;1);ORARIO(8;30;0))*24)-(24-MIN(ORARIO(18;30;0);RESTO(A5;1))*24)-(A4-A3)*24


Ho durante le prove che la formula mi considerava giorno lavorativa anche il 1/1/09 enon capivo perchè, allora sono andato a verificare sull'help in linea: la funzione GIORNI.LAVORATIVI.TOT prevede un terzo parametro facoltativo nel quale va indicata in quale zona dove sono stati elencati i giorni di chiusura aziendale oltre il sabato e la domenica.
Fammi sapere se funziona.

Ciao e buon lavoro
il lupo ululà, il castello ululì
pietrol
Utente Senior
 
Post: 270
Iscritto il: 07/01/09 14:34

Re: Excel -Formula per Calcolo SLA

Postdi Anthony47 » 14/01/09 22:13

Temo, caro pietrol, di non averti messo sulla strada giusta; le combinazioni sono purtroppo molte, e considerarle tutte e' un gioco di pazienza che non mi ha appassionato e che ha prodotto questa formula, da usare separatamente per i due gruppi di Inizio/Fine:
Codice: Seleziona tutto
=GIORNI.LAVORATIVI.TOT(A2;A3)*10-20+(ORARIO(18;30;0)-SE(GIORNO.SETTIMANA(A2;2)>5;ORARIO(8;30;0);SE(RESTO(A2;1)<ORARIO(8;30;0);ORARIO(8;30;0);MIN(RESTO(A2;1);ORARIO(18;30;0)))))*24+(SE(GIORNO.SETTIMANA(A3;2)>5;ORARIO(18;30;0);SE(RESTO(A3;1)>ORARIO(18;30;0);ORARIO(18;30;0);MAX(RESTO(A3;1);ORARIO(8;30;0))))-ORARIO(8;30;0))*24

Poi si sommano i risultati; ovviamente si puo' combinare le due formule in una unica cella.

Non tiene conto dei festivi infrasettimanali eventualmente dichiarati nel calendario di Giorni.Lavorativi.Tot, andrebbe ampliata quelle parti che dicono " SE(GIORNO.SETTIMANA(Ax;2)>5 etc etc", trasformandola in un "SE(O(GIORNO.SETTIMANA(Ax;2)>5;NON(VAL.NON.DISP(CERCA.VERT(Ax;Area_dei_Festivi;0)))); etc etc"

Spero che funzioni...
Ciao.
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel -Formula per Calcolo SLA

Postdi pietrol » 15/01/09 02:07

Ciao Anthony
complimenti per la creazione della formula più complessa che io abbia mai visto, di gran lunga.
Mi hai annichilito! Dopo aver visto la tua, ho riguardato la mia con commiserazione.
In effetti la mia soluzione funziona e sembra restituire risultati corretti, solo se il lavoro non inizia o finisce in un giorno non lavorativo, caso che però roberto.g sembra escludere a priori (attendo conferma).
Se comunque fossero poche le eccezioni si potrebbe pensare di gestirle in maniera diversa, per esempio creando una colonna a fianco in cui inserire una formula di controllo che semplicemente avvisi dell'anomalia:

Codice: Seleziona tutto
=SE(O(GIORNI.LAVORATIVI.TOT(A2;A2)=0;GIORNI.LAVORATIVI.TOT(A3;A3)=0;GIORNI.LAVORATIVI.TOT(A5;A5)=0);"Festivo!";"")

oppure addirittura nidificando in questa la formula precedente, così da restituire direttamente il risultato corretto.
Forse da un punto di vista pratico sarebbe più semplice anche se la tua soluzione è ineccepibile e molto più rigorosa da un punto di vista teorico, tu che ne pensi?

ciao
pietrol
il lupo ululà, il castello ululì
pietrol
Utente Senior
 
Post: 270
Iscritto il: 07/01/09 14:34

Re: Excel -Formula per Calcolo SLA

Postdi roberto.g » 15/01/09 10:23

Ciao
grazie a tutti e due. Le due formule funzionano bene entrambe. In rif. a, pietro, il lavoro può iniziare anche fuori dal normale orario di lavoro (quindi anche sabati e domeniche) , mentre puo' terminare solo nell'orario di lavoro.Da questo punto di vista la formula è perfetta. C'e' solo una cosa che forse nn ho specificato bene. La sospensione puo' esserci o meno. Quindi in taluni casi i campi inizio e fine sospensione possono essere vuoti. In tali casi la formula restituisce una cella in errore. Potete risolvermi anche questa anomalia?

Grazie ancora
Roberto
roberto.g
Utente Junior
 
Post: 32
Iscritto il: 14/01/09 09:54

Re: Excel -Formula per Calcolo SLA

Postdi pietrol » 15/01/09 11:12

Avevo provato anche senza la pausa, lasciando vuote le celle, e funzionava, se invece c'è un testo o uno spazio dà errore.
Mi sembra di capire che ricavi questi dati da un sistema di segnalazione presenze per cui i dati sono sempre nello stesso formato, se ci dici esattamente cosa c'è nelle celle quando manca la pausa possiamo correggere le formule più facilmente.

Una curiosità: dici che le formule, entrambe, funzionano anche se il lavoro inizia in un giorno festivo; vuol dire il tempo viene calcolato comunque a partire da quello lavorativo?
il lupo ululà, il castello ululì
pietrol
Utente Senior
 
Post: 270
Iscritto il: 07/01/09 14:34

Re: Excel -Formula per Calcolo SLA

Postdi roberto.g » 15/01/09 11:25

Si, i dati sono sempre nello stesso formato

INZIO E FINE LAVORO sono campi sempre presenti (obbligatori) e sono nel formato gg/mm/aa h.mm.ss (es: 09/01/2009 13.30.00)

INIZIO E FINE SOSPENSIONE sono campi non sempre compilati. Possono esserci entrambi e sempre nel formato di sopra, oppure posso mancare entrambi, e nel caso queste due celle sarebbero vuote

Grazie ancora per la disponibilità
Roberto
roberto.g
Utente Junior
 
Post: 32
Iscritto il: 14/01/09 09:54

Re: Excel -Formula per Calcolo SLA

Postdi pietrol » 15/01/09 11:50

ciao roberto.g
prova questa, ho aggiunto la verifica che entrambi i campi pausa siano numerici altrimenti vengono ignorati.
Codice: Seleziona tutto
=(GIORNI.LAVORATIVI.TOT(A2;A5)-1)*10+24-(MAX(RESTO(A2;1);ORARIO(8;30;0))*24)-(24-MIN(ORARIO(18;30;0);RESTO(A5;1))*24)-SE(CONTA.NUMERI(A3:A4)=2;(A4)-(A3);0)*24
il lupo ululà, il castello ululì
pietrol
Utente Senior
 
Post: 270
Iscritto il: 07/01/09 14:34

Re: Excel -Formula per Calcolo SLA

Postdi roberto.g » 15/01/09 12:03

La formula funziona perfettamente. L'unica cosa, e che primo mi ero dimenticato di dire e che tu mi avevi chiesto, è quando l'orario di inizio lavoro sta al di fuori del normale orario di lavoro, la formula calcola erroneamente i tempi.

Grazie
Roberto
roberto.g
Utente Junior
 
Post: 32
Iscritto il: 14/01/09 09:54

Re: Excel -Formula per Calcolo SLA

Postdi pietrol » 15/01/09 13:38

A questo punto dovresti farmi degli esempi concreti, per esempio:
se il lavoro inizia sabato alle 07.00 quante ore di lavoro devono essere conteggiate fino alla pausa di lunedi alle 10.00?

Ciao
pietrol
il lupo ululà, il castello ululì
pietrol
Utente Senior
 
Post: 270
Iscritto il: 07/01/09 14:34

Re: Excel -Formula per Calcolo SLA

Postdi roberto.g » 15/01/09 13:49

Seguendo il tuo esempio se il lavoro inizia sabato alle 7, il conteggio dello SLA parte dalle 8.30 di lunedi mattina, quindi fino all'inizio della pausa alle 10 conto 1h 30 min.

Grazie
Roberto
roberto.g
Utente Junior
 
Post: 32
Iscritto il: 14/01/09 09:54

Re: Excel -Formula per Calcolo SLA

Postdi pietrol » 15/01/09 14:10

Con i dati che ti ho chiesto prima il risultato che ottengo è 1,50 come tu vorresti che fosse.
Forse è meglio se tu mi dai un esempio in cui hai verificato che il conteggio fallisce...

Dai che ci siamo, quasi.

Ciao
pietrol
il lupo ululà, il castello ululì
pietrol
Utente Senior
 
Post: 270
Iscritto il: 07/01/09 14:34

Re: Excel -Formula per Calcolo SLA

Postdi roberto.g » 15/01/09 14:21

Prova a mettere come ora di partenza le 20 di sabato. Dovrebbe sempre darti 1h 30 min invece da un valore negativo

Sempre tnks
roberto.g
Utente Junior
 
Post: 32
Iscritto il: 14/01/09 09:54

Re: Excel -Formula per Calcolo SLA

Postdi pietrol » 15/01/09 14:48

Prova adesso, ho aggiunto una verifica se il giorno (A2) di inizio è festivo, nel caso forza come ora di inizio 8.30, se come hai detto ne' la pausa ne' il file lavoro puo avvenire in giorni festivi dovrebbe andare
=(GIORNI.LAVORATIVI.TOT(A2;A5)-1)*10+24-(SE(GIORNI.LAVORATIVI.TOT(A2;A2)=0;ORARIO(8;30;0);MAX(RESTO(A2;1);ORARIO(8;30;0)))*24)-(24-MIN(ORARIO(18;30;0);RESTO(A5;1))*24)-SE(CONTA.NUMERI(A3:A4)=2;(A4)-(A3);0)*24


Ciao
pietrol
il lupo ululà, il castello ululì
pietrol
Utente Senior
 
Post: 270
Iscritto il: 07/01/09 14:34

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "Excel -Formula per Calcolo SLA":


Chi c’è in linea

Visitano il forum: Nessuno e 109 ospiti