VBA: gestion des dates

Version imprimable

Bonjour,

J'essaye de développer une macro permettant de savoir si un élément est en avance ou en retard par rapport à un autre. du coup je compare la date et l'heure d'arrivée par rapport à la date et l'heure prévue. Mais ces 4 données (heures et dates) sont dans des cellules différentes.

Le souci est que j'obtiens un format d'heure erronée (0,956944444444444 au lieu de 22:58 par exemple)

De plus, j'ai une cellule où la date est au format "DD/MM/YYYY" et lorsque que je demande à ma macro de la copier dans une autre cellule, le jour et le mois sont inversés :( exemple: 01/06/16 (01 juin 2016) devient 06/01/16 (06 janvier 2016). Par contre des que mon jour est à partir du 13 du mois, là il n'y a plus d'inversion et le format est identique....

Code:

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
Sub retard_frais()
 
Dim cpt, cpt2, cpt3, CNUF1, CNUF2, Cat1, Cat2 As Integer
Dim base As String
Dim FichO, Recept, HeuRecpt, CarnetCde As Workbook
Dim Recept1, FichO1 As Worksheet
Dim der As Long
 
 
Set Recept = Workbooks.Open(Filename:="C:\Users\kguesmia\Documents\Retard Frais\code camion réception.xls")
Set FichO = Workbooks.Open(Filename:="C:\Users\kguesmia\Documents\Retard Frais\Plan de transport AE 2016.xlsx")
Set Recept1 = Recept.Sheets(1)
Set FichO1 = FichO.Sheets(1)
Set HeuRecpt = Workbooks.Open(Filename:="C:\Users\kguesmia\Documents\Retard Frais\Heure arrivé camion.xlsx")
Set CarnetCde = Workbooks.Open(Filename:="C:\Users\kguesmia\Documents\Retard Frais\Carnet de cde Louviers.xls")
 
Recept.Activate
 
With Recept1
 
der = .Cells.SpecialCells(xlCellTypeLastCell).Row
 
  Cells.Replace What:="Libact:BASE DE ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
Range("R3").Value = "Heure d'arrivé"
 
' je récupère une heure issue d'une cellule au format hh:mm:ss
 
Range("R4").FormulaLocal = "=RECHERCHEV(N4;'Heure arrivé camion.xlsx'!Tableau_Gestion_des_Zones_Reception_V.07.01.2013[[N° de Passage]:[Heure de Presentation du Camion au Guichet]];8;FAUX)"
       Range("R4").Select
       Selection.AutoFill Destination:=Range("R4:R" & der)
       Calculate
 
       Columns("R:R").Select
       Selection.NumberFormat = "h:mm;@"  'je modifie le format pour supprimer les secondes
 
 
base = RTrim(Range("A1"))
 
End With
 
 
 cpt = 1
  Do While FichO1.Cells(1, cpt).Value <> base
    cpt = cpt + 1
  Loop
 
  cpt2 = 5
  cpt3 = 2
 
 
Recept1.Range("S3").Value = "Heure MAX prévu"
 
' je récupere une date issue d'une cellule au format standard sous la forme DD/MM/YY
 
Recept1.Range("S4").FormulaLocal = "=RECHERCHEV(P4;'[Carnet de cde Louviers.xls]Feuille 1'!$F$6:$J$28;5;FAUX)"
          Range("S4").Select
          Selection.AutoFill Destination:=Range("S4:S" & der)
          Calculate
 
 
Do
  CNUF1 = Val(Recept1.Range("O" & cpt2).Value)
  Cat1 = Val(Recept1.Range("A" & cpt2).Value)
 
     Do
       cpt3 = cpt3 + 1
       Cat2 = Val(FichO1.Range("C" & cpt3).Value)
       CNUF2 = Val(FichO1.Range("B" & cpt3).Value)
    Loop While CNUF1 <> CNUF2 Or Cat1 <> Cat2 And FichO1.Range("A" & cpt3).Value <> ""
 
   HeurePrev = Right(FichO1.Cells(cpt3, cpt), 5) ' je récupere une heure issue d'une cellule standard.
   DatePrev = Recept1.Range("S" & cpt2).Value ' date récupérée auparavant avec mon RechercheV
   Recept1.Range("S" & cpt2).Value = DatePrev & " " & HeurePrev ' j'associe les deux varariable du dessus pour en faire un format DD/MM/YY hh:mm
   DatePrev = Recept1.Range("S" & cpt2).Value ' j'associe la concaténation du dessus à ma variable 
   DateArriv = Recept1.Range("Q" & cpt2).Value & " " & Recept1.Range("R" & cpt2).Value  ' j'associe deux cellules pour en faire un format DD/MM/YY hh:mm
 
  If DatePrev < DateArriv Then
    Recept1.Range("T" & cpt2).Value = DatePrev - DateArriv
  End If
 
 cpt2 = cpt + 2
 cpt3 = 2
 
