Discussion :

[patricktoulon]

je suis tomber sur une discussion a la quelle j'ai répondu et j'ai décidé d'aller plus loin dans mes recherche
alors le sujet c'etait les dates
ah... les dates francais /americains /javanorusse

j'ai voulu par exemple transférer des dates au format "dd.mm.yyyy" d'une colonne a une autre en changeant le separateur textuel "." pour "/" et sans boucle

je me suis dit je vais faire une fonction public puis inserer la formules dans les cellules concernées jusque la rien de bien compliqué

mais la ou le bas blaisse c'est le format
et tout particulierement l'enorme coquille dans la fonction interne entre les formats dd/mm/yyyy" "m/d/yyyy" et enfin "mm/dd/yyyy" tout du moins les deux derniers

voila deux fonctions qui parraissent similaires mais il n'en est rien

Code :

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

1
2
3
Public Function convertDATE1(cel)
    convertDATE1 = CDate(Replace(cel.Text, ".", "/"))
 End Function

la 2d

Code :

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

1
2
3
Public Function convertDATE2(cel)
     convertDATE2 = Replace(cel.Text, ".", "/")
 End Function

bon elle ne sont pas tres compliqué faut etre honnete

maintenant faisons quelques testes avec la fonction 1

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
Sub test_convertedate1_1()
    With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
        .Offset(0, 1).FormulaR1C1 = "=convertdate1(RC[-1])"
        .Offset(0, 1).NumberFormat = "dd/mm/yyyy"
    End With
End Sub
'
Sub test_convertedate1_bis()
    With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
        .Offset(0, 1).FormulaR1C1 = "=convertdate1(RC[-1])"
        .Offset(0, 1).NumberFormat = "mm/dd/yyyy"
    End With
End Sub
'
Sub test_convertedate1_trois()
    With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
        .Offset(0, 1).FormulaR1C1 = "=convertdate1(RC[-1])"
        .Offset(0, 1).NumberFormat = "m/d/yyyy"
    End With
End Sub

et bien examinez le résultat avec la "bis" et trois" la 1ere n'en parlons pas nous connaissons ce soucis (francais/americain)


bien maintenant testons la 2d 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
Sub test_convertedate2_1()
    With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
        .Offset(0, 1).FormulaR1C1 = "=convertdate2(RC[-1])"
        .Offset(0, 1).NumberFormat = "m/d/yyyy"
    End With
End Sub
'
Sub test_convertedate2_bis()
    With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
        .Offset(0, 1).FormulaR1C1 = "=convertdate2(RC[-1])"
        .Offset(0, 1).NumberFormat = "mm/dd/yyyy"
    End With
End Sub
'
Sub test_convertedate2_trois()
    With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
        .Offset(0, 1).FormulaR1C1 = "=convertdate2(RC[-1])"
        .Offset(0, 1).NumberFormat = "dd/mm/yyyy"
    End With
End Sub

examinez bien le resultat avec les 3


la celui qui me l'expliquera dans un language que je comprends méritera un + 1000
y a vraiment de quoi tourner en bourique non?
messieux a vous l'honneur

[Invité]

Bonjour Patrick,

les date en VBA sont au format américain, excel et Cdate prennent en compte les paramètres régionaux!

si tu fais un replace tu fournis à excel le choix d’interpréter une valeur du type américain en régional!

idéalement il faudrait spliter la date et utiliser DateSerial(2018, 4, 1) qui également prend en compte paramétres régionaux.

qu'adviendrait il d'une date française avec Cdate et le paramètres régionaux américain ?

[clementmarcotte]

Bonjour,

Bonjour Patrick,

les date en VBA sont au format américain, excel et Cdate prennent en compte les paramétres régionaux!

idéalement il faudrait spliter la date et utiliser DateSerial(2018, 4, 1) qui également prend en compte paramétres régionaux.

