Séparation HO-HNO sous excel

Résolu
micbru14 Messages postés 24 Date d'inscription   Statut Membre Dernière intervention   -  
micbru14 Messages postés 24 Date d'inscription   Statut Membre Dernière intervention   - 4 mars 2010 à 17:48
Bonjour,
J'ai un tableau d'alarmes qui me synthétise l'indisponibilité d'équipement en notant l'heure de coupure et l'heure de rétablissement de celui-ci.
J'ai donc actuellement un tableau dans lequel je calcule la différence d'heures entre deux cellules au format "jj/mm/aaa hh:mm".
Ma cellule résultat est au format [hh]:mm:ss et je peux donc avoir des résultats comme "112:45:32" si la coupure de l'équipement dure plusieurs jours
Je souhaite améliorer ce tableau et obtenir 2 résultats de cette différence:
- dans une cellule la durée de coupure des heures non ouvrables (HNO) à savoir, en semaine de 18h00 à 8h00 le lendemain ainsi que le week-end du vendredi 18h00 au lundi 8h00
- dans la cellule d'à côté la durée de coupure en heures ouvrables (HO) à savoir du lundi au vendredi de 8h00 à 18h00

Merci de votre aide

A bientôt.
A voir également:

13 réponses

Réponse 1 / 13
Le Pingou Messages postés 12242 Date d'inscription   Statut Contributeur Dernière intervention   1 458
 
Bonjour,
Est-il possible d'avoir un exemple de votre tableau ?
0
micbru14 Messages postés 24 Date d'inscription   Statut Membre Dernière intervention  
  
Voici un extrait de mon tableau:

la formule tapée colonne D est :  =SI(D4="Contact Established";B4-B3;"")

le format des cellules colonne A est: jj/mm/aaaa hh:mm

le format des cellules colonne D est: [h]:mm

 

        A           B               C                      D       

1  Date - Heure  Eqpt           Evenement              Coupure

2  2/2/10 8:00    toto          contact lost               

3  2/2/10 8:35    toto      contact established        0:35

4  3/2/10 13:10   titi           contact lost               

5  3/2/10 19:15   titi       contact established        6:05

6  5/2/10 14:00   tata          contact lost               

7  8/2/10 9:10    tata      contact established        67:10
 

Ce que je souhaiterais obtenir ce présente comme l'exemple ci dessous:

- Exemple 1, on est un jour de semaine (mardi) et la coupure est entre 8h et 18h.

- Exemple 2, on est un jour de semaine (mercredi) et la coupure est entre 13h10 et 19h15 
                                         soit 4h50 en HO de 13h10 à 18h00 et 1h15 en HNO après 18h00

- Exemple 3, la coupure est du vendredi avant 18:00 jusqu'au lundi après 8h00
                         soit  4h00 HO avant 18h le vendredi + 1h10 HO après 8h le lundi et 
                        et 62h00 HNO du vendredi 18h au lundi 8h00

- Exemple 4, la coupure à lieu entre le samedi et le dimanche soit uniquement des HNO


        A           B                C                  D          E         

1  Date - Heure  Eqpt         Evenement                HO         HNO

2  2/2/10 8:00    toto          contact lost               

3  2/2/10 8:35    toto      contact established        0:35      0:00

4  3/2/10 13:10    titi         contact lost               

5  3/2/10 19:15    titi      contact established      4:50      1:15

6  5/2/10 14:00   tata          contact lost               

7  8/2/10 9:10    tata      contact established        5:10      62:00


J'ai essayé de changer le format de date colonne A en mettant du type prédéfini:  *mercredi 14 mars 2001

juste pour m'assurer qu'excel a bien la notion du jour de la semaine et conserve l'heure même si elle ne s'affiche
que dans le volet de lecture.

Merci de votre aide
0
micbru14 Messages postés 24 Date d'inscription   Statut Membre Dernière intervention  
 
Désolé, mais je viens de m'apercevoir que le résultat de la mise en page de ma réponse est déplorable et donc incompréhensible. Je vais essayer d'y remédier en écrivant le contenu des cellules les uns à la suite des autres.
Il vous faudra interpréter car je n'ai pas trouvé comment intégrer des tableaux excel à mes messages.
1er tableau:

La formule tapée colonne D est : =SI(D4="Contact ok";B4-B3;"")
Le format des cellules colonne A est: jj/mm/aaaa hh:mm
Le format des cellules colonne D est: [h]:mm

