Conteggi con orari che non tornano

[systemcrack]

Ciao a tutti,
spero che il nuovo anno sia partito bene, per quanto riguarda me ancora non ve lo so dire.. di sicuro in maniera stimolante

I ragazzi dell'operativo/piazzale hanno un file excel (SCHEMA ORARI), che utilizzano per segnarsi giornalmente gli orari del personale esterno ed il loro. Mi hanno chiesto se lo potevo sistemare un pò, ma nel farlo mi sono accorto che lo si potrebbe automatizzare un pò di più senza pensare a cose incredibili.. così mi sono messo a lavorarci:

Il file è formato da 2 fogli RAPPORTO GIORNALIERO e SCHEDATECNICA

Nel primo abbiamo tutti i dati che ci interessano ( Nominativo personale, ora di entrata, ora di uscita, ore ordinarie, ore straordinarie, ore notturne) + un breve riepilogo/conteggio ripartito diversamente (in basso a destra)



Nel secondo foglio invece dei dati per così dire d'appoggio per poter ottenere i vari conteggi e orari suddivisi nel primo.
Lo so che così sono una schifezza e che avrei potuto gestire la cosa diversamente, ma sto andando dietro allo schema che mi è giunto così e che non volevo sconvolgere troppo.



A questo punto sto riscontrando diversi problemi nel riuscire a fare uscire i conteggi giusti, ma allo stesso tempo a non ricevere vero falso oppure tanti ##### o un risultato non corretto.

- Il primo problema è fare in modo che non escano valori 0, ####, falso, vero quando non tutti i campi sono compilati (esempio orario non inserito del tutto) ed allo stesso tempo inserire tutta la stringa in modo che questa funzioni anche quando gli orari che vengono scalati non sono ideali per la sottrazione (esempio 22:00 - 06:00 il turno è a cavallo dei 2 giorni).

Codice: Seleziona tutto

=SE('RAPPORTO GIORNALIERO'!D5<>"";SE('RAPPORTO GIORNALIERO'!E5<'RAPPORTO GIORNALIERO'!D5;'RAPPORTO GIORNALIERO'!E5+1;'RAPPORTO GIORNALIERO'!E5)-'RAPPORTO GIORNALIERO'!D5;"")

-Il secondo problema invece è composto su come calcolare le ore notturne.. ho provato in SCHEDATECNICA con

Codice: Seleziona tutto

=RESTO(E5-D5;1)-(E5<D5)*(SCHEDATECNICA!G1-SCHEDATECNICA!F1)+MEDIANA(SCHEDATECNICA!F1;SCHEDATECNICA!G1;D5)-MEDIANA(SCHEDATECNICA!F1;SCHEDATECNICA!G1;E5)

ma non sembra funzionare

-Ultimo problema nelle celle M35 e N38 del foglio RAPPORTO GIORNALIERO se non utilizzo la formattazione della tabella come da immagine di seguito restituisce un risultato completamente sballato.



Di seguito per scaricare il file di prova su cui sto lavorando.

Spero in un qualche consiglio perchè mi sono un pò arenato.. metto a posto da una parte e si incasina dall'altra

[systemcrack]

Forse sono riuscito a fare andare gli orari notturni, il problema non era il comando errato quando gli orari impostati nel secondo foglio prima era 5.00-22.00 ora è 6.00-22.00 ed il conteggio torna.

Ora un difetto di cui mi sono accorto e che non voglio assolutamente che appaia, è che se in foglio RAPPORTOGIORNALIERO nella cella degli orari (E5 ora uscita) se l'ora immessa non raggiunge le 8 ore nella cella (G5 ore straordinari) compare un errore ##### dove sta l'inghippo?

[systemcrack]

Attualmente non sono riuscito a risolvere i seguenti problemi:
- In cella G5 è presente la seguente funzione:

Codice: Seleziona tutto

=SE(SCHEDATECNICA!B1>SCHEDATECNICA!D1;SCHEDATECNICA!B1-SCHEDATECNICA!D1;"")

