Empêcher la conversion en date heure lors d'un chercher remplacer

Version imprimable

Bonjour,

j'ai un problème sur une macro sur Excel 2007. Je copie/colle une liste de date/heure d'un fichier .csv

Le format est en texte sous la forme dd/mm/aa-hh:mm:ss

Le copier/coller ne me pose aucun problème.

Par contre lorsque je fais un rechercher/remplace pour enlever le tiret (-), Excel me converti automatiquement les date/heure en format date heure (jj/mm/aaaa hh:mm:ss) pour toutes les date du 1 au 12 du mois en inversant les mm et jj et me laisse les autre au format texte.

Exemple: 05/11/15-19:30:28 devient 11/05/2015 19:30:28 en format date/heure
alors que 13/11/15-00:00:44 devient 13/11/15 00:00:44 en format texte

Ma question est comment empêcher la conversion en date des 12 premiers jours pour avoir un format cohérent pour le traitement suivant?

Ci-dessous mon code.
Code:

1 2 3 4 5 6 7 8 9 10 i = ligneForme + 1 Do While Cells(i, 2) <> "" With Sheets("Feuil1").Cells(i, 2) .Replace What:="-", Replacement:=" " .Value = CDate(.Value) .NumberFormat = "dd/mm/yyyy hh:mm:ss" End With i = i + 1 Loop
Merci par avance si quelqu'un peut me dépanner... je sèche depuis 3 heures sur les forums

Salut.

Attention que dans ton code, tu utilises Cells(i,2) pour ton test puis Sheets("Feuil1").Cells(i, 2) dans la boucle. Si Feuil1 n'est pas la feuille active, tu ne pointes pas vers les mêmes cellules.
Attention également que dans le DO, tu boucles jusqu'à ce que la valeur de la cellule soit un espace (<> cellule vide)...

Cela étant dit, le problème vient du fait que en VBA, les dates s'expriment selon la notation anglo-saxonne (MM/DD/YY). Je ne vois pas d'autre technique que de "parser" la valeur de la cellule
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Sub Test() Dim ligneforme As Long Dim Row As Long Dim Cell As Range ligneforme = 10 Row = ligneforme + 1 Set Cell = Cells(ligneforme + 1, 2) Do While Cell.Value <> "" Cell.Value = DateSerial(Mid(Cell.Value, 7, 2), Mid(Cell.Value, 4, 2), Left(Cell.Value, 2)) + _ TimeSerial(Mid(Cell.Value, 10, 2), Mid(Cell.Value, 13, 2), Right(Cell.Value, 2)) Set Cell = Cell(2) Loop End Sub

02/12/2015, 10h30
CeluiQuiCherche

Merci beaucoup, cela fonctionne exactement comme je le souhaite.

