Discussion :

[JEJ-675]

Bonjour,

Je me permets de vous contacter car, suite à mes différentes recherches et lectures (j'ai bien lu le tuto gérer et comprendre les dates sous Excel du site et différents Topics sur le forum), je ne parviens pas à trouver une solution "simple" à mon problème.

J'ai une extraction TXT de données que je cherche à exploiter sous Excel.
J'ai déjà codé pas mal d'éléments qui me permettent d'exploiter mes fichiers de mieux en mieux, grâce notamment aux très bons conseils lus sur ce forum, mais sur ce point je bloque.

Dans la colonne (C) créée après un TextToColumns, la plage de dates apparaît au format DD/MM-DD/MM, soit par exemple 31/03-07/04 pour la période de 31/03/2018 au 07/04/2018. J'avais dans un premier temps séparé cette colonne en trois colonnes DD/MM (C) | - (D) | DD/MM (E) afin d'exploiter les colonnes C et E et de supprimer la D, soit celle des tirets. Plusieurs problèmes ont été rencontrés :

• Dans certains cas, le format date de la liste est D/MM (exemple 8/03 au lieu de 08/03) ce qui pose problème pour le TextToColumns
• Dans d'autres, la date affichée après le TextToColumns inverse le mois et le jour (exemple 12/01 qui devient 01/12)

Enfin, autre problème rencontré, la date générée met automatiquement l'année en cours mais je souhaiterais que dans le cas où ma période est 08/12-15/01, je puisse appliquer la condition suivante "si MM est compris entre 10 et 12 alors YY = N-1 sinon N".

Mon objectif est donc de passer d'une colonne C contenant une valeur texte type "31/03-07/04" à une colonne C contenant la date 31/03/2018 et une colonne D la date 07/04/2018 et géré les exceptions possibles du type "15/12-8/01" pour qu'elle devienne des valeurs dates 15/12/2017 en colonne C et 08/01/2018 en colonne D.

Je ne vous propose pas de début de code parce que je coince réellement sur l'angle d'attaque à aborder. Dans pas mal d'autres situations que j'ai eu avec mon extraction, j'ai pu trouver une réponse en cherchant et en essayant mais là je coince

Je vous remercie par avance pour votre aide !!

JEJ

[Jean-Philippe André]

Salut,

j'ai un doute sur ce que tu tentes de nous dire :

la plage de dates apparaît au format MM/YY-MM/YY, soit par exemple 31/03-07/04

Pour moi ce format déjà c'est du DD/MM-DD/MM avec implicitement l'année en cours...
C'est bien cela ?

Ensuite, tu nous donnes un bout de ton point de départ, mais je n'ai pas pu comprendre ton point d'arrivée, pourrais-tu nous dire par exemple :
- je pars de 31/03-07/04
- je veux arriver .....

Parce qu'au delà des problèmes que tu rencontres sur le texttocolumn, il nous faut savoir ce que tu veux avoir au bout

Merci pour nous.

[JEJ-675]

Bonjour Jean-Philippe,

Merci pour ton retour et désolé pour le manque de clarté !
J'ai modifié mon post initiale pour essayer d'être plus précis dans ma requête...
Dis moi si ce n'est toujours pas clair !

[Jean-Philippe André]

D'accord, oui c'est déjà beaucoup mieux

On peut arriver facilement à ce que tu souhaites.

On va fonctionner en trois temps
- manipuler la chaine JJ1/MM1-JJ2/MM2 pour en extraire les Jours Mois 1 et 2
- déterminer l'année qui s'y rattache (je crois comprendre que si la date de début est nécessaire dans le passé, et que la date de fin est celle qui suit immédiatement la date de début)
- faire une fonction qui retourne les 2 dates (une par retour de fonction + 1 par ByRef, ou sinon une procédure avec 2 ByRef)

Une fois le résultat obtenu, il suffira de faire la mise à jour des cellules.

[patricktoulon]

bonsoir
a tester

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
Option Explicit
Private Type dat: debut As String: fin As String: End Type
Sub test()
    Dim x As dat
    x = date_debut_fin("31/03-07/04")
    MsgBox "test1" & vbCrLf & "debut " & x.debut & " fin :" & x.fin
 
 x= date_debut_fin("31/03-01/03")
    MsgBox "test2" & vbCrLf & "debut " & x.debut & " fin :" & x.fin
 
    'test format mono chiffre
    x = date_debut_fin("8/3-9/3")
    MsgBox "test3 avec format mono chiffre" & vbCrLf & "debut " & x.debut & " fin :" & x.fin
End Sub
'
'
'
Function date_debut_fin(ByVal chaine$) As dat
    Dim d As dat
    d.debut = CDate(Split(chaine, "-")(0) & "/" & Year(Date))
    d.fin = CDate(Split(chaine, "-")(1) & "/" & Year(Date))
    If CDate(d.fin) < CDate(d.debut) Then d.fin = CDate(Split(chaine, "-")(1) & "/" & Year(Date) + 1)
    date_debut_fin = d
End Function

[JEJ-675]

Bonjour à tous les deux,

Désolé pour le délai de réponse, je m’attèle à ce problème entre d'autres sujets...
N'étant pas du tout expert en codage (je m'y suis mis cet été et découvre petit à petit ses joies et difficultés...), vos retours me semblent intéressant mais j'ai des difficultés à voir par où commencer...
Alors pour reprendre dans l'ordre :

