Demande d'aide pour simplifier une formule

Salut Patrick,

Problème intéressant!

Je ne sais pas à quel point ta formule est compliquée. En voici une qui n'est
pas simple non plus. :-)

Avec
A1: 19:00
B1: 22:00
C1: 22:30
D1: 01:35
E1: 04:30
F1: 06:30
Haut: 21:00 ' dans une cellule nommée ou par un nom directement
Bas : 06:00 ' idem

=MAX(0;MIN(B1;Bas)-SI(B1>A1;A1;MIN(0;A1-Bas)))+MAX(0;1-MAX(Haut;A1)-SI(B1>A1;1-B
1;MIN(0;Haut-B1)))+
MAX(0;MIN(D1;Bas)-SI(D1>C1;C1;MIN(0;C1-Bas)))+MAX(0;1-MAX(Haut;C1)-SI(D1>C1;1-D1
;MIN(0;Haut-D1)))+
MAX(0;MIN(F1;Bas)-SI(F1>E1;E1;MIN(0;E1-Bas)))+MAX(0;1-MAX(Haut;E1)-SI(F1>E1;1-F1
;MIN(0;Haut-F1)))

Format: [h]:mm

Tu remarqueras que c'est la somme de 3 fois la même formule (1 fois/vacation).
Il y a probablement moyen de faire plus court mais j'ai pas trouvé et il fait
beau.

Si tu es patient, peut-être y arriverai-je (ou un/e autre)... :-)

Salutations,

Daniel M.

"Patrick Bastard" <bastardp"chez"free.fr> wrote in message
news:%23pVVQ0ySEHA.796@TK2MSFTNGP10.phx.gbl...

Bonjour, toutes et tous.

Je cherche à calculer le nombre d'heures de nuit (entre 21:00 et 6:00) pour
des positions de travail pouvant avoir 3 vacations par jour.
Par exemple : De 19:00 à 22:00, De 22:30 1:35, De 4:30 à 6:30 (les horaires
sont sur la même ligne, dans des colonnes adjacentes).
*Résultat attendu 5:35*
J'ai monté une usine à gaz avec 4 colonnes intermédiaires contenant des
formules avec des si, et, ou imbriqués. Ca marche, mais c'est lourd...

Si l'un d'entre vous pouvait m'aiguiller sur une solution plus légère (et
plus élégante), ma reconnaissance n'aurait d'égal que mon admiration.
;-))

A vous lire bientôt, j'espère,


--
Bien cordialement,
==================================== > P. Bastard.
Pour me contacter, remplacez "Chez" par @ dans l'adresse

Une foule de conseils utiles sur le site de Misange :
http://www.excelabo.net
Mais ça, j'ai pô trouvé.

Bonjour, toutes et tous.

Je cherche à calculer le nombre d'heures de nuit (entre 21:00 et 6:00) pour
des positions de travail pouvant avoir 3 vacations par jour.
Par exemple : De 19:00 à 22:00, De 22:30 1:35, De 4:30 à 6:30 (les horaires
sont sur la même ligne, dans des colonnes adjacentes).
*Résultat attendu 5:35*
J'ai monté une usine à gaz avec 4 colonnes intermédiaires contenant des
formules avec des si, et, ou imbriqués. Ca marche, mais c'est lourd...

Si l'un d'entre vous pouvait m'aiguiller sur une solution plus légère (et
plus élégante), ma reconnaissance n'aurait d'égal que mon admiration.
;-))

A vous lire bientôt, j'espère,

Bonsoir Patrick,

Via une fonction perso :
Le principe est de décaler les heures de 12:00 et d'en faire la
soustraction (pour éviter un mod 1 dont je ne me sors pas).

Public Function hn(inp As Range)
Application.Volatile True
debut = "21:00": fin = "6:00"
debut = CDbl(TimeValue(debut)) + 0.5
debut = debut - Int(debut)
fin = CDbl(TimeValue(fin)) + 0.5
fin = fin - Int(fin)
hn = 0
For a = 1 To 5 Step 2
b = inp(a + 0) + 0.5:b = b - Int(b)
If b < debut Then b = debut
c = inp(a + 1) + 0.5:c = c - Int(c)
If c > fin Then c = fin
hn = hn + c - b
Next a
End Function

