J'ai une colonne qui contient la date et l'heure des e-mails d'Outlook. Certaines dates sont au format - 2 janvier 2020 16h15 , 14 janvier-20 12h44 , 24-19 décembre 20h15 PM .
J'ai essayé d'utiliser les fonctions Remplacer et Remplacer, Remplacer fonctionne comme défini mais de la chaîne, l'heure est supprimée. J'aimerais avoir toutes les dates sous la forme 2019-12-27 15:02 PM .
sub Replace()
Dim sString, searchString as String
dim i, LastCell as Long
LastCell = Range("C" & Rows.Count).End(xlUp).Row
Set searchRng = Range("C3:C" & lastCell)
For Each c In searchRng
sString = c
searchString = "-"
If InStr(1, sString, searchString, vbTextCompare) > 0 Then
i = InStr(1, sString, SearchString, vbTextCompare)
i = InStr(i + 1, sString, SearchString, vbTextCompare)
c.Offset(0, 1) = WorksheetFunction.Replace(sString, i, "19", " 2020")
End If
Next c
End Sub
3 Réponses :
CDate convertira une date / heure qui est une chaîne en une date / heure réelle qui peut être formatée.
Donc, code comme:
For Each c In rngSrc
c.Offset(0, 1) = CDate(c)
c.Offset(0, 1).NumberFormat = "yyyy-mm-dd h:mm AM/PM"
Next c
Pourquoi ne pas le faire en 1 ligne? quelque chose comme c.offset (0, 1) = format (C.Value), "AAAA-MM-DD HH: NN AM / PM")
@FoxfireAndBurnsAndBurns La fonction Format renvoie une chaîne. Mon objectif est de renvoyer une date réelle (numéro de série) qui peut être manipulée comme une date.
@FoxfireAndBurnsAndBurns Et, en fonction de toutes les exigences, je formaterais probablement toute la plage de destination en une seule étape. Mais le code ci-dessus est juste pour la preuve de concept .
@RonRosenfeld Il vaut peut-être la peine de mentionner que si vous laissez Excel convertir une chaîne en une date qu'Excel devine simplement et que vous n'avez aucune influence sur ce que fait réellement Excel. Ici, dans cet exemple, c'est assez clair et donc cela fonctionne. Mais si votre chaîne ressemble à 02/03/2020 , le résultat n'est pas clair (peut être mm / jj / aaaa ou jj / mm / aaaa ) et Excel pourrait se tromper! • En fait, la seule méthode sûre consiste à analyser et diviser la chaîne et à créer une date / heure réelle avec DateSerial / TimeSerial .
@ Pᴇʜ CDate est OK avec des dates sans ambiguïté, comme cela a été donné à titre d'exemple. Si vous avez une date ambiguë, vous devez fournir des informations externes pour pouvoir l'analyser correctement. Mais je ne pense pas que cela fasse partie du problème ici, donc je ne suis pas entré dans toutes les ramifications de l'utilisation des dates dans VBA / Excel.
@RonRosenfeld Oui, c'est ce que j'ai dit, ici ça marche! Je viens de le mentionner que personne ne se demande que cela ne fonctionne pas pour d'autres dates :) Non, problème ici.
Mais en fait, j'obtiens une incompatibilité de type d'erreur d'exécution 13 sur CDate (c) si ce sont des chaînes et aucune date. Au moins dans un Excel allemand. Il ne semble donc pas que fiable.
@ Pᴇʜ Oui, il faudrait ajouter quelques tests pour s'assurer que CDate ne renvoie pas d'erreur, si c'est une possibilité et si c'est l'endroit pour le vérifier. Comme je l'ai écrit dans un autre commentaire, le code que j'ai fourni est une preuve de concept et non un code fini pour la distribution.
@RonRosenfeld Après avoir exécuté votre code, date - 24 décembre-19 10h58 convertie en 2024-12-19 10h58.
@Cody Je ne peux pas reproduire ce problème ici. Pour moi, il renvoie 2019-12-24 10:58 AM comme prévu, la date courte des paramètres régionaux de Windows étant DMY ou MDY . Quels sont vos paramètres régionaux Windows pour la date courte? Et quelle est votre langue? Ou peut-être y a-t-il quelque chose sur la façon dont vous avez intégré cette partie de code dans le reste de votre code?
@RonRosenfeld Merci pour toutes les suggestions. Cependant, en utilisant la méthode d'expression régulière, cela a fonctionné au point pour mon scénario.
Je pense toujours que vous pouviez le faire plus facile:
'
'
'
LastCell = Range("C" & Rows.Count).End(xlUp).Row
With Range("C3:C" & lastCell).Offset(0, 1)
.FormulaR1C1 = "=TEXT(RC[-1],""yyyy-mm-dd hh:mm AM/PM"")"
.Value = .Value
End With
Un moyen vraiment sûr de le faire sans laisser Excel deviner:
Utilisez un Expression régulière pour analyser et diviser la date en morceaux (par exemple en utilisant ce modèle ):
Et puis utilisez le Fonction DateSerial et TimeSerial function pour créer une valeur de date réelle:
Range("A1").NumberFormat = "yyyy-mm-dd hh:mm AM/PM"
Cela peut être écrit en tant que valeur de date réelle dans une cellule:
Range("A1").Value = RealDateTime
Et formaté comme vous le souhaitez (car il s'agit d'une valeur de date réelle)
Dim RealDateTime As Date RealDateTime = DateSerial(2020, 2, 20) + TimeSerial(16, 50, 22)
sont les "dates", les dates vraies ou les chaînes de texte qui ressemblent à des dates? Si vous modifiez le format numérique de la cellule en "Général", la date est-elle modifiée en nombre ou reste-t-elle la même?
Où
DataStringest déclaré et quand reçoit-il une valeur?ils sont au format "Général".
@FaneDuru Mon mauvais! J'ai édité le code.
Stil
Replace (DataString, .... Après correction, je vais essayersString = CStr (c.Value)au lieu desString = c. ..Vous n'avez pas répondu à @ScottCraner. Lorsque vous modifiez le format de la cellule en
Général, voyez-vous toujours le texte ou voyez-vous un nombre?Ils sont comme du texte, quand c'est dans "Général".
Notez que
dim i, LastCell as Longne déclare queLastCell As Longmaisi As Variant. Dans VBA, vous devez spécifier un type pour chaque variable:Dim i As Long, LastCell as Longsinon c'estVariantpar défaut. Idem pour vos variables de chaîne.Avez-vous essayé quelque chose comme le format
(cdate (c.value), "aaaa-mm-jj hh: nn AM / PM")? Notez que cela ne fonctionnera que si votre Excel est anglais (janvier). Si vous avez une autre langue, vous devez traduirejanvier@FoxfireAndBurnsAndBurns Je suis dans un Excel allemand et je le traduis même en allemand, je ne pouvais pas faire fonctionner ce type de chaîne de date avec
cdate. Il y a donc une forte probabilité que cela dépende du format de date et de la localisation définis dans le système d'exploitation également. En fait, vous pouvez obtenir une chance, mais rien de fiable.