Jean-Philippe :

• Je ne suis pas bien sûr de comprendre ce que tu veux dire là :
  

  (je crois comprendre que si la date de début est nécessaire dans le passé, et que la date de fin est celle qui suit immédiatement la date de début)

• et je n'ai jamais fait de fonction sur VBA, d'où mon sentiment quand je lis ça :
  

  faire une fonction qui retourne les 2 dates (une par retour de fonction + 1 par ByRef, ou sinon une procédure avec 2 ByRef)

Patrick :

• la première partie "31/03-07/04" renvoie bien date début "31/03/2018" et date fin ":07/04/2018".
• la seconde partie ne correspond pas aux cas que je rencontre : quand tu fais le test sur "31/03-01/03", il me renvoie bien date début "31/03/2018" et date fin ":01/03/2019" mais ce n'est pas un cas que je rencontre dans mes données. La plage sera toujours du 01/10/201X au 30/09/201X+1 (nous sommes donc en ce moment sur l'année X+1). Il faudrait donc que pour toutes périodes (date début ou date début et date fin) se trouvant du 01/10 au 31/12 cela affiche en ce moment 2017 et pour tout le reste depuis janvier 2018. A partir du 1er octobre de cette année, j'aimerais que 2017 devienne 2018 et 2018 devienne 2019.
• Enfin le test format mono chiffre fonctionne bien.

J'ai testé de mon côté un code beaucoup moins sympa et qui ne fonctionne pas complètement :

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
28
29
30
31
32
33
34
35
Sub TEST2()
'
' TEST2 Macro
'
'
 Dim PremLigne As Long, DernLigne As Long
    PremLigne = 6
    DernLigne = Range("A" & Rows.Count).End(xlUp).Row
 
    Columns("D:E").Insert Shift:=xlToRight
    Range("D" & PremLigne & ":D" & DernLigne).FormulaR1C1 = _
        "=IFERROR(DATEVALUE(LEFT(RC[-1],5)),DATEVALUE(LEFT(RC[-1],4)))"
    Range("E" & PremLigne & ":E" & DernLigne).FormulaR1C1 = _
        "=IFERROR(DATEVALUE(RIGHT(RC[-2],5)),DATEVALUE(RIGHT(RC[-2],4)))"
 
    Range("D" & PremLigne & ":E" & DernLigne).Formula = Range("D" & PremLigne & ":E" & DernLigne).Value
 
    Range("D" & PremLigne & ":E" & DernLigne).NumberFormat = "m/d/yyyy"
    Range("A5:J5").AutoFilter
 
    ActiveSheet.Range("A" & PremLigne - 1 & ":J" & DernLigne).AutoFilter Field:=4, Operator:= _
        xlFilterValues, Criteria2:=Array(1, "10/1/2018", 1, "12/31/2018")
    Columns("D:D").Replace What:="2018", Replacement:="2017", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
    ActiveSheet.Range("A" & PremLigne - 1 & ":J" & DernLigne).AutoFilter Field:=5, Operator:= _
        xlFilterValues, Criteria2:=Array(1, "10/1/2018", 1, "12/31/2018")
    Columns("E:E").Replace What:="2018", Replacement:="2017", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
    ActiveSheet.ShowAllData
 
End Sub

