Problème lié aux formats de cellule (date non reconnue)

Le 27/11/2015 à 16:13

Bonjour à tous,

Je rencontre un soucis lié aux dates.

J'ai un fichier de données, comportant une colonne contenant des dates au format JJ/MM/AAAA.

Je fais quelques formules afin d'obtenir un résultat, mais ce dernier est incohérent.

Il semblerait qu'Excel ne s'aperçoit pas qu'il s'agit de dates (sur le fichier de données, on s'aperçoit d'ailleurs que les dates sont collées à gauche des cellules).

Ces données sont extraites depuis un logiciel métier.

J'ai tenté de recopier la colonne en question, de placer le curseur sur chaque cellule... Rien n'y fait !

Lorsque je rentre manuellement dans chaque cellule et que j’appuie sur la touche ENTREE, la date se remet bien à droite de la cellule, mais les calculs semble tout de même erroné.

J'ai donc changé mon type de formule afin de traiter cette date comme un String.

Malheureusement, je n'ai pas plus de succès avec mes formules...

Au cas où, je vous présente mon projet en quelques lignes :

* A l'ouverture du fichier Excel, un bouton nous permet de copier le contenu d'une feuille excel appartenant à un autre classeur dans le présent classeur.

* En fonction du contenu des données, je génère des onglets et des tableaux contenant les formules qui m'interressent afin d'obtenir une statistique des résultats de l'année en cours.

* (Pour plus tard Une fois les tableaux remplis, je dois générer les graphiques correspondant.

Je vais vous mettre le morceau du code qui a mon avis pose soucis (je suis novice ET autodidacte, désolé si mon code pique les yeux des pros présents sur ce site lol) :

       Range("B" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***01***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***01***" & Chr(34) & "))"

Et dans son contexte :

Sub CreerTab(Titre As String)
    Dim Debut As Integer
    Dim DerniereLigne As Integer
    Dim NbExperts As Integer
    Dim Formule As String

    Debut = Range("A1:A" & Range("A65536").End(xlUp).Row).Count + 3
    NbExperts = Sheets("D7").Range("Y4:Y" & Range("Y65536").End(xlUp).Row).Count
       Range("A" & Debut).Value = Titre
    DerniereLigne = Range("A1:A" & Range("A65536").End(xlUp).Row).Count + 1

       Range("A" & DerniereLigne).Value = "Expert"
       Range("B" & DerniereLigne).Value = "Janvier"
       Range("C" & DerniereLigne).Value = "Février"
       Range("D" & DerniereLigne).Value = "Mars"
       Range("E" & DerniereLigne).Value = "Avril"
       Range("F" & DerniereLigne).Value = "Mai"
       Range("G" & DerniereLigne).Value = "Juin"
       Range("H" & DerniereLigne).Value = "Juillet"
       Range("I" & DerniereLigne).Value = "Août"
       Range("J" & DerniereLigne).Value = "Septembre"
       Range("K" & DerniereLigne).Value = "Octobre"
       Range("L" & DerniereLigne).Value = "Novembre"
       Range("M" & DerniereLigne).Value = "Décembre"
    For i = 0 To NbExperts
       DerniereLigne = DerniereLigne + 1
       IniExpert = Sheets("D7").Range("Y" & i + 4).Value
       Range("A" & DerniereLigne).Value = IniExpert
       Range("B" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***01***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***01***" & Chr(34) & "))"
       Range("C" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***02***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***02***" & Chr(34) & "))"
       Range("D" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***03***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***03***" & Chr(34) & "))"
       Range("E" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***04***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***04***" & Chr(34) & "))"
       Range("F" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***05***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***05***" & Chr(34) & "))"
       Range("G" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***06***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***06***" & Chr(34) & "))"
       Range("H" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***07***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***07***" & Chr(34) & "))"
       Range("I" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***08***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***08***" & Chr(34) & "))"
       Range("J" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***09***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***09***" & Chr(34) & "))"
       Range("K" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***10***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***10***" & Chr(34) & "))"
       Range("L" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***11***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***11***" & Chr(34) & "))"
       Range("M" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***12***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***12***" & Chr(34) & "))"
    Next
       Range("A" & DerniereLigne + 1).Value = "Cabinet"
       Range("B" & DerniereLigne + 1).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***01***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***01***" & Chr(34) & "))"
       Range("C" & DerniereLigne + 1).Value = ""
       Range("D" & DerniereLigne + 1).Value = ""
       Range("E" & DerniereLigne + 1).Value = ""
       Range("F" & DerniereLigne + 1).Value = ""
       Range("G" & DerniereLigne + 1).Value = ""
       Range("H" & DerniereLigne + 1).Value = ""
       Range("I" & DerniereLigne + 1).Value = ""
       Range("J" & DerniereLigne + 1).Value = ""
       Range("K" & DerniereLigne + 1).Value = ""
       Range("L" & DerniereLigne + 1).Value = ""
       Range("M" & DerniereLigne + 1).Value = ""

       Range("A" & DerniereLigne + 2).Value = "Objectif"
       Range("B" & DerniereLigne + 2).Value = ""
       Range("C" & DerniereLigne + 2).Value = ""
       Range("D" & DerniereLigne + 2).Value = ""
       Range("E" & DerniereLigne + 2).Value = ""
       Range("F" & DerniereLigne + 2).Value = ""
       Range("G" & DerniereLigne + 2).Value = ""
       Range("H" & DerniereLigne + 2).Value = ""
       Range("I" & DerniereLigne + 2).Value = ""
       Range("J" & DerniereLigne + 2).Value = ""
       Range("K" & DerniereLigne + 2).Value = ""
       Range("L" & DerniereLigne + 2).Value = ""
       Range("M" & DerniereLigne + 2).Value = ""