Merci aussi pour le signalement des maladresses de programmation (un copier collé d'un autre programme non correctement expurgé :oops: ).

Je marque le problème comme résolu.

Bonjour.

Comment éviter l'inversion jour/mois : envoyer une valeur de type Date vers une cellule au format Date.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub Pour_Eviter_Inversion_Jour_Mois()
 
For i = 1 To 10
 
    tout = Cells(i, 2)
    If tout <> "" Then
 
     tout = Replace(tout, "-", " ")
 
     Cells(i, 2).NumberFormat = "General" 'Le format joue un rôle essentiel.
     'Vers une cellule au format "General", le type Date s'impose pour éviter l'ìnversion jour/mois
     Cells(i, 2).Value = CDate(tout)
 
    End If
 
   Next
 
End Sub

02/12/2015, 12h50
Pierre Fauconnier

Salut Docmarty

Ca n'empêche pas l'inversion, dans la mesure où la date est comprise en fonction, notamment, des paramètres régionaux.

De plus, le format n'influe en rien la valeur contenue dans la cellule. C'est au moment de la transformation de la date par la suppression du "-" que le problème se pose, pas au moment où la valeur est affectée à la cellule.

Personnellement, j'ai rencontré de nombreuses fois ce problème entre mon pc où tout fonctionne bien et celui de certains clients chez qui l'inversion était effectuée, et à ce jour, la seule réponse que j'ai trouvée est celle que j'ai renseignée.

Mais si tu as d'autres pistes testées sur divers environnements au niveau des paramètres régionaux, je suis preneur... :)
02/12/2015, 12h54
Invité

bonjour Pierre et Doc,
il faut toujours passer les dates au format international! format(date,"yyyy-mm-dd")!

ainsi cdat(format(date,"yyyy-mm-dd")) ! retourne bien une date en fonction des paramétrés régionaux!
02/12/2015, 13h07
CeluiQuiCherche

bonjour Docmarti et Rdurupt,

Effectivement, comme l'a souligné Pierre, c’est au moment du retrait du tiret que le problème se pose et cela quelque soit le format.

Je les ai tous essayés.
02/12/2015, 13h16
Invité

Code:

Cell.Value =format(Cell.value,"yyyy-mm-dd")
02/12/2015, 13h43
Pierre Fauconnier

Salut

Citation:

Envoyé par rdurupt

Code:

Cell.Value =format(Cell.value,"yyyy-mm-dd")

renvoie forcément une erreur avec la date telle que saisie dans la cellule, à savoir 15/04/15-12:41:19, puisque VBA ne sait pas traduire cette valeur en date, notamment à cause du tiret. Et enlever le tiret revient à inverser ou non la date en fonction des paramètres régionaux...
02/12/2015, 16h25
Docmarti
Citation:

Envoyé par Pierre Fauconnier

Mais si tu as d'autres pistes testées sur divers environnements au niveau des paramètres régionaux, je suis preneur... :)

Le code suivant produit l'inversion Jour/Mois

Code:

.Replace What:="-", Replacement:=" "

Tout comme le code suivant qui produit la même inversion
Code:

1 2 3 4 Dim tout as String tout = Replace(.Value, "-", " ") .Value = tout
Par contre le code ci-dessous ne produit pas d'inversion Jour/Mois
Code:

1 2 3 4 5 Dim tout as String tout = Replace(.Value, "-", " ") .NumberFormat = "dd/mm/yyyy hh:mm:ss" .Value = CDate(tout)
Testé avec de multiples paramètres régionaux.

Ma théorie est que l'inversion ne se produit que quand une valeur de type String est assignée à une cellule qui n'est pas au format TEXTE.
Et se produit également quand une valeur de type Date est assignée à une cellule qui n'est pas au format Date.

Mais si la valeur et la cellule sont du même type (Ex.: valeur de type Date et cellule au format Date ), VBA n'a pas à effectuer une conversion d'un type à un autre et il n'y a donc pas d'inversion.
02/12/2015, 16h41
Pierre Fauconnier

2 pièce(s) jointe(s)
Docmarty,

Désolé, mais cela ne fonctionne pas, et l'inversion s'effectue toujours...

Voici ton code adapté pour placer la conversion dans la cellule d'à côté
Code:

1 2 3 4 5 6 7 Sub Test1() Dim Tout As String Tout = Replace(ActiveCell, "-", " ") ActiveCell(1, 2).NumberFormat = "dd/mm/yy" ActiveCell(1, 2).Value = CDate(Tout) End Sub
Pièce jointe 194123

Pièce jointe 194121

Ma conclusion: Actuellement, la seule solution qui fonctionne à tous les coups est de parser le texte pour reconstruire la date...
02/12/2015, 16h55
Docmarti
Citation:
Envoyé par Pierre Fauconnier

Docmarty,

Désolé, mais cela ne fonctionne pas, et l'inversion s'effectue toujours...

Voici ton code adapté pour placer la conversion dans la cellule d'à côté

Code:

1 2 3 4 5 6 7 Sub Test1() Dim Tout As String Tout = Replace(ActiveCell, "-", " ") ActiveCell(1, 2).NumberFormat = "dd/mm/yy" ActiveCell(1, 2).Value = CDate(Tout) End Sub

Ma conclusion: Actuellement, la seule solution qui fonctionne à tous les coups est de parser le texte pour reconstruire la date...
Je suis désolé également car, comme tu as choisi Anglais (Etats-Unis) dans les paramètres , l'inversion apparente est causée par ton choix du format de date dans la cellule:

Code:

ActiveCell(1, 2).NumberFormat = "dd/mm/yy"

Le format de date aux Etats-Unis devrait être

Code:

ActiveCell(1, 2).NumberFormat = "mm/dd/yy"
02/12/2015, 16h57
Invité

oui effectivement, j'ai fait la diagonale du fou dans ma lecture,je n'avais pas vue le "-"! mais je viens de tester ActiveCell=format(replace("13/11/15-00:00:44","-"," "),"yyyy-mm-dd hh:mm:ss") ! et ça marche.
02/12/2015, 17h04
Pierre Fauconnier

rdurupt

Chez moi, avec ton code, j'obtiens le 15/11/2013 :(

Mais de toute façon, il faut tester avec une date "inversable", ce qui n'est pas le cas ici...8O
Raté :)

Mais p'têt ben qu'on arrivera à trouver...

Docmarty,

Cela ne change absolument rien chez moi... Et pour cause, puisque la transformation se fait indépendamment du format de la cellule. Pour rappel, le format de la cellule ne change jamais la valeur de la cellule (sauf pour les décimales si l'on coche une option très spécifique d'Excel, ce que je déconseille fortement).

Ce que tu dois faire, c'est vérifier, via la fonction mois(), que la donnée est bien celle attendue. Le format que tu spécifies dans la formule te leurre visuellement sur la vraie valeur de la cellule.

Ce qu'il faut comprendre, c'est qu'au départ, Excel reçoit du texte => "02/12/2015".

