[Risolto] Calcolo giorni lavorativi trimestrale

[sandro merlino]

Ciao a tutti,
In D3 devo fare il calcolo dei giorni lavorativi trimestrali partendo dal 1/1 e aggiornandolo giorno per giorno. Il calcolo deve essere processato solo se il periodo dell'anno è Gennaio, Febbraio, Marzo. Il trimestre considerato è il primo. Nella colonna F ci sono i numeri, nella colonna G ci sono i giorni della settimana, nella colonna J ci sono i turni lavorativi oppure "chiuso" oppure "close".
Si può trovare "chiuso" oppure "close" perchè il file usa 2 lingue da sceglire in Q5.
Il funzionamento dovrebbe essere questo:
In D4 mi calcola i giorni lavorativi dal 1/1 al 31/1 dopo di che
dovrei sommare i giorni lavorativi di gennaio calcolati nella cella D7 e utilizzare la funzione in D5 che calcola i giorni lavorativi di febbraio dal 1/2 al 28-29/2 dopo di che
dovrei sommare i giorni lavorativi di gennaio calcolati nella cella D7 e i giorni lavorativi di febbraio calcolati in D8 e utilizzare la funzione in D6 che calcola i giorni lavorativi di marzo dal 1/3 al 31/3 .
Putroppo non funziona. Qualcuno saprebbe dirmi perchè e dove sbaglio.

non rispondere non ancora finito

Grazie 1000

Sandro

[gioh66]

Buondì, anche se hai messo di non rispondere, dando un'occhiata alle formule ti posso dire: e cosa ti aspettavi?
Traducendo la tua formula hai chiesto se oggi (OGGI()) in cifre mese (18/10/2017=10) è uguale a "01" (FALSO), moltiplicami dei test logici che restituiscono solo dei VERO(=1) o dei FALSO(=0).

[sandro merlino]

Ciao gioh66,
Mi sono scordato nella fretta di levare la frase.
Ora spero che qualcuno mi dia una mano.
Ho messo espressamente che OGGI() sia uguale a 1, 2 e 3 così quando oggi() è 4,5,6,7,8,9 il risultato è 0.
Il mio problema è che la formula non mi torna anche quando sposto la data

Grazie 1000

sandro

[gioh66]

No forse non ti è chiaro un concetto, la funzione OGGI() serve a darti la data odierna, 18/10/2017, domani sarà 19/10/17 e così via.
Se tu vuoi calcolare i giorni lavorati in gennaio ti basta

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO($J$125:$J$155<>"chiuso";$J$125:$J$155<>"CLOSE")

febbraio

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO($J$225:$J$253<>"chiuso";$J$225:$J$253<>"CLOSE")

marzo

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO($J$322:$J$352<>"CHIUSO";$J$322:$J$352<>"CLOSE")

Per saper quanti giorni lavorativi ci sono nel trimestre, sommi i risultati delle formule precedenti.

Ps. le funzioni matrice.somma.prodotto andavano bene, eccetto la prima condizione...

[sandro merlino]

Ciao gioh66,
Sicuramente mi sono espresso male e provo a spiegare ancora quello che voglio fare.
Se utilizzassi le tue formule avrei un solo valore per i 3 mesi invece io voglio giorno per giorno la somma dei giorni che sono diversi da "close" o "chiuso". Ecco perché utilizzo la funzione
TESTO(OGGI();"gg") in D4 che poi è quella che mi hai insegnato tu nella precedente domanda "somma e conta in base al giorno del mese".
Ti faccio alcuni esempi:
01/01-numero giorni lavorativi ( 0 )
10/01-numero giorni lavorativi ( 9 )
12/01-numero giorni lavorativi ( 10 )
31/01-numero giorni lavorativi ( 29 )
01/02-numero giorni lavorativi ( 29 )
11/02-numero giorni lavorativi ( 39 )
12/02-numero giorni lavorativi ( 39 )
e così via fino al 31/03
Come vedi dovrei trovare una formula che utilizzi:
la funzione in D4 per conteggiare i giorni diversi da "close" o "chiuso" fino a fine gennaio ( giorno per giorno ) poi
somma costante dei giorni diversi da "close" o "chiuso " di gennaio più la funzione in D5 che somma i giorni diversi da "close" o "chiuso " di febbraio ( giorno per giorno ) poi
somma costante dei giorni diversi da "close" o "chiuso " di gennaio più somma costante dei giorni diversi da "close" o "chiuso " di febbraio più la funzione in D6 che somma i giorni diversi da "close" o "chiuso " di marzo.
Per conteggiare la somma costante dei giorni diversi da "close" o "chiuso " di gennaio userei la funzione in D7 e per conteggiare la somma costante dei giorni diversi da "close" o "chiuso " di febbraio userei la funzione in D8.

