FORMULE CON ORARI

[luca]

Apparentemente semplice dovrei fare dei calcoli con orari per determinare il lavoro del personale. il problema che non vorrei avere tante caselle per suddividere questi orari ma farlo in automatico. mi spiego meglio

i parametri di lavoro principali sono:
lavoro diurno dalle 5:00 alle 22:00
lavoro notturno dalle 22:00 alle 5:00

quindi ESEMPIO:

A B C D
dalle alle diurno notturno
21:00 22:00 1:00 0 un ora diurna
20:00 23:00 2:00 1:00 due ore diurne e una notturna
19:00 3:30 3:00 4:30 tre ore diurne e quattro notturne
21:00 6:00 2:00 6:00 due ore diurne e sei notturne

quale formula inserire nella colonne C e D affinchè riconosca gli orari secondo i parametri di lavoro?
grazie per i suggerimenti

[luca]

errata corrige :19:00 3:30 sono 3ore diurne 5:30 notturne errore di trascrizine :))

[paoloard]

"luca" ha scritto nel messaggio
news:18200396-efed-4899...@googlegroups.com...

Il giorno marted� 5 marzo 2013 13:09:01 UTC+1, luca ha scritto:
> Apparentemente semplice dovrei fare dei calcoli con orari per determinare
> il lavoro del personale. il problema che non vorrei avere tante caselle
> per suddividere questi orari ma farlo in automatico. mi spiego meglio
>
>
>
> i parametri di lavoro principali sono:
>
> lavoro diurno dalle 5:00 alle 22:00
>
> lavoro notturno dalle 22:00 alle 5:00
>
>
>
> quindi ESEMPIO:
>
>
>
> A B C D
>
> dalle alle diurno notturno
>
> 21:00 22:00 1:00 0 un ora diurna
>
> 20:00 23:00 2:00 1:00 due ore diurne e una notturna
>
> 19:00 3:30 3:00 4:30 tre ore diurne e quattro notturne
>
> 21:00 6:00 2:00 6:00 due ore diurne e sei notturne
>
>
>

> quale formula inserire nella colonne C e D affinch� riconosca gli orari

> secondo i parametri di lavoro?
>
> grazie per i suggerimenti

errata corrige :19:00 3:30 sono 3ore diurne 5:30 notturne errore di
trascrizine :))

Inserisci una colonna C come totale ore.
Immetti in K2: 22:00 e in L2: 5:00 (le ore di inizio e fine notturno)
quindi
in C2: =(A2>B2)+B2-A2 (totale ore)
in D2: =C2-E2 (ore diurne)
in E2:
=C2-SE(B2>=A2;MAX(0;MIN(B2;$K$2)-MAX(A2;$L$2));MAX(0;$K$2-MAX(A2;$L$2))+MAX(0;MIN(B2;$K$2)-$L$2))
(ore notturne)

tutte e tre da copiare in basso

--
Ciao
paoloard
http://www.riolab.org/

[Maurizio Borrelli]

"luca" ha scritto nel messaggio
news:18200396-efed-4899...@googlegroups.com...

Il giorno martedě 5 marzo 2013 13:09:01 UTC+1, luca ha scritto:
> Apparentemente semplice dovrei fare dei calcoli con orari per determinare
> il lavoro del personale. il problema che non vorrei avere tante caselle
> per suddividere questi orari ma farlo in automatico. mi spiego meglio
> i parametri di lavoro principali sono:
> lavoro diurno dalle 5:00 alle 22:00
> lavoro notturno dalle 22:00 alle 5:00
> quindi ESEMPIO:
> A B C D
> dalle alle diurno notturno
> 21:00 22:00 1:00 0 un ora diurna
> 20:00 23:00 2:00 1:00 due ore diurne e una notturna
> 19:00 3:30 3:00 4:30 tre ore diurne e quattro notturne
> 21:00 6:00 2:00 6:00 due ore diurne e sei notturne

> quale formula inserire nella colonne C e D affinchč riconosca gli orari

> secondo i parametri di lavoro?

errata corrige :19:00 3:30 sono 3ore diurne 5:30 notturne errore di
trascrizine :))

Ciao luca,
un modo:

F1: Diurno
G1: 05:00
H1: =G1+17/24

F2: Notturno
G2: =H1
H2: =G1+1

F3: PostNotturno
G3: =H2
H3: =G3+17/24

C1: Diurno
C2:
=MAX(0;MIN(B2+(B2<A2);$H$1)-MAX(A2;$G$1))+MAX(0;MIN(B2+(B2<A2);$H$3)-MAX(A2;$G$3))
(Da copiare in basso q.b.)

D1: Notturno
D2: =MAX(0;MIN(B2+(B2<A2);$H$2)-MAX(A2;$G$2))
(Da copiare in basso q.b.)