Loop While Recept1.Range("A" & cpt2).Value <> ""

Pourriez-vous m'aider svp :oops:

16/06/2016, 11h16
Slooby

Bonjour,

Quand les mois et jours s'inversent c'est parce que ça prend le format des dates anglophones. Les dates sont toujours les même en réalité, c'est juste un soucis d'affichage.

Tu as regardé si avec un simple Format de Cellule / Date / %tonFormat% ça marche ?

Slooby
16/06/2016, 11h19
kimou75

C'est ce que je pensais, mais quand je passe ma cellule au format date libellé, j'obtiens bien "06 janvier 2016" au lieu de "01 juin 2016".
16/06/2016, 15h38
Menhir
Citation:

Envoyé par kimou75

Le souci est que j'obtiens un format d'heure erronée (0,956944444444444 au lieu de 22:58 par exemple)

Pour moi, ça n'a rien d'erroné.
Si tu mets dans une cellule Excel 0,956944444444444 puis que tu passes le format de cette cellule en [hh]:mm, tu obtiendras bien 22:58.

Citation:

De plus, j'ai une cellule où la date est au format "DD/MM/YYYY" et lorsque que je demande à ma macro de la copier dans une autre cellule, le jour et le mois sont inversés :(

Est-ce que ce que tu mets dans ta cellule est une vrai date ou est-ce une chaine de caractère (Texte) ayant la forme d'une date.

Pour ceux qui n'ont pas envie de se pastiller ta centaine de lignes de code, tu pourrais dire à quel endroit tu transfères cette valeur ?
Citation:
Code:

1 2 3 4 5 Dim cpt, cpt2, cpt3, CNUF1, CNUF2, Cat1, Cat2 As Integer Dim base As String Dim FichO, Recept, HeuRecpt, CarnetCde As Workbook Dim Recept1, FichO1 As Worksheet Dim der As Long
Ca, c'est une mauvaise idée.
Avec de telles déclarations, seule la dernière variable de chaque ligne est déclarée du type donné. Les autres sont déclarées en Variant.
Mets un stop dans ton code et regarde le type de chaque variable dans la fenêtre de variables locales, tu pourras vérifier ce que je dis.
16/06/2016, 16h10
kimou75

Citation:

Pour moi, ça n'a rien d'erroné.
Si tu mets dans une cellule Excel 0,956944444444444 puis que tu passes le format de cette cellule en [hh]:mm, tu obtiendras bien 22:58.

Oui mais associée à une date au format "jj/mm/aaa" je souhaite obtenir une cellule au format "jj/mm/aaa hh:mm", mais après avoir appliqué ce format manuellement, j'obtiens "01/06/2016 0;956944"

Citation:

Est-ce que ce que tu mets dans ta cellule est une vrai date ou est-ce une chaine de caractère (Texte) ayant la forme d'une date.

Pour ceux qui n'ont pas envie de se pastiller ta centaine de lignes de code, tu pourrais dire à quel endroit tu transfères cette valeur ?

Pour une des deux variables de comparaison, j'obtiens le bon format attendu. Pourtant la date est au format standard et l'heure est bien au format heure.

Date 1 au format standard

Code:

Recept1.Range("S4").FormulaLocal = "=RECHERCHEV(P4;'[Carnet de cde Louviers.xls]Feuille 1'!$F$6:$J$28;5;FAUX)"

Date 2

Code:

DateArriv = Recept1.Range("Q" & cpt2).Value & " " & Recept1.Range("R" & cpt2).Value ' j'associe deux cellules (la 1ere au format date et la 2eme au format heure) pour en faire un format DD/MM/YY hh:mm