Mon filtre n'a remplacé les années que pour le mois de décembre. Dans mon échantillon test, la première date "de 2017" rencontrée est le 7 novembre, je pense donc que c'est peut être pour ça que mon filtre ne prend pas tous les mois souhaités. Sinon, j'avoue que ça a moins de "gueule" que vos fonctions et que je suis assez intéressé de comprendre vos méthodes pour progresser !

Merci encore pour vos réponses

[patricktoulon]

re

ma fonction ajoute 1 année si la date2 est plus petite que la date 1 c'est ce que tu a demandé au depart
exemple
28/04//2018 au 15/01/2018 n'est pas possible a moins d'avoir une doloreane et de s'appeler Marty ou Mcfly
si ca n'est pas ca que tu veux il faut etre plus clair

[JEJ-675]

Merci Patrick,
Oui je me suis rendu compte qu'elle ajoute +1 dans ce cas.
Dans mes données, il faudra plutôt du "-1" comme je l'ai expliqué dans mon précédent message :

La plage sera toujours du 01/10/201X au 30/09/201X+1 (nous sommes donc en ce moment sur l'année X+1). Il faudrait donc que pour toutes périodes (date début ou date début et date fin) se trouvant du 01/10 au 31/12 cela affiche en ce moment 2017 et pour tout le reste depuis janvier 2018. A partir du 1er octobre de cette année, j'aimerais que 2017 devienne 2018 et 2018 devienne 2019.

Dans mon message initial, j'avais mis :

Enfin, autre problème rencontré, la date générée met automatiquement l'année en cours mais je souhaiterais que dans le cas où ma période est 08/12-15/01, je puisse appliquer la condition suivante "si MM est compris entre 10 et 12 alors YY = N-1 sinon N".

Je ne parlais donc pas de première date supérieur à la deuxième mais bien si MM est compris entre 10 et 12 alors YY = N-1...

[patricktoulon]

tu semble penser que l'on devine tes intentions c'est pas le cas

N-1 ok mais pour la date(1) ou la date(2)
parce que quand je te lis tu semble vouloir modifier la date2 sauf que si la date(2) est superieur avec ton N-1 on va jouer retour vers le futur 10

exemple
22/03-10/11 va devenir 22/03/2018-10/11/2017 tu vois le shmilblik

maintenant si tu me dis clairement que 22/03-10/11 doit devenir 22/03/2017-10/11/2018 uniquement si date 2> octobre la on se comprendrait mieux

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
28
Option Explicit
Private Type dat: debut As String: fin As String: End Type
Sub test()
    Dim x As dat
    x = date_debut_fin("31/03-07/04")
    MsgBox "test1" & vbCrLf & "debut " & x.debut & " fin :" & x.fin
 
 x = date_debut_fin("31/03-01/03")
    MsgBox "test2" & vbCrLf & "debut " & x.debut & " fin :" & x.fin
 
    'test format mono chiffre
    x = date_debut_fin("8/3-9/3")
    MsgBox "test3 avec format mono chiffre" & vbCrLf & "debut " & x.debut & " fin :" & x.fin
 
 'test decal année
    x = date_debut_fin("8/3-20/10")
    MsgBox "test3 avec decalage de l'année " & vbCrLf & "debut " & x.debut & " fin :" & x.fin
End Sub
'
'
'
Function date_debut_fin(ByVal chaine$) As dat
    Dim d As dat
    d.debut = (Split(chaine, "-")(0) & "/" & Year(Date))
    d.fin = CDate(Split(chaine, "-")(1) & "/" & Year(Date))
    If Month(CDate(d.fin)) > 9 Then d.debut = CDate(Split(chaine, "-")(0) & "/" & Year(Date) - 1)
    date_debut_fin = d
End Function

[JEJ-675]

Ok excuse moi, je vais essayer d'être plus précis.
La situation :

maintenant si tu me dis clairement que 22/03-10/11 doit devenir 22/03/2017-10/11/2018 uniquement si date 2> octobre la on se comprendrait mieux

n'est pas possible dans mon échantillon. La plage des dates va du 01/10/2017 au 30/09/2018.
La première date ne peut être comprise qu'entre le 01/10/2017 et le 30/09/2018 tout comme la seconde date mais date1 sera toujours inférieur à date2.
Je peux donc avoir un cas où la date1 et la date2 seront en N-1 (Exemple : 25/10-15/12), un cas où la date1 uniquement sera en N-1 (exemple : 20/11-13/01) et un dernier cas où les deux dates seront en N (exemple : 22/03-07/04).

[JEJ-675]

J'ai continué sur le code que j'ai proposé un peu plus haut et j'ai l'impression d'avoir une potentielle solution mais j'ai un élément qui coince à la fin du code. La macro tourne jusqu'au bout sans erreur mais ne me corrige pas l'année.

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
28
Sub TEST2()
'
' TEST2 Macro
'
'
 Dim PremLigne As Long, DernLigne As Long
    PremLigne = 6
    DernLigne = Range("A" & Rows.Count).End(xlUp).Row
 
    Columns("D:E").Insert Shift:=xlToRight
    Range("D" & PremLigne & ":D" & DernLigne).FormulaR1C1 = _
        "=IFERROR(DATEVALUE(LEFT(RC[-1],5)),DATEVALUE(LEFT(RC[-1],4)))"
    Range("E" & PremLigne & ":E" & DernLigne).FormulaR1C1 = _
        "=IFERROR(DATEVALUE(RIGHT(RC[-2],5)),DATEVALUE(RIGHT(RC[-2],4)))"
 
    Range("D" & PremLigne & ":E" & DernLigne).Formula = Range("D" & PremLigne & ":E" & DernLigne).Value
    Range("D" & PremLigne & ":E" & DernLigne).NumberFormat = "m/d/yyyy"
 
Dim MaPlage As Range
Set MaPlage = Range("D" & PremLigne & ":E" & DernLigne)
 
    For Each cell In MaPlage
        Select Case Month(cell)
            Case 10 To 12: cell = DateAdd("yyyy", -1, cell)
        End Select
    Next cell
 
End Sub

C'est cette partie du code qui ne semble pas fonctionner :

Code :

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

1
2
3
4
5
For Each cell In MaPlage
        Select Case Month(cell)
            Case 10 To 12: cell = DateAdd("yyyy", -1, cell)
        End Select
    Next cell

J'ai vérifié avec MsgBox que mon cas 10 à 12 renvoie bien toutes les cellules qui ont un mois compris entre 10 et 12 mais par contre il ne semble pas faire le DateAdd.

Auriez-vous une idée ? Merci beaucoup !

[JEJ-675]

Bon ben j'ai fini par trouver une solution pour ceux que ça pourrait intéresser.
La dernière partie du code pour l'histoire de mois sera :

Code :

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

1
2
3
4
5
6
7
8
9
Dim i As Long, j As Integer
 
    For i = PremLigne To DernLigne
        For j = 4 To 5
            Select Case Month(Cells(i, j))
                Case 10 To 12: Cells(i, j) = DateAdd("yyyy", -1, Cells(i, j))
            End Select
        Next j
    Next i

Et du coup, l'intégralité du code ressemble à ça :

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
28
29
30
31
32
33
34
35
36
' TEST2 Macro
'
'
Columns("D:E").Insert Shift:=xlToRight
 
Dim PremLigne As Long, DernLigne As Long, Date1 As Range, Date2 As Range
    PremLigne = 6
    DernLigne = Range("A" & Rows.Count).End(xlUp).Row
Set Dates1 = Range("D" & PremLigne & ":D" & DernLigne)
Set Dates2 = Range("E" & PremLigne & ":E" & DernLigne)
 
    Dates1.FormulaR1C1 = _
        "=IFERROR(DATEVALUE(LEFT(RC[-1],5)),DATEVALUE(LEFT(RC[-1],4)))"
    Dates2.FormulaR1C1 = _
        "=IFERROR(DATEVALUE(RIGHT(RC[-2],5)),DATEVALUE(RIGHT(RC[-2],4)))"
 
Set Dates1 = Nothing
Set Dates2 = Nothing
Set MesDates = Range("D" & PremLigne & ":E" & DernLigne)
 
    MesDates.Formula = MesDates.Value
    MesDates.NumberFormat = "m/d/yyyy"
 
Set MesDates = Nothing
 
Dim i As Long, j As Integer
 
    For i = PremLigne To DernLigne
        For j = 4 To 5
            Select Case Month(Cells(i, j))
                Case 10 To 12: Cells(i, j) = DateAdd("yyyy", -1, Cells(i, j))
            End Select
        Next j
    Next i
 
End Sub

Ce n'est peut-être pas le plus beau des codes mais il fonctionne.
Merci beaucoup à Patrick et Jean-Philippe pour leur aide et propositions !