modifica file excel 2002 conteggio straordinario

[trittico69]

Salve a tutti…qualcuno può darmi una mano a modificare questo programmino?
Vi spiego quello che va modificato.
Intanto inizio con il dirvi che dove lavoro ci sono due tipi di conteggio e si chiamano mese solare che va dal primo all’ultimo del mese e mese contabile che va a settimane e viene deciso annualmente quante settimane devono essere calcolate per i pagamenti delle varie competenze.
Detto questo il programmino conteggia le ore di straordinario in base al mese solare ma dobbiamo fare in modo che conteggi nel mese contabile.
Da tener presente che ogni anno cambiano le settimane del mese contabile quindi una settimana del mese contabile puo capitare nel foglio successivo o precedente.
Lo straordinario viene calcolato settimanalmente e sarebbe tutto quello che supera le 36 ore lavorative e viene messo in AG21/30/39/48/57-AH21/30/39/48/57-AI21/30/39/48/57 di ogni foglio che sono straordinari diurni, notturni e festivi e il totale di queste settimane va in AG31-AH31-AI31 e viene riportato e arrotondato tramite codici in F10-G10-H10.
Per aprire tutte le righe nascoste ciccare su 1 nel foglio “riep”
Potete aiutarmi?
Grazie e un saluto.
http://uploading.com/eb52bdee/SERVIZIO-2013-vuoto-XLS

[Anthony47]

Bentornato.
Ricordo che hai un foglio mica male in quanto a complessita', ma lo guardero' (anche se in questi giorni sono abbastanza tirato); nel frattempo magari qualcun altro vorra' contribuire...

Ciao

[trittico69]

grazie! aspetto!

[wallace&gromit]

non è chiara la domanda: il programma calcola già ora secondo il mese contabile e non quello solare, visto che sulle pagine ci sono i mesi contabili ed i totali sono fatti pagina per pagina.

[Anthony47]

Per evitare equivoci, anche io ho il dubbio di w&g; quindi dovresti integrare la prima richiesta con i chiarimenti in proposito.

Ciao a tutti

[trittico69]

chiedo scusa ho invertito contabile con solare metto la frase corretta
Detto questo il programmino conteggia le ore di straordinario in base al mese contabile ma dobbiamo fare in modo che conteggi nel mese solare.

[Anthony47]

