Quiz del Giorno #3 Ore Straordinari

Stato
Discussione chiusa ad ulteriori risposte.
30 Maggio 2015
5,319
72
48
50
Milano, Italy
office 365/2016
Miglior risposte
21
#1
Ciao
Quante ore di straordinari ci sono dopo le 8 ore lavorate?
io ho scritto il risultato a mano in D2 ... ma con le formule?
Grazie per le vostre risposte

Row\Col

A​

B​

C​

D​

1​

entrata

uscita

 

straordinari

2​

23:00​

08:00​

 

01:00​

 
Ultima modifica:
4 Settembre 2015
9
0
0
53
Pescara
2014 PC
Miglior risposte
0
#7
Buonasera! Sono molto interessato a questa formula....L'ho applicata e mi funziona perfettamente quando mi conteggia le ore in più di lavoro (straordinario) ma se dovessi finire prima del termine lavorativo (permesso) mi esce errore o meglio.....tanti cancelletti.
ESEMPIO: Il lavoro dura 8 ore dalle 08:00 alle 16:00 e se inserisco come orario di uscita le 17:00 mi da un'ora di straordinario ma se finisco alle 15:00 non mi da un'ora da recuperare ma ERRORE!
 
30 Maggio 2015
5,319
72
48
50
Milano, Italy
office 365/2016
Miglior risposte
21
#8
Ciao SalsaRico
Quando si lavora con le ore si mettono i due punti :_sorriso_:) tra le ore e i minuti questo non è solo una formattazione ma si stabilisce delle regole ben precise, cioè excel lavora con delle porzioni di 24 ore esempio le ore 8:00 corrisponde alle 0,3333333333333333 ed excel fa i calcoli con questo ultimo numero invisibile e non con il numero che scriviamo sopra.. tutto questo è stato creato per farci restituire i numeri in sessantesimi, solo che lo svantaggio è che quando il risultato è negativo anzichè restituirci -1:00 ci restituisce ############### all'infinito cioè non sopporta i numeri negativi
allora per aggirare il problema l'unica cosa che posso consigliarti di trasformare i numeri da sessantesimi es (14:30) a numeri centesimi cioè l'equivalente (14,50)

inserisci questa formula in C2:

=SE(RESTO(B2-A2;1)*24>8;RESTO(B2-A2;1)*24-8;RESTO(B2-A2;1)*24-8)

 

A

B

C

2

07:00​

13:00​

-2​




PS sicuramente la formula ti darà un numero 00:00 indipendentemente se il risultato è negativo e positivo, questo perchè la formula ha strappato il formato ore:minuti dalle celle a sinistra e a questo punto cambio il formato cella in formato generale
Fai CTRL+1 e scegli il formato generale

fammi sapere!

 

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
13,325
174
63
Como
2011MAC 2016WIN
Miglior risposte
88
#9
  • ges

    ges

Una possibile alternativa:

=SE(B2>=A2;(B2-A2)-"8:00";24-(A2-B2)-"8:00")
 
30 Maggio 2015
5,319
72
48
50
Milano, Italy
office 365/2016
Miglior risposte
21
#10
Una possibile alternativa:

=SE(B2>=A2;(B2-A2)-"8:00";24-(A2-B2)-"8:00")
Ciao ges
c'è qualcosa che non va nella tua formula se
se il risultato è un numero negativo mi da ################################## (con formato hh:mm)
se invece gli do il formato generale mi da (esempio -0,00353113154)
forse dovresti aggiustarla alla fine

=SE(B2>=A2;(B2-A2)-"8:00";24-(A2-B2)-"8:00")*24

[EDIT]
Poi nella tua formula ho notato un'altro problema
se l'oraro è a cavallo della mezzanotte mi da risultati sballati
esempio:

 

A

B

C

D

2

22:00​

02:00​

-4​

Gerardo

3

22:00​

02:00​

548​

Ges



Fammi sapere!
 
Ultima modifica:

Andrea