ottengo:

| A | B | C | D |
--+------+------+-------+---------+
1|dalle |alle |diurno |notturno |
2| 21:00| 22:00| 01:00| 00:00|
3| 20:00| 23:00| 02:00| 01:00|
4| 19:00| 03:30| 03:00| 05:30|
5| 21:00| 06:00| 02:00| 07:00|
6| 23:00| 01:00| 00:00| 02:00|

--
Ciao!
Maurizio

[Maurizio Borrelli]

"Maurizio Borrelli" ha scritto nel messaggio
news:513617b3$0$26776$4faf...@reader2.news.tin.it...

"luca" ha scritto nel messaggio
news:18200396-efed-4899...@googlegroups.com...

Il giorno marted� 5 marzo 2013 13:09:01 UTC+1, luca ha scritto:
> Apparentemente semplice dovrei fare dei calcoli con orari per determinare
> il lavoro del personale. il problema che non vorrei avere tante caselle
> per suddividere questi orari ma farlo in automatico. mi spiego meglio
> i parametri di lavoro principali sono:
> lavoro diurno dalle 5:00 alle 22:00
> lavoro notturno dalle 22:00 alle 5:00
> quindi ESEMPIO:
> A B C D
> dalle alle diurno notturno
> 21:00 22:00 1:00 0 un ora diurna
> 20:00 23:00 2:00 1:00 due ore diurne e una notturna
> 19:00 3:30 3:00 4:30 tre ore diurne e quattro notturne
> 21:00 6:00 2:00 6:00 due ore diurne e sei notturne

> quale formula inserire nella colonne C e D affinch� riconosca gli orari

> secondo i parametri di lavoro?
errata corrige :19:00 3:30 sono 3ore diurne 5:30 notturne errore di
trascrizine :))

Ciao luca,

devo integrare la mia proposta precedente perche' non contemplava casi
particolari.

G1: Prediurno
H1: 0:00
I1: 05:00

G2: Diurno
H2: =I1
I2: =H2+17/24

G3: Notturno
H3: =I2
I3: =H2+1

G4: PostNotturno
H4: =I3
I4: =H4+17/24

C1: diurno
C2:
=MAX(0;MIN(B2+(B2<A2);$I$2)-MAX(A2;$H$2))+MAX(0;MIN(B2+(B2<A2);$I$4)-MAX(A2;$H$4))

(Da copiare in basso q.b.)

D1: notturno
D2:
=MAX(0;MIN(B2+(B2<A2);$I$1)-MAX(A2;$H$1))+MAX(0;MIN(B2+(B2<A2);$I$3)-MAX(A2;$H$3))

Ottengo:

| A | B | C | D |
--+------+------+-------+---------+
1|dalle |alle |diurno |notturno |
2| 21:00| 22:00| 01:00| 00:00|
3| 20:00| 23:00| 02:00| 01:00|
4| 19:00| 03:30| 03:00| 05:30|
5| 21:00| 06:00| 02:00| 07:00|
6| 23:00| 01:00| 00:00| 02:00|

7| 04:00| 03:00| 17:00| 06:00| (*)

Il caso particolare di cui dicevo.