A1:"Date-heure", B1:"Eqpt", C1:"contact", D1:"Durée coupure"
A2:"02/02/10 08:00", B2:"toto", C2:"contact lost", D2:""
A3:"02/02/10 08:35", B3:"toto", C3:"contact ok", D3:"0:35"
A4:"03/02/10 13:10", B4:"tata", C4:"contact lost", D4:""
A5:"03/02/10 19:15", B5:"tata", C5:"contact ok", D5:"6:05"
A6:"05/02/10 14:00", B6:"tutu", C6:"contact lost", D6:""
A7:"08/02/10 09:10", B7:"tutu", C7:"contact ok", D7:"67:10"
A8:"06/02/10 14:00", B8:"tutu", C8:"contact lost", D8:""
A9:"07/02/10 09:10", B9:"tutu", C9:"contact ok", D9:"67:10"

Ce que je souhaiterais obtenir ce présente comme l'exemple ci dessous:
- Exemple 1, on est un jour de semaine (mardi) et la coupure est entre 8h et 18h.
- Exemple 2, on est un jour de semaine (mercredi) et la coupure est entre 13h10 et 19h15
soit 4h50 en HO de 13h10 à 18h00 et 1h15 en HNO après 18h00
- Exemple 3, la coupure est du vendredi avant 18:00 jusqu'au lundi après 8h00
soit 4h00 HO avant 18h le vendredi + 1h10 HO après 8h le lundi et
et 62h00 HNO du vendredi 18h au lundi 8h00
- Exemple 4, la coupure à lieu entre le samedi et le dimanche soit uniquement des HNO

A1:"Date-heure", B1:"Eqpt", C1:"contact", D1:"H0", E1:"HNO"
A2:"02/02/10 08:00", B2:"toto", C2:"contact lost", D2:"", E2:""
A3:"02/02/10 08:35", B3:"toto", C3:"contact ok", D3:"0:35", E3:"0:00"
A4:"03/02/10 13:10", B4:"tata", C4:"contact lost", D4:"", E4:""
A5:"03/02/10 19:15", B5:"tata", C5:"contact ok", D5:"4:50", E5:"1:15"
A6:"05/02/10 14:00", B6:"tutu", C6:"contact lost", D6:"", E6:""
A7:"08/02/10 09:10", B7:"tutu", C7:"contact ok", D7:"5:10", E7:"62:00
A8:"06/02/10 14:00", B8:"tutu", C8:"contact lost", D8:"", E8:""
A9:"07/02/10 09:10", B9:"tutu", C9:"contact ok", D9:"0:00", E9:"19:10"

J'ai essayé de changer le format de date colonne A en mettant du type prédéfini: "*mercredi 14 mars 2001" juste pour m'assurer qu'excel a bien la notion du jour de la semaine et conserve l'heure même si elle ne s'affiche que dans le volet de lecture.

J'espère que ce message est un peu plus clair et Merci de votre aide
0
Réponse 2 / 13
Le Pingou Messages postés 12242 Date d'inscription   Statut Contributeur Dernière intervention   1 458
 
Bonjour,
Merci je vais examiner le tout et une réponse d'ici à demain.
Note:
il est aussi possible de mettre le fichier(tableau) sur https://www.cjoint.com/ et poster le lien, c'est plus simple.
0
Réponse 3 / 13
Le Pingou Messages postés 12242 Date d'inscription   Statut Contributeur Dernière intervention   1 458
 
Bonjour,
Voici ma proposition, en espérant que se soit dans le bon sens : https://www.cjoint.com/?clxBk2ZP3C
0
Réponse 4 / 13
micbru14 Messages postés 24 Date d'inscription   Statut Membre Dernière intervention  
 
Bonjour,
"Chapeau bas" votre solution fonctionne à merveille et si j'osais, j'abuserais encore un peu de vos connaissances pour savoir quelles modifications il faudrait apporter à la macro pour considérer le samedi comme un jour ouvrable également.
En tous cas merci beaucoup.

Salutations.

Micbru14
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
Réponse 5 / 13
Le Pingou Messages postés 12242 Date d'inscription   Statut Contributeur Dernière intervention   1 458
 
Bonjour,
Modifier cette ligne :
If Weekday(datdeb + c) = 1 Or Weekday(datdeb + c) = 7 Then ' Dimanche / Samedi (non ouvrable)
If Weekday(datdeb + c) = 1 Then ' Dimanche (non ouvrable)
0
Nono
 
Génial !
0
Réponse 6 / 13
micbru14 Messages postés 24 Date d'inscription   Statut Membre Dernière intervention  
 