Au moment de la conversion, en fonction des paramètres régionaux, Excel va comprendre "02 décembre" ou "12 février". Le format n'a rien à voir là-dedans! Tant que vous convertirez directement en date, quel que soit le format de la cellule, vous serez dépendant de la manière dont Windows comprend votre date.

La seule solution fiable, quels que soient les paramètres régionaux, est donc de considérer ce qui est reçu, à savoir "02/12/2015", et de parser pour recréer la vraie date, à savoir "02 décembre"...
02/12/2015, 17h13
Invité

Ok alors disons que je n'ai rien dit, a l'occasion je configurerai un Vm pour tester, car ne voyant pas le problème, je n'arrive pas à évaluer mais j'ai entièrement confiance en toi!
02/12/2015, 17h26
Pierre Fauconnier

rdurupt,

Ce qui intéressant dans cette discussion, c'est que si on ne trouve pas d'autres solutions que le parsing (perso, j'aurais apprécié), cela met au moins en évidence que la manipulation des dates est un casse-tête, rendu d'autant plus ardu qu'Excel "s'amuse" à inverser lorsqu'une "date" rentrée n'en est pas une mais que son inversion en est une (par inversion, j'entends jj/mm qui devient mm/jj ou mm/jj qui devient jj/mm).

VBA vient mettre son grain de sel dans l'histoire, modifiant encore la donne. Au passage, je signalerai que VBA et Excel acceptent les débordements avec les trucs du genre dateserial(2015,23,35) (VBA) ou DATE(2015,23,35) (Excel)...

Donc, pas simple, la gestion des dates lorsque les classeurs sont partagés entre personnes n'utilisant pas les mêmes paramètres régionaux. Mais tu as sûrement déjà été confronté à cela en ta qualité de développeur .net, et les gestionnaires de bases de données savent aussi que c'est "amusant" de gérer des DB avec les paramètres régionaux qui entrent en conflit avec les paramètres de la DB.

Ce n'est pas sans raison que la norme ISO 8601 spécifie un format (texte!) pour les échanges de calendrier...
02/12/2015, 17h43
Invité

Au que oui, la date c'est un vrai casse tête!

Moi dans mes base données il y a longtemps que je fais comme toi , du texte.

MySql ne comprend le champ date qu'en yyyy-mm-dd H24:Mn:ss!

Oracle a l'installation tu définis short long time.. Et a l'ouverture de la connection tu fais une requêtes pour modifier le format le temps de la session!

Nous sommes confronté à la meilleur façon de développé et le confort de l'utilisateur!

Bien sur l'un ne vas pas sens l'autre, mais les éditeurs de logiciel devient communiquer sur la façon la plus pertinente d'éviter ce conflit philosophique!

Ça fait maintenant 20 et je ne me rappelle plus avoir vue ce genre d'informations.

Potasser les 100Ko de doc Oracle oui!

Salut,
comme cela ca marche aussi

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub Macro1()
'---------------------------------------------------------------------------------------
' Procedure : Macro1
' Author    : Oliv-
' Date      : 02/12/2015
' Purpose   :
'---------------------------------------------------------------------------------------
'
 
Set c = Range("E2")
  c.NumberFormat = "@"
  c.Value = Replace(c.Value, "-", " ")
 
    c.TextToColumns Destination:=c, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
    c.NumberFormat = "m/d/yyyy h:mm"
End Sub

02/12/2015, 18h15
Pierre Fauconnier

Oliv-,

Recalé :)

Avec mes paramètres régionaux (ceux illustrés plus tôt dans la discussion), si je testes le mois, je reçois 2 et pas 12...
02/12/2015, 18h59
Oliv-

T es sûr?
02/12/2015, 19h00
Pierre Fauconnier

Oh oui je suis sûr... ;)

Comme je l'ai dit plus haut, j'ai déjà testé cela depuis pas mal d'années et chez pas mal de clients... J'aurais apprécié que la solution soit aussi simple que de laisser faire VBA, mais au final, la solution que je présente est simplissime, non?

Pourquoi voudrais-tu que ton code fonctionne alors que, comme je l'ai dit plus haut, la conversion se fait au moment de la transformation en date par "le système". Pourquoi voudriez-vous que Excel ou VBA transforme votre donnée en fonction du format d'une cellule, qui n'est jamais que de l'habillage sur une valeur qui ne se modifie pas en fonction dudit format?
Maintenant, ok pour le défi de trouver plus simple et qui fonctionne dans tous les cas ;)
02/12/2015, 21h12
Oliv-

Pierre,
Parce que naïvement en mettant dans TextToColumns...FieldInfo:=Array(1, xlDMYFormat) ou xlDMYFormat = 4.

je me dis qu'il doit faire ce qu'on lui demande ! prendre le jour en premier puis le mois et l'année !

mais apparemment il n'en fait qu'à sa tête !
02/12/2015, 21h49
Pierre Fauconnier

1 pièce(s) jointe(s)