End Sub

Je vous met également le fichier de données sources excel (la colonne qui pose problème est K:K).

Si vous avez besoin de tout le code présent :

Sub Bouton1Cliquer()

    Dim NbExperts As Integer

    '**************************************
    'Importation de la feuille de données *
    '**************************************
    'Création d'un nouvel onglet
    NomOnglet = "D" & Sheets.Count

    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Select
    Sheets(Sheets.Count).Name = NomOnglet
    Range("A1").Select

    'Je définis le chemin où se trouve mon fichier de données
    Chemin = Sheets("Résultats").Range("J2").Value
    '"C:\Users\poste2\Desktop\Stats\Données MACIF.xlsx"
    'Je définis que le classeur actuel, celui comportant les calculs, s'appelle Fichier1
    Fichier1 = ActiveWorkbook.Name
    'J'ouvre mon fichier de données
    Workbooks.Open Chemin, 0, ReadOnly:=False
    'Je définis ce nouveau classeur sous le nom Fichier2
    Fichier2 = ActiveWorkbook.Name
    'Je recopie ma feuille de Fichier2 dans le classeur Fichier1
    Workbooks(Fichier2).Worksheets(1).Cells.Copy _
    Workbooks(Fichier1).Worksheets(NomOnglet).Range("A1")
    'Je referme le second classeur Fichier2
    Workbooks(Fichier2).Close

    '*********************************
    'Formatage des données importées *
    '*********************************
    'Création des nouvelles en-tête
    Range("AA1") = "NB H MO"
    Range("Y1") = "TEMP"
    Range("AB1") = "INI EXPERT"

    'Calcul du NB H MO par dossier
    Range("AA4:AA" & Range("X65536").End(xlUp).Row).FormulaR1C1 = "=SUM(R[0]C19:R[0]C24)"
    'Copie sans doublon de la colonne expert
    Range("H4:H" & Range("H65536").End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Y4"), Unique:=True
    Range("Y4").Delete

    '****************************
    'Initialisation des Onglets *
    '****************************
    'Création des onglets en fonction du nombre d'experts du cabinet
    CreerOnglets

    '**********************************
    'Initialisation de l'onglet Recap *
    '**********************************
    'Création des tableaux
    CreerTab "Nombre de dossiers déposés"
    CreerTab "Coût Moyen Réparations"

End Sub

Sub CreerTab(Titre As String)
    Dim Debut As Integer
    Dim DerniereLigne As Integer
    Dim NbExperts As Integer
    Dim Formule As String

    Debut = Range("A1:A" & Range("A65536").End(xlUp).Row).Count + 3
    NbExperts = Sheets("D7").Range("Y4:Y" & Range("Y65536").End(xlUp).Row).Count
       Range("A" & Debut).Value = Titre
    DerniereLigne = Range("A1:A" & Range("A65536").End(xlUp).Row).Count + 1

       Range("A" & DerniereLigne).Value = "Expert"
       Range("B" & DerniereLigne).Value = "Janvier"
       Range("C" & DerniereLigne).Value = "Février"
       Range("D" & DerniereLigne).Value = "Mars"
       Range("E" & DerniereLigne).Value = "Avril"
       Range("F" & DerniereLigne).Value = "Mai"
       Range("G" & DerniereLigne).Value = "Juin"
       Range("H" & DerniereLigne).Value = "Juillet"
       Range("I" & DerniereLigne).Value = "Août"
       Range("J" & DerniereLigne).Value = "Septembre"
       Range("K" & DerniereLigne).Value = "Octobre"
       Range("L" & DerniereLigne).Value = "Novembre"
       Range("M" & DerniereLigne).Value = "Décembre"
    For i = 0 To NbExperts
       DerniereLigne = DerniereLigne + 1
       IniExpert = Sheets("D7").Range("Y" & i + 4).Value
       Range("A" & DerniereLigne).Value = IniExpert
       Range("B" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***01***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***01***" & Chr(34) & "))"
       Range("C" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***02***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***02***" & Chr(34) & "))"
       Range("D" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***03***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***03***" & Chr(34) & "))"
       Range("E" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***04***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***04***" & Chr(34) & "))"
       Range("F" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***05***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***05***" & Chr(34) & "))"
       Range("G" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***06***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***06***" & Chr(34) & "))"
       Range("H" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***07***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***07***" & Chr(34) & "))"
       Range("I" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***08***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***08***" & Chr(34) & "))"
       Range("J" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***09***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***09***" & Chr(34) & "))"
       Range("K" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***10***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***10***" & Chr(34) & "))"
       Range("L" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***11***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***11***" & Chr(34) & "))"
       Range("M" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***12***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***12***" & Chr(34) & "))"
    Next
       Range("A" & DerniereLigne + 1).Value = "Cabinet"
       Range("B" & DerniereLigne + 1).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***01***" & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & "***01***" & Chr(34) & "))"
       Range("C" & DerniereLigne + 1).Value = ""
       Range("D" & DerniereLigne + 1).Value = ""
       Range("E" & DerniereLigne + 1).Value = ""
       Range("F" & DerniereLigne + 1).Value = ""
       Range("G" & DerniereLigne + 1).Value = ""
       Range("H" & DerniereLigne + 1).Value = ""
       Range("I" & DerniereLigne + 1).Value = ""
       Range("J" & DerniereLigne + 1).Value = ""
       Range("K" & DerniereLigne + 1).Value = ""
       Range("L" & DerniereLigne + 1).Value = ""
       Range("M" & DerniereLigne + 1).Value = ""

       Range("A" & DerniereLigne + 2).Value = "Objectif"
       Range("B" & DerniereLigne + 2).Value = ""
       Range("C" & DerniereLigne + 2).Value = ""
       Range("D" & DerniereLigne + 2).Value = ""
       Range("E" & DerniereLigne + 2).Value = ""
       Range("F" & DerniereLigne + 2).Value = ""
       Range("G" & DerniereLigne + 2).Value = ""
       Range("H" & DerniereLigne + 2).Value = ""
       Range("I" & DerniereLigne + 2).Value = ""
       Range("J" & DerniereLigne + 2).Value = ""
       Range("K" & DerniereLigne + 2).Value = ""
       Range("L" & DerniereLigne + 2).Value = ""
       Range("M" & DerniereLigne + 2).Value = ""