Heure 1

Code:

HeurePrev = Right(FichO1.Cells(cpt3, cpt), 5) ' je récupère une heure issue d'une cellule au format date.

Heure 2

Code:

DateArriv = Recept1.Range("Q" & cpt2).Value & " " & Recept1.Range("R" & cpt2).Value ' j'associe deux cellules pour en faire un format DD/MM/YY hh:mm
16/06/2016, 16h30
Menhir

Citation:

Envoyé par kimou75

Oui mais associée à une date au format "jj/mm/aaa" je souhaite obtenir une cellule au format "jj/mm/aaa hh:mm", mais après avoir appliqué ce format manuellement, j'obtiens "01/06/2016 0;956944"

Je ne sais pas comment tu as "associé" les deux mais pour relier une date en jour et une date en heures, il suffit de faire une addition.

Si dans une cellule tu tapes 01/06/2016 puis que tu forces cette cellule au format Standard, tu obtiens 42522.
La différence entre les deux, c'est uniquement une question d'affichage. Pour Excel, du point de vue du contenu de la cellule, c'est exactement la même chose.
Idem pour 0,956944444444444 et 22:58.

Si tu tapes en A1 : 01/06/2016
Si tu tapes en A2 : 22:58
Si tu tapes en A3 : =A1+A2
Et que tu mets A3 au format jj/mm/aaaa hh:mm, tu obtiendras 01/06/2016 22:58.

Les valeurs de date sont simplement des valeurs numérique.
L'erreur à ne pas faire est de les considérer comme des chaînes de caractères et essayer de les concaténer, comme tu le fais dans :

Code:

Recept1.Range("S" & cpt2).Value = DatePrev & " " & HeurePrev ' j'associe les deux varariable du dessus pour en faire un format DD/MM/YY hh:mm

Je le répète : ce sont des valeurs, pas du texte. Même les heures sont en fait des fractions de jours.
Traite-les comme telles, aussi bien dans les cellules que dans les variables.
Pense aussi à mettre pour les variables qui les traite le bon Type dans la déclaration.
16/06/2016, 16h32
kiki29

Salut, toujours d'actualité : Comprendre et gérer les dates sous Excel et en VBA
16/06/2016, 17h26
kimou75

Merci à vous deux, j'arrive à avancer dans la compréhension et dans mon programme.

Je bloque à l’étape où je récupère l'heure 1 car il s'agit de tronquer une plage horaire. J'ai besoin que de l'heure max de cette plage. Le souci est que je ne peux pas l'associer à une variable numérique via une fonction "Droite

Code:

HeurePrev = Right(FichO1.Cells(cpt3, cpt), 5) ' je récupère une heure issue d'une cellule standard.
16/06/2016, 17h33
Menhir

Citation:

Envoyé par kimou75

Je bloque à l’étape où je récupère l'heure 1 car il s'agit de tronquer une plage horaire.

Ne pense pas "Texte".
Traite-là comme ce qu'elle est : une valeur date.

Code:

HeurePrev = Hour(FichO1.Cells(cpt3, cpt))

Et déclare HeurePrev comme un Integer.

Quand on manipule ce type de données, les déclaration, c'est important.
Laisser VBA décider du type qu'il va choisir, c'est jouer à la roulette russe.
16/06/2016, 20h27
kimou75

1 pièce(s) jointe(s)

Menhir, j'ai essayé mais ça me retourne une valeur à 0 bien que ma variable soit déclarée en Integer :(

Et comme tu le vois, c'est des tranches horaires mais je souhaite récupérer uniquement l'heure max de chaque cellule.

Pièce jointe 213038
17/06/2016, 08h52
Menhir

Citation:

Envoyé par kimou75

c'est des tranches horaires mais je souhaite récupérer uniquement l'heure max de chaque cellule.

Non, visiblement ce que tu souhaites récupérer n'est pas l'heure mais le temps arrondi à l'heure entière inférieure, ce qui n'est pas la même chose.

Si tu as 12:45, l'heure c'est 12, le temps arrondi à l'heure entière inférieure, c'est 12:00.