Oliv,

Ok. Je crois avoir compris ce que tu veux dire :)

Tu t'attends à ce que Excel interprète correctement ta donnée à cause de l'option entourée dans la boite de dialogue suivante:
Pièce jointe 194149

Au passage, remarque qu'il ne te donne pas de format (du type dd/mm/yy) mais simplement l'ordre des données JMA, MJA, ... (en fait, il va tenter de trouver une date qui correspond à ton schéma).

Mais cela ne fonctionne que si la donnée est déjà une date ( ou interprétable comme telle, éventuellement via une conversion), et que tu veux l'interpréter dans un autre système de date. Or, tu as du texte dans ta cellule, même après avoir supprimé le "-". Donc, dans la fenêtre, pour pouvoir utiliser un autre système de dates, Excel convertit d'abord le texte en fonction des paramètres régionaux, puis applique à cette date le système de date renseigné par l'option. C'est donc toujours bien Excel qui interprète d'abord ton texte en date, et donc tu perds la main dessus.

C'est pour cela qu'il faut parser.

Citation:

Envoyé par Pierre Fauconnier

Docmarty,

Ce que tu dois faire, c'est vérifier, via la fonction mois(), que la donnée est bien celle attendue. Le format que tu spécifies dans la formule te leurre visuellement sur la vraie valeur de la cellule.

Voici donc un démo avec vérification des résultats obtenus, démontrant ma prétention qu'une date de type String lorsque envoyée vers une cellule au format autre que Texte, doit subir une conversion d'un type à un autre, conversion au cours de laquelle le jour et le mois seront inversés si VBA y reconnaît une date au format américain mm/jj/aa. Car VBA à l'origine ne connaissait que 2 formats de Date : le format mm/jj/aaaa et le format aaaa/mm/jj.

Une date de type Date envoyée vers une cellule au format Texte provoque aussi l'inversion jour/mois.
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 Sub Demo_Inversions() Range("A11:d14").Clear Set B = Range("B12:B13") Set c = Range("c12:C13") Set D = Range("D12:D13") B.NumberFormat = "General" c.NumberFormat = "0.00" D.NumberFormat = "@" B(1).Offset(-1) = "'" & B.NumberFormat: B(1).Offset(-1).Select c(1).Offset(-1) = "'" & c.NumberFormat: c(1).Offset(-1).Select D(1).Offset(-1) = "'" & D.NumberFormat: D(1).Offset(-1).Select Range("B12:B14").NumberFormat = B(1).Offset(-1) Range("C12:C14").NumberFormat = c(1).Offset(-1) Range("D12:D14").NumberFormat = D(1).Offset(-1) B(1).Offset(, -1) = TypeName("10/01/2015"): B(1).Offset(, -1).Select B(2).Offset(, -1) = TypeName(CDbl(CDate("10/01/2015"))): B(2).Offset(, -1).Select B(3).Offset(, -1) = TypeName(CDate("10/01/2015")): B(3).Offset(, -1).Select 'Valeur de type String B(1).Value = "10/01/2015": B(1).Select c(1).Value = "10/01/2015": c(1).Select D(1).Value = "10/01/2015": D(1).Select 'Valeur de type Double B(2).Value = CDbl(CDate("10/01/2015")): B(2).Select c(2).Value = CDbl(CDate("10/01/2015")): c(2).Select D(2).Value = CDbl(CDate("10/01/2015")): D(2).Select 'Valeur de type Date B(3).Value = CDate("10/01/2015"): B(3).Select c(3).Value = CDate("10/01/2015"): c(3).Select D(3).Value = CDate("10/01/2015"): D(3).Select For Each c In Range("B12:D14") c.Select If CDate(c.Value) <> CDate("10/01/2015") Then c.Interior.Color = RGB(255, 100, 50) Else c.Interior.Color = RGB(100, 255, 100) End If Next End Sub

03/12/2015, 08h18
Pierre Fauconnier

2 pièce(s) jointe(s)

Salut Docmarty,

Je n'ai bien compris le sens de ton code. Avec la configuration régionale que j'ai illustrée plus haut, toutes les cellules renvoient octobre comme date, alors que ton code passe "10/01/2015" ==> la conversion ne fonctionne donc pas correctement.

Pièce jointe 194158

Par contre, si je bascule en configuration "français (Belgique), j'ai janvier ou octobre selon les cas... Ce n'est guère plus probant.

Pièce jointe 194159

Du coup, je ne comprends pas ce que tu souhaitais démontrer. Mais cela illustre que, par rapport à la demande initiale, il n'y a pour l'instant que le parsing qui passe correctement les tests et qui garantit que la valeur texte reçue est traduite correctement.

Citation:

Envoyé par Pierre Fauconnier

Salut Docmarty,

Je n'ai bien compris le sens de ton code. Avec la configuration régionale que j'ai illustrée plus haut, toutes les cellules renvoient octobre comme date, alors que ton code passe "10/01/2015" ==> la conversion ne fonctionne donc pas correctement.