Vi chiedo cortesemente aiuto. Spero di essere stato più chiaro. Per chiarimenti sono a vostra disposizione


Grazie 1000

Sandro

[gioh66]

Buondì. se ho capito quello che vuoi
in D4 =MATR.SOMMA.PRODOTTO((DATA(2017;1;$F$125:$F$155)<=OGGI());$J$125:$J$155<>"CHIUSO";$J$125:$J$155<>"CLOSE")
in D5 =D4+MATR.SOMMA.PRODOTTO((DATA(2017;2;$F$225:$F$253)<=OGGI());$J$225:$J$253<>"CHIUSO";$J$225:$J$253<>"CLOSE")
in D6 =D5+MATR.SOMMA.PRODOTTO((DATA(2017;3;$F$322:$F$352)<=OGGI());$J$322:$J$352<>"CHIUSO";$J$322:$J$352<>"CLOSE")
In D7 =MATR.SOMMA.PRODOTTO($J$125:$J$155<>"CHIUSO";$J$125:$J$155<>"CLOSE")
in D8 =D7+MATR.SOMMA.PRODOTTO($J$225:$J$253<>"CHIUSO";$J$225:$J$253<>"CLOSE")
Ho inserito nel matr.somma.prodotto, la funzione DATA, che si compone di tre argomenti: anno, mese (in formato numerico, da 1 a 12) e il giorno. Con questa formula ho trasformato i numeri nella colonna verde in date relative ai mesi di interesse: gennaio, febbraio e marzo.
Ora essendo il periodo già trascorso hai già i totali, ma se applichi queste formule all'ultimo periodo, cambiando l'argomento mese, della funzione DATA con 10, 11 e 12, puoi vedere come lavorano le formule.

[sandro merlino]

Ciao gioh66 ,
Ci siamo quasi.
Come ho scritto all'inizio avrei bisogno di mettere in D3 una formula unica. Utilizzando la somma delle formule che avevo messo in D4,D5,D6,D7,D8 non mi tornava.
Devo fare ancora i controlli ma utilizzando le formule in D4,D5,D6 della versione 1 e quelle in E7 e E8 dove viene usato il controllo sul mese che avevo inserito ma
utilizza la matrice.somma.prodotto che hai suggerito te dovrebbe funzionare.

Che ne dici ?
Grazie 1000

Sandro

[gioh66]