Che restituisce #valore! se nelle celle degli orari (D5 - E5) non sono inseriti dati.. eppure ho messo le "" per definire la casistica falso

- Nelle celle M35 e N38 del foglio RAPPORTO GIORNALIERO se non utilizzo la formattazione della tabella come da immagine di seguito restituisce un risultato completamente sballato. Le ho provate tutte, ma non ne vengo fuori!

[systemcrack]

Dopo ore di prove sono riuscito ad arrivare fino a qui.. sembra che inizi ad avvicinarmi al risultato ma ci sono ancora delle caselle che si colorano sebbene siano impostate per farlo se diverse da vuote.. ormai ci vedo doppio
Qui l'ultima versione a cui sono arrivato.

[Anthony47]

M'è venuto mal di testa

[systemcrack]

Ciao Anthony, ben ritrovato. Si hai ragione questo post è un delirio.. facciamo così.. concentriamoci su una cosa alla volta.

Il primo problema che vorrei risolvere è il risultato che compare solo con una formattazione particolare. Nel precedente post:

Nelle celle M35 e N38 del foglio RAPPORTO GIORNALIERO se non utilizzo la formattazione della tabella come da immagine di seguito restituisce un risultato completamente sballato

[Anthony47]

I calcoli con Orari restituiscono un risultato in formato Data; quindi 8h+8+8+8=1g 8h
Se usi un formato orario standard ti visualizzerà 8h, e 1g viene "perso"
La formattazione che si raccomanda in questi casi e': categoria Personalizzato usando il tipo [h]:mm

[systemcrack]

eh non sai quante volte ho provato a ricontrollare la formattazioni di tutte le celle che sono coinvolte, ma il problema persiste.
Allego un paio di screen sperando possano aiutare a capire dove sto sbagliando:

[Anthony47]

Vedo vari tentativi, ma nessuno fatto con il tipo [h]:mm (lo devi scrivere tu)

Per una overview dei formati "personalizzati" vedi https://support.microsoft.com/it-it/off ... f18b4eae68

[systemcrack]

Vedo vari tentativi, ma nessuno fatto con il tipo [h]:mm (lo devi scrivere tu)

Per una overview dei formati "personalizzati" vedi https://support.microsoft.com/it-it/off ... f18b4eae68

Uuuh grande Anthony.. e io che pensavo che le immagini fossero vecchie

passiamo alla seconda cosa che proprio non riesco a comprendere:
perchè una volta immessi i dati degli orari, si colorano le caselle nella colonna H anche se in realtà essa non è influenzata dagli orari immessi.
Mi spiego al momento il file è così strutturato: nelle colonne D e E vengono immesse le ore (entrata e uscita), se le ore sono nel range 22:00 - 06:00 (notturne) vengono conteggiate nella colonna H che di conseguenza si colora.
Spesso però la colonna H si colora anche se non è presente nessuna ora.. ecco questo è il prossimo problema che vorrei risolvere.



Come da immagine la prima cella in colonna H è la H5 che contiene il seguente comando:

Codice: Seleziona tutto

=SE(E5<>"";RESTO(E5-D5;1)-(E5<D5)*(SCHEDATECNICA!G1-SCHEDATECNICA!F1)+MEDIANA(SCHEDATECNICA!F1;SCHEDATECNICA!G1;D5)-MEDIANA(SCHEDATECNICA!F1;SCHEDATECNICA!G1;E5);"")

Ho impostato le ragole di formattazione



ma a questo punto è ovvio che stia dimenticando qualche cosa..

[Raffaele53]

Secondo me il foglio "SCHEDATECNICA" non serve a nulla, perlomeno si può fare il tutto con un solo foglio. Solo un metodo (ipotizzando tutte le celle = [h]:mm), vedi totali ed FC

https://we.tl/t-k84Rv7QUF6

[systemcrack]

Ok ho risolto cambiando la condizione di formattazione.. ho modificato con la regola:
Formatta solo le celle che contengono - Valore tra 1:00 e 24:00 ed in questo modo ho risolto il problema delle caselle che si colorano anche se vuote.