Le "10/01/2015" pour un Américain, c'est le premier octobre 2015. C'est le seul pays au monde qui utilise ce format de date. Ca peut sûrement surprendre un Européen.

Donc "10/01/2015" étant le mois d'octobre, ca veut dire que la conversion fonctionne correctement. D'ailleurs VBA ne connaissant que le format américain "10/01/2015" et le format international "2015-10-01", il n'y a pas de problème de dates sur VBA pour Excel pour les Américains.

Le code suivant va être plus apte à illustrer le bonheur des Américains utilisateurs de Excel.

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
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set c = Cells(16, 3)
 c.Value = "": c.Offset(, -2).Value = "": c.Offset(, -1).Value = ""
 c.Interior.Color = xlNone
 
 If Target.Count = 1 And Trim(Target.Value) <> "" Then
 If IsNumeric(Target.Value) Then
   c.Value = Format(CDate(Target), "mmmm")
   c.Interior.Color = Target.Interior.Color
   c.Offset(, -1).NumberFormat = "@":
   c.Offset(, -1).Value = "Mois = " & Month(CDate(Target.Value))
   c.Offset(, -2).Value = Target.Address
  ElseIf IsDate(Target.Value) Then
   c.Value = Format(Target, "mmmm")
   c.Interior.Color = Target.Interior.Color
   c.Offset(, -1).NumberFormat = "@":
   c.Offset(, -1).Value = "Mois = " & Month(CDate(Target.Value))
   c.Offset(, -2).Value = Target.Address
  End If
 End If
End Sub
 
Public Sub Demo_Inversions()
 
Range("A11:d14").Clear
 
Set B = Range("B12:B13")
Set c = Range("c12:C13")
Set D = Range("D12:D13")
 
 B.NumberFormat = "General"
 c.NumberFormat = "0.00"
 D.NumberFormat = "@"
 
 
 B(1).Offset(-1) = "'" & B.NumberFormat: B(1).Offset(-1).Select
 c(1).Offset(-1) = "'" & c.NumberFormat: c(1).Offset(-1).Select
 D(1).Offset(-1) = "'" & D.NumberFormat: D(1).Offset(-1).Select
 
 Range("B12:B14").NumberFormat = B(1).Offset(-1)
 Range("C12:C14").NumberFormat = c(1).Offset(-1)
 Range("D12:D14").NumberFormat = D(1).Offset(-1)
 
  B(1).Offset(, -1) = TypeName("10/01/2015"): B(1).Offset(, -1).Select
  B(2).Offset(, -1) = TypeName(CDbl(CDate("10/01/2015"))): B(2).Offset(, -1).Select
  B(3).Offset(, -1) = TypeName(CDate("10/01/2015")): B(3).Offset(, -1).Select
 
'Valeur de type String
  B(1).Value = "10/01/2015": B(1).Select
 
  c(1).Value = "10/01/2015": c(1).Select
 
  D(1).Value = "10/01/2015": D(1).Select
 
'Valeur de type Double
  B(2).Value = CDbl(CDate("10/01/2015")): B(2).Select
  c(2).Value = CDbl(CDate("10/01/2015")): c(2).Select
  D(2).Value = CDbl(CDate("10/01/2015")): D(2).Select
 
 'Valeur de type Date
  B(3).Value = CDate("10/01/2015"): B(3).Select
  c(3).Value = CDate("10/01/2015"): c(3).Select
  D(3).Value = CDate("10/01/2015"): D(3).Select
 
 For Each c In Range("B12:D14")
 
  c.Select
 
  If CDate(c.Value) <> CDate("10/01/2015") Then
   c.Interior.Color = RGB(255, 100, 50)
  Else
   c.Interior.Color = RGB(100, 255, 100)
  End If
 Next
 
End Sub

03/12/2015, 10h28
Pierre Fauconnier

Citation:

Envoyé par Docmarti

[...]

Donc "10/01/2015" étant le mois d'octobre, ca veut dire que la conversion fonctionne correctement.[...]

Le problème du départ (voir la question initiale) est de recevoir 10/01/2015 en tant que texte et le transformer en 10/01/2015 ( date valant 10 janvier, mode européenne) en VBA.

Venir dire que "10/01/2015" est le mois d'octobre est ne pas tenir compte du tout de la question initiale... Cette discussion n'a aucun sens si tu ne tiens pas compte du problème initial et on perd tous notre temps!
03/12/2015, 11h15
Docmarti

Tu as probablement raison.

Je voudrais te remercier pour les magnifiques tutoriaux que tu as produits et mis à la disposition de tous sur ce forum. C'est absolument fantastique ce qu'on arrive à faire grâce à tes connaissances.
03/12/2015, 11h21
Marc-L

Bonjour, bonjour !

Petit rappel concernant la fonction DateValue (cf Demo4) …