Bonjour,
désolé d'avoir tardé à vous remercier (vacances!).
C'est impeccable
Merci bcp
0
Réponse 7 / 13
micbru14 Messages postés 24 Date d'inscription   Statut Membre Dernière intervention  
 
Bonjour,
je me permets de vous recontacter car je viens de rencontrer un pb avec la macro.
En effet lorsque les coupures sont en semaine et en heures non ouvrables, cela me signale des erreurs. Un petit message me signale des heures négatives (?????).
Le phénomène ne se produit pas quand les coupures sont le week-end même entre 18h00 et 8h00.
Je vous ai mis cet exemple dans le fichier joint. https://www.cjoint.com/?dbnGZ8JlTf
Pouvez-vous m'aider s'il vous plait.

Cordialement
0
Réponse 8 / 13
Le Pingou Messages postés 12242 Date d'inscription   Statut Contributeur Dernière intervention   1 458
 
Bonjour,
Merci de prendre cette version corrigée : https://www.cjoint.com/?dbwXLvKUwJ
0
Réponse 9 / 13
Le Pingou Messages postés 12242 Date d'inscription   Statut Contributeur Dernière intervention   1 458
 
Bonjour micbru14,
Je vous ais mis le mauvais fichier, désolé, merci de voir le MP
0
Réponse 10 / 13
micbru14 Messages postés 24 Date d'inscription   Statut Membre Dernière intervention  
 
Bonjour,
j'ai encore un pb avec la macro (voir: http://cjoint.com/?ddljaFbYjE).
Le pb des heures négatives est maintenant dans la colonne HNO.
Le premier essai que j'ai fait était ok mais la coupure avait lieu sur une même journée.
Il semblerait que ça ne marche pas quand la coupure est à cheval sur 2 dates.

Merci de votre aide

Cordialement
0
Réponse 11 / 13
Le Pingou Messages postés 12242 Date d'inscription   Statut Contributeur Dernière intervention   1 458
 
Bonjour,
Est-ce la version que je vous ai envoyée via le MP ?
0
Réponse 12 / 13
Le Pingou Messages postés 12242 Date d'inscription   Statut Contributeur Dernière intervention   1 458
 
Bonjour,
Remplacer la procédure existante par la suivante : 
Function NbHeuJourOuv(dd, df, hd, hf)
Dim datdeb, datfin, hdeb, hfin, nbj
Dim houvd, houvf, hnhjo
Dim c As Byte, test, fin
houvd = CDate(hd)
houvf = CDate(hf)
datdeb = dd
datfin = df
hdeb = datdeb - 1 * Int(datdeb / 1)
hfin = datfin - 1 * Int(datfin / 1)
nbj = DateDiff("d", datdeb, datfin)
For c = 0 To nbj
test = datdeb + c
If Weekday(datdeb + c) = 1 Or Weekday(datdeb + c) = 7 Then
'    MsgBox "Samedi / Dimanche"
Else
'    MsgBox "ouvrable"
    If nbj = 0 Then
        If houvd < hdeb And hfin <= houvf Then
            hnhjo = hfin - hdeb
        ElseIf hdeb < houvd And (hfin > houvd And hfin <= houvf) Then
            hnhjo = (hfin - houvd)
        ElseIf (hdeb >= houvd And hdeb < houvf) And hfin > houvf Then
            hnhjo = houvf - hdeb
        ElseIf hdeb < houvd And hfin > houvf Then
            hnhjo = houvf - houvd
        End If
    Else
        If test = datdeb Then
            If houvd < hdeb And hdeb < houvf Then
                hnhjo = hnhjo + houvf - hdeb
            ElseIf hdeb < houvd Then
                hnhjo = hnhjo + (houvf - houvd)
            End If
        ElseIf Format(test, "dd mm yyyy") = Format(datfin, "dd mm yyyy") Then
            If houvd < hfin And hfin < houvf Then
                hnhjo = hnhjo + hfin - houvd
            ElseIf hfin > houvf Then
                hnhjo = hnhjo + (houvf - houvd)
            End If
        Else
            hnhjo = hnhjo + (houvf - houvd)
        End If
    End If
End If
Next c
NbHeuJourOuv = hnhjo
End Function
0
Réponse 13 / 13
micbru14 Messages postés 24 Date d'inscription   Statut Membre Dernière intervention  
 
Bonjour,
tout est "nickel" maintenant;
Encore merci bcp de votre aide.

Cordialement.

Michel
0