Usage :
En A1: 19:00
En B1: 22:00
En C1: 22:30
En D1: 01:35
En E1: 04:30
En F1: 06:30
En G1: =hn(A1:F1) à mettre au format hh:mm

@+
FxM

Salut Fx,

Lorsque E1 commence à l'extérieur des heures, c'est problématique.

19:00 22:00 22:30 01:35 06:15 06:30

Comptabilise ce temps en moins dans le résultat final.

Salutations,

Daniel M.

"FxM" <fxmanceaux@chello.fr> wrote in message
news:ePWszjzSEHA.2544@TK2MSFTNGP10.phx.gbl...

Bonjour, toutes et tous.

Je cherche à calculer le nombre d'heures de nuit (entre 21:00 et 6:00) pour
des positions de travail pouvant avoir 3 vacations par jour.
Par exemple : De 19:00 à 22:00, De 22:30 1:35, De 4:30 à 6:30 (les horaires
sont sur la même ligne, dans des colonnes adjacentes).
*Résultat attendu 5:35*
J'ai monté une usine à gaz avec 4 colonnes intermédiaires contenant des
formules avec des si, et, ou imbriqués. Ca marche, mais c'est lourd...

Si l'un d'entre vous pouvait m'aiguiller sur une solution plus légère (et
plus élégante), ma reconnaissance n'aurait d'égal que mon admiration.
;-))

A vous lire bientôt, j'espère,

Bonsoir Patrick,

Via une fonction perso :
Le principe est de décaler les heures de 12:00 et d'en faire la
soustraction (pour éviter un mod 1 dont je ne me sors pas).

Public Function hn(inp As Range)
Application.Volatile True
debut = "21:00": fin = "6:00"
debut = CDbl(TimeValue(debut)) + 0.5
debut = debut - Int(debut)
fin = CDbl(TimeValue(fin)) + 0.5
fin = fin - Int(fin)
hn = 0
For a = 1 To 5 Step 2
b = inp(a + 0) + 0.5:b = b - Int(b)
If b < debut Then b = debut
c = inp(a + 1) + 0.5:c = c - Int(c)
If c > fin Then c = fin
hn = hn + c - b
Next a
End Function

Usage :
En A1: 19:00
En B1: 22:00
En C1: 22:30
En D1: 01:35
En E1: 04:30
En F1: 06:30
En G1: =hn(A1:F1) à mettre au format hh:mm

@+
FxM

Bonsoir , *Daniel*.


Et merci de ta proposition.

Je ne sais pas à quel point ta formule est compliquée.

La formule, que j'utilise actuellement fait la somme des fins moins la
somme des débuts calculés comme suit :
(s et v : valeurs 0.25 et 0.875, soit 6:00 et 21:00)

Début 1 :
SI(G13=H13;0;SI(ET(G13<s;H13<=s);H13-G13;SI(ET(G13<s;H13>s;H13<=v);s-G13;SI(
ET(G13=s;H13>s;H13<=v);0;SI(ET(G13>s;G13<v;H13<s);H13-v+1;SI(ET(G13>=s;G13< v;H13>=s;H13<=v);0;))))))

Fin 1 :
SI(ET(G13>=s;G13<=v;H13>v);H13-v;SI(ET(G13>=v;H13<s);1-G13+H13;SI(ET(G13=v;H
13>v);H13-v;SI(ET(G13>v;H13=s);H13+1-G13;SI(ET(G13>v;H13>v);H13-G13;)))))

Début 2 :
SI(I13=J13;0;SI(ET(I13<s;J13<=s);J13-I13;SI(ET(I13<s;J13>s;J13<=v);s-I13;SI(
ET(I13=s;J13>s;J13<=v);0;SI(ET(I13>s;I13<v;J13<s);J13-v+1;SI(ET(I13>=s;I13< v;J13>=s;J13<=v);0;))))))

Fin 2 :
SI(ET(I13>=s;I13<=v;J13>v);J13-v;SI(ET(I13>=v;J13<s);1-I13+J13;SI(ET(I13=v;J
13>v);J13-v;SI(ET(I13>v;J13=s);J13+1-I13;SI(ET(I13>v;J13>v);J13-I13;)))))