J'ai joué un peu avec le macros de Patrick. Avec des paramètres régionaux canadiens-français et la date au format ISO (AAAA-MM-JJ, avec des tirets au lieu de la barre oblique, c'est délirant.

[Invité]

Bonjour Clément,

oui le format international c'est l'idéal mais encore, dans le cas de transfert internationales, puisse être interprété comme date la valeur d'origine!

Pierre Fauconnier nous l'a démontré sur un autre poste!

Il faut à coup sur s'affranchir des paramètres régionaux!

[eriiic]

Bonjour,

Code :

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

.NumberFormat = "m/d/yyyy;"

lui remet les idées en place apparemment.
eric

[Docmarti]

Bonne annee a tous.

Quand tu changes manuellement le format de date d'une cellule, tu vas
dans Format de cellule/ Nombre puis tu choisis Date.
Tu vois alors apparaitre differents formats de date mais le premier est precede de "*".
Si tu choisis ce format de date precede de "*", Excel attribuera m/d/yyyy a la propriete Numberformat de la cellule.

m/d/yyyy indique a Excel qu'il doit afficher le format de date courte tel que defini dans les parametres linguistiques et locaux de Windows.
L'affichage sera donc different selon le pays et la langue, mais la propriete numberformat sera toujours la meme qu'importe la langue ou le pays.

[patricktoulon]

Bonjour,

Code :

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

.NumberFormat = "m/d/yyyy;"

lui remet les idées en place apparemment.
eric

@eriic
oui eriic ca m'a l'air d'etre un truc du genre en tout cas les resultats ne sont pas conforme a l'argument de numberformat selon ces exemples

@docmarti :heu.... franchement ta reponse ne m'aide pas ca m'explique pas (date courte/ date pas courte) le probleme de format non respecté

je parle du resultat qui ne correspond pas a l'argument textuel

m/d/yyyy c'est bien mois jour année non?

si j'essai d'interpréter ceci

m/d/yyyy indique a Excel qu'il doit afficher le format de date courte tel que defini dans les parametres linguistiques et locaux de Windows.
L'affichage sera donc different selon le pays et la langue, mais la propriete numberformat sera toujours la meme qu'importe la langue ou le pays.

ca voudrais dire que l'argument textuel n'a rien a voir avec son veritable effet et que comme on est en france le format appliqué sera dd/mm/yy qui est le format court francais si je ne me trompe pas

au quel cas il ne fonctionne pas puisque j'obtiens le long format dd/mm/yyyy

en tout cas si tu a raison tu viens de resoudre un bon nombre de question sur ce forum et que avec cet argument on a a coup sur le bon format en france tel que nous le concevons

non pas clair du tout

edit:
je viens de faire deux tests simple et bizarement ici l'argument textuel correspond au format appliqué

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub test1()
With Cells(1, 1)
.Value = CDate("04/05/2017")
.NumberFormat = "m/d/yy"
End With
End Sub
 
Sub test()
With Cells(2, 1)
.Value = CDate("20/05/2017")
.NumberFormat = "m/d/yy"
End With
End Sub

il faudra m'expliquer

[patricktoulon]

J'ai joué un peu avec le macros de Patrick. Avec des paramètres régionaux canadiens-français et la date au format ISO (AAAA-MM-JJ, avec des tirets au lieu de la barre oblique, c'est délirant.

bonjour clement oui c'est vraiment delirant

un truc tres simple et encore plus delirant

prend n'importe quelle cellule dans la feuille qui n'ai jamais été touchée d'aucune sorte tu regarde son format il sera en "standard" d'accords

et bien met lui une date

.value="20/01/2018"
maintenant regarde sa propriété elle sera plus en standard mais en rien du tout sans l'avoir fait avec numberformat


ok d'accords c'est donc une date qui n'en ai pas une

