EXCEL-sottrazione di date

[muflix]

CIao,

Vorrei riuscire a creare una formula che oltre a sottrarre 2 date o due ore risucisse eventualmente a scorporare l' intervallo di tempo dalle 13.30 alle 15.30.

Esempio:

Dovrei effettuare la sottrzione di questi 2 orari 15:59:23 e 13:12:36 però nel risultato vorrei mi scorporasse l' intervallo di tempo dalle 13.30 alle 15.30. in pratica dovrebbe eseguire la sottrazione e poi fare meno 2.
Logicamente però dovrebbe fare meno 2 solo dove gli intervelli di tempo comprendono tale fascia oraria mentre per gli altri non dovrebbe fare meno 2.
E' possibile?

Grazie

[muflix]

=IF((VALUE(MID(Z2;1;10))>"15:30:00"&VALUE(MID(E2;1;10))<"13:30:00");Z2-E2-2;IF("15:30:00"<(VALUE(MID(Z2;1;10)))>"13:30:00"&(VALUE(MID(E2;1;10))<"13:30:00");ABS((Z2-"13:30:00")-E2);Z2-E2))

La funziona precedentemente postata dovrebbe effettuare la verifica di due orari con formato 00:00:00.
se tra il primo è maggiore delle 15.30 e il secondo è inferiore alle 13.30 allora alla differenza dei 2 sottrae due ore.
Mentre se il primo è compreso tra 13.30 e 15.30 e il secondo è inferiore delle 13.30 sottrae al primo le 13.30 e e poi sottrae il secondo.
Altrimenti sottrae i due orari normalmente.
Non mi da il risultato desiderato.
Potreste darmi una mano?

[Anthony47]

Credo che dovresti spendere ancora qualche rigo per spiegare il problema, perche' giorni e ore sono cose diverse, pur appartendendo ambedue al "regno" del tempo; quindi in che senso dal numero (di giorni) che ottieni sottraendo le due date vuoi scorporare l' intervallo orario 13:30-15:30? E' per caso un calcolo di "ore lavorative"?

Per l' esempio che hai fatto (sottrazione degli orari 15:59:23 e 13:12:36), se inizio e' in A1 e fine in A2 potresti allora usare una formula come questa:

Codice: Seleziona tutto

=A2-A1-MIN(A2;ORARIO(15;30;0))+MAX(A1;ORARIO(13;30;0))

Se hai excel in inglese, Orario si traduce Time.

Ciao

[muflix]

CERCO 15;30;0))+MAX(A1;ORARIO(13;30;0))[/code]
Se hai excel in inglese, Orario si traduce Time.

Ciao[/quote]
di spiegare meglio il problema.
Dovrei sottrarre due ore che sono in formato 00:00:00.
la sottrazione è semplice ma dovrebbe rispettare i seguenti criteri.
Bisogna scorporare dalla differenza le ore comprese tra le 13.30 e le 15.30.
QUindi sia che l' intervallo inizi prima delle 13.30 e finisca dopo le 15.30; sia che l' intervallo inizi dopo le 13.30 ma prima delle 15.30 e finisca dopo le 15.30 (in questo caso bisognerebbe scorporare solo i minuti dopo le 13.30); sia in quei casi dove si inizi prima delle 13.30 e finisca tra le 13.30 e le 15.30.
Spero di aver spiegato bene.
Grazie

[Anthony47]

Mah, questi giochi di pazienza non mi eccitano tanto; questa?

Codice: Seleziona tutto

=MIN(ORARIO(13;30;0);A2)-MIN(A1;ORARIO(13;30;0))+MAX(A2;ORARIO(15;30;0))-MAX(A1;ORARIO(15;30;0))


Ciao.

[muflix]

Mah, questi giochi di pazienza non mi eccitano tanto; questa?

Codice: Seleziona tutto

=MIN(ORARIO(13;30;0);A2)-MIN(A1;ORARIO(13;30;0))+MAX(A2;ORARIO(15;30;0))-MAX(A1;ORARIO(15;30;0))


Ciao.

Grazie Anthony...
C è ancora un eccezione che vorrei implementare nella formula sempre se possibile...
Possiamo tenere conto anche delcasoin cui il primo valore sia tipo 18.27.00 mentre il secondo sia tipo 8.47.00, e che da questo intervallo di tempo dovremmo scorporare la fascia oraria notturna...ossia dalle 18.30 alle 8.30.
GRazie mille

[muflix]

Mah, questi giochi di pazienza non mi eccitano tanto; questa?

Codice: Seleziona tutto

=MIN(ORARIO(13;30;0);A2)-MIN(A1;ORARIO(13;30;0))+MAX(A2;ORARIO(15;30;0))-MAX(A1;ORARIO(15;30;0))


Ciao.

Grazie Anthony...
C è ancora un eccezione che vorrei implementare nella formula sempre se possibile...
Possiamo tenere conto anche delcasoin cui il primo valore sia tipo 18.27.00 mentre il secondo sia tipo 8.47.00, e che da questo intervallo di tempo dovremmo scorporare la fascia oraria notturna...ossia dalle 18.30 alle 8.30.
GRazie mille

Inoltre Anthony è possibile eseguire le stesse operazioni anche sulle date?
Esempio data iniziale 2010-01-04 e data finale 2010-01-12.
Dovrei sottrarre le due date e ottenere i giorni di differenza tra le due, quando la differenza da zero in automatico dovrebbe mettere uno; inoltre dalla differenza bisognerebbe scorporare i festivi, i sabati e le domeniche.
Nell' esempio precedente il risultato dovrebbe essere 5 poichè ci sono un festivo, un sabato e una domenica.
Ossia 8 - 5.

Grazie.

[Anthony47]

Per quanto riguarda la formula che fa le differenze orarie con neutralizzazione, vista la (prima) variazione sul tema che hai proposto forse e' meglio passare a una formula "generica", che gestisce anche orari a cavallo della mezzanotte, sia come inizio/fine che come periodo da neutralizzare, e soprattutto dove gli orari da neutralizzare non sono "nella formula" ma in due celle di appoggio.
Se Inizio e' in A1, Fine in A2, neutralizzazione inizio in G1, neutralizzazione fine in H1, allora dovrebbe andare bene questa formula:

Codice: Seleziona tutto

=A2+(A2<A1)-A1-SE(E(A1<=(H1+(H1<G1));(A2+(A2<A1))>=H1+(H1<G1));H1+(H1<G1)-MAX(A1;G1);0)-SE(E((A2+(A2<A1))<H1+(H1<G1);(A2+(A2<A1))>=G1);MIN(A2+(A2<A1);H1+(H1<G1))-G1;0)


Questa dovrebbe funzionare sia con orari tipo 18:27/08:47 che con orari tipo 12:00/17:00.

Per quanto riguarda il calcolo dei giorni, lo fai con santa pazienza usando colonne di appoggio e, per il calcolo della differenza di date, usando la funzione GIORNI.LAVORATIVI.TOT che consente di gestire Sabati, Domeniche e giorni festivi.
Se si tratta di calcolo di SLA ti rimanderei anche alla consultazione di questa discussione: viewtopic.php?f=26&t=77760
Se si tratta invece di gestione di orari lavorativi, le combinazioni sono tante che per svicolare ho reso disponibile una funzione ad hoc; vedi qua: viewtopic.php?f=26&t=69705

Ciao.