(Interessante nella soluzione di Paolo il calcolo del solo diurno o
notturno, ottenendo l'altro per differenza dal totale.)

--
Ciao!
Maurizio

[luca]

perfettoooooooo, grandi ...........
la formula di paoloard potrei riuscire a capirla con le 2 celle di appoggio, quella di maurizio non comprendo, nelle caselle appoggio la divisione 17/24
cmq grazie infinite

[Maurizio Borrelli]

"luca" ha scritto nel messaggio

news:1f20cbe4-b868-47db...@googlegroups.com...

Il giorno martedě 5 marzo 2013 13:09:01 UTC+1, luca ha scritto:

perfettoooooooo, grandi ...........
la formula di paoloard potrei riuscire a capirla con le 2 celle di appoggio,
quella di maurizio non comprendo, nelle caselle appoggio la divisione 17/24
cmq grazie infinite

Grazie del riscontro, luca.

Riguardo la divisione 17/24 considera che per Excel le date sono numeri e le
ore frazioni, ovvero la parte decimale di un numero-giorno. Cosě 1 = 1
giorno, 17/24 = 17 ore = 0,708333333333333.
--
Ciao!
Maurizio

[Andrea22]

Buongiorno mi sono permesso di implementare i Vostri sempre validi suggerimenti, visto che è un tema importante e che coinvolge tutte le aziende: quello del calcolo delle ore del personale, suddivise in modo corretto tra ore ordinarie, straordinarie, ferie, ecc…

Vi allego il mio file, modificato dopo le Vs dritte; lo stesso contiene:
Database orari, forse trascurabile, per ora

Database Dipendenti dove inserire i nominativi; qui ogni volta che si aggiunge un nome si deve ordinare in ordine alfabetico visto che poi abbiamo un elenco nel foglio Inserimento dati legato a questo database.

Sarebbe bello che ci fosse una macro che nel momento in cui si torna ad Inserimento Dati ordini faccia automaticamente il tutto.

Inserimento Parametri orari dove vengono riportate le formule create da Maurizio Qui dobbiamo stabilire una cosa importante: se la settimana lavorativa è di 5 o 6 giorni e quindi di 8 ore lav al gg o 6,40. Questo ci verrà utile successivamente. Il valore andrà indicato in M1, mentre in automatico verrà inserito il sabato, come gg festivo, o meno.
Inserimento Dati come utilizzarlo:

Nella prima colonna andiamo ad inserire il nome del dipendente (per l’elenco ALT+freccia giù); poi solo la data, mentre il mese ed il gg compariranno in automatico;

Sarebbe bello che il testo del giorno festivo, in relazione a quanto stabilito in inserimento parametri orari, si colori di rosso. La stessa cosa se fosse legato ad altre festività di quell’anno, ma qui io non ci arrivo proprio!

A questo punto inserire l’ora d’ingresso e di uscita per la mattina; lo stesso
per il pomeriggio. Nota bene le ore andranno inserite con il “punto” e alle volte nel tastierino numerico non lo “prende” non è configurato e bisogna usare il punto (stesso tasto dei due punti), questa nn l’ho mai capita!
Le ore inserite possono essere solo la mattina (ent ed usc); solo il pomeriggio (idem); oppure senza la pausa come in riga 11.

Nello stesso tempo ho pensato di inserire “variazioni strane” sempre qui, e qui iniziano i problemi, problemi che potrei risolver einserendo una colonna nuova dove inserire PR o M, ecc, ma voglio vedere se qualcuno ha una soluzione più “professionale”:

Supponiamo di avere messo la settimana lavorativa breve dal lun al ven, 8 ore giornaliere

Se io inserisco nella colonna entrata del pomeriggio la lettera PR (che sta per permesso retribuito), come in riga 15, devo registrare le 4 ore di lavoro nei due totali ma anche, nella colonna AC devo inserire le ore di permesso cioè 4.

In giallo vengono evidenziate le celle della riga 15 con problemi:N15 deve sommare solo i numeri; idem S15 ed U15.

Una volta trovata la formula sarà riproducibile quante volte si vuole per malattie, infortuni, ecc basta assegnare il prefisso breve ad ognuna

Adesso guardiamo la colonna S dove vengono riportati i totali delle ore lavorate nella giornata; qui il totale andrà ripartito in base alle ore di lavoro giornaliere o le 8 o le 6,40 (parametro che abbiamo scelto precedentemente) e quindi le ore totali della giornata 8,30 della riga 12, andranno splittate in 8 in colonna S e 0,30 in colonna T.

Per tutte le altre celle evidenziate in giallo si dovrebbe per maggior chiarezza di lettura evitare che se non vi sono valori le celle delle colonne restino vuote, e non ci riesco!

Ed infine un foglio Report dove vengono riportate tutte le variazioni; questo è il sacrificio di un mese di inserimenti ed è il foglio che andrà consegnato al consulente del lavoro.

Spero che che vi appassionate anche voi al tema e che si possa sviluppare per tutti

Grazie 1000

Andrea
Allego il file
https://skydrive.live.com/redir?resid=5E856AF67A873B3E!283&authkey=!AOSHbQZ8vfjGtR4

[Andrea22]

Ho provato il foglio ed implementato e sembra girare abbastanza bene. Il database e formule relative possono una volta inserita l’anagrafica dei dipendenti dividere automaticamente le ore di lavoro giornaliere in ordinario, ordinario straordinario, notturno, festivo, ecc…

Ma facendo un po’ di prove ho riscontrato che in riga 14, ad esempio, ma anche in tutte le altre, se l’orario supera le 15.20 combina casini, ovvero non calcola le ore correttamente ripartendole in ord e strao, provare per credere.

Inoltre perché se sommo due celle che contengono ore ed una delle due è vuota mi restituisce errore VALORE?
Ad esempio in T10 =SE(T10="";N10=X10+AA10;SE(X10="";N10=T10+W10;""))

Grazie dell'aiuto che vorrete darmi e buonanotte, è stata una lunga giornata!

file link
https://skydrive.live.com/redir?resid=5E856AF67A873B3E!283&authkey=!AOSHbQZ8vfjGtR4

[r]

Il giorno martedì 5 marzo 2013 18:05:58 UTC+1, Maurizio Borrelli ha scritto:
> "Maurizio Borrelli" ha scritto nel messaggio
>
> news:513617b3$0$26776$4faf...@reader2.news.tin.it...
>
>
>
> "luca" ha scritto nel messaggio
>
> news:18200396-efed-4899...@googlegroups.com...
>
>
>
> Il giorno martedì 5 marzo 2013 13:09:01 UTC+1, luca ha scritto:
>
> > Apparentemente semplice dovrei fare dei calcoli con orari per determinare
>
> > il lavoro del personale. il problema che non vorrei avere tante caselle
>
> > per suddividere questi orari ma farlo in automatico. mi spiego meglio
>
> > i parametri di lavoro principali sono:
>
> > lavoro diurno dalle 5:00 alle 22:00
>
> > lavoro notturno dalle 22:00 alle 5:00
>
> > quindi ESEMPIO:
>
> > A B C D
>
> > dalle alle diurno notturno
>
> > 21:00 22:00 1:00 0 un ora diurna
>
> > 20:00 23:00 2:00 1:00 due ore diurne e una notturna
>
> > 19:00 3:30 3:00 4:30 tre ore diurne e quattro notturne
>
> > 21:00 6:00 2:00 6:00 due ore diurne e sei notturne
>

> > quale formula inserire nella colonne C e D affinchè riconosca gli orari

Ciao maurizio,
nella tua formula in c2:

=MAX(0;MIN(B2+(B2<A2);$I$2)-MAX(A2;$H$2))+MAX(0;MIN(B2+(B2<A2);$I$4)-MAX(A2;$H$4))

questa parte MAX(A2;$H$4) restituisce sempre $H$4 essendo comunque un orario + 1 giorno. Quindi mantenendo i dati e volendo usare i soli orari 5:00 e 22:00 nel tuo esempio in diurno G2, H2 possiamo scriverla così:

=MAX(;MIN(B2+(B2<A2);$H$2)-MAX(A2;$G$2))+MAX(;MIN(B2+(B2<A2);$H$2+1)-($G$2+1))
o ovviamente risparmiando 2 parentesi:
=MAX(;MIN(B2+(B2<A2);$H$2)-MAX(A2;$G$2))+MAX(;MIN(B2+(B2<A2);$H$2+1)-$G$2-1)

saluti
r

[Maurizio Borrelli]

"r" ha scritto nel messaggio
news:3fbab737-f119-41bb...@googlegroups.com...

Il giorno martedě 5 marzo 2013 18:05:58 UTC+1, Maurizio Borrelli ha scritto:
> "Maurizio Borrelli" ha scritto nel messaggio
> news:513617b3$0$26776$4faf...@reader2.news.tin.it...
> "luca" ha scritto nel messaggio
> news:18200396-efed-4899...@googlegroups.com...

[...]

nella tua formula in c2:
=MAX(0;MIN(B2+(B2<A2);$I$2)-MAX(A2;$H$2))+MAX(0;MIN(B2+(B2<A2);$I$4)-MAX(A2;$H$4))
questa parte MAX(A2;$H$4) restituisce sempre $H$4 essendo comunque un orario
+ 1 giorno. Quindi mantenendo i dati e volendo usare i soli orari 5:00 e

22:00 nel tuo esempio in diurno G2, H2 possiamo scriverla cosě:

=MAX(;MIN(B2+(B2<A2);$H$2)-MAX(A2;$G$2))+MAX(;MIN(B2+(B2<A2);$H$2+1)-($G$2+1))
o ovviamente risparmiando 2 parentesi:
=MAX(;MIN(B2+(B2<A2);$H$2)-MAX(A2;$G$2))+MAX(;MIN(B2+(B2<A2);$H$2+1)-$G$2-1)

Ciao r,
ebbene si'. Anche se privarsi (pure!) degli zeri fa tanto dopoguerra.
--
Ciao!
Maurizio

[r]

Quindi in perfetta sintonia con il clima post elettorale ...

[Andrea22]

Il giorno martedì 5 marzo 2013 13:09:01 UTC+1, luca ha scritto:

Supponiamo di avere due turni lavorativi e quindi
mattino colonna A e B
pomeriggio colonna C e D
diurno colonna E e notturno colonna F

applicando le vostre ultime formule mi costringe sempre ad immettere tutti e quattro i valori, anche se un dipendente farà un turno solo!
Dovrò, per esempio scrivere:
mattino: ent 8.00 usc 12.00
pomeriggio ent 0.00 usc 0.00

per ottenere il totale delle ore corretto

C'é un metodo per evitarlo e dare la possibilità di inserire solo un turno?

questa é la formula che uso, quella più semplice

((($a3>$b3)+$b3-$a3))+(($c3>$d3)+$d3-$c3))))

Grazie 1000 e buona domenica