Début 3 :
SI(K13=L13;0;SI(ET(K13<s;L13<=s);L13-K13;SI(ET(K13<s;L13>s;L13<=v);s-K13;SI(
ET(K13=s;L13>s;L13<=v);0;SI(ET(K13>s;K13<v;L13<s);L13-v+1;SI(ET(K13>=s;K13< v;L13>=s;L13<=v);0;))))))

Fin 3 :
SI(ET(K13>=s;K13<=v;L13>v);L13-v;SI(ET(K13>=v;L13<s);1-K13+L13;SI(ET(K13=v;L
13>v);L13-v;SI(ET(K13>v;L13=s);L13+1-K13;SI(ET(K13>v;L13>v);L13-K13;)))))

En voici une qui n'est pas simple non plus. :-)

Avec
A1: 19:00
B1: 22:00
C1: 22:30
D1: 01:35
E1: 04:30
F1: 06:30
Haut: 21:00 ' dans une cellule nommée ou par un nom directement
Bas : 06:00 ' idem


=MAX(0;MIN(B1;Bas)-SI(B1>A1;A1;MIN(0;A1-Bas)))+MAX(0;1-MAX(Haut;A1)-SI(B1>A1

;1-B

1;MIN(0;Haut-B1)))+

MAX(0;MIN(D1;Bas)-SI(D1>C1;C1;MIN(0;C1-Bas)))+MAX(0;1-MAX(Haut;C1)-SI(D1>C1;

1-D1

;MIN(0;Haut-D1)))+

MAX(0;MIN(F1;Bas)-SI(F1>E1;E1;MIN(0;E1-Bas)))+MAX(0;1-MAX(Haut;E1)-SI(F1>E1;

1-F1

;MIN(0;Haut-F1)))

Format: [h]:mm

Tu remarqueras que c'est la somme de 3 fois la même formule (1
fois/vacation).

Après l'avoir testée, elle fonctionne à condition que les 3 plages de la
vacation soient utilisées, ce qui n'est hélas pas forcément le cas.

Il y a probablement moyen de faire plus court mais j'ai pas trouvé et il
fait beau.

*Ici, il est 21:56 et il fait nuit*

Si tu es patient, peut-être y arriverai-je (ou un/e autre)... :-)

*Je confirme, je suis patient. C'est un classeur que j'ai mis il y a quelque

temps sur le site de Misange, et que je souhaite améliorer*


--
Bien cordialement,
==================================== P. Bastard.
Pour me contacter, remplacez "Chez" par @ dans l'adresse

Une foule de conseils utiles sur le site de Misange :
http://www.excelabo.net

Bonjour à toi aussi, *Daniel.M*.


Et merci de ta proposition.

Je ne sais pas à quel point ta formule est compliquée.

La formule, que j'utilise actuellement fait la somme des fins moins la
somme des débuts calculés comme suit :
(s et v : valeurs 0.25 et 0.875, soit 6:00 et 21:00)

Début 1 :
SI(G13=H13;0;SI(ET(G13<s;H13<=s);H13-G13;SI(ET(G13<s;H13>s;H13<=v);s-G13;SI(
ET(G13=s;H13>s;H13<=v);0;SI(ET(G13>s;G13<v;H13<s);H13-v+1;SI(ET(G13>=s;G13< v;H13>=s;H13<=v);0;))))))

Fin 1 :
SI(ET(G13>=s;G13<=v;H13>v);H13-v;SI(ET(G13>=v;H13<s);1-G13+H13;SI(ET(G13=v;H
13>v);H13-v;SI(ET(G13>v;H13=s);H13+1-G13;SI(ET(G13>v;H13>v);H13-G13;)))))

Début 2 :
SI(I13=J13;0;SI(ET(I13<s;J13<=s);J13-I13;SI(ET(I13<s;J13>s;J13<=v);s-I13;SI(
ET(I13=s;J13>s;J13<=v);0;SI(ET(I13>s;I13<v;J13<s);J13-v+1;SI(ET(I13>=s;I13< v;J13>=s;J13<=v);0;))))))

Fin 2 :
SI(ET(I13>=s;I13<=v;J13>v);J13-v;SI(ET(I13>=v;J13<s);1-I13+J13;SI(ET(I13=v;J
13>v);J13-v;SI(ET(I13>v;J13=s);J13+1-I13;SI(ET(I13>v;J13>v);J13-I13;)))))

