Discussion :

[NicoTico]

Bonjour à tous
J’ai l’habitude d’utiliser votre forum pour me dépanner, mais là, je ne trouve pas de solution…
Je travaille sur Excel 2010 dans la maintenance industrielle et je manipule de gros volume d’heure de fonctionnement.

Je n’avais jamais rencontré de problème coriace en utilisant le format [h]:mm pour la manipulation d’heures jusqu’à présent.
Or, lorsque je saisi des heures supérieures à 10000:00, Excel ne reconnait plus le format et les formules de calculs se mettent en erreur.
En revanche si ces heures sont des résultats de calculs, Excel ne pose pas de problème.

Un exemple d’utilisation serait :
Une machine ayant un volume dont la maintenance dois se faire toute les 15 000 heures de fonctionnement et dont le total en fonctionnement est de 8 000 heures, le but de la manœuvre est de visualiser ne nombre d’heure de fonctionnement qu’il reste à la machine avant la maintenance.

Evidemment nous n’utilisons que ce format : [h]:mm, l’utilisation de l’écriture en centième (38:30 = 38,50 ) est donc prohibé même si cela faciliterai grandement les calculs.

Pour l’instant j’ai trouvé comme solution la conversion manuelle en date et heure pour qu’Excel reconnaisse le format : « 20000:00 » = « 12/04/1902 08:00:00 », mais la recherche de la date précise est assez fastidieuse et donc pas optimale.

Si vous auriez une solution, je suis preneur.

Cordialement

[njhub]

Abordez le problème différemment,

Dans quelle mesure avez vous besoin d'afficher les heures, minutes, secondes pour des durées aussi longues.
Je pense que les calculs ordinaires suffisent, quitte à convertir le résultat au format horaire, quand on approche de l'échéance à 5% près.
Quand l'un de vos appareils à servi pendant 14 250 H/15 000 H , il reste 750H, plus d'un mois de service continu avant l'échéance.

La solution pourrait être :

En B2, l'échéance et en C2 la durée utilisée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(B2;-C2)&":00"

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(C2/B2>0,95;TEXTE(SOMME(B2;-C2)/24;"[hh]");SOMME(B2;-C2))&":00"

[NicoTico]

Bonjour njhub et merci de votre réponse

Dans quelle mesure avez vous besoin d'afficher les heures, minutes, secondes pour des durées aussi longues.