Scusa ma io non capisco cosa vuoi ottenere con le tue formule, in particolare con SE(TESTO(OGGI();"MM")="02" .Otterrai sempre come risposta FALSO.
Fai una prova, in un foglio vuoto metti la formula =TESTO(OGGI();"MM") e dimmi cosa ti restituisce.

[sandro merlino]

Ciao gioh66,

Come vedi nel Foglio2 ti ho fatto due esempi in B6 e B12.
Praticamente questa formula =TESTO(OGGI();"MM") estrapola il mese tra virgolette e effettua il controllo.
-se è diverso a quello tra virgolette dopo l' = allora non succede niente (B6)
-se è uguale a quello tra virgolette dopo l' = allora esegue (B12)

Grazie 1000

Sandro

[gioh66]

Ti riposto le mie formule, in maniera più dettagliata, poi decidi tu come (e se) usarle, perchè secondo me con quel sistema che usi tu non torna.
giorni lavorati nel trim. aggiornati giornalmente

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO((DATA(2017;1;$F$125:$F$155)<=OGGI());$J$125:$J$155<>"CHIUSO";$J$125:$J$155<>"CLOSE")+MATR.SOMMA.PRODOTTO((DATA(2017;2;$F$225:$F$253)<=OGGI());$J$225:$J$253<>"CHIUSO";$J$225:$J$253<>"CLOSE")+MATR.SOMMA.PRODOTTO((DATA(2017;3;$F$322:$F$352)<=OGGI());$J$322:$J$352<>"CHIUSO";$J$322:$J$352<>"CLOSE")

giorni lavorati di gennaio aggiornati giornalmente

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO((DATA(2017;1;$F$125:$F$155)<=OGGI());$J$125:$J$155<>"CHIUSO";$J$125:$J$155<>"CLOSE")

giorni lavorati totali di gennaio

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO($J$125:$J$155<>"CHIUSO";$J$125:$J$155<>"CLOSE")

giorni lavorati di febbraio aggiornati giornalmente

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO((DATA(2017;2;$F$225:$F$253)<=OGGI());$J$225:$J$253<>"CHIUSO";$J$225:$J$253<>"CLOSE")

giorni lavorati totali di febbraio

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO($J$225:$J$253<>"CHIUSO";$J$225:$J$253<>"CLOSE")

giorni lavorati di marzo aggiornati giornalmente

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO((DATA(2017;3;$F$322:$F$352)<=OGGI());$J$322:$J$352<>"CHIUSO";$J$322:$J$352<>"CLOSE")

giorni lavorati totali di marzo

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO($J$322:$J$352<>"CHIUSO";$J$322:$J$352<>"CLOSE")

Adattando la formula data, cambiando il numero del mese e il range dei giorni [DATA(2017;3;$F$322:$F$352)] le puoi adattare a tutti i trimestri.

[sandro merlino]

Ciao gioh66,

Dopo attenta lettura delle formule che mi hai dato ho capito alcuni dettagli in più che mi erano sfuggiti. Le mie lacune sull'argomento sono molte e per questo mi rivolgo a esperti come voi.
I dubbi che mi sono rimasti sono questi:
La formula funziona anche se non siamo nel 2017 ?
Cambiando la data al 1/3 vedo che i giorni che vengono contati sono 27 e non 26 nonostante non sia anno bisestile. C'è bisogno di un controllo ?

Grazie 1000

Sandro

[gioh66]

Per rendere la formula dinamica, adattabile per ogni anno metti queste
in D3

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO((DATA(ANNO(OGGI());1;$F$125:$F$155)<=OGGI());$J$125:$J$155<>"CHIUSO";$J$125:$J$155<>"CLOSE")+SE(GIORNI(DATA(ANNO(OGGI());12;31)+1;DATA(ANNO(OGGI());1;1))=366;MATR.SOMMA.PRODOTTO((DATA(ANNO(OGGI());2;$F$225:$F$253)<=OGGI());$J$225:$J$253<>"CHIUSO";$J$225:$J$253<>"CLOSE");MATR.SOMMA.PRODOTTO((DATA(ANNO(OGGI());2;$F$225:$F$252)<=OGGI());$J$225:$J$252<>"CHIUSO";$J$225:$J$252<>"CLOSE"))+MATR.SOMMA.PRODOTTO((DATA(ANNO(OGGI());3;$F$322:$F$352)<=OGGI());$J$322:$J$352<>"CHIUSO";$J$322:$J$352<>"CLOSE")

in D6

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO((DATA(ANNO(OGGI());1;$F$125:$F$155)<=OGGI());$J$125:$J$155<>"CHIUSO";$J$125:$J$155<>"CLOSE")

in D7

Codice: Seleziona tutto

=SE(GIORNI(DATA(ANNO(OGGI());12;31)+1;DATA(ANNO(OGGI());1;1))=366;MATR.SOMMA.PRODOTTO($J$225:$J$253<>"CHIUSO";$J$225:$J$253<>"CLOSE");MATR.SOMMA.PRODOTTO($J$225:$J$252<>"CHIUSO";$J$225:$J$252<>"CLOSE"))

in D8

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO((DATA(ANNO(OGGI());3;$F$322:$F$352)<=OGGI());$J$322:$J$352<>"CHIUSO";$J$322:$J$352<>"CLOSE")

In pratica ho sostituito 2017 nella funzione DATA con ANNO(OGGI)), così si adegua all'anno in corso.
Poi per febbraio ho usato la funzione SE e GIORNI per fare in modo che se l'anno è bisestile il matrice somma prodotto venga fatto sul range $J$225:$J$253, altrimenti usa la formula con il range $J$225:$J$252 (più corto di 1 giorno).
Ti riallego il file con le nuove formule accanto alle vecchie così puoi confrontarle.

[sandro merlino]

Ciao gioh66,

Stupefacente soluzione.
Ho imparato cose nuove anche questa volta.
Grazie 1000

Sandro

Ps: Non è che per caso mi daresti una mano nella soluzione della mia precedente domanda con titolo
"Piano turni semplificato - funzione da trovare".
E' un grattacapo incredibile e impossibile per me.

[gioh66]

Grazie a te del riscontro.

[sandro merlino]

Ciao gioh66,

Posso solo dire:


Sandro