Sans compter la fonction CDate !

______________________________________________________________________________________________________
Je suis Paris, …
03/12/2015, 11h47
Pierre Fauconnier

Bonjour Marc,

Avec les paramètres régionaux tels que décrit plus haut, datevalue("10/01/2015") renvoie le 01 octobre 2015... (Ce qui n'est pas la demande de départ)... cdate("10/01/2015") aussi...
03/12/2015, 11h59
Invité

Bonjour tous,
Désolé pierre, j'ai fait un tour sur le site d Microsoft, et je n'ai pas trouvé d'autres exemples que DateSerial!
03/12/2015, 12h34
Pierre Fauconnier

Salut rdurupt,

Pas de soucis... Au delà du post initial, cette discussion permet de mettre en lumière que les paramètres régionaux peuvent jouer des tours assez gênants, notamment avec une formule du style =texte(a1;"jj/mm/aa") qui va mal passer sur un Excel anglais parce que "jj/mm/aa" ne sera pas traduit par Excel en "dd/mm/yy".

Dans le monde globalisé dans lequel nous travaillons, il n'est pas rare qu'un fichier Excel "voyage" des USA en Inde en passant par la France puis le Japon... Dans ce cadre de travail, il est particulièrement important d'être attentif à ces notions de dates et de paramètres régionaux, que ce soit en Excel pur ou en VBA.
03/12/2015, 12h48
Invité

oui tu a raison, et les fichier CSV par exemple font légion!

Citation:

Envoyé par CeluiQuiCherche

Bonjour,

j'ai un problème sur une macro sur Excel 2007. Je copie/colle une liste de date/heure d'un fichier .csv

Le format est en texte sous la forme dd/mm/aa-hh:mm:ss

Le copier/coller ne me pose aucun problème.

Par contre lorsque je fais un rechercher/remplace pour enlever le tiret (-), Excel me converti automatiquement les date/heure en format date heure (jj/mm/aaaa hh:mm:ss) pour toutes les date du 1 au 12 du mois en inversant les mm et jj et me laisse les autre au format texte.

Exemple: 05/11/15-19:30:28 devient 11/05/2015 19:30:28 en format date/heure
alors que 13/11/15-00:00:44 devient 13/11/15 00:00:44 en format texte

Ma question est comment empêcher la conversion en date des 12 premiers jours pour avoir un format cohérent pour le traitement suivant?

Pierre
La question initiale est :
"Ma question est comment empêcher la conversion en date des 12 premiers jours pour avoir un format cohérent pour le traitement suivant?"

Je vois que ton code répond parfaitement à la demande d'empêcher l'inversion non seulement des 12 premiers jours, mais également des jours qui suivent.
Mais dans la question, il y a inversion seulement pour les 12 premiers jours et le demandeur aimerait savoir pourquoi.

Mon code veut précisément expliquer pourquoi il y a inversion des 12 premiers jours dans le cas où la date correspond pourtant aux paramètres régionaux et veut montrer une façon d'éviter cette inversion.
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 Sub Demo_Inversion() Ladate = "10/01/15-02:03:04" Range("B1:D4").Clear Set B1 = Range("B1") Set b2 = Range("b2") Set b3 = Range("b3") Set b4 = Range("b4") B1.Value = Ladate b2.Value = Ladate b3.Value = Ladate b4.Value = Ladate B1.NumberFormat = "@" b2.NumberFormat = "dd/mm/yyyy" b3.NumberFormat = "dd/mm/yyyy" b4.NumberFormat = "@" B1.Offset(, 2) = TypeName(Replace(B1.Value, "-", " ")) & " vers " & B1.NumberFormat B1.Value = Replace(B1.Value, "-", " ") b2.Offset(, 2) = TypeName(Replace(b2.Value, "-", " ")) & " vers " & b2.NumberFormat b2.Value = Replace(b2.Value, "-", " ") b3.Offset(, 2) = TypeName(CDate(Replace(b3.Value, "-", " "))) & " vers " & b3.NumberFormat b3.Value = CDate(Replace(b3.Value, "-", " ")) b4.Offset(, 2) = TypeName(CDate(Replace(b4.Value, "-", " "))) & " vers " & b4.NumberFormat b4.Value = CDate(Replace(b4.Value, "-", " ")) End Sub
La seule chose qui varie entre la ligne 1 et 2, c'est la propriété Numberformat de la cellule. Et il y a inversion dans un cas et pas dans l'autre.
La seule chose qui varie entre la ligne 3 et 4, c'est la propriété Numberformat de la cellule. Et il y a inversion dans un cas et pas dans l'autre.

Pourquoi n'y a-t-il pas inversion quand la valeur envoyée est de type String et la propriété Numberformat est Texte?
Ou quand la valeur envoyée est de type Date et la propriété Numberformat est Date?

Est-ce qu'il y aurait inversion seulement quand le type de la date envoyée est différent du type généré par la propriété NumberFormat ?

04/12/2015, 11h11
Oliv-

Bonjour,
Vraiment très intéressant et enrichissant ce sujet ! les experts sont en plus au rendez-vous.

Alors ne pourrions nous pas ensemble créer une fonction qui fasse la conversion dans la majorité des cas ?
Etant donné que généralement on ne fait pas cela juste pour une date mais tout une colonne, on doit avoir de la matière pour trouver où se trouvent les élements DD MM YYYY hh:m:ss.
en éliminant les caractères incorrects puis en utilisant par exemple les REGEX,
on peut trouver facilement l'année = 4 chiffres (sauf dans l'exemple ici présent de notre ami)
on isole les heures avec la présence de ":"
on parcours toutes les zones où il y a 2 chiffres et lorsque cela dépasse 12 = jour.

