Problème lié aux formats de cellule (date non reconnue)
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
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.
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
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
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