dans la mesure ou nous avons un programme de maintenance complexe avec des maintenances gérées à différentes échelles, (15h, 150h, 1500h et maintenant 15000h) nous avons donc une gestion du temps de fonctionnement des machines assez précise, (de l'ordre des minutes).

il est vrai que je n'est pas préciser que ces entrées Excel sont utilisé par un autre logiciel, et ces pourquoi l'utilisation de ce format est nécessaire.

néanmoins la piste du 5% est très intéressante et la formule simple à utiliser, merci beaucoup

[Qwazerty]

Salut

en effet c'est curieux comme fonctionnement.
Si on tape 12000:00, il ne le reconnait plus comme un format heure. Par contre si je tape 500 (500jours), il convertie bien au format heure, note 12000:00 et le contenu est bien prit en charge comme étant un format heure.

Évidemment nous n’utilisons que ce format : [h]:mm, l’utilisation de l’écriture en centième (38:30 = 38,50 ) est donc prohibé même si cela faciliterai grandement les calculs.

Vous utilisez déjà les centièmes 1 représente une journée et Excel le convertie en 24:00 via le format. Si tu tapes directement une valeur en centième dans la cellule formatée en [h]:mm tu obtiendras bien le bon résultat, y compris au delà de 10000:00 (par exemple 500 pour avoir 12000:00).

Peux-tu donner plus de détails sur la structure du tableau.

[Edit]
Peut-être une fonction perso avec cette tête là, elle est perfectible, je n'ai pas fait de contrôle de contenu par exemple, j'espère ne pas m'être trompé sur la conversion de format.
J'ai juste fait un test de soustraction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Option Explicit
Public Enum enOperation
    opAddition = 1
    opSoustraction = 2
    opMultiplication = 3
    opDivision = 4
End Enum
 
Public Function testOperationDate(Date1 As Range, Date2 As Range, Optional Operation As enOperation = opSoustraction)
Dim aDate As Double
Dim bDate As Double
 
'On regarde si la valeur contient ":"
'En temps normal (en dessous de 10000:00 donc) Value est en forme décimale
'Au delà Value contient du texte
If InStr(1, Date1.Value, ":") Then
    'On convertie en centième de jour
    aDate = CDbl(Split(Date1.Value, ":")(0)) / 24 + CDbl(Split(Date1.Value, ":")(1)) * 60 / 100
Else
    'On conserve la valeur renvoyé par excel (100ème de jour)
    aDate = Date1.Value
End If
 
'Idem pour la deuxième date
If InStr(1, Date2.Value, ":") Then
    bDate = CDbl(Split(Date2.Value, ":")(0)) / 24 + CDbl(Split(Date2.Value, ":")(1)) * 60 / 100
Else
    bDate = Date2.Value
End If
 
'On prend en compte l'opération demandée
Select Case Operation
    Case opAddition
        testOperationDate = aDate + bDate
    Case opSoustraction
        testOperationDate = aDate - bDate
        If testOperationDate < 0 Then testOperationDate = [na()]
    Case opMultiplication
        testOperationDate = aDate * bDate
    Case opDivision
        testOperationDate = aDate / bDate
End Select
 
'On demande à excel de ne recalculer la formule que sur modification d'une des cellules datex
Application.Volatile False
End Function

Ce code doit être placé dans un Module (Alt+F11 pour lancer VBE, puis insertion, puis Module). Le module apparait dans l'arborescence du projet, double-cliques dessus pour ouvrir le Module et colle le contenu à l'intérieur.
Pour l'utiliser il suffit de faire comme avec n'importe quelle fonction intégrée d'Excel

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= testOperationDate(A1;A2;2)

Deux remarques

• Il est sans doute possible de faire la même chose avec une formule
• Il faudra enregistrer le fichier au format Macro (xlsm) et du coup il y aura un message d'alerte à chaque lancement de fichier pour demander l'autorisation d'activation des Macros

[/Edit]

[Edit2]
Bon il y a surement mieux mais je ne suis pas très doué avec les formules :p

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(SIERREUR(CHERCHE(":";H15);0)=0;H15;CNUM(GAUCHE(H15;CHERCHE(":";H15)-1))/24 + SIERREUR(CNUM(STXT(H15;CHERCHE(":";H15)+1;CHERCHE(":";H15;CHERCHE(":";H15)+1)-(CHERCHE(":";H15)+1)));NBCAR(H15))*60/100)-SI(SIERREUR(CHERCHE(":";I15);0)=0;I15;CNUM(GAUCHE(I15;CHERCHE(":";I15)-1))/24 + SIERREUR(CNUM(STXT(I15;CHERCHE(":";I15)+1;CHERCHE(":";I15;CHERCHE(":";I15)+1)-(CHERCHE(":";I15)+1)));NBCAR(I15))*60/100)

Les données se trouve en H15 et I15, le type de données acceptés sont centièmes de jour et format [h]:00 (ou [h]:00:00 mais ne tient pas compte des secondes) en format texte.
[/Edit2]
++Qwaz

[Qwazerty]

Salut

Sinon, plus simple avec macro, une fonction qui fait juste la conversion en jour décimal

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Public Function ConvertCentiH(DateAConvertir As Range)
'Exemple d'utilisation
'=ConvertCentiH(H15)-ConvertCentiH(I15)
 
    'On demande à excel de ne recalculer la formule que sur modification d'une des cellules datex
    Application.Volatile False
 
    'On regarde si la valeur contient ":"
    'En temps normal (en dessous de 10000:00 donc) Value est en forme décimale
    'Au delà Value contient du texte
    If InStr(1, DateAConvertir.Value, ":") Then
        'On convertie en centième de jour
        ConvertCentiH = CDbl(Split(DateAConvertir.Value, ":")(0)) / 24 + CDbl(Split(DateAConvertir.Value, ":")(1)) * 60 / 100
    Else
        'On conserve la valeur renvoyé par excel (100ème de jour)
        ConvertCentiH = DateAConvertir.Value
    End If
End Function

++
Qwaz

[unparia]

Bonjour
Je n'ai personnellement pas compris le sens de cette phrase :

Une machine ayant un volume dont la maintenance dois se faire toute les 15 000 heures de fonctionnement et dont le total en fonctionnement est de 8 000 heures, le but de la manœuvre est de visualiser ne nombre d’heure de fonctionnement qu’il reste à la machine avant la maintenance.

L'une des deux parties colorées doit être modifiée pour qu'elle soit compréhensible. Laquelle et comment ?

[NicoTico]

Bonjour
Je n'ai personnellement pas compris le sens de cette phrase :

L'une des deux parties colorées doit être modifiée pour qu'elle soit compréhensible. Laquelle et comment ?

je m'excuse de mon manque de clarté:

prenons :
une maintenance nommée A, (graissage, vérification, etc...) et devant être faite toute les 15 000 heures de fonctionnement
une machine totalisant aujourd'hui et à cette heure 8 000 heures de fonctionnement

Le but de la manœuvre est de visualiser ne nombre d’heure de fonctionnement restant à la machine avant la maintenance A.

[NicoTico]

Salut

Sinon, plus simple avec macro, une fonction qui fait juste la conversion en jour décimal

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Public Function ConvertCentiH(DateAConvertir As Range)
'Exemple d'utilisation
'=ConvertCentiH(H15)-ConvertCentiH(I15)
 
    'On demande à excel de ne recalculer la formule que sur modification d'une des cellules datex
    Application.Volatile False
 
    'On regarde si la valeur contient ":"
    'En temps normal (en dessous de 10000:00 donc) Value est en forme décimale
    'Au delà Value contient du texte
    If InStr(1, DateAConvertir.Value, ":") Then
        'On convertie en centième de jour
        ConvertCentiH = CDbl(Split(DateAConvertir.Value, ":")(0)) / 24 + CDbl(Split(DateAConvertir.Value, ":")(1)) * 60 / 100
    Else
        'On conserve la valeur renvoyé par excel (100ème de jour)
        ConvertCentiH = DateAConvertir.Value
    End If
End Function

++
Qwaz

salut Qwaz et merci pour ton implication!

ta dernière fonction ma été très utile et m'a permis d'aboutir à cette macro ( améliorable j'imagine) mais qui répond bien a ma demande !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
Sub convertionDEdate()
 
Dim DateAConvertir As Range
Dim a As Integer, b As Integer
Dim ConvertCentiH As Double
 
For Each DateAConvertir In Selection
If DateAConvertir <> "" Then
 
On Error GoTo ErrorCase ' en cas d'erreur la variable IfError passe à Vrai sans arrêter le déroulement de la macro
    'On regarde si la valeur contient ":"
    'En temps normal (en dessous de 10000:00 donc) Value est en forme décimale
    'Au delà Value contient du texte
    If InStr(1, DateAConvertir.Value, ":") Then
        'On convertie en centième de jour
 
        a = Split(DateAConvertir.Value, ":")(0)
        b = Split(DateAConvertir.Value, ":")(1)
        ConvertCentiH = CDbl(a) / 24 + CDbl(b) * 60 / 100
 
    Else
        'On conserve la valeur renvoyé par excel (100ème de jour)
        ConvertCentiH = DateAConvertir.Value
    End If
 
    If IfError = False Then
        DateAConvertir.Value = ConvertCentiH
    Else:                                                                                            'en cas d'erreur détectée la cellule ne change pas de valeur mais est repérée
                                                                                                       'par sa ligne et son contenue afin d'etre traité ( dans mon cas des informations
                                                                                                       'importantes peuvent parfois se glissées dans ces cellules)
                                                                                                       ' retour à Faux pour IfError
        MsgBox DateAConvertir.Value & " ligne : " & DateAConvertir.Row
        IfError = False
    End If
 
End If
'réinitialisation des variables
a = Empty
b = Empty
ConvertCentiH = Empty
 
Next
Exit Sub
 
ErrorCase:
    IfError = True
Resume Next
End Sub

Edit :
en utilisant des variable long plutôt qu'integer pour les variables a et b nous ne somme pas limité a ~32700 heures !

[Pierre Fauconnier]

Salut.

Si tu saisis 625 au format [h]:mm, ça t'affichera bien 15000:00. Ca ne pourrait pas être un pis-aller?


[EDIT]

[...]
Pour l’instant j’ai trouvé comme solution la conversion manuelle en date et heure pour qu’Excel reconnaisse le format : « 20000:00 » = « 12/04/1902 08:00:00 », mais la recherche de la date précise est assez fastidieuse et donc pas optimale.[...]

Si tu dois saisir 20000 heures, tu saisis =20000/24 et tu valides avec F9 puis ENTER => transformation de la formule en valeur puis validation. Tu n'as alors plus besoin de chercher la date précise...

[Qwazerty]

Salut

Proposition de modification de ta fonction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Sub convertionDEdate()
 
Dim DateAConvertir As Range
Dim a As Long, b As Byte
Dim ConvertCentiH As Double
 
    On Error GoTo ErrorCase
 
    For Each DateAConvertir In Selection
        If DateAConvertir.Value <> "" Then
 
            'On regarde si la valeur contient ":"
            'En temps normal (en dessous de 10000:00 donc) Value est en forme décimale
            'Au delà Value contient du texte
            If InStr(1, DateAConvertir.Value, ":") Then
                'On convertie en centième de jour
 
                a = Split(DateAConvertir.Value, ":")(0)
                b = Split(DateAConvertir.Value, ":")(1)
                DateAConvertir.Value = CDbl(a) / 24 + CDbl(b) * 60 / 100
            End If
 
        End If
NextRange:
    Next
 
    Exit Sub
 
ErrorCase:
    'On passe la couleur de fond en rouge
    DateAConvertir.Interior.ColorIndex = 3
    GoTo NextRange
End Sub

Pour info, inutile de réinitialiser à Empty les variables, de toute façon leur contenu sera écrasé à la boucle suivante quand les valeur de Split leur seront attribuées.
Pour la déclaration des variables a et b, autant mettre l’échelle qui va bien de suite, ça ne consomme pas grand chose en ressource sur de telle application et ça peut éviter un bug inopiné. Pour b un type byte suffit de 00 à 60 (le byte va de 0 à 255).
Plutôt que d'afficher un message à chaque erreur, un coloriage de la case en rouge me semble moins "agressif" envers l'utilisateur et évite d'avoir à noter les valeurs et cliquer sur OK (oblige à rester devant son PC).
Soigne tes indentations de code (les tabulations), ça facilitera la lecture et le débogage de ton code.

++
Qwaz

[NicoTico]

Merci beaucoup pour l'optimisation!
le "msgbox" était pour la phase de test, c'est vrai que sinon c'est contraignant.

J'ai également transformé la ligne 20, d'origine :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
                DateAConvertir.Value = CDbl(a) / 24 + CDbl(b) * 60 / 100

par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
                DateAConvertir.Value = a / 24 + b / 24 / 60

le CDbl me semble redondant à la déclaration préalable des variables, et le calcul "CDbl(b) * 60 / 100" donne un résultat assez loin de la réalité (convertit les minutes en centième d'une heure), " b / 24 / 60 " donne la correspondance en fraction de journée.

Merci encore

[Qwazerty]

Salut
Oui j'avais manqué ça.

Par contre

1. le CDbl me semble redondant à la déclaration préalable des variables,
2. et le calcul "CDbl(b) * 60 / 100" donne un résultat assez loin de la réalité (convertit les minutes en centième d'une heure)," b / 24 / 60 " donne la correspondance en fraction de journée.

1. En fait CDbl() permet de transtyper une variable vers un type double. Si par exemple tu as une variable String qui contient une donnée numérique, CDbl retourne la valeur de type numérique double de ce qui est contenu en texte dans le string. Le "problème" avec Visual Basic, c'est qu'il fait lui même les transtypage dans la plupart des cas, du coup, par, au mieux, économie de code et au pire par méconnaissance, on laisse souvent VB se charger des transtypage.
   Dans le cas présente, transtyper une variable de type Entier (sans décimale) en Double ne sert à rien puisque 15 ou 15,00 ne charge rien.
   L'inverse par contre est obligatoire, si on essaie de passer 15,21 à un variable de type Integer, on aura une erreur, il faudra soit tronquer, soit arrondir la valeur pour pouvoir supprimer les décimales.
2. Hum, en fait il faudrait tout convertir en 100ème de jours, c'est une erreur en effet.
   Oui ta formule semble plus adaptée.

[/LIST]


++
Qwaz