maintenant change le format de date par exemple ".numberformat="m/d/yy"
et puis non demo code et image

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
Sub test1()
With Cells(1, 1)
.Value = CDate("04/05/2017")
.NumberFormat = "m/d/yy"
End With
End Sub
'
Sub test2()
With Cells(2, 1)
.Value = CDate("20/05/2017")
.NumberFormat = "m/d/yy"
End With
End Sub
'
Sub test3()
With Cells(3, 1)
.Value = "20/05/2017"
.NumberFormat = "m/d/yy"
End With
End Sub
'
Sub test6()
With Cells(4, 1)
.Value = "01/01/2018"
.NumberFormat = "m/d/yy"
End With
End Sub

franchement perso j'en perds mon latin je sais pas pour vous
il me reste plus qu'a me fier a grand mere lustuvru (pas d'oeufs félés chez lustucru)
mon je vais chercher de l'aspirine il me reste plus que ca hein

c'est bon vous la voyez la coquille ?

si vous pensez avoir compris grace au 2 dates "20/05/2017" et "01/01/2018
histoire de vous rendre encore plus dingue ajoutez ces deux tests et vos précédentes conclusion seront walouh walouh

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub test7()
With Cells(5, 1)
.Value = "20/05/2017"
.NumberFormat = "mm/dd/yy"
End With
End Sub
Sub test8()
With Cells(6, 1)
.Value = "01/01/2018"
.NumberFormat = "yy/mm/dd"
End With
End Sub

ET MEME MIEUX


CE SIMPLE TEST RESUME TOUT LES AUTRES

regardez bien ou ce trouve les mois et jour dans les 12 premiers et dans les suivants

Code :

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

1
2
3
4
5
6
7
8
Sub test_trop_rigolo()
For i = 1 To 31
With Cells(i, 1)
.Value = Format(i, "##") & "/01/2018"
.NumberFormat = "yy/mm/dd"
End With
Next
End Sub

demain j'arrete

[unparia]

Bonjour à tous
Il serait assez sage de rester avec les deux pieds sur terre.
Ne pas confondre les "largesses" de VBA avec des obligations de VBA.
Lorsque VBA n'a aucun doute dans l'examen d'une chaîne de caractères (et donc de ce qui n'est pas défini EXPRESSEMENT comme étant une date), il se montre assez magnanime et veut bien transformer en type date ce qui n'était qu'une chaîne de caractères, si on lui attribue un format laissant penser que l'on veut en fait traiter une date.
Lorsqu'un doute subsiste, il ne modifie que le format de la chaîne de caractères, qu'il laisse typée comme elle l'était (chaîne de caractères et non date).

Quand VBA s'octroit-il (abusivement, à mes yeux personnels ... de la même manière que je considère abusives TOUTES LES autres LARGESSES de VBA) la liberté de "transformer" en vraie date ce qui n'était qu'une chaîne de caractères ? --->> tout simplement lorsque, à la fois :
- le nombre représenté par les deux premiers caractères d'une chaîne @@/@@/@@@@ est inférieur à 13 (en rappelant que VBA est internement conçu "en anglo-saxon", avec des dates exprimées sous la forme mm/dd/yyyy.
- le nombre représenté par les 2 caractères à partir du 3ème lui parait également "compatible"

Dans ce rôle de "grand seigneur" voulant distribuer des "caresses", VBA se trouve forcément embêté et peut bien évidemment dans certains cas ne plus savoir quel "cadeau" faire en fait .

