conversione numeri con virgola in interi (ore)

[THECAPITAN]

Ciao ragazzi! Ho un foglio di lavoro http://www.freeimagehosting.net/g4h1l al cui interno sono presenti 3 colonne:
-colonna ORE PREVISTE
-colonne ORE PRESENZE
-colonne ORE ASSENZE (differenza tra ore previste e ore presenze)
tutte in formato generale.
Inizialmente le ore da indicare nelle colonne dovevano contenere numeri interi e di conseguenza anche il totale delle ore (presenze e assenze) era un intero. Ora, però, in alcuni casi vengono inseriti numeri con la virgola (esempio 3,30 per indicare 3 ore e 30 minuti oppure 0,20 per indicare 20 minuti). Ovviamente così facendo la colonna ASSENZE non restituisce correttamente la differenza (perchè 4-3.30=0,70 anche se in realtà sarebbe 0,30) Potrei cambiare il formato della cella per risolvere, ma questo significherebbe cambiare un pò tutti i formati delle celle essendo il formato di tipo generale ma soprattutto per modificare dati già inseriti.
Indipendentemente da questo, quello che mi interessa maggiormente è il totale delle ore complessive (presenze e assenze). Infatti, se il totale delle ore previste è 80(cella d57)nel totale presenze compare il valore 71,90 (72 ore e 30 minuti) e in quello delle assenze 6,90 cioè 7 ore e 30 minuti.
Quello che vorrei nelle celle F57 e G57 il valore delle ore già convertito. E' possibile ottenere una formula che sia valida in tutti i casi? Grazie

[wallace&gromit]

penso che la soluzione più semplice per non dovere cambiare tutti i formati è inserire le ore con la virgola in modo corretto:
3 ore e 1/2 si scriverà 3,5; 3 ore e 1/4 3,25 ecc..
oppure anche in sesti: per 3 ore e 20 si inserirà 20/6 ed il valore calcolato sarà 3,333333 (cioè 3 e 2/6).
Può funzionare?

[THECAPITAN]

ti ringrazio, ma 3 ore e 30 minuti risultano 3, 30 quindi non 3, 50 perchè se le ore previste sono 4 risulteranno 3,30 (3 ore e 30 minuti) di presenza e 0,30 minuti di assenza. Tralasciando l'inserimento, quello che mi interessa sono i totali (ore presenze e assenze). La somma di entrambi deve restituirmi il totale delle ore previste. Aspetto altre soluzioni

[ricky53]

Ciao,
ipotizziamo che:

in "A2" ci sia 8,0 in formato "Generale"

in "B2" ci sia 7,2 in formato "Generale"

in C2 scrivi

Codice: Seleziona tutto

=ORARIO(ORA(A3/24); MINUTO(A3/24)*100/60;  SECONDO(A3/24)*100/60) - ORARIO(ORA(B3/24); MINUTO(B3/24)*100/60;  SECONDO(B3/24)*100/60)

ed otterrai (impostando il formato di "C2" a "h:mm") 0:40

[THECAPITAN]

Ok, ma intendevi A2 e B2 nella formula.
In questo modo riesco a risolvere il problema della differenza (vedi http://www.freeimagehosting.net/3ljvj) ma la somma tra presenze (16,75) e assenze (2.45.00) ovviamente non mi restituisce come verifica il totale delle ore previste cioè 20.. anche se in realtà è così (16,00+1.15+2.45). Come risolvo?

[ricky53]

Ciao,
mi sono accorto adesso che nella formula che ti ho scritto ho utilizzato
A3 e B3 che sono riferimenti di celle errati
le celle esatte sono
A2 e B2.


Volendo, per risolvere definitivamente ed avere gli orari in "h:mm" effettivi, puoi utilizzare tre colonne di appoggio dove scrivere i dati convertiti utilizzando le formule che ho scritto io.
La prima parte della formula (quella prima del segno meno) per la colonna "A" e la seconda parte per la colonna "B" (quella dopo il segno meno) e la formula complessiva per la differenza ... ovviamente vanno modificati i riferimenti di cella.

Prova a fare da solo e ... sono QUI.

[wallace&gromit]

l'errore non è nella soluzione di Ricky (quando mai ), ma nel tuo valore 16,75,
che in relatà non corrisponde a niente, perchè è come dire 16 ore e 75 minuti, cioè 17 ore e 15.
Ottengo il risultato voluto con questa semplice e agile formula matriciale (devi confermare premendo Ctrl + Maiusc + Enter invece di solo enter :

Codice: Seleziona tutto

=SOMMA(INT(B2:B5))+INT(SOMMA(RESTO(B2:B5;1))*100/60)+RESTO(SOMMA(RESTO(B2:B5;1));1)-(INT(SOMMA(RESTO(B2:B5;1))*100/60))/10*6

in pratica somma in primo luogo tutte le parti intere delle tue ore, poi calcola quante ore intere si ricavano da ciò che c'è dopo la virgola, e infine aggiunge i minuti rimanenti.

Probabilmente si può scrivere la formula in maniera più concisa, ma ancora meglio sarebbe rivedere tutto e scrivere ore e minuti in formato ore e minuti, o no?

[ricky53]

Ciao W&G,

TROPPO BUONO !!!

[Anthony47]

Leggi i suggerimenti di Ricky e soprattutto di w&g, sopra.
Comunque se hai gli orari espressi in quel formato "molto personale" (e quindi incline a interpretazioni altrettanto personalizzate da parte di chi compila le celle) non puoi usare il calcolo decimale per fare le somme (che invece e' legittimo se le celle contengono Orari in formato "Orario" e non in formato "numero decimale").
Con il layout che si desume dall' immagine, quindi in B6 non puoi usare =somma(B2:B5), ma userai qualcosa come

Codice: Seleziona tutto

=SOMMA(ORARIO(ORA(B2:B5/24);MINUTO(B2:B5/24)*100/60;0))


Da confermare con Contr-Maiusc-Enter; e' una variante della formula w&g.

Aggiungo che nell' esempio specifico, usando il metodo di Ricky anche per fare la somma tra B6 e C6 (quindi usando la formula =ORARIO(ORA(B6/24);MINUTO(B6/24)*100/60;0)+C6) otterresti il risultato corretto (20h00m), ma solo perche' la somma dei decimali non ha raggiunto "100".

Ciao