Si tu avais pris la peine de regarder dans l'aide VBA Excel la description de la fonction Hour(), tu l'aurais compris.
https://msdn.microsoft.com/fr-fr/lib.../gg264741.aspx

Comme je le disais, le temps, dans Excel, ce sont des fractions de jours. Donc :

Code:

HeurePrev = Hour(FichO1.Cells(cpt3, cpt)) / 24

En déclarant bien sûr HeurePrev de type Date.
17/06/2016, 09h01
jeanmidudu

Bonjour,

Peux-tu mettre une image de ton classeur avant traitement?

Pour ce qui est du passage des dates de DD/MM/YYYY à MM/DD/YYYY, il y a plusieurs étapes à suivre pour éviter ça :
- Toujours enregistrer le document avec les paramètres locaux de Windows (Problème très courant lorsque l'enregistrement est automatique)
- Modifier le format des cellules avant d'y insérer les valeurs ( .NumberFormat = "dd/mm/yyyy;@" )
- Modifier le format des valeurs au moment de l'insertion ( .cells(i,j).value ou .range("A1").value =Format(laDate,"dd/mm/yyyy") )

Parfois il suffit de modifier un seul des éléments ci-dessus, néanmoins, pour éviter tout problème, le mieux est d'appliquer ces 3 modifications.

Cdt,
17/06/2016, 09h24
kimou75

Merci Jeanmidudu, je vois que tu as bien compris mon problème :D. Je vais essayer cela.

Menhir :

Citation:

Si tu as 12:45, l'heure c'est 12, le temps arrondi à l'heure entière inférieure, c'est 12:00.

Si tu avais pris la peine de regarder dans l'aide VBA Excel la description de la fonction Hour(), tu l'aurais compris.

Si tu regarde la copie de mon classeur Excel, tu vois bien que les cellules contiennent des tranches horaires. Je n'ai pas besoin de l'arrondi, je souhaite récupérer la valeur exacte de l'heure max de chaque tranche horaire. Hors la fonction Hour() ne me le permettra pas.
17/06/2016, 09h40
Menhir

1 pièce(s) jointe(s)

Citation:

Envoyé par kimou75

Si tu regarde la copie de mon classeur Excel, tu vois bien que les cellules contiennent des tranches horaires.

Etant donné que tu n'as mais de copie d'écran qu'après que j'ai proposé cette fonction, il eut été difficile de l'anticiper.
Pièce jointe 213058

Citation:

Je n'ai pas besoin de l'arrondi, je souhaite récupérer la valeur exacte de l'heure max de chaque tranche horaire. Hors la fonction Hour() ne me le permettra pas.

Dans ce cas :

Code:

HeurePrev = FichO1.Cells(cpt3, cpt) - Int(FichO1.Cells(cpt3, cpt))

Toujours avec HeurePrev au format Date.
17/06/2016, 16h47
kimou75
Merci beaucoup,

j'ai réussi à obtenir ce que je voulais :D

Je bute encore pour afficher dans une cellule, le résultat d'une soustraction de date malgré avoir déclaré toutes mes variables au format Date et modifié auparavant le format de la cellule.
Code:

1 2 3 4 HeureRetard = DatePrev - DateArriv Recept1.Range("T" & cpt2).Select Selection.NumberFormat = "h:mm;@" Recept1.Range("T" & cpt2).Value = HeureRetard
je vais continuer à investiguer en espérant trouver mais en tout cas le problème initiale est résolu !
17/06/2016, 17h25
Menhir

Citation:

Envoyé par kimou75

Je bute encore pour afficher dans une cellule, le résultat d'une soustraction de date malgré avoir déclaré toutes mes variables au format Date et modifié auparavant le format de la cellule.

Une soustraction de date n'est pas une date mais une durée (à moins que ce soit la durée qui est soustraite à une date, dans ce cas le résultat est une date).

Il vaut donc mieux la considérer comme une donnée de type Double.
La façon de l'afficher dépend du contexte : nombre de jours (dans ce cas, on laisse un format de nombre), nombres d'heures ([hh]:mm, les crochets sont importants), combinaison des deux ?
17/06/2016, 23h46
kimou75

Merci Menhir pour la précision. Je souhaitais effectivement obtenir une durée au format hh:mm et ça fonctionne avec la variable de type double :mrgreen: