formattazione condizionale di più' valori e celle

[diego68]

Buongiorno a tutti sono nuovo ed ho un problema con la formattazione di un foglio che sto creando per gli orari dei turni dipendenti
Nel primo riquadro che ho chiamato Riquadro A scrivo gli orari dei turni.Ho messo una formattazione condizionale che in base alla data se e' festivo la cella si colora di giallo, se e' feriale si colora di verde ma vorrei anche che se per esempio scrivessi ferie o malattia o riposo si colorasse di rosso e non ci riesco.Sarebbe bello che se fosse ferie si colorasse di grigio per esempio e se fosse malattia di rosso e riposo di blu (ad esempio).
Secondo quesito.
Nel riquadro B per ciascun dipendente ho creato due colonne la prima fa il calcolo delle ore che nella rispettiva giornata hanno lavorato. Il problema e' che se scrivo per esempio ferie o riposo o malattia mi da errore e vorrei che invece calcolasse 0 e si colorasse con il rispettivo colore (es per malattia rosso etc.) perché' al termine di ciascuna colonna ho fatto la somma delle ore lavorate e quindi anche qui mi da errore se dovessi scrivere per esempio ferie. Se metto tutti numeri ovviamente non mi da errore.
Terzo ed ultimo quesito
Nel riquadro C sarebbero le presenze da riportare automaticamente in base a quanto digitato a partire dal riquadro A e che mi scrivesse per ogni giorni il totale ore oppure se e' in malattia o ferie che scrivesse in automatico ferie
Alcune celle sono formattate perche' ho fatto delle prove ma non riesco a mettere insieme il tutto
qualcuno mi aiuta?
grazie

[diego68]

scusate mi sono dimenticato di dire che il foglio che ho modificato e' nella pagina di dicembre

[gioh66]

Ciao per quanto riguarda il riquadro A mi sa che sei limitato da openoffice che non prevede più di tre opzioni di formattazione condizionale sullo stesso range di celle; dovresti passare a libreoffice che non ha limiti.
Per il riquadro B prova a modificare la formula in B41 così

Codice: Seleziona tutto

=SE(VAL.ERRORE((C6<B6)+C6-B6);B6;(C6<B6)+C6-B6)

e tira in basso. E in B81

Codice: Seleziona tutto

=SE($AO6="feriale";B41;SE(O(B41="malattia";B41="ferie");"ferie";"RIPOSO"))

sempre da tirare in basso.
Ps.: se ti accontenti di avere la cella con sfondo rosso fai così: seleziona l'area da W36 a B6 in modo da avere la cella attiva su quest'ultima. Poi apri formato>formattazione condizionale e come regola metti

Codice: Seleziona tutto

O(B6="ferie";B6="malattia";B6="riposo")

e crei uno stile con sfondo rosso (se già non l'hai fatto)

[diego68]

grazie tanto per il tuo aiuto il riquadro B funziona ora e mi calcola il totale giusto alla fine della colonna
per quanto riguarda invece il riquadro C pertanto mi pare di capire che si applica la stessa formula perché' in sostanza ricopia le celle con il valore totale oppure con la scritta ferie o malattia etc etc.
Se usassi libre office che mi da più' formattazioni condizionali per colorare le celle come dicevo prima che formula uso?
grazie

[diego68]

scusa mi sono sbagliato per il riquadro c.
Vorrei che i festivi mi scrivesse sempre riposo come nella formula che ho gia' scritto ma a questo punto la formula dovrebbe essere che se e' festivo scriva sempre e solo riposo mentre nei feriali mi riportasse il totale ore lavorate o eventuali riposi extra o malattie ferie etc.etc se nella casella corrispondente ho scritto malattia ferie etc..
Grazie

[gioh66]

prova a modificare così

Codice: Seleziona tutto

=SE(O(GIORNO.SETTIMANA($A81;2)>5;VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0)));"RIPOSO";B41)

sperando di aver ben interpretato, se il fiorno è festivo o una festività ti da "RIPOSO" altrimenti copia il dato della cella B41.

[diego68]

grazie sei molto preparato la formula e' corretta hai interpretato benissimo
posso fare una altra richiesta?
a questo punto invece vorrei fare su una altra colonna invece solo il totale delle ore che sono state fatte nei giorni festivi, se ci sono ovviamente.
in sostanza vorrei solo un totale di tutta la colonna ad esempio B41-B71 sia delle ore fatte durante i festivi che solo quelle fatte durante i feriale ma che siano sopra le 8 ore per conteggiare il totale ore straordinari per intenderci.per esempio se sabato faccio 4 ore mi conteggi 4 ore e se lunedì faccio 9 ore me ne conteggi solo 1
grazie

[diego68]

scusa mi ero dimenticato di chiedere una cosa per la formula che mi hai dato prima ovvero questa:
=SE(O(GIORNO.SETTIMANA($A81;2)>5;VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0)));"RIPOSO";B41)
e' possibile quindi riportare come numero massimo di ore il numero 8? ovvero se anche ne faccio 9 me ne riporta sempre e solo massimo 8 se ne faccio 6 me ne riporta solo 6, se ne faccio 5 deve riportarmene 5 etc etc..
perche' le ore straordinarie le voglio calcolare a parte come ho scritto nell'altra risposta.
grazie

[gioh66]

scusa mi ero dimenticato di chiedere una cosa per la formula che mi hai dato prima ovvero questa:
=SE(O(GIORNO.SETTIMANA($A81;2)>5;VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0)));"RIPOSO";B41)
e' possibile quindi riportare come numero massimo di ore il numero 8? ovvero se anche ne faccio 9 me ne riporta sempre e solo massimo 8 se ne faccio 6 me ne riporta solo 6, se ne faccio 5 deve riportarmene 5 etc etc..
perche' le ore straordinarie le voglio calcolare a parte come ho scritto nell'altra risposta.
grazie

Prova a modificare la formula così

Codice: Seleziona tutto

=SE(O(GIORNO.SETTIMANA($A81;2)>5;VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0)));"RIPOSO";SE(B41>1/3;1/3;B41))

trascina in basso e/o inserisci nelle celle di interesse e ridai il formato HH.MM, in quanto la dove l'orario supera le 8 ore il dato ti viene mostrato in formato decimale.

[diego68]

si funziona
mentre per l'altra formula dovrebbe essere l'ultima che mi manca ovvero come ho scritto prima:
a questo punto invece vorrei fare su una altra colonna invece solo il totale delle ore che sono state fatte nei giorni festivi, se ci sono ovviamente.
in sostanza vorrei solo un totale di tutta la colonna ad esempio B41-B71 sia delle ore fatte durante i festivi che solo quelle fatte durante i feriale ma che siano sopra le 8 ore per conteggiare il totale ore straordinari per intenderci.per esempio se sabato faccio 4 ore mi conteggi 4 ore e se lunedì faccio 9 ore me ne conteggi solo 1
grazie

[diego68]

aspetta nelle caselle b6 se scrivo malattia o ferie non me lo riposta nel riquadro C...ma solo nel riquadro B

[diego68]

ricapitolando la formula che mi hai scritto va bene con le ore ma se nel riquadro A scrivo per esempio ferie o malattia nel riquadro C mi riporta sempre 8 e non la parola ferie o malattia.
quindi la formula dovrebbe riportare sia che i festivi mi lascia sempre scritto festivi e che le ore siano massimo 8 e che se scrivo ferie o malattia o riposo, per eventuali recuperi di riposo nello schema del riquadro C mi riportasse anche la dicitura ferie, malattia o riposo invece che il numero 8
non so se sono riuscito a spiegarmi bene.

[gioh66]

Ok! Prova così

Codice: Seleziona tutto

=SE(E(O(GIORNO.SETTIMANA($A81;2)>5;VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0)));B41="malattia");"malattia";SE(E(O(GIORNO.SETTIMANA($A81;2)>5;VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0)));B41="ferie");"ferie";SE(O(GIORNO.SETTIMANA($A81;2)>5;VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0)));"RIPOSO";SE(B41>1/3;1/3;B41))))

ho aggiunto che se A81 è un giorno festivo o festività e in B41 c'è scritto "ferie" o "malattia" copia questi valori.

[diego68]

ti riallego il file che sto modificando ma non funziona continua a scrivere 8

[gioh66]

Adesso (spero ) dovrebbe andare

Codice: Seleziona tutto

=SE(E(O(GIORNO.SETTIMANA($A81;2)>5;VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0)));B41="malattia");"malattia";SE(E(O(GIORNO.SETTIMANA($A81;2)>5;VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0)));B41="ferie");"ferie";SE(O(GIORNO.SETTIMANA($A81;2)>5;VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0)));"RIPOSO";SE(E(VAL.NUMERO(B41);B41>1/3);1/3;B41))))

[diego68]

Mi.Ti.CO..
davvero complimenti per la tua preparazione ora funziona bene
a questo punto invece vorrei fare su una altra colonna invece solo il totale delle ore che sono state fatte nei giorni festivi e le ore extra durante i feriali sopra le 8 ore, se ci sono ovviamente.
in sostanza vorrei solo un totale di tutta la colonna ad esempio B41-B71 sia delle ore fatte durante i festivi che solo quelle fatte durante i feriale ma che siano sopra le 8 ore per conteggiare il totale ore straordinari per intenderci.per esempio se sabato faccio 4 ore mi conteggi 4 ore e se lunedì faccio 9 ore me ne conteggi solo 1 questo solo il totale non mi serve giorno per giorno
io ho elaborato una formula ma ovviamente non funziona nemmeno questa forse perché' ora il foglio comincia ad essere un po complesso non saprei...
grazie

[gioh66]

Prova così in una cella formattata come [HH]:MM metti

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$41:$A$71;2)>5;B$41:B$71)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$41:$A$71)<6;RESTO(B41:B71;1/3)*(B$41:B$71>1/3))

[diego68]

Ho notato che non mi calcola i festivi per esempio se metto lavorativo il 25 dicembre o il 26 dicembre nella formula non me li calcola come straordinari e vanno aggiunti. calcola solo sabato domenica e le ore sopra le 8
in più' vorrei mettere su una colonna per esempio colonna V e colonna W che automaticamente ogni mese, se cambio l'anno nel calendario perpetuo segnasse di default sempre 8 ore dal lunedì al venerdì. ad esempio dalle 8 alle 16.
poi cosi' se faccio copia incolla sul mese di gennaio, febbraio, o cambio l'anno in automatico mi sposta le 8 ore sempre dal lunedì al venerdì e di default mi scriva anche riposo sabato e domenica. Secondo te e' fattibile?

[gioh66]

Buondì modifica la formula così per aggiungere le ore delle festività

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$41:$A$71;2)>5;B$41:B$71)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$41:$A$71)<6;RESTO(B41:B71;1/3)*(B$41:B$71>1/3))+MATR.SOMMA.PRODOTTO(VAL.NUMERO(CONFRONTA($A$41:$A$71;$AP$6:$AP$16;0));B41:B71)

Per la seconda parte non mi è chiaro cosa vorresti, forse un modello base già precompilato?

[diego68]

ciao a te
si quelle sono le mie ore che devono essere precompilate che sono sempre 8 dal lunedì al venerdì ma se cambio mese devono cambiare in automatico.
una altra cosa mi avevi dato questa formula
=SE(O(GIORNO.SETTIMANA($A81;2)>6;VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0)));"RIPOSO";F41)
che mi segna sabato e domenica come festivo
sto creando un foglio che pero' devo mettere festivo solo la domenica ma ho provato varie modifiche ma non funziona perché' la prima settimana mi da sempre sabato e domenica festivo mentre le altre 3 settimane mi da solo domenica festivo e non capisco come mai

[gioh66]

Per il foglio precompilato io salverei una copia del file che hai già mettendo gli orari e le formule al loro posto. Poi quando lo richiami dovrebbe bastare cambiare l'anno nel foglio configurazione e si dovrebbe adattare.
Per quanto riguarda la formula hai provato così
=SE(O(GIORNO.SETTIMANA($A81;2)=7;VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0)));"RIPOSO";F41)
altrimenti allega il nuovo file.

[diego68]

anche io avevo fatto quella formula ma mi da il primo sabato riposo e non capisco
ti allego il file guarda in basso a destra riquadro D

[gioh66]

Te lo da come così perché nel riquadro AP6:AP16 in AP12 c'è un "7 dicembre" tra le festività, per cui il pezzo di formula
VAL.NUMERO(CONFRONTA($A81;$AP$6:$AP$16;0))
restituisce un VERO che fa si che la formula per quel giorno dia RIPOSO.

[diego68]

che idiota non ci avevo pensato sono diventato matto tutta la mattina per capire..
se guardi in B74 ho fatto la somma di B41-B71 ma mi mancano delle ore nella somma totale lavorate...anche qui non capisco perché' se faccio ore 160 del mese più' per esempio 106 straordinari come c'e' scritto in B77 il totale e' diverso da B74

[gioh66]

Non capisco! In B77 hai la somma delle ore straordinarie (4 domenica 1 + 2 di martedì 3) In B74 hai invece il totale delle ore lavorate, ordinarie e straordinarie. Cosa c'è che non funziona?

[diego68]

Allora in C73 ci sono le ore ordinarie del mese di dicembre che sono 160
in B74 le ore totali lavorate che sono 245
in B77 il totale degli straordinari sopra le 160 ore che sono 106 che sono calcolati sabato e domenica e oltre le 8 ore perché le ore ordinarie sono 8 al giorno da lunedì al venerdì
se io faccio 245 ore totali meno 160 ore ordinarie in teoria dovrebbe dare 106 che sono le ore straordinarie invece da 85 che dovrebbero essere gli straordinari.
oppure se faccio totale straordinari 106+totale ore ordinarie 160= 366 che dovrebbero essere le ore totali lavorate invece nella riga B74 da totale 245 ore con la formula somma che ho messo io. quindi qualcosa non mi quadra.
Poi volevo dire che per prima con le ore fisse che devono cambiare ogni mese a secondo della data ho risolto cosi':
=SE(AO6="feriale";AQ20;"RIPOSO") e pare funzionare

[gioh66]

Scusami ma il file è quello di questo messaggio? Perchè i numeri sono diversi.
viewtopic.php?f=9&t=9970#p55597

[diego68]

Non ho capito

[diego68]

si si il file e' di questo messaggio

[diego68]

Ho capito dove sta il problema del calcolo degli straordinari secondo questa formula che mi hai scritto che io ho inserito nella casella B77
=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$41:$A$71;2)>5;B$41:B$71)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$41:$A$71)<6;RESTO(B41:B71;1/3)*(B$41:B$71>1/3))+MATR.SOMMA.PRODOTTO(VAL.NUMERO(CONFRONTA($A$41:$A$71;$AP$6:$AP$16;0));B41:B71)
quando inserisco orario per esempio il giorno 7 o il giorno 8 che e' festivo (sabato o domenica) ma e' anche un giorno di festa come segnato nella casella da AP6-AP16 me lo calcola doppio (se segno 8 ore esempio nel totale me lo calcola 16 ore) quindi il totale non viene corretto.
come si fa in questo caso?