Mi pare che quindi stai chiedendo di correggere i calcoli fatti nelle celle AG31-AH31-AI31, per eliminare eventuali conteggi che si riferiscano a mesi diversi dal corrente e aggiungere i conteggi che invece, vista la particolare organizzazione del calendario di ogni foglio, si trovano sul foglio precedente o successivo (e non sto' a preoccuparmi di cosa fare a Gennaio se il mese contabile cominciasse dopo il 1° gen e a Dicembre se analogamente il mese contabile finisse prima del 31 dic).
Come problema ulteriore, legato pero' all' uso dello strumento, c' e' da ricordarsi che il contenuto del foglio mensile non e' corretto fintanto che il mese di calendario non termina e che le macro che ricalcolano F10-G10-H10 sono da rieseguire.
Metto le mani avanti: in caso di intervento sul foglio tocca a te fare un collaudo decente, vista la complessa articolazione che e' stata adottata per i calcoli.

Ciao

[trittico69]

non dovrebbe essere difficile ma non ci riesco...
in pratica penso che basterebbe modificare le formule in AG31-AH31-AI31 dicendogli che se nel mese successivo e precedente le date nelle colonne "A" corrispondono al mese in questione allora sommare lo straordinario che si trova in AG21/30/39/48/57-AH21/30/39/48/57-AI21/30/39/48/57 corrispondente alla settimana interessata. una formula tipo quella messa in O11 di ogni mese.
ne prendo una a caso e la posto
=SOMMA.SE('FEB '!AZ14:AZ57;MESE(H6);'FEB '!BA14:BA57)+SOMMA.SE('MAR '!AZ14:AZ57;MESE(H6);'MAR '!BA14:BA57)+SOMMA.SE('APR '!AZ14:AZ57;MESE(H6);'APR '!BA14:

[Anthony47]

Vediamo se la seguente ipotesi e' percorribile:
in AG1 di FEB inserisci la formula

Codice: Seleziona tutto

=SOMMA(SE($A$14:$A$30<>"";SE(MESE($A$14:$A$30)<MESE($A$26);AG$14:AG$30;0);0))

In AG2 invece metti

Codice: Seleziona tutto

=SOMMA(SE($A$32:$A$57<>"";SE(MESE($A$32:$A$57)>MESE($A$26);AG$32:AG$57;0);0))

Ambedue da confermare con Contr-Maiusc-Enter, non il solo Enter; quindi copia AG1:AG2 in AH1:AI1
In questo modo avrai calcolato in riga1 quali sono gli straordinari che appartengono al mese di Gen, e in riga 2 quelli che appartengono a Mar. Questi calcoli sono fatti solo sulle prime 2 settimane ($A$14:$A$30, per il mese "prima") e sulle ultime 3 ($A$32:$A$57, per il mese "dopo")
Ricopia le formule su ognuno dei fogli mensili successivi, eccetto Dicembre.
Se e' vero che ora tu calcoli in AG31-AH31-AI31 gli straordinari del singolo Foglio, allora alle formule contenute in queste celle dovai sottrarre /aggiungere questi calcoli addizionale; cioe', ad esempio in AG31:
-ora hai =SE(AE22<0;0;AE22)
-dovrai variare in =SE(AE22<0;0;AE22)-AG1+'MAR '!AG2

Secondo me a questo punto i risultati dovrebbero essere corretti anche nei calcoli successivi.

Rimane da risolvere Gennaio e Dicembre.
In gennaio il mese precedente e' Dicembre, quindi non possiamo usare nella formula di AG1 il confronto sul mese [SE(MESE($A$14:$A$30)<MESE($A$26)] ma si usera' l' anno [quindi usare SE(ANNO($A$14:$A$30)<ANNO($A$26)]
La formula in AG2 e' invece quella standard
In dicembre la formula in AG1 e' quella standard e quella in AG2 usera' il calcolo sull' anno [SE(ANNO($A$32:$A$57)>ANNO($A$26)]

Spero di averti confuso abbastanza...

Fai sapere cosa ne pensi.

Ciao

[trittico69]

e in AH31-AI31 cosa metto?
ripeto in AG31 vengono sommati gli straordinari di prima fascia, come puoi notare dalla scritta in AG13
in AH31 seconda fascia
in AI31 terza fascia

[Anthony47]

In AG1:AI1 e AG2:AI2 hai calcolato gli orari che non appartengono alla contabilita' del singolo Foglio; in AG31/AH31/AI31 devi ricalcolare il valore del singolo mese, quanto scritto per AG31 era l' esempio da replicare nella altre due celle.

Ora pero' mi rendo conto che il ricalcolo che ti ho dato come esempio per AG31 E' SBAGLIATO:
-vanno SOTTRATTI ambedue i calcoli fatti in AG1 e AG2
-vanno sommati AG2 del foglio "precedente" e AG1 del foglio "successivo"
Quindi, nell' esempio di AG31 di FEB:
=SE(AE22<0;0;AE22)-AG1-AG2+'GEN '!AG2'+'MAR '!AG1

Ciao

[trittico69]

scusa ma non ti seguo... forse ti chiedo troppo ma potresti farmi la modifica e allegarmi il file modificato?

[Anthony47]

http://rapidshare.com/files/32349772/by ... B30327.xls
Ho inserito le formule aggiuntive (AG1:AI2) solo in GEN, FEB, MAR, APR; basta copiarle da APR nei mesi successivi (salvo DIC, dove ci sono formule diverse).
In FEB ho anche modificato le formule in AG31:AI31; vanno modificate analogamente sui fogli MAR-NOV. In GEN le modifiche possibili sono parziali, perche' posso sottrarre quanto calcolato in riga1 e riga2, posso aggiungere quanto calcolato su FEB riga1, ma dovrei anche sommare calcoli provenienti da Dicembre 2012. Idem su DIC, dove dovrei sommare un calcolo fatto sul Gennaio 2014.

Spero che sia piu' chiaro...

[trittico69]

sembrerebbe non funzionare...
prendiamo il mese di febbraio, i primi 4 giorni appartengono a gennaio ma lo straordinario me lo carica a febbraio e non a gennaio.

[Anthony47]

Purtroppo io non so come funziona quel foglio e quindi come collaudare le formule. Ho assunto che i calcoli dello straordinario si consolidano nelle colonne AE:AI, e sui dati di quelle colonne ho lavorato.
Ad esempio nel file che ho pubblicato ho gia' inserito degli orari a caso, es Lun 28/01, E=9:00 U=21:00; idem per il 29/01.
Non so quale dovrebbe essere il risultato corretto in F10/G10/H10 (con le modifiche fa 14 /12 /0); ma se estendo l' orario, ad esempio facendo 9:00-22:00 i valori in F10/G10/H10 non cambiano, mentre viene incrementato di 1h il dato in AG1, quello che va a incrementare il risultato sul foglio GEN.
Immagino che il foglio non calcola solo le orea straordinarie, che vanno ricalcolate col metodo "mese solare", ma anche altre cose che debbano rimanere calcolate su "mese contabile" (le date visualizzate nei vari fogli del file); quindi non oso toccare le celle a monte delle colonne AG:AI.
Aggiungo che quanto richiesto (ricalcolo sul mese solare) secondo me mette in crisi anche la definizione data di straordinario ("Lo straordinario viene calcolato settimanalmente e sarebbe tutto quello che supera le 36 ore lavorative") visto che la settimana puo' capitare a cavallo di due mesi solari.
E, riguardando le formule di calcolo giornaliero dello straordinario), mi pare che stia proprio qui l' errore di quanto ti ho proposto: ad esempio con l' orario 9:00-21:00 nei gg Lun 28/01 e Mar 29/01 non risulta nessun straordinario, perche' viene calcolato solo al superamento delle 36h, cosa che avviene (con gli orari che ho segnato sul mio file) solo al 31/gen per 1 sola ora mentre il grosso viene contabilizzato nei gg 1/feb e 2/feb.
Quindi una domanda cruciale che secondo me condiziona la fattibilita' logica di realizzare quanto richiesto:
-con gli oari che ho segnato e che qui sotto riepilogo, quali sarebbero gli straordinari che andrebbero calcolati sul mese di Gennaio e quali sul mese di Febbraio?

Codice: Seleziona tutto

  Data           E    U
lun 28-01-13 * 9:00 21:00
mar 29-01-13 * 9:00 21:00
mer 30-01-13 * 9:00 17:30
gio 31-01-13 * 8:30 15:00
ven 01-02-13 * 9:00 17:30
sab 02-02-13 * 9:00 15:30


Ciao

[trittico69]

lo straordinario del mese di gennaio sono i giorni 28-29-30-31 mentre febbraio 1-2..
ma mi sto rendendo conto che mi sa si fa prima a fare tutto d'accapo...

[Anthony47]

Sull' ipotesi di rifare tutto d'accapo non mi trovi contrario, ho sempre ritenuto troppo contorti quei calcoli.
Se effettivamente pensate di rifare tutto, io non farei piu' una rappresentazione mensile, ma una sequenziale (nominativo, data, orari, calcoli giornalieri /settimanali); poi farei un altro foglio di "presentazione dei risultati": scelto il mese e il nominativo si estraggono solo le informazioni relative a quella scelta e si organizza il risultato come serve.

Per quanto riguarda la domanda che ti avevo fatto, mi aspettavo una risposta in "ore"; pero' la regola corrente dello straordinario ("tutto quello che supera le 36 ore lavorative" settimanali) ti impedisce (secondo me) di dire quanto straordinario e' fatto nei primi 4 giorni della settimana e quanto nei 2 gorni successivi: al massimo potresti dire che, avendo lavorato 37 ore e' stata fatta un' ora di straordinario nei primi 4 giorni (relativi a Gennaio) e nessuno straordinario nei due giorni successivi. Avendo pero' lavorato complessivamente 51 ore tale risultato e' palesemente sballato; quindi devi definire una regola diversa per il calcolo (ad esempio un calcolo giornaliero e non settimanale), oppure mantenere il calcolo a settimana e decidere una regola su come suddividere giornalmente lo straordinario settimanale, oppure... oppure...
Cose che pero' dovrebbe decidere l' Amministrazione per cui lavori.
Ma fatta questa decisione non sarebbe impossibile intervenire sui calcoli correnti, ad esempio in attesa di avere uno strumento piu' aggiornato.

Ciao

[trittico69]

mi è venuta una mezza idea prima di rifare tutto...se nel foglio riep in I2:I13 mettessimo i numeri del giorno che devono comparire nei fogli dei mesi e nella colonna "j" tutti zero invece del numero delle settimane e nelle colonne" K-L" indicherebbe la data di inizio mese e fine mese in base a come io metterò i giorni nella colonna "I" che dici potrebbe andare?
se si mi fai una formula che faccia ciò?
grazie!

[Anthony47]

Per prima cosa devi definire una nuova regola per il calcolo degli straordinari, perche' come e' adesso (straordinario e' "quello che supera le 36 ore lavorative" settimanali) ti impedisce di gestire la settimana a cavallo del mese.

[trittico69]

ok! ti allego un altro programmino con conteggio giornaliero ma dobbiamo modificare alcune cose, facciamo una cosa alla volta...
vorrei far si, in automatico, che nel momento che io cambio l'anno nel foglio "stat" in "N1"cambino le date nella colona "A" dal primo all'ultimo del mese.
facciamo solo gennaio mi dici come hai fatto e gli altri mesi li faccio io.
http://dfiles.eu/files/69tb8q918

(i dati presenti nel file sono "verosimili" ma non veritieri)