Utente junior
22 Giugno 2015
99
1
8
61
Levico Terme
2016
Miglior risposte
0
#11
Ciao SalsaRico
Quando si lavora con le ore si mettono i due punti :_sorriso_:) tra le ore e i minuti questo non è solo una formattazione ma si stabilisce delle regole ben precise, cioè excel lavora con delle porzioni di 24 ore esempio le ore 8:00 corrisponde alle 0,3333333333333333 ed excel fa i calcoli con questo ultimo numero invisibile e non con il numero che scriviamo sopra.. tutto questo è stato creato per farci restituire i numeri in sessantesimi, solo che lo svantaggio è che quando il risultato è negativo anzichè restituirci -1:00 ci restituisce ############### all'infinito cioè non sopporta i numeri negativi
allora per aggirare il problema l'unica cosa che posso consigliarti di trasformare i numeri da sessantesimi es (14:30) a numeri centesimi cioè l'equivalente (14,50)

inserisci questa formula in C2:

=SE(RESTO(B2-A2;1)*24>8;RESTO(B2-A2;1)*24-8;RESTO(B2-A2;1)*24-8)

 

A

B

C

2

07:00​

13:00​

-2​




PS sicuramente la formula ti darà un numero 00:00 indipendentemente se il risultato è negativo e positivo, questo perchè la formula ha strappato il formato ore:minuti dalle celle a sinistra e a questo punto cambio il formato cella in formato generale
Fai CTRL+1 e scegli il formato generale

fammi sapere!


Ci sarebbe questa possibilità =TESTO(ASS(B2-A2),"-h:mm:ss") però non riesco ad inserirla nella formula
 

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
13,325
174
63
Como
2011MAC 2016WIN
Miglior risposte
88
#12
  • ges

    ges

Ciao ges
c'è qualcosa che non va nella tua formula se
se il risultato è un numero negativo mi da ################################## (con formato hh:mm)
se invece gli do il formato generale mi da (esempio -0,00353113154)
forse dovresti aggiustarla alla fine

=SE(B2>=A2;(B2-A2)-"8:00";24-(A2-B2)-"8:00")*24

[EDIT]
Poi nella tua formula ho notato un'altro problema
se l'oraro è a cavallo della mezzanotte mi da risultati sballati
esempio:

 

A

B

C

D

2

22:00​

02:00​

-4​

Gerardo

3

22:00​

02:00​

548​

Ges



Fammi sapere!
Ho provato diverse volte e a me funzionava .... allora ho approfondito.
In pratica, utilizzano io il MAC excel il mio excel utilizza il sistema di data 1904 che accetta gli orari negativi, mentre il windows che utilizza il sistema di data 1900 non li accetta e compaiono gli errori che tu hai descritto.
Se utilizzi la formula senza togliere 8 ore
=SE(B2>=A2;(B2-A2);24-(A2-B2))
dovrebbe funzionare anche a te.
Anche quest'altra formula dovrebbe funzionarti:
=SE(B2>=A2;(B2-A2);(B2+1)-A2)
Se però provo a togliere 8 ore a me continua a funzionare per il sistema di data del mio MAC mentre nel tuo windows da errori.
Può essere anche che questo problema venga corretto con excel 2016 che io già uso.

https://support.microsoft.com/it-it/kb/182247


 
4 Settembre 2015
9
0
0
53
Pescara
2014 PC
Miglior risposte
0
#14
Troppo forte la curiosità di sapere il risultato e quindi sono andato adesso a curiosare sul mio progetto le vostre formule.....Sono tutte e due perfettamente funzionanti (quella di GES dopo la piccola correzione di Gerardo). Non voglio approfittare della vostra conoscenza e disponibilità e temo che non sarò MAI in grado di aiutarvi perchè io rispetto a voi sono un IGNORANTONE! Un dubbio mi rimane sulle formule suggerite........Se non inserisco alcun orario in tabella mi esce un bel "-8" Come è possibile evitare questo?
 
30 Maggio 2015
5,319
72
48
50
Milano, Italy
office 365/2016
Miglior risposte
21
#15
@Andrea
Ciao andrea grazie per il tuo suggerimento infatti sono riuscito ad adattare la tua formula alle esigenze di SalsoRico
Cioè in caso di permesso e vuole il numero negativo in negativo
Andrea la tua formula presentava un piccolo grande errore perchè questa formula è stata tradotta male dalla versione d excel in inglese all'Italiano infatti noterai che c'è una virgola (,) anzichè punto e virgola (;)

=TESTO(ASS(B2-A2),"-h:mm:ss")

comunque questa è la formula adattata

=TESTO(ASS(SE(RESTO(B3-A3;1)>8/24;RESTO(B3-A3;1)-8/24;RESTO(B3-A3;1)-8/24));"-hh:mm")