si on n'a pas assez de matière on demande à l'utilisateur de parser la date...
Qu'en pensez vous ?
04/12/2015, 13h33
Marc-L

Pierre, j'ai dû avoir du bol car pour des applis développées en France et utilisées worldwide, je n'ai jamais eu ton cas ! …

Citation:

Envoyé par Pierre Fauconnier

Au delà du post initial, cette discussion permet de mettre en lumière que les paramètres régionaux peuvent jouer des tours assez gênants, notamment avec une formule du style =texte(a1;"jj/mm/aa") qui va mal passer sur un Excel anglais parce que "jj/mm/aa" ne sera pas traduit par Excel en "dd/mm/yy".

Dans le monde globalisé dans lequel nous travaillons, il n'est pas rare qu'un fichier Excel "voyage" des USA en Inde en passant par la France puis le Japon... Dans ce cadre de travail, il est particulièrement important d'être attentif à ces notions de dates et de paramètres régionaux, que ce soit en Excel pur ou en VBA.

Pas de souci dans ce cas de mon côté en VBA soit via la propriété International soit via la puissante fonction Evaluate …

Exemple pour International

Exemple pour Evaluate
04/12/2015, 14h59
Pierre Fauconnier

Yeah, Marc... Enfin une solution autre que le parsing qui tient la route... pour VBA Excel...

Solution à laquelle je n'avais pas pensé parce que confronté au même problème en Access, qui n'expose pas EVALUATE par exemple, j'étais parti plus naturellement sur le parsing.

Mais pour Excel, c'est à retenir.

Merci!
04/12/2015, 18h49
Invité
Salut,

Comme ça, cela fonctionne ?
Code:

1 2 3 4 Sub WriteDateInRange() Me.Range("E1").Value = CDate(Format$(VBA.Replace(Me.Range("A1").Value, "-", " "), "dd/mm/yy hh:mm:ss")) End Sub
La donnée d'entrée est en A1, et est écrite en E1.
Il n'y a pas de validation de données d'entrée. Le texte doit correspondre au format fourni par 'CeluiQuiCherche' en 1ère page (sous la forme 05/11/15-19:30:28).

En espérant ne pas écrire de conneries :mouarf:

EDITÉ

Citation:

Envoyé par Pierre Fauconnier

Salut Docmarty

...

De plus, le format n'influe en rien la valeur contenue dans la cellule. ...

Personnellement, j'ai rencontré de nombreuses fois ce problème entre mon pc où tout fonctionne bien et celui de certains clients chez qui l'inversion était effectuée, et à ce jour, la seule réponse que j'ai trouvée est celle que j'ai renseignée.

Mais si tu as d'autres pistes testées sur divers environnements au niveau des paramètres régionaux, je suis preneur... :)

Bonjour Pierre

Tu as raison de dire que "le format n'influe en rien la valeur contenue dans la cellule". Car il influe uniquement sur la future valeur qui sera attribuée à la cellule.
D'après une recherche très poussée que j'ai effectuée, l'inversion jour/mois dans une cellule est toujours reliée au format de la cellule. Je t'en donne un exemple en reprenant ton code: l'inversion se fait avec tous les paramètres régionaux sauf le paramètre Anglais(Etats-Unis).
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Sub Test() Dim ligneforme As Long Dim Cell As Range ligneforme = 0 Set Cell = Cells(ligneforme + 1, 2) Do While Cell.Value <> "" Cell.Offset(, 1).NumberFormat = "@" Cell.Offset(, 1).Value = DateSerial(Mid(Cell.Value, 7, 2), Mid(Cell.Value, 4, 2), Left(Cell.Value, 2)) Cell.Offset(, 2).NumberFormat = "General" Cell.Offset(, 2).Value = DateSerial(Mid(Cell.Value, 7, 2), Mid(Cell.Value, 4, 2), Left(Cell.Value, 2)) Set Cell = Cell(2) Loop End Sub