Début 3 :
SI(K13=L13;0;SI(ET(K13<s;L13<=s);L13-K13;SI(ET(K13<s;L13>s;L13<=v);s-K13;SI(
ET(K13=s;L13>s;L13<=v);0;SI(ET(K13>s;K13<v;L13<s);L13-v+1;SI(ET(K13>=s;K13< v;L13>=s;L13<=v);0;))))))

Fin 3 :
SI(ET(K13>=s;K13<=v;L13>v);L13-v;SI(ET(K13>=v;L13<s);1-K13+L13;SI(ET(K13=v;L
13>v);L13-v;SI(ET(K13>v;L13=s);L13+1-K13;SI(ET(K13>v;L13>v);L13-K13;)))))

En voici une qui n'est pas simple non plus. :-)

Avec
A1: 19:00
B1: 22:00
C1: 22:30
D1: 01:35
E1: 04:30
F1: 06:30
Haut: 21:00 ' dans une cellule nommée ou par un nom directement
Bas : 06:00 ' idem

=MAX(0;MIN(B1;Bas)-SI(B1>A1;A1;MIN(0;A1-Bas)))+MAX(0;1-MAX(Haut;A1)-SI(B1>A
1;1-B

1;MIN(0;Haut-B1)))+
MAX(0;MIN(D1;Bas)-SI(D1>C1;C1;MIN(0;C1-Bas)))+MAX(0;1-MAX(Haut;C1)-SI(D1>C1
;1-D1

;MIN(0;Haut-D1)))+
MAX(0;MIN(F1;Bas)-SI(F1>E1;E1;MIN(0;E1-Bas)))+MAX(0;1-MAX(Haut;E1)-SI(F1>E1
;1-F1

;MIN(0;Haut-F1)))

Format: [h]:mm

Tu remarqueras que c'est la somme de 3 fois la même formule (1
fois/vacation).

Après l'avoir testée, elle fonctionne à condition que les 3 plages de la
vacation soient utilisées, ce qui n'est hélas pas forcément le cas.

Il y a probablement moyen de faire plus court mais j'ai pas trouvé et il
fait beau.

*Ici, il est 21:56 et il fait nuit*

Si tu es patient, peut-être y arriverai-je (ou un/e autre)... :-)

*Je confirme, je suis patient*

C'est un classeur que j'ai mis il y a quelque temps sur le site de Misange,
et que je souhaite améliorer.

--
Bien cordialement,
==================================== P. Bastard.
Pour me contacter, remplacez "Chez" par @ dans l'adresse

Une foule de conseils utiles sur le site de Misange :
http://www.excelabo.net

Bonsoir à vous deux.


A partir de la proposition de Daniel,

=SI(A1="";0;MAX(0;MIN(B1;Bas)-SI(B1>A1;A1;MIN(0;A1-Bas)))+MAX(0;1-MAX(Haut;A
1)-SI(B1>A1;1-B1;MIN(0;Haut-B1))))
+SI(C1="";0;MAX(0;MIN(D1;Bas)-SI(D1>C1;C1;MIN(0;C1-Bas)))+MAX(0;1-MAX(Haut;C
1)-SI(D1>C1;1-D1;MIN(0;Haut-D1))))
+SI(E1="";0;MAX(0;MIN(F1;Bas)-SI(F1>E1;E1;MIN(0;E1-Bas)))+MAX(0;1-MAX(Haut;E
1)-SI(F1>E1;1-F1;MIN(0;Haut-F1))))
semble fonctionner pour calculer la part d'heures de nuit d'une vacation.

Bas =0.25 (soit heure de fin des heures de nuit : 6h00)
Haut =0.875 (soit heure de début des heures de nuit :21h00)
En A1: Début 1° plage horaire
En B1: Fin 1° plage horaire
En C1: Début 2° plage horaire
En D1: Fin 2° plage horaire
En E1: Début 3° plage horaire
En F1: Fin 3° plage horaire.

Encore merci.

Je suis bien entendu preneur au cas où vos productives méninges
accoucheraient d'une formule encore plus expurgée.
;-))

