XL 2019 Format Date Anglais / Francais

[EDI9366]

Bonjour à tous.

Débutant et autodidacte en général je m'inspire des réponses sur ce forum mais là j'ai besoin d'aide.

Dans le fichier attaché j'ai deux colonnes avec des dates et je dois connaitre l'écart en nombre de jour et d'heure de ces 2 dates. (C'est pour par la suite calculer des frais d'annulation). Mais mon problème c'est que une fois le script utilisé ma colonne "CancelledDate" devient en format Anglais.

L'extraction de mon logiciel m'ajoute un premier caractère ' en début de ligne et su certaine ligne des . au lieu des /.
J'ai contré ce problème avec ceci :

'Mise en forme colonne DateFrom
Cells.Find(What:="DateFrom").Activate
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("Tableau1").Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

'Mise en forme colonne CancelledDate
Cells.Find(What:="CancelledDate").Activate
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("Tableau1").Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

J'ai essayé avec ce script mais le résultat est le même, (je m'étais dis en passant par "Convertir" en largeur fixe et choisissant le format de colonne JMA ca pourrait marcher, mais non) :
' Mise en forme de la colonne CancelledDate
Cells.Find(What:="CancelledDate").Activate
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:=".", Replacement:="/"
Selection.TextToColumns , DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(1, 1)), TrailingMinusNumbers:=True

Problème :

Pour la colonne "CancelledDate" une fois ce script utilisé j'ai les dates au format Anglais et je n'arrive pas a trouver la solution pour mettre au format Français et sans perdre l'info des heures et minutes.
(Vous avez l'exemple sur la ligne 40 où la date d'origine est le 05/01/2022 et une fois le script exécuté j'ai comme date 01/05/2022)

J'espère que ma demande est explicite

Merci de votre aide.

Eddie

 

[pierrejean]

Bonjour
A tester

Sub test()
On Error Resume Next
 For n = 2 To Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & n) = CDate(Replace(Range("A" & n), "'", ""))
 Next
 On Error GoTo 0
End Sub

 

[Hasco]

Bonjour,

Avec une requête power query (qui pourrait extraire vos données depuis leur source), vous pouvez le faire facilement.

Voir dans dans le fichier joint. J'ai volontairement supprimé la colonne écart pour la calculée dans PowerQuery
Si vous voulez des 0 là où il n'y a pas de valeur 'CancelledDate' c'est facile de le faire.
La différence est calculée sur [DateFrom]- [CancelledDate], mais on peut faire [CancelledDate]-[DateFrom]
Rien n'indique dans votre fichier, dans quel sens vous voulez la différence.

Power query travaille avec un 'objet duration' qui s'exprime en Jours.h.m.s par exemple 13.03.07.00 (13 jours 3 heures 7 minutes et 0 secondes. qu' excel exprime en 13/01/1900 03:07:00 formatée en j.hh:mm:ss
Vous verrez donc

que vous (ou la requête) pourrez manipuler comme bon vous semble.

J'ai supprimer 30782 CancelledDate nulles et supprimer les macros pour que le fichier une fois traité puisse être joint et même là c'est limite. Ce qui est étonnant d'ailleurs.
M'est avis que vous devriez recommencer sur un fichier tout propre.

Lorsque vous nous communiquez un fichier exemple, on a pas besoin de tant de données. Juste le nombre de lignes nécessaires pour comprendre les données du problème.


Cordialement
[Edit] Hello @pierrejean

 

[Bernard_XLD]

Bonjour Edi9366, pierrejean, Hasco, le forum

Voici une proposition de code fonctionnel, avec un tableau VB, cela reste rapide sur une grande série de données.

Bien cordialement, @+

Sub test02()
Dim Cel_Ref As Range, Tab_Ref As Range, Tab_Hor, Compteur&
'Mise en forme colonne DateFrom
    Set Cel_Ref = Cells.Find(What:="DateFrom").Offset(1, 0)
    Set Tab_Ref = Range(Cel_Ref.Address & ":" & Cells(Rows.Count, Cel_Ref.Column).End(xlUp).Address)
    Tab_Hor = Tab_Ref.Value2
    For Compteur = LBound(Tab_Hor, 1) To UBound(Tab_Hor, 1)
        Tab_Hor(Compteur, 1) = CDate(Replace(Right(Tab_Hor(Compteur, 1), Len(Tab_Hor(Compteur, 1)) - 1), ".", "/"))
    Next Compteur
    Tab_Ref = Tab_Hor
'Mise en forme colonne CancelledDate
    Set Cel_Ref = Cells.Find(What:="CancelledDate").Offset(1, 0)
    Set Tab_Ref = Range(Cel_Ref.Address & ":" & Cells(Rows.Count, Cel_Ref.Column).End(xlUp).Address)
    Tab_Hor = Tab_Ref.Value2
    For Compteur = LBound(Tab_Hor, 1) To UBound(Tab_Hor, 1)
        If Tab_Hor(Compteur, 1) = "'" Then
            Tab_Hor(Compteur, 1) = ""
        Else
            Tab_Hor(Compteur, 1) = CDate(Replace(Right(Tab_Hor(Compteur, 1), Len(Tab_Hor(Compteur, 1)) - 1), ".", "/"))
        End If
    Next Compteur
    Tab_Ref = Tab_Hor
End Sub

 

[Bernard_XLD]

re,

on peut le faire aussi en 1 passe si les colonnes à traiter sont toujours positionnées de la même façon entre elles.

Bien cordialement, @+

Sub test03()
Dim Cel_Ref As Range, Tab_Ref As Range, Tab_Hor, Compteur&
    Set Cel_Ref = Cells.Find(What:="DateFrom").Offset(1, 0)
    Set Tab_Ref = Range(Cel_Ref.Address & ":" & Cells(Rows.Count, Cel_Ref.Column).End(xlUp).Offset(0, 1).Address)
    Tab_Hor = Tab_Ref.Value2
    For Compteur = LBound(Tab_Hor, 1) To UBound(Tab_Hor, 1)
        Tab_Hor(Compteur, 1) = CDate(Replace(Right(Tab_Hor(Compteur, 1), Len(Tab_Hor(Compteur, 1)) - 1), ".", "/"))
        If Tab_Hor(Compteur, 2) = "'" Then
            Tab_Hor(Compteur, 2) = ""
        Else
            Tab_Hor(Compteur, 2) = CDate(Replace(Right(Tab_Hor(Compteur, 2), Len(Tab_Hor(Compteur, 2)) - 1), ".", "/"))
        End If
    Next Compteur
    Tab_Ref = Tab_Hor
End Sub

 

[EDI9366]

Bonjour Yeahou, Pierrejean, Hasco et tous ce du forum.

Un grand merci à vous pour votre aide, j'ai ma solution

Eddie