[EXCEL 2007] suddivisione ore

[fabbio84]

Ciao a tutti, ho letto nel forum qualche discussione simile ma non mi è stata di aiuto quindi ho deciso di chiedere il vostro aiuto.
Sostanzialmente vorrei fare in modo che date in ingresso un orario di inizio ed uno di fine lavoro la tabella mi divida su due colonne ore e minuti per lavoro diurno e lavoro notturno.
ES con suddivisione diurno 6-22 notturno 22-6

Codice: Seleziona tutto

inizio lavoro     fine lavoro        lavoro diurno     lavoro notturno
18.03               23.45                3:57                 1:45


Come posso fare? Scorperto questo poi potrò anche aggiungere eventuali voci in più quali lo straordinario se il totale delle ore supera una certa soglia, ma vedremo poi, intanto GRAZIE a chi mi potrà aiutare

[Flash30005]

Il tuo grosso problema non sono le ore notturne o diurne ma la formattazione delle ore (inizio e fine) in formato testo
ovvero prima usi il punto come separatore di ore e minuti poi i due punti
Con una macro si potrebbe realizzare qualcosa (come credo con complicate formule) ma decidi tu cosa preferiresti

ciao

[fabbio84]

si scusa il punto e i due punti sono una svista mia di battitura :-P
bè io sinceramente come si usano le macro devo capirlo ancora bene.
Cosa consigli Flash?
Per me una vale l'altra, basta che i conti mi tornino, ne va del cash a fine mese :-D

[Flash30005]

Manda un foglio con qualche dato
le macro puntano su dei range e se ne creo una con range non corrispondente al tuo dopo dovremmo correggere
per l'invio del file usa la procedura posta nella testata di questa sezione del forum

ciao

[Anthony47]

Vedi il messaggio di Flash, sopra.
Per questi calcoli avevo tempo fa sviluppato la funzione modulare XSTRA, vedi qui: viewtopic.php?f=26&t=69705
E' programmabile e penso faccia quello che chiedi, tipo calcolo di festivi, notturni, prefestivi, con qualsiasi calendario.
Prova a usarla...

Ciao

[fabbio84]

In tutta sincerità signori non ho capito niente
Io pensavo fosse facile, prendi la funzione, copiala nella cella e taaac. Mi trovo in seria difficoltà

[Anthony47]

Non esiste una funzione "Orario Notturno", e se chiedi a 10 lavoratori quale e' l' orario "notturno" avrai 4 risposte diverse.
Hai quindi bisogno di personalizzare le tue formule, oppure di usare una funzione programmabile come era la XSTRA.
Ammetto che Xstra ha obiettivi piu' ampi (notturno, festivi, notturni-festivi, straordinario per tipo, etc), quindi ha una complessita'superiore a quanto si potrebbe pensare mirando al solo tuo quesito; ma personalmente preferisco impegnarmi una volta per tanti quesiti piuttosto che tante volte quesito per quesito; se dici dove ti sei arenato ti supportero' volentieri.

Ciao

[fabbio84]

Ma meglio che faccia quei calcoli la formula, tanto mi servono anche quelli, comunque mi sono arenato dopo aver aperto l'editor ed incollato la formula dell'XSTRA, poi torno su excel e che devo fare? creare tabelle coi dati o cosa?

[Anthony47]

Stava finendo nel dimenticatoio...
Parti da viewtopic.php?f=26&t=69705#p402581
Per il tuo foglio presenze usa un layout come questo:


Uploaded with ImageShack.us
(Colonne A:F)
Le celle con lo stesso colore devono essere contigue.