--
Bien cordialement,
==================================== P. Bastard.
Pour me contacter, remplacez "Chez" par @ dans l'adresse

Une foule de conseils utiles sur le site de Misange :
http://www.excelabo.net

Salut Fx,

Lorsque E1 commence à l'extérieur des heures, c'est problématique.

19:00 22:00 22:30 01:35 06:15 06:30
Comptabilise ce temps en moins dans le résultat final.

Salutations,

Daniel M.

Bonsoir Maître Daniel,

Tout à fait exact. Nous disons "Et un bug, un !"
Alors patchons ... ça me rappelle des choses :o)

Public Function hn(inp As Range)
Application.Volatile True
deb = "21:00": fin = "6:00"

'conversion heures limites
deb = CDbl(TimeValue(deb))
fin = CDbl(TimeValue(fin))
'heure moyenne servant à variables b & c
moy = (deb + fin) / 2
'passage jour J+1 éventuel
If fin < deb Then fin = fin + 1

hn = 0
For a = 1 To 5 Step 2
'variable pour détection 2 valeurs hors des limites
Tag = False
'récupération des valeurs
b = inp(a + 0): c = inp(a + 1)
'changement jour suivant heure
If b < moy Then b = b + 1
If (c < b) Or (c < moy) Then c = c + 1

'rejet si deux valeurs horslimites ou cellule(s) vide(s)
If ((b < deb) And (c < deb)) Then Tag = True
If ((b > fin) And (c > fin)) Then Tag = True
If IsEmpty(inp(a + 0)) Then Tag = True
If IsEmpty(inp(a + 1)) Then Tag = True

'ajustement des valeurs f(limites) ?
If Tag = False Then
If b < deb Then b = deb
If c > fin Then c = fin
hn = hn + c - b
End If
Next a
End Function

Un exemple de cas non géré :
De 19:00 à 23:00, De 22:30 à 1:35, De 0:20 à 6:30
(début de période avant la fin de période précédente)

@+
FxM

Salut Fx,

Merci pour le code.

1. Autre cas non-géré (mais plausible celui-là)
13:00 22:00 22:30 01:35 04:30 06:30 'causera des difficultés
alors que
14:00 22:00 22:30 01:35 04:30 06:30 ' passe

Ce genre de problème génère plus de 70 cas possibles si on teste les bornes
explicitement.
AMA, la stratégie consistant à déterminer une heure mitoyenne est condamnée à
l'échec puisqu'il y aura toujours une heure se situant en deça (ou au-dessus)
qui aménera une comptabilisation erronée du temps (mais tu peux me confondre,
pas de problème) :-)

2.
T'as pas besoin d'Application.Volatile.

3.
Au lieu de
deb = "21:00"
CDbl(TimeValue(deb))
pourquoi pas:
deb = TimeSerial(21,0,0)

Salutations,

Daniel M.

A partir de la proposition de Daniel,
...
semble fonctionner pour calculer la part d'heures de nuit d'une vacation.

Encore merci.

Je suis bien entendu preneur au cas où vos productives méninges
accoucheraient d'une formule encore plus expurgée.
;-))

Bienvenue Patrick.
On va regarder cela. Es-tu plus favorable à une solution VBA?

Salutations,

Daniel M.

Bonsoir Patrick

Bonjour, toutes et tous.

Je cherche à calculer le nombre d'heures de nuit (entre 21:00 et 6:00)
pour

des positions de travail pouvant avoir 3 vacations par jour.
Par exemple : De 19:00 à 22:00, De 22:30 1:35, De 4:30 à 6:30 (les
horaires

sont sur la même ligne, dans des colonnes adjacentes).
*Résultat attendu 5:35*
J'ai monté une usine à gaz avec 4 colonnes intermédiaires contenant des
formules avec des si, et, ou imbriqués. Ca marche, mais c'est lourd...

Si l'un d'entre vous pouvait m'aiguiller sur une solution plus légère
(et

plus élégante), ma reconnaissance n'aurait d'égal que mon admiration.
;-))

Je me lance.
±-SI(A1>«S("21:00");A1;ABS("21:00"))+"24:00"-C1+D1-E1+SI(F1<«S("6:00")
;F1;ABS("6:00"))
--
.

Fred