End Sub

Sub CreerOnglets()
    Dim NbExperts As Integer

    NbExperts = Sheets("D7").Range("Y4:Y" & Range("Y65536").End(xlUp).Row).Count

    For i = 0 To NbExperts - 1
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = Sheets("D7").Range("Y" & i + 4).Value
    Next
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Select
    Sheets(Sheets.Count).Name = "Recap"
'    Range("A1:M3").Merge
    Range("A1").Value = "Récaptitulatif année courante - " & Year(Date)
End Sub

En espérant avoir pu être clair et vous en remerciant par avance.

175donnees-cie-copie.xlsx (207.92 Ko)
Le 27/11/2015 à 18:23

Bonjour

Pour avoir des dates

Écris 1 dans cellule libre

Clic droit sur cette cellule --> Copier

Sélectionne K4:K1322

Clic droit sur une de ces cellules

Clique sur Collage spécial --> dans la fenêtre qui apparait coche Multiplication --> Ok

Clic droit sur une cellule --> Format de cellule --> Onglet Nombre --> Date et valide

efface le 1

C'est finit

En colonne K tu auras de vraies dates

Le 27/11/2015 à 19:59

Bonjour,

Tu peux aussi t'inspirer de cette procédure pour convertir ta colonne.

Cdlt.

Sub DEMO()

    Columns(11).TextToColumns _
            Destination:=Range("K1"), _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            Tab:=True, _
            FieldInfo:=Array(1, 4)

End Sub
Le 30/11/2015 à 08:21

Merci beaucoup pour vos réponses.

Le fichier étant destiné à plusieurs entreprises, j'ai privilégié la solution de Jean-Eric (qui fonctionne merveilleusement bien !).

J'ai juste modifié ma formule en

       Range("B" & DerniereLigne).Value = "=IF(COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & ">=01/01/" & Year(Date) & Chr(34) & ",D7!K:K," & Chr(34) & "<01/02/" & Year(Date) & Chr(34) & ")=0,NA(),COUNTIFS(D7!H:H," & Chr(34) & IniExpert & Chr(34) & ",D7!K:K," & Chr(34) & ">=01/01/" & Year(Date) & Chr(34) & ",D7!K:K," & Chr(34) & "<01/02/" & Year(Date) & Chr(34) & "))"

Et du coup les résultats obtenus sont corrects.

Merci encore

Rechercher des sujets similaires à "probleme lie formats date reconnue"