Ora rimane un ultimissimo problema in alcune situazioni sempre in colonna H comparivano in alcuni casi degli ##### così ho fatto ricerca ed ho scoperto che sono valori negativi, quindi in opzioni ho modificato il sistema data



ora vedo l'orario negativo che nel mio caso corrisponde sempre a -0:00



ma non capisco da cosa sia dovuto e come fare in modo che non esca.. risolto questo dovrei essere a posto.

[systemcrack]

Secondo me il foglio "SCHEDATECNICA" non serve a nulla, perlomeno si può fare il tutto con un solo foglio. Solo un metodo (ipotizzando tutte le celle = [h]:mm), vedi totali ed FC

https://we.tl/t-k84Rv7QUF6

Ciao Raffaele ben trovato.. do subito un'occhiata al tuo file

dopo 5 min
Ok ho visto il file.. grande, ma se dovessero essere cambiati gli orari solo di alcuni palisti, carrelli grandi, ecc, per chi non è "abituato" a mettere mano in certe cose.. è un pò ostica la cosa.
Per come me l'ero pensata io, si va in schedatecnica e si cambiano i dati/riferimento riga per riga. Comunque farò tesoro del tuo file e delle tue formule.

[Anthony47]

La formula di colonna H con la sua formattazione originale crea due problemi
1) In Excel non e’ vero che “” corrisponde a una cella vuota (purtroppo le formule Excel non consentono di restituire Niente); e la condizione “”>0 restituisce Vero, quindi cella colorata
2) quando si lavora con gli orari, puo’ capitare che due orari apparentemente uguali, a seguito di calcoli differiscano per valori microscopici, sufficienti pero’ a far fallire un eventuale confronto.
Il suggerimento pertanto e’ di usare ARROTONDA per eliminare errori microscopici; es Arrotonda con 5 decimali azzera tutto cio’ che e’ inferiore a 0.5 secondi.
Pertanto la tua formula potrebbe essere modificata in

Codice: Seleziona tutto

=SE(E5<>"";ARROTONDA(RESTO(E5-D5;1)-(E5<D5)*(SCHEDATECNICA!G1-SCHEDATECNICA!F1)+MEDIANA(SCHEDATECNICA!F1;SCHEDATECNICA!G1;D5)-MEDIANA(SCHEDATECNICA!F1;SCHEDATECNICA!G1;E5);5);0)


(lo Zero non viene visualizzato perché, in Opzioni /Impostazioni avanzate, hai disabilitato la visualizzazione del valore Zero)

Quanto ai risultati Negativi (premesso che quanto detto al punto 2 sopra puo’ produrre risultati negativi invece che Zero; quindi l’uso di Arrotonda cura anche quella situazione), personalmente raccomando di fare in modo che la formula controlli che il risultato sia >=0 invece che usare il sistema data 1904. Ti e’ chiaro che la data 1-gen-2025 già eventualmente scritta col formato data standard, cambiando il formato al sistema 1904 diventerà 2-gen-2029?
Analoghe situazioni si avranno facendo Copia /Incolla di date tra file che usano sistemi di data diversi, o anche facendo “collegamenti”, tipo la formula

Codice: Seleziona tutto

='[SCHEMA ORARI.xlsx]RAPPORTO GIORNALIERO'!$P$H5

In questo caso, se il file SCHEMA ORARI.xlsx ha il sistema data 1904 e il file che contiene la formula usa il sistema standard ti ritroverai con date sballate di 4 anni e 1 giorno

[systemcrack]

La formula di colonna H con la sua formattazione originale crea due problemi
1) In Excel non e’ vero che “” corrisponde a una cella vuota (purtroppo le formule Excel non consentono di restituire Niente); e la condizione “”>0 restituisce Vero, quindi cella colorata
2) quando si lavora con gli orari, puo’ capitare che due orari apparentemente uguali, a seguito di calcoli differiscano per valori microscopici, sufficienti pero’ a far fallire un eventuale confronto.
Il suggerimento pertanto e’ di usare ARROTONDA per eliminare errori microscopici; es Arrotonda con 5 decimali azzera tutto cio’ che e’ inferiore a 0.5 secondi.
Pertanto la tua formula potrebbe essere modificata in