Conclusion (et je ne saurais jamais trop insister en ce qui concerne cet aspect) : Ne jamais ACCEPTER la moindre LARGESSE (de qui que ce soit, d'ailleurs, dans la vraie vie également) : rester RIGOUREUX à CHAQUE INSTANT et les vaches se porteront bien.
Amitiés

EDIT : cela me rappelle un procès fait il y a quelques décennies par un clochard à une vieille dame qui avait pris l'habitude de lui donner une pièce de cinq francs chaque jour (elle n'y était pas obligée. C'était là une largesse). Elle a été poursuivie en justice (et a perdu) par le clochard concerné le jour où elle a cessé de lui donner cette pièce de cinq franc tous les jours.

EDIT 2 : une analogie permettra de mieux "cerner" tout cela :
Que se passe-t-il lorsque, sur ce forum, un demandeur se montre insuffisamment précis en ce qui concerne les tenants et aboutissants ? --->>
- Il arrive que d'aucuns DEVINENT ce dont il s'agit et lui donne une réponse satisfaisant le demandeur.
- Mais il arrive également que ce qu'ils croyaient avoir "deviné ne correspond finalement pas à ce qui voulait être exprimé
Quel est alors le comportement reprochable ? -->>
- celui du répondeur, trop certain d'avoir deviné et n'exigeant pas (voulant se montrer "magnanime") les précisions nécessaires ?
- celui du demandeur, trop imprécis ?
Voilà tout.

[patricktoulon]

bonjour jacques je comprends bien le principe du depassement de 12 pour vba et sa largesse par rapport a ca conception de date initialement angloxasonne

il faut reconaitre que c'est gros quand meme je ne sais pas si on peut appeler cela de la largesse

autrement dit

avec une chaine de caracteres formant une date vba ou excel interprete la date valide du 1 a 12/1 a 12/xxxx et peut donc la formater ,apres circulez y a rien a voir jusqu'au 1er du mois prochain

bon ben on va faire avec n'importe comment on a pas le choix

[unparia]

apres circulez y a rien a voir

est exactement la réponse qu'il conviendrait de faire dans certains cas (voir le EDIT 2 de mon message précédent).
Ne jamais distribuer de largesses est la meilleure manière de ne pas ensuite se faire "accuser", comme est ici "accusé" VBA !

[Invité]

Bonjour,
Il ne faut jamais au grand jamais fournir des date vue par vba au format américain à Excel qui va convertir en fonction de paramètres régionaux!

Soit comme l'a dit Clément au format internationale soit en décomposant année,mois,jour!

Code :

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

1
2
3
[A1]="2018-01-04"
[A2]=DateSerial(2018, 1, 4) 
[A3]"04/01/2018" Donnera 01/04/2018 tu parle d'un poisson!

Format("04/01/2018" "yyyy-mm-dd") donnera 01/04/2018 si les paramètres régionaux sont américain!

Vba étant américain il valide une date en fonction de cela de son point de vue 56/4/5 est validé le résultat au petit bonheur la chance!
Vba ou Excel va triturer pour trouver la première date valide.

[unparia]

Il est tout simplement anormal qu'un langage de développement se substitue à l'utilisateur pour décider à sa place de ce qu'il veut, au prétexte de l' "aider".
C'est un reproche que je fais personnellement à VBA.
Il est par exemple et entre autres anormal que si l'on écrit :

Code :

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

Range("A1").Value = "1"

ce qui devrait manifestement montrer la volonté d'y mettre la chaîne de caractères "1", VBA la transforme de manière péremptoire en numérique 1 et qu'il faille, si l'on veut se faire respecter, agir en plus sur le format de la cellule (à passer en format texte).
Je comprends bien que Excel a lui (de son côté), besoin de cette précision, dans la mesure où la frappe de l'utilisateur se fait dans une zone d'édition (en arrière plan) qui, étant toujours la même (et forcément toujours du même type alphanumérique), pour déterminer la volonté réelle de l'utilisateur.
Je devine dans la foulée que l'on ("on", c'est l'équipe Microsoft Office) a choisi la facilité : tout traiter de la même manière, que la source soit une saisie clavier ou une instruction VBA ... moins de boulot ...
Il eût à mon sens été préférable de "lire" la volonté de développeur VBA dans le typage même de la valeur à inscrire. Et de ne pas la "discuter", ni même lui proposer un format (sauf de présentation d'affichage de ce qui est uniquement numérique -donc y compris les dates).
Ainsi :

Code :

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

Range("A1").Value = "12/12/2010"

ne devrait nullement être "interprété et être inscrit en chaîne uniquement alphabétique.
Alors que

Code :

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

Range("A1").value = (toute expression de date autre que purement alphanumérique)

devrait être considéré comme uniquement du numérique auquel l'utilisateur devrait alors donner (s'il le souhaite) le format d'affichage de son choix.

Ce ne sont là que des exemples de ce que j'appelle les "largesses finalement contre-productives"

[Invité]

Microsoft implémente ses outils pour une utilisations large public, il réutilise ses librairie dans la programmation vba mais considère cependant que le développeur est capable via les bonnes instruction d'arriver à ses fin!

Il est évidant, avec parfois des artifice, qu'il est possible d'obtenir le résultat souhaiter mais que de prouesse à mettre en oeuvre !

Je vous renvoi au sondage posant la question de savoir si vba était un langages de programmation professionnel j'ai répondu non!

[AurelienG_]

Ajout d'informations (peut être que ca fera avancer le sujet on ne sait jamais ) :

J'ai été confronté à ce problème de format de dates plus d'une fois et j’avoue que c'est déroutant de la part d'Excel. Pour remédier le problème je me suis martelé dans la tête le principe suivant : VBA est en anglais, Excel est en langue locale.

En conséquence je ne travaille qu'avec le format anglais quand j'utilise le format date (ou je contourne en faisant une conversion date > décimal, puis mes calculs sur dates, puis décimal > date), la conversion au format Européen est ensuite effectuée par les paramètres régionaux de l'ordinateur et/ou d'Excel. Ainsi si je souhaite afficher une date au format jj/mm/aaaa en français dans Excel, je travaille et convertis au format mm/dd/yyyy dans VBA.

Si tu veux un autre exemple d'application qui illustre la différence entre la "langue" de VBA et celle d'Excel , saisis une formule quelconque en mode enregistrement et observe ton code : la formule est systématiquement mise en anglais et au format L1C1.
Ainsi, une formule "si" saisie en B2 : =SI(A1=3;.....) va devenir .formula = "=IF(L(-1)C(-1)=3.....". Tu peux néanmoins forcer la conversion en langue régionale grâce à la méthode .FormulaLocal (mais à ce jour je ne connais pas de méthode .DateLocal )

Bien à toi.

[unparia]

Bonjour AurelienG
La méthode de très loin la plus sensée est celle indiquée par Robert (dysorthographie). Une date interne n'est jamais rien d'autre qu'un numérique. Son format d'affichage est ensuite celui que l'on décide, ce numérique restant toujours le même, lui, quels que puissent être les paramètres locaux.

[AurelienG_]

Bonjour AurelienG
La méthode de très loin la plus sensée est celle indiquée par Robert (dysorthographie). Une date interne n'est jamais rien d'autre qu'un numérique. Son format d'affichage est ensuite celui que l'on décide, ce numérique restant toujours le même, lui, quels que puissent être les paramètres locaux.

.....Et je suis parfaitement d'accord sur ce point. Le problème vient surtout du fait qu'il est très facile vu la souplesse du VBA de se mélanger les pinceaux avec les valeurs en elle-même et leur format.

Ce que je veux mettre en avant, c'est qu'il faut bien séparer ces 2 composantes et utiliser les instructions/méthodes adaptées à ce que l'on utilise (date numérique ou pur numérique ?) et surtout comprendre de quel point de vue on se place dans un programme pour anticiper les erreurs de conversion et/ou les conversion forcées. Pour exemple : ajouter 1j3h45min (soit 01:03:45:00, 27.75h, 1.15625j,....) à une date peut se faire de plein de manières possibles si on parle en date ou en numérique, ou plus compliqué encore, en fraction journalière ou en centiminutes, ce à quoi il faut (en plus) rajouter les contraintes de format.

[Docmarti]

ca voudrais dire que l'argument textuel n'a rien a voir avec son veritable effet et que comme on est en france le format appliqué sera dd/mm/yy qui est le format court francais si je ne me trompe pas

au quel cas il ne fonctionne pas puisque j'obtiens le long format dd/mm/yyyy

Si, avec le format m/d/yyyy, tu obtiens un affichage au format dd/mm/yyyy, c'est que le format Date courte dans tes parametres linguistiques et regionaux de Windows est dd/mm/yyyy.

Tu as tres bien compris. Le format m/d/yyyy est un format magique qui commande a Excel d'afficher avec le format Date courte qui est defini par l'utilisateur dans les parametres linguistiques de Windows. Si tu modifies le parametre Date courte dans ton Windows, l'affichage dans Excel des cellules au format m/d/yyyy sera instantanement modifie.

L'utilisation de ce format magique me semble un moyen facile d'exporter un fichier Excel dans le monde entier. La valeur de la date est numerique et le format d'affichage s'adaptera au format date courte de la langue locale.

[Docmarti]

ca voudrais dire que l'argument textuel n'a rien a voir avec son veritable effet et que comme on est en france le format appliqué sera dd/mm/yy qui est le format court francais si je ne me trompe pas

au quel cas il ne fonctionne pas puisque j'obtiens le long format dd/mm/yyyy

en tout cas si tu a raison tu viens de resoudre un bon nombre de question sur ce forum et que avec cet argument on a a coup sur le bon format en france tel que nous le concevons

non pas clair du tout

edit:
je viens de faire deux tests simple et bizarement ici l'argument textuel correspond au format appliqué

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub test1()
With Cells(1, 1)
.Value = CDate("04/05/2017")
.NumberFormat = "m/d/yy"
End With
End Sub
 
Sub test()
With Cells(2, 1)
.Value = CDate("20/05/2017")
.NumberFormat = "m/d/yy"
End With
End Sub

il faudra m'expliquer

Pourquoi tu fais un test avec

Code :

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

.NumberFormat = "m/d/yy"

Le seul format de date (propriete NumberFormat) qui indique a Excel d'afficher la date courte telle que definie dans les parametres regionaux de Windows de l'ordinateur est le format suivant:

Code :

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

.NumberFormat = "m/d/yyyy"

[Pierre Fauconnier]

Salut.

Comme le dit Robert, une date est un nombre dans Excel.

Si je reçois une chaine de caractères représentant une date (censée réprésenter une date), je vais m'assurer du format, transformer en nombre (date réelle) puis pousser en Excel, avec éventuellement un format spécifique (format qui ne change en rien la valeur de ladite date).

Ca pourrait déja éviter des surprises (et encore! Car avec le calendrier 1904 d'Excel, ça peut encore être beaucoup plus rigolo ⁽¹⁾)

La meilleure façon de travailler serait que les exports et imports soient réalisés en exportant et important les dates au format international, laissant à chacune des parties le soin de traiter la date comme il l'entend mais utilisant un standard commun pour les échanges.

Je rejoins donc Jacques par rapport aux largesses d'Excel et préfère, autant que faire se peut⁽²⁾, utiliser une norme de transfert indiscutable plutôt que de laisser faire Excel/VBA ou de tenter, par code empirique, d'évaluer la date reçue.

⁽¹⁾ Il suffit de saisir la date du 16/03/2018 dans une cellule puis de regarder ce qu'elle vaut en numérique (Excel ou VBA). Avec un calendrier Excel normal (Option calendrier 1904 décochée), la date a 43175 comme numéro de série.

En cochant la case Calendrier 1904 dans les options, le numéro de série reste 43175 en Excel et devient dans la cellule le 17/03/2022, qui vaut en VBA 44637. Donc, alors que le numéro de série de la date Excel n'a pas changé, VBA, qui se fout quant à lui du calendrier 1904, continue à utiliser son système et donc altère la valeur numérique (le numéro de série)...



⁽²⁾ Je sais d'expérience que je rêve