Tout le problème avec les dates au niveau des paramètres régionaux est au contraire relié au type de la valeur envoyée par VBA et au type de la cellule réceptrice, autrement dit à sa propriété NumberFormat. Je m'explique : la raison en est que, dès que VBA écrit une valeur dans une cellule, une fonction de conversion de type intervient
1) d'abord pour vérifier si le type de la valeur envoyée par VBA et le type de valeur désiré par la cellule selon sa propriété NumberFormat sont identiques ( 3 types sont possibles dans la cellule : Date, Double ou Texte )
2) si les types ne sont pas identiques, la fonction de conversion procède à la conversion du type de la valeur envoyée. Exemple : si la valeur envoyée est de type Date et la cellule de type Texte, une conversion de type a lieu.
Notre problème avec les dates, c'est que cette fonction de conversion de type ne connaît que 2 formats de dates : le format américain (m/j/aaaa) et le format international (aaaa/m/j). D'où l'inversion jour/mois si le format de la date envoyée est j/m/aaaa.

Sachant cela, il faut absolument s'arranger pour que cette inversion de dates n'ait pas lieu.
Une façon de faire, c'est de toujours envoyer vers la cellule une valeur du type attendu par la cellule pour que la conversion de type ne soit jamais requise lors du transfert de la valeur vers la cellule. Pas de conversion de type, donc pas d'inversion jour/mois.

Une autre façon de faire, c'est d'utiliser .FormulaLocal à la place de .Value pour indiquer à VBA que la valeur est une valeur locale qu'il doit accepter telle quelle sans la modifier.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Sub test_FormulaLocal_vs_Value() Dim t d = CDate("2015-01-31") Range("A1:A31").NumberFormat = "General" For i = 1 To 31 Cells(i, 1).Value = d + i Next Set r = Range("A1:A31") t = r.Value Range("D1:D31").NumberFormat = "@" Range("E1:E31").NumberFormat = "@" Range("D1:D31").FormulaLocal = t ' Valeur de Type Date --> cellule Texte; il n'y a pas d'inversion jour/mois avec FormulaLocal Range("E1:E31").Value = t ' Valeur de Type Date --> cellule Texte; il y a inversion jour/mois avec Value End Sub
'Illustration du rôle de la propriété NumberFormat dans l'inversion jour/mois

En B5 une date de type Date vers une cellule qui attend une valeur de type Texte requiert la conversion de type et provoque l'inversion.
En C8, une date de type String vers une cellule qui attend une valeur de type Date requiert la conversion de type et provoque l'inversion.
En D8, une date de type String vers une cellule qui attend une valeur de type Double provoque l'inversion.
En D5, une date de type Date vers une cellule qui attend une valeur de type Double ne requiert pas de conversion de type. Pas d'inversion.
En C5, une date de type Date vers une cellule qui attend une valeur de type Date ne requiert pas de conversion de type. Pas d'inversion.
En B8, une date de type String vers une cellule qui attend une valeur de type String ne requiert pas de conversion de type. Pas d'inversion.
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 Sub Typename_Date_vers_Numberformats() Columns("A:D").Clear Dim s As Date s = CDate("10-01-2000") Cells(1, 1) = "Valeur attendue" Cells(1, 2) = CDate(s) Cells(1, 3) = CDate(s) Cells(1, 4) = CDbl(CDate(s)) col = 1 lig = 5 Cells(lig, "B").NumberFormat = "@" Cells(lig, "C").NumberFormat = "General" Cells(lig, "D").NumberFormat = "0" Cells(lig - 1, "B").Value = "'" & Cells(lig, "B").NumberFormat Cells(lig - 1, "C").Value = "'" & Cells(lig, "C").NumberFormat Cells(lig - 1, "D").Value = "'" & Cells(lig, "D").NumberFormat Cells(lig - 1, "A").Value = TypeName(s) & " -->" Cells(lig, "A").Value = "Valeur obtenue" Cells(lig, "B").Value = s Cells(lig, "C").Value = s Cells(lig, "D").Value = s Call Typename_String_vers_Numberformats Cells(lig - 2, "B").Value = "Proprietes NumberFormat" End Sub Sub Typename_String_vers_Numberformats() Dim s As String s = "10-01-2000" Cells(1, 2) = CDate(s) Cells(1, 3) = CDate(s) Cells(1, 4) = CDbl(CDate(s)) col = 1 lig = 8 Cells(lig, "B").NumberFormat = "@" Cells(lig, "C").NumberFormat = "General" Cells(lig, "D").NumberFormat = "0" Cells(lig - 1, "B").Value = "'" & Cells(lig, "B").NumberFormat Cells(lig - 1, "C").Value = "'" & Cells(lig, "C").NumberFormat Cells(lig - 1, "D").Value = "'" & Cells(lig, "D").NumberFormat Cells(lig - 1, "A").Value = TypeName(s) & " -->" Cells(lig, "A").Value = "Valeur obtenue" Cells(lig, "B").Value = s Cells(lig, "C").Value = s Cells(lig, "D").Value = s End Sub