Codice: Seleziona tutto

=SE(E5<>"";ARROTONDA(RESTO(E5-D5;1)-(E5<D5)*(SCHEDATECNICA!G1-SCHEDATECNICA!F1)+MEDIANA(SCHEDATECNICA!F1;SCHEDATECNICA!G1;D5)-MEDIANA(SCHEDATECNICA!F1;SCHEDATECNICA!G1;E5);5);0)


(lo Zero non viene visualizzato perché, in Opzioni /Impostazioni avanzate, hai disabilitato la visualizzazione del valore Zero)

E se volessi aggiungere l'arrotonda anche alla colonna F in cui una ipotetica cella contiene la seguente funzione:

Codice: Seleziona tutto

=SE(E7="";"";SE(H7=SCHEDATECNICA!B3;"";SE(SCHEDATECNICA!B3<>"";SE(SCHEDATECNICA!B3<SCHEDATECNICA!D3;SCHEDATECNICA!B3;SCHEDATECNICA!D3-H7);"")))

ho provato così:

Codice: Seleziona tutto

=SE(E7="";ARROTONDA;SE(H7=SCHEDATECNICA!B3;"";SE(SCHEDATECNICA!B3<>"";SE(SCHEDATECNICA!B3<SCHEDATECNICA!D3;SCHEDATECNICA!B3;SCHEDATECNICA!D3-H7);"");5);0)


e così:

Codice: Seleziona tutto

=SE(E7="";ARROTONDA;SE(H7=SCHEDATECNICA!B3;"";SE(SCHEDATECNICA!B3<>"";SE(SCHEDATECNICA!B3<SCHEDATECNICA!D3;SCHEDATECNICA!B3;SCHEDATECNICA!D3-H7);5);0))

Ma restituisce errore "PER LA FUNZIONE SONO STATI IMMESSI TROPPI ARGOMENTI" .. cosa sto dimenticando?

[Anthony47]

Sbagli la sintassi di Arrotonda:
Arrotonda(Valore;N°diCifre)
Quindi:

Codice: Seleziona tutto

=SE(E7="";ARROTONDA(SE(H7=SCHEDATECNICA!B3;"";SE(SCHEDATECNICA!B3<>"";SE(SCHEDATECNICA!B3<SCHEDATECNICA!D3;SCHEDATECNICA!B3;SCHEDATECNICA!D3-H7);""));5);0)

[systemcrack]

Grande Anthony..! Questi ultimi passaggi senza di te sarebbero stati un ostacolo invalicabile. Formidabile! Grazie infinite!

PS
Per rigore della cronaca la prima funzione che mi hai passato

Codice: Seleziona tutto

=SE(E5<>"";ARROTONDA(RESTO(E5-D5;1)-(E5<D5)*(SCHEDATECNICA!G1-SCHEDATECNICA!F1)+MEDIANA(SCHEDATECNICA!F1;SCHEDATECNICA!G1;D5)-MEDIANA(SCHEDATECNICA!F1;SCHEDATECNICA!G1;E5);5);0)

arrotondava ed invece che dare il risultato corretto di 8:00+8:00 = 16:00 restituiva 15:59. Per sopperire al problema ho modificato la parte della funzione sopracitata da ;5);0) in ;6);0) e così funziona.

Mentre per la seconda, così com'era restituiva la dicitura Valore se non erano inseriti nella rispettiva riga gli orari. Anche in questo caso ho variato una parte della funzione corretta che inizialmente era così:

Codice: Seleziona tutto

=SE(E7="";ARROTONDA(SE(H7=SCHEDATECNICA!B3;"";SE(SCHEDATECNICA!B3<>"";SE(SCHEDATECNICA!B3<SCHEDATECNICA!D3;SCHEDATECNICA!B3;SCHEDATECNICA!D3-H7);""));5);0)

e che di cui ho cambiato la parte iniziale, seguendo il tuo consiglio, da così =SE(E7=""; a così =SE(E7>0; ed anche così funziona.