In Foglio2 ho creato la tabella di "definizione orari" (A1:I11, nell' esempio) e la tabella dei giorni festivi (esempio in col N). L' origine della tabella orari (A1) l' ho nominata "DefOr1", l' intera tabella dei festivi (area azzurra), l' ho nominata "festivi": usero' questi nomi nelle formule successive.


Uploaded with ImageShack.us

Nella col A delle presenza (la data) ho usato la formattazione condizionale per evidenziare in grigio i Sab-Dom, usando la clausola "la formula è" e come formula

Codice: Seleziona tutto

=GIORNO.SETTIMANA(A3;2)>5

In B3 e sottostanti sono evidenziati i festivi con la formula

Codice: Seleziona tutto

=SE(VAL.ERRORE(CERCA.VERT(A3;Festivi;1;0));0;1)

(da copiare verso il basso)

Ho quindi copiato la funzione XSTRA tra le macro del foglio;
-da excel, Alt-F11 per aprire l' editor delle macro
-Menu /Inserisci /Modulo
-copia il codice dal link che ho dato prima e incollalo nel frame di dx
-inserisci la modifica descritta nel messaggio datato 07/04/09
-torna al foglio orario

A questo punto calcoli gli orari che ti interessano usando la formula

Codice: Seleziona tutto

=XSTRA(CellaData; PrimaCellaTimbratura;IndirizzoTabDefinizioneOrari;TipoOrario)

Avendo scritto in H2:L2 le sequenza 0, 1, 2, 3, 4 ho usato in H3 la formula

Codice: Seleziona tutto

=Xstra($A3;$C3;DefOr1;H$2)

L' ho copiata "in lungo e in largo" ottenendo i risultati mostrati nella prima figura.
(le celle poco visibili contengono 00h00m e ho usato la formattazione condizionale per nasconderli)

Ricorda che TipoOrario=0 calcola tutto l' orario corrispondente alle timbrature, mentre un valore diverso (ma <=16) identifica uno straordinario identificato con quelnumero nella tabella di Definizione Orari.
Nell' esempio di tabella (seconda immagine), ho usato solo 4 tipi (ne puoi definire fino a 16); nell' esempio inoltre ho usato solo 6 fasce orarie (dalle 00:00, dalle 6:00, dalle 22:00, dalle 00:00 del "giorno dopo", dalle 6:00 del giorno dopo, dalle 22:00 del giorno dopo), ma ne puoi impostare a piacimento, con la precauzione che esse devono terminare con una colonna vuota.
E' opportuno che ogni riga copra dalle ore 0:00 alle 24:00 del giorno successivo, per poter gestire timbrature quali Entrata=23:00 / Uscita=10:30 (orario da negrieri).
Ogni riga corrisponde a una tipologia di giornata, come indicato in colonna K; devono essere presenti 10 righe di dati, oltre le intestazioni.
La seconda colonna (B, nell' esempio) contiene l' orario standard di lavoro per quella giornata, il calcolo del "tipo" e' effettuato sugli orari successivi alle ore standard. Ne consegue che se si vuol suddividere per "tipo" tutto l' orario lavorato bisogna /basta inserire 0:00 come orario standard.

Se hai solo 2 timbrature userai solo le prime 2 colonne E(ntrata)-U(scita), lasciando vuote le seconde due.

Prova anche tu e fai sapere dove arrivi; e se non risolvi spiega dove invece ti sei arenato.

Ciao

[fabbio84]

grazie Anthony proverò appena trovo del tempo, come vedi non sono stato molto presente :-(
Grazie mille ancora

[marco089]

Salve mi sono appena inscritto a questo forum molto interessante..., scrivo per la funzione xtras, che mi servirebbe e che da quanto ho letto è eccezionale se riuscirò a farla funzionare.
Ho copiato l'esempio che ho trovato qui passo passo su un foglio excel ma la funzione non ne vuol sapere di funzionare mi serve aiuto, grazie infinite.
Uso excel 2010.
PS vorrei inviare il file ma non so come fare

[marco089]

Salve mi sono appena inscritto a questo forum molto interessante..., scrivo per la funzione xtras, che mi servirebbe e che da quanto ho letto è eccezionale se riuscirò a farla funzionare.
Ho copiato l'esempio che ho trovato qui passo passo su un foglio excel ma la funzione non ne vuol sapere di funzionare mi serve aiuto, grazie infinite.
Uso excel 2010.
PS vorrei inviare il file ma non so come fare

Sono riuscito a caricare il file ecco il link
http://www.ddlstorage.com/vya9misilbim/ ... d.xlsm.htm
Grazie x le risposte.

[Anthony47]

Ciao marco089, benvenuto nel forum.
Nel tuo file ci sono alcuni errori:
-la tabella "definizione orari" si trova in Foglio2!A1 (non H1, come hai scritto nella formula)
-la cella di origine (quindi Foglio2!A1) deve contenere la scritta DefOrari mentre ora contiene DefOrar
-la Function Xstra che hai usato non corrisponde all' ultimissimo codice: parti da quello descritto qui: viewtopic.php?f=26&t=69705#p402581 e poi inserisci la correzione citata qui: viewtopic.php?f=26&t=69705&start=20#p451870

Dopo queste considerazioni /correzioni, tenendo presente che nella tabella DefOrari sono presenti valori tra 1 e 4:
-in H2:O2 creati una serie contenente 0, 1, 2, ... 7
-in H3 metti la formula

Codice: Seleziona tutto

=Xstra($A3;$C3;Foglio2!$A$1;H$2)

-copia H3 verso destra, poi copia H3:O3 verso il basso

Vedrai per ogni tipo definito su riga 2 le ore di straordinario corrispondenti a quella tipologia; il tipo"0" corrisponde alle ora compessivamente lavorate.

Ciao

[marco089]

Grazie davvero per la risposta veloce e ancora complimenti per la funzione xstra che ripeto è davvero geniale, erano anni che non riuscivo a separare per bene gli straordinari, avevo provato con le funzioni standard e con una miriade di concatenamenti della funzione "se" è comunque il risultato non era del tutto soddisfacente.
Detto questo ho fatto come hai detto sopra e funziona tutto, ho solo qualche dubbio ancora e cioè:
1) nel file ore_straord (di cui sotto il link) nel foglio 2 in DefOrari ho erroneamente cancellato l'ultima colonna che ho letto + volte doveva invece essere vuota, con mia sorpresa però sembra funzionare tutto come prima, volevo sapere se alla luce delle ultime modifiche del codice macro ciò è irrilevante;
2) la funzione Xstra presenta 5 valori in ingresso, l'ultimo è "TotH" che non sono riuscito a capire a cosa serve, ho messo dei valori interi 1, 2 e in formato orario "xx.xx" etc ma il risultato non cambia.
Grazie ancora per la risposta.
P.S. se puoi controllare il codice della funziona Xstra - dovrebbe essere quello con le ultime modifiche - mi tranquillizzi.
http://www.ddlstorage.com/nglhzuxgf00v/ ... d.xlsm.htm

[Anthony47]

Il parametro "TotH" e' opzionale, e serve per attivare una prestazione non documentata:
-la funzione e' definita "volatile", quindi si ricalcola a qualsiasi ricalcolo nel foglio di lavoro; in presenza di tantissime formule con xstra questo potrebbe rallentare i tempi di reazione del foglio.
-in questo caso si puo' eliminare nel codice l' istruzione Application.Volatile e richiamare la funzione avendo inserito in ultima posizione una cella in cui viene calcolato il totale delle ore lavorate; in questo modo le formule con XSTRA si ricalcoleranno solo quando cambia il totale ore lavorate, cioe' quando vengono inserite o cambiate gli orari di timbratura; ma soprattutto si ricalcolera' 1 formula alla volta, non tutte insieme.

La colonna vuota alla destra della tabella DefOrari serve a garantire l' identificazione della fine colonna; avendo comunque la tabella una copertura che va fino a 24-48 ore a partire dalla timbratura che si va a esaminare direi che si coprira' al 99.99% qualsiasi orario, per "prolungato" che possa essere. Comunque fare un Menu /Inserisci /Colonne non e' cosi' difficile...

Il codice usato sembra il piu' aggiornato.

Ciao

[sonork]

Ciao a tutti e ciao Anthony,

sono Fabio e sono un nuovo iscritto; sono capitato googlando un po' a dx e sx per cercare una soluzione al mio "problema".
La funzione Xstra dovrebbe far al caso mio e ho provato a creare il file con le indicazioni fornite in questo topic.
Il file che ho creato l'ho uploadato qui: http://www.filedropper.com/provaxstra
Non mi funziona o meglio mi funzionano solo le formule corrispondenti alla tipologia=0 (il totale) presenti nella colonna H dello sheet1.

Riuscite a capire in cosa sbaglio? la formattazione di qualche cella?
Io utilizzo Excel2010 nella versione Inglese; se servono altre info chiedete pure

Grazie
Fabio

[Anthony47]

Ciao sonork, benvenuto nel forum.
Nella tabella DefOrari, le celle C1:J1 e B2:B11 devono contenere degli orari, mentre nella tua tabella ho trovato del "Testo"
Corretti questi campi il comportamento e' stato come da attese.

Ciao

[sonork]

Grazie Anthony; dopo un po' ho capito cosa intendevi. Excel mi mostrava orari e anche dal formato celle vedevo "Time".
Entrando però in ognuna delle celle che mi hai indicato e dando "invio" le cose sono iniziate a cambiare (anche visivamente) per gli orari >= di 24:00.
Qui mi verrebbe da farti la domanda che fanno i bimbi: perchè? perchè solo entrando nella cella e dando invio ho visto i formati cambiare?

Tornando a noi, ho selezionato le celle che mi hai indicato e le ho assegnato il formato time.
Il file però ancora non mi funziona e credo che dipenda dal fatto che per i formati orari dalle 24:00 in poi il mio excel, attualmente, riparte da 00:00.
Per capirci nell'immagine puoi vedere proprietà e valore della cella F1 dopo aver scritto "24:00" e aver dato Invio.


subirimagenes

Dove sbaglio? sicuramente dipende da qualche impostazione del SO o di excel.

ps: era per questo che forse avevo messo tra vari tentativi il formato testo, per poter avere nelle celle 24:00, 48:00 etc

[sonork]

Scusa Anthony, mi rispondo da solo ma magari è utile per altri utenti.
Dovrei aver risolto usando il formato celle "[h]:mm;@" però continua a mostrami nella cella "01/01/1900 00:00:00"
E' corretto?

[Anthony47]

Se cambi il formato di una cella ma non ci scrivi niente, perdura il precedente contenuto (quindi nel tuo caso un "testo"); editando il contenuto il nuovo input segue la formattazione impostata sulla cella.

Il formato "[h]:mm;@" che hai usato va bene (nei messaggi in cui e' descritta la XSTRA si suggerisce "[h]:mm"); quindi nella cella vedrai es 24:00 e nella barra della formula vedrai 1/1/1900 00:00:00

Ciao