Attenzione!!!
però visto che utilizzi la funzione =TESTO questa formula alla fine mi restituisce un testo per cui quella specie di numero negativo che vedi non può più essere utilizzato per farci altri calcoli sopra infatti vedrai il numero allineato sulla parte sinistra della cella a prova del fatto che non è un numero

 

A

B

C

D

2

22:00​

04:00​

-2

Gerardo

3

22:00​

04:00​

-02:00

Andrea

 

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
13,325
174
63
Como
2011MAC 2016WIN
Miglior risposte
88
#16
  • ges

    ges

Troppo forte la curiosità di sapere il risultato e quindi sono andato adesso a curiosare sul mio progetto le vostre formule.....Sono tutte e due perfettamente funzionanti (quella di GES dopo la piccola correzione di Gerardo). Non voglio approfittare della vostra conoscenza e disponibilità e temo che non sarò MAI in grado di aiutarvi perchè io rispetto a voi sono un IGNORANTONE! Un dubbio mi rimane sulle formule suggerite........Se non inserisco alcun orario in tabella mi esce un bel "-8" Come è possibile evitare questo?
Mi permetto di usare le formula di Gerardo, che mi sembra la più efficace!
Per evitare di avere valori se non inserisci le date usa questa correzione:

=SE(A2*B2=0;"";SE(RESTO(B2-A2;1)*24>8;RESTO(B2-A2;1)*24-8;RESTO(B2-A2;1)*24-8))
 
30 Maggio 2015
5,319
72
48
50
Milano, Italy
office 365/2016
Miglior risposte
21
#17
Se utilizzi la formula senza togliere 8 ore
=SE(B2>=A2;(B2-A2);24-(A2-B2))
dovrebbe funzionare anche a te.
Anche quest'altra formula dovrebbe funzionarti:
=SE(B2>=A2;(B2-A2);(B2+1)-A2)


@ Ges
Ciao e grazie per il tuo grande contributo al forum
infatti come tu hai precisato "con Window la formula funziona......" ma se sottraggo a 8 non funziona più strano.... e comunque non capisco perchè in questo caso MAC è meglio di windows, dovrebbe essere al contrario visto che Excel è un prodotto Microsoft....:253:

Comunque queste tue ultime formule sono molto migliorate rispetto alla prima perchè se non altro quando lavora a cavallo della mezza-notte funziona bene, mentre nella formula precedente no

Credo che in questa discussione abbiamo fatto grossi passi in avanti ma non sono pienamente soddisfatto perchè mi piacerebbe vedere un numero negativo in formato -hh:mm

In attesa che qualcun'altro posso contribuire
Saluti a tutti gli amici del forum
 
30 Maggio 2015
5,319
72
48
50
Milano, Italy
office 365/2016
Miglior risposte
21
#18
=SE(A2*B2=0;"";SE(RESTO(B2-A2;1)*24>8;RESTO(B2-A2;1)*24-8;RESTO(B2-A2;1)*24-8))
Simpatico trucchetto se moltiplichi qualcosa per zero ti da zero....:285:

Comunque sono arrivato ad una conclusione
se il risultato finale saranno numeri che non utilizzeremo per farci sopra altri calcoli possiamo utilizzare questa soluzione che soddisfa anche l'ultima richiesta di SalsoRico

=SE(A2*B2=0;"";TESTO(ASS(SE(RESTO(B2-A2;1)>8/24;RESTO(B2-A2;1)-8/24;RESTO(B2-A2;1)-8/24));"-hh:mm"))
 
Ultima modifica:

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
13,325
174
63
Como
2011MAC 2016WIN
Miglior risposte
88
#19
  • ges

    ges

Simpatico trucchetto se moltiplichi qualcosa per zero ti da zero....:285:
Con tutte queste formule che mi girano per la testa qualche trucchetto l'ho imparato, no??? :57:


@ Ges
.... e comunque non capisco perchè in questo caso MAC è meglio di windows, dovrebbe essere al contrario visto che Excel è un prodotto Microsoft....:253:
...
Che dire, excel 2016 è uscito già da alcuni mesi per MAC e dovrà ancora uscire per Windows!!!:92:
 
Stato
Discussione chiusa ad ulteriori risposte.

Sostieni ForumExcel

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