Génération automatique de dates sous Excel Fermé

Question

Bonjour,
Je voudrais créer un fichier excel contenant toutes les dates (par jour) du 1er janvier 1949 jusqu'au 31 décembre 2149, ceci dans le but de l'utiliser dans un cadre décisionnel (création d'une dimension temps).

J'ai plusieurs colonnes (donnant le nom du jour, le numéro de la semaine...), ce que je voudrais c'est créer automatiquement les lignes correspondant aux dates automatiquement car à la main, ça va être coton 0_o

Je voudrais obtenir quelque chose dans le genre :

date                     annee               mois                ....
01/01/1949           1949                 01                   ....
02/01/1949           1949                 01                   ....
03/01/1949           1949                 01                   ....
...
...
30/12/2149           2149                 12                   ....
31/12/2149           2149                 12                   ....

Est-ce possible avec Excel? Merci d'avance de votre aide.

qwerty- · Answer

salut, si tu écris les 2 premières lignes, puis tu tires vers le bas en sélectionnant les deux, ca devrait le faire

tinwul · Answer

Salut, 

merci de t'intéresser à mon problème.
Tirer la ligne vers le bas, c'est ce que j'ai tenté en premier, mais après 5 min de 'tirage', je n'en étais qu'aux années 60 :D . Donc pour arriver à l'année 2149, t'imagines! C'est pourquoi j'aurais voulu le faire automatiquement

michel_m · Answer

Bonjour

le problème est que tu utilises XL2003 et donc Tes 2 siècles (quelques 73000 lignes) ne rentre pas dans une feuille...
que comptes faire: 2 feuilles d'un siècle, 2 groupes de colonnes ?
Combien as tu de colonnes pour chaque journée ?
Il y a t il des formules ? mois, numéro de semaine (ISO ou US), autres...?
Rappel: 1 classeur de plus de 2 Mo est très fragile ( >75% de l'annonce "erreur fatale fichier corrompu")

A toi de voir et d'être précis dans ta demande....

qwerty- · Answer

oui ce serait plus simple dans une db,

connais tu acces ?

chossette9 · Answer

Bon alors déjà je viens de faire un test sous Excel 2003 :
en mettant 01/01/1949 en A1, je me retrouve à la dernière ligne (A65536) avec... 06/06/2128
=> toutes tes dates ne tiendront jamais en une seule colonne sous Excel 2003. Si tu peux avoir le 2007, mets le car tu peux mettre plus d'1 million de lignes.
ça m'a pris quasiment 4 minutes en effectuant un cliquer-glisser, donc c'est pas trop long, mais le VBA est sûrement une meilleure solution.

Je tente de te pondre quelque chose voir si ça peut fonctionner en VBA.

Cordialement.

tinwul · Answer

Merci chossette!



Bon, à coup de 'copier un ensemble de cellules puis le coller en-dessous' (collage spécial avec formule et format de cellule) j'ai fini par y arriver aussi et effectivement ça ne va que jusqu'à 2128. Au pire ce n'est pas grave, je peux revoir mes ambitions à la baisse.
Néanmoins le VBA serait effectivement mieux car on pourrait le réutiliser. Donc merci encore chossette pour ton aide car je serais incapable de pondre quoi que ce soit en vba :)

tontong · Answer

Bonjour,
On peut aussi utiliser Edition -->recopier --> serie de donnée--> etc --> dernière valeur 83435
83435 = 65536 + 17899
17899 correspond au 01/01/1949
La limite des 65536 lignes ne permet pas d'atteindre la fin de 2149.

tinwul · Answer

Je n'ai pas vu les autres réponses désolé!

Michel_m : effectivement il y a beaucoup de fonctions liées aux dates, mais pour l'instant cela ne pose pas de problème, quant à la taille du fichier, ce que tu me dis me fait peur, j'espère ne pas avoir de soucis avec çà, en même temps, j'ai déjà travaillé avec un fichier de 113 Mo, je n'ai jamais eu ce genre d'erreurs. Pour l'instant, mon fichier (jusqu'en 2128) fait 36 Mo.

Qwerty- : j'essaie justement de remplir une db sql server. En fait je travaille avec DATA Integrator de Business Object et je veux remplir une table de dates. Le problème est que ce logiciel (comme Access je suppose) ne me permet pas beaucoup de manoeuvre avec les dates, ce que je peux faire avec Excel : par exemple le nom du jour, est-ce le week end, le semestre, le trimestre, la date sous différent formats.... je compte y ajouter aussi le flag 'jour de vacance', 'jour férié' mais je n'en suis pas encore là. Tout cela pour te dire que pour créer des fonctions sur les dates, Excel me semble le plus approprié.

michel_m · Answer

Ok, mais ca ne résouT pas le problème des 73000 lignes...
et tirer à la main...
Donc il faut utiliser le VBA
Avec VBA, on remplirait le tableau avec tes formules et fonctions d'un coup. avantage: tu peux aller boire un café pendant que VBA bosse
mais pour cela il faudrait que tu nous dises ce que tu veux...
enfin re-appel: si tu es en europe la fonction Excel NO.SEMAINE n'est pas valable. IL faut utiliser la formule de Laurent Longre
=ENT((D-SOMME(MOD(DATE(ANNEE(D-MOD(D-2;7)+3);1;2);{1E+99;7})*{1;-1})+5)/7) 
je te marques tout ça pour te faire comprendre que l'on a besoin de renseignement précis avant de lancer une macro.

Maintenant, je peux te citer quelques chefs d'entreprise qui ont pleuré (pour de vrai, hélas!) avec leur excel de 6 ou 7 Mo corrompus: ils avaient le patrimoine de la boite dessus.... A toi de voir

tinwul · Answer

Bon, je vais expliciter un peu tout ça alors :

le choix des années 1949-2149 est personnel, je peux bien n'aller que jusqu'à 2128, ça me convient aussi.

Ensuite, pour ce qui est du fichier corrompu, une fois le fichier excel prêt, je ne l'utilise qu'une seule fois pour remplir ma table sous sql server. En espérant que cette fois là sera la bonne. Et même si cela ne fonctionne pas, j'utiliserai d'autres solutions plus compliquées pour moi, comme écrire un script par exemple.

Bref, voici les données que doivent contenir ma table (nom des champs avec un exemple et la formule) :

Id_date : 19490101 = concaténation annee mois jour
date jour : 01/01/1949 = date au format jj/mm/aaaa
date complète : 1 janvier 1949 = date au format jj mmmm aaaa
annee : 1949 = annee(date)
annee/mois : 1949/01 = annee & "/" & mois
annee/trimestre : 1949/T1 = annee & "/" & trimestre
no semestre : S1 = "S" & ENT((MOIS(date)+5)/12+1)
trimestre : T1 = "T" & ENT((MOIS(date)+2)/3)
nom mois : janvier = mois au format mmmm
no mois : 01 = mois(date)
no semaine : 53 = ENT(MOD(ENT((date-2)/7)+0,6;52+5/28))+1
no jour dans annee : 1 = date-DATE(ANNEE(date);1;0)
no jour dans mois : 1 = jour(date)
nom jour : samedi = date au format jjjj
no jour semaine : 6 = joursem(date;2)
est dernier jour mois: 0 = SI(date=DATE(ANNEE(date);MOIS(date)+1;0);1;0)
est week end : 0 =SI(OU(jour_semaine=6;jour_semaine=7);1;0)

Pour ces champs, c'est bon j'ai vérifié, ça fonctionne mais je voudrais aussi ces autres champs :

- No mois dans trimestre : 1 (cad le mois de janvier est le premier mois du trimestre comme mars serait le troisième mois du trimestre)

- est travaillé? : le jour est-il un jour travaillé? (ni en vacance, ni férié)
- jour de vacance : est-ce un jour de vacances scolaires ?
- vacances zone A : est-ce un jour de vacances scolaires en zone A ?
- vacances zone B : est-ce un jour de vacances scolaires en zone B ?
- vacances zone C : est-ce un jour de vacances scolaires en zone C ?
- saison : été, automne, hiver, printemps?
- évènement : y a-t-il un évènement particulier ce jour là (finale de coupe du monde?...) évidemment ce champs sera à remplir manuellement (je le suppose pour l'instant)


Si vous avez d'autres questions, n'hésitez pas!

chossette9 · Answer

Si tu veux générer des données jusqu'en 2149, tu vas avoir du mal à deviner si c'est un jour de vacances scolaire, et encore pire si tu veux spécifier une zone, car les calendriers des vacances ne sont définis que 3 à 4 ans à l'avance (je ne sais plus trop, pour plus d'information aller sur le site du ministère de l'Education Nationale). Pareil si c'est pour mettre des événements sportifs, culturels ou autres.

Pour avoir le numéro du mois dans le trimestre tu peux faire :
=SI(MOD(mois(date);3)=0;3;MOD(mois(date);3))

La fonction MOD permet de retourner le reste d'une division. Ici si tu divises par 3, te restes seront 0 ou 1 ou 2. Donc ensuite avec le SI, tu remplaces le 0 par 3.


Je cherches toujours un code à te faire pour que lorsque tu arrives à la ligne 65536, tu puisses continuer mais en repartant d'une autre colonne (c'est-à-dire que tu reprennes par exemple avec 06/07/2128 en cellule BA1 et qu'à la suite tu mettes toutes tes colonnes).

Cordialement.

tinwul · Answer

Merci beaucoup Chossette, ton dévouement me va droit au coeur!

Dommage que je n'ai pas lu ton post plus tôt, je viens de finir de copier une valeur par défaut sur toutes la colonne no_mois_dans_trimestre !

Merci aussi pour les renseignement sur les vacances, je vais revoir la pertinence de ces champs.

Bon courage, je continue de chercher de mon côté!