Comprendre et gérer les dates sous Excel et en VBA

I. Introduction

Le but de ce tutoriel est d'expliciter la gestion et l'interprétation des dates (et heures) par Excel : nous n'aborderons donc ici ni les fonctions, ni les formats en dehors du minimum nécessaire au sujet. Nous nous efforcerons aussi de lever toutes les ambiguïtés possibles, ce qui devrait éclairer les comportements inattendus (mais pas illogiques) à travers les différents emplois de date.

Pour aborder ce type de sujet, on est obligé de faire mention à certains moments de noms ou de notions qui ne seront explicités que plus tard : il est donc important dans ce type de documents, de faire une première lecture globale, sans s'attarder sur des points inconnus ou précis, ceci afin de se familiariser avec l'ensemble, puis d'attaquer l'étude attentive proprement dite (exemple : Numéro de série dans le contexte de ce document).

Il peut sembler paradoxal par moment d'illustrer des approches déconseillées, mais le but ici est de décrire et ainsi de faire comprendre un phénomène qu'on aurait vite fait de qualifier de bogue quand il surgit.

La complexité d'un tutoriel n'est en rien liée à son nombre de pages, surtout s'il contient de nombreux graphismes.

II. Les dates sous Excel (feuilles) : généralités

Dans Microsoft Windows, la plage des dates reconnues valides s'étend du 1er janvier 100 au 31 décembre 9999 du calendrier grégorien. Ces plages varient en fonction des systèmes d'exploitation.

Sous Excel (depuis Excel 2000), une feuille de calcul peut afficher des dates du 1 janvier 1900 au 31 décembre 9999.

Quand vous entrez une date dans une cellule, Excel, s'il reconnaît la saisie comme un format valide, mémorise le Numéro de série correspondant et affiche la date correspondante selon le format par défaut. Tous les calculs seront effectués sur la base du Numéro de série et non du format.

Une date-heure sous Excel est stockée sous forme de nombre appelé Numéro de série (explicité plus loin). L'affichage dans une cellule est la date correspondante au Numéro de série sous-jacent appliqué au calendrier de l'environnement contextuel.

Il est important de bien comprendre que ce qui est affiché dans une cellule n'est qu'une représentation de la donnée mémorisée par Excel selon un format donné. Ce qui est présent dans la barre de formule en est une autre interprétation (correspond à l'option système date courte du poste de travail) qui peut ne pas être non plus ce qui est mémorisé (on le verra plus loin avec les numéros de série négatifs).

Vous avez l'habitude de ce phénomène avec les nombres classiques : si votre format de cellule est " Nombre à 2 décimales " et que vous tapez 12,1285632, vous aurez en affichage 12,13 mais vous aurez, dans la barre de formule, le nombre non arrondi et c'est sur cette valeur que les calculs concernant la cellule seront effectués.

Un format valide est une saisie qu'Excel peut interpréter comme telle, avec l'utilisation de séparateurs adéquats " / ", " - ", voire un espace pour les noms de mois et " : " pour les heures. Pour les deux réunis, se servir d'un espace entre la date et l'heure.

Prenez l'habitude de saisir les années sur 4 chiffres : en effet Windows (donc Excel sous Windows) interprète, par défaut, toutes les années à 2 chiffres de la façon suivante :

• De 00 à 29 interprétées en 2000 - 2029.
• De 30 à 99 interprétées en 1930 - 1999.

Ceci étant, de plus, paramétrable au niveau système (…,XP, W7), les risques d'ambiguïtés deviennent importants… Si en revanche, vous saisissez une année de date à 4 chiffres dans une cellule formatée avec les années à 2, le Numéro de série mémorisé sera bien sur la base de l'année à 4 chiffres et les ambiguïtés levées.

Voir tableau ci-dessous avec les réglages de base par défaut (sous les dates, se trouvent les N° de série correspondants)

la construction des N° de série sera explicitée dans ce qui suit. Pour afficher un Numéro de série, appliquez le format Nombre / Standard.

Dans les options d'Excel, on peut paramétrer, dans l'onglet Calcul, la base du calendrier, soit avec une année de base de 1900, soit avec 1904, cette option devant assurer une compatibilité avec les Macs… A n'utiliser qu'en parfaite connaissance de cause car vos dates changeant du coup automatiquement…, les N° de série restent, mais ils n'ont plus la même correspondance ! Cette option ne concerne que le classeur où elle est prise.

Une date contient implicitement une date et une heure. Ci-dessous on a fait un copier-coller de la case du haut deux lignes plus bas et on change le format.

On constate lorsqu'une saisie est " abrégée ", c'est-à-dire qu'elle ne spécifie pas toutes les données, qu'Excel s'il peut l'identifier comme date ou heure, interprète ainsi les paramètres absents : année en cours ou point d'origine du compteur.

• an = année en cours
• jours (1)
• mois (1)
• heures (0)
• minutes (0)
• secondes(0))
• Si une heure est saisie sans date, la date sera interprétée au 01/01/1900.
• Si une date est saisie sans heure, l'heure sera interprétée en 00 :00 :00.

II-A. Interprétation des dates

Les cellules de la colonne " Interprétation Excel " ci-dessous sont pré-formatées en date classique (Date *14/03/2001) pour éviter les passages en format personnalisé.

II-B. Interprétation des heures

Dans le tableau ci-dessous :

• Les cellules bleues sont pré-formatées en : Heure 13 :30 :55.
• La cellule jaune en « Heure 14/3/01 13 :30.
• La cellule orange sans formatage préalable.
• Les cellules mauves sont dans un format personnalisé qui permet de cumuler :
  1. Des heures au-delà de 24 (cumul maxi d'heures saisies dans une cellule : => 10 000 heures, calcul via formule = 71003183 :59 :59).
  2. Des minutes.
  3. Des secondes.

II-C. Interprétations et affichages

Attention aux interprétations : ce ne sont, souvent, que le reflet de ce que vous avez initié, mais pas forcément de ce que vous auriez voulu… Pour un même Numéro de série, donc une date-heure identique, tous les affichages suivants sont corrects :

Ci-dessous, l'affichage est identique alors que le Numéro de série est différent : voyez les valeurs dans la barre de formule pour la case active (colorée).

II-D. Stockage des Dates et Heures sous Excel

Pour comprendre son fonctionnement, il faut savoir qu'Excel stocke :

• Les dates sous forme de nombres de jours entiers (depuis l'" origine" ).
• Les heures sous forme de fractions décimales de jour.

On obtient un nombre à virgule, qu'Excel désigne sous le terme de Numéro de série, dont la partie gauche (entière) concerne la date et la partie droite (décimale) l'heure.

Pour s'en convaincre, il suffit de saisir une date ou une heure dans une cellule, puis de changer le format de celle-ci en Standard => on obtient alors un nombre, " le Numéro de série ". Ce système de numéros de série permet d'effectuer des opérations mathématiques et comparatives sur les dates.

Ci-dessous, pour chacune des 3 dates, on a recopié sa valeur 2 lignes plus bas, en lui appliquant le format Standard.

Ce principe, sur lequel fonctionne Excel Windows, régit toutes les manipulations de dates ou d'heures. Ce sont les catégories de format sous Excel (ou les types pour les variables en VBA) qui déterminent si un nombre est une date : ensuite, les formats permettent de gérer les apparences diverses qui assurent une meilleure lisibilité et une compatibilité d'ordre " linguistique ", à travers la mise en forme du Numéro de série.

Dans Excel, le 1 représente le 1er janvier 1900 à 00 :00 :00 heures (attention : c'est différent sous Mac !).

L'heure est une fraction de jour ; or une journée de 24 heures comprend exactement 86 400 secondes, ce qui explique les transcriptions suivantes :

• 12 :00 => 43200 / 86400 = 0.5.
• 18 :45 => (18x3600)+(45x60) = 67500/ 86400 = 0,78125.

L'expression de fractions en 24^es et en 1440^es ne sont que des simplifications des fractions en 86400^es pour les heures et les minutes, la seconde étant l'unité de base du temps journalier universel (cliquer pour voir article Wikipedia à ce sujet) :
2 jours => 172800/86400^es = 48/24^es (fraction en 24^es après simplification par 3600), pour une représentation du numérateur en heures.

S'il peut être utile, pour bien analyser le comportement des heures et dates, de remonter à la base universelle de la seconde, il serait en revanche malcommode, dans l'utilisation courante, de dire : " j'ai surfé 4200 secondes sur Développez.com, c'était super ! " ou bien : " hier j'ai fait 7200 secondes supplémentaires, ça me fera tant de plus à la fin du mois  ".

Ainsi, pour Excel, la base de temps unitaire retenue est la journée, ce qui fait que, pour une durée donnée, le Numéro de série sera identique en valeur à sa représentation décimale (ceci sera explicité plus loin ; voir aussi le petit moteur d'unité de temps). C'est fondamental aussi pour comprendre l'interprétation des nombres convertis en date-heure et l'ambiguïté des calculs horaires basiques. Le nombre qui intervient est toujours exprimé en jours, comme dans le tableau ci-dessous :

Nous avons adopté en colonne D un format jour + heure afin de mettre en avant les répercutions voulues sur des opérations basiques.

Selon le schéma suivant :

• En ligne 4, nous avons à la base une date : si l'on ajoute un 1 sans plus de précision, Excel va ajouter son unité et on se retrouvera le lendemain.
• En ligne 5 on rajoute en fait 1 unité + ¾, ce qui se traduit bien en D.
• En ligne 6 : même explication que pour la ligne 4.
• En ligne 7, nous avons à la base une heure (06:00:00) à laquelle on veut ajouter 3 heures. Si l'on ajoute un 3 sans plus de précision, Excel va ajouter ses 3 unités, et on se retrouvera avec… 3 jours en plus !

Il faut donc être plus explicite :

• En ligne 8, on rajoute clairement 3 en format horaire : il n'y a plus d'ambiguïté et on a le résultat escompté.
• En ligne 9, on rajoute une fraction d'unité de base, soit 3/24^es, et on obtient bien le résultat attendu.

II-D-1. Variations sur les dates

Dans le tableau suivant, on a entré les mêmes valeurs dans les 2 colonnes, et on leur a appliqué les formats affichés en dessous. " Numéro de série " de la date Excel à gauche et son affichage en tant que format date à droite.

Toutes les 86400 secondes l'heure revient à 0 et le nombre de jours augmente de 1, comme on peut le voir dans le tableau suivant…

La précision maximale gérée par Excel est le millième de seconde :

Si vous saisissiez 0,999999999 (donc avec une décimale excédentaire), Excel, qui ne gère pas au-delà de la " troisième décimale du Type ", arrondit automatiquement au millième de seconde supérieur, ce qui donne exactement 1 jour (ou 24 heures), et on retrouve, dans la cellule, soit 00:00:00 en format horaire, soit 01/01/1900 00:00:00 en format date.

Pour concrétiser tout cela, je vous propose en téléchargement un petit moteur (sans macro) d'unité de temps (voir à la fin du tutoriel) . La feuille est protégée, mais sans mot de passe, par défaut ; on ne peut que choisir la durée unitaire et rentrer le nombre d'unités de temps voulues dans les cases bleues.

voir plus loin " II-E-1 Représentation décimale et calculs sur base de salaire horaire  ".

II-D-2. Particularités sur les dates

II-D-2-a. Années bissextiles - Cas de 1900

Excel (pas le vba) considère que l'année 1900 est bissextile, ce qui est faux, mais non sans conséquences..

On a donc des numéros de série supérieurs à 1 par rapport aux nombres de jours mathématiques. Ce n'est pas à proprement parler un bug d'Excel, car à une certaine époque, le tableur prédominant sur le marché était Lotus 123, et il renfermait ce bug ; du coup Microsoft, Borland et ceux qui voulaient conquérir le marché ont cru bon de se soumettre à ce standard pour que les clients migrateurs ne rencontrent pas de problème de compatibilité.

II-D-2-b. Dates " négatives "

Excel ne sait pas afficher dans ses cellules des dates négatives (antérieures au 01/01/1900 dans son système).

• On ne peut les saisir (sauf via Numéro de série et conversion format date=> affichage ####).
• On ne peut pas non plus les affecter à une cellule, via vba, en format date.
• On peut, par contre, en obtenir par calcul dans une feuille ; Excel affiche alors à la place une série de #, mais conserve cependant la valeur.
• Il peut, de ce fait, opérer des calculs dessus, la limitation concernant l'affichage et non la valeur contenue.

Il en est de même pour les heures négatives.

Pour inclure une date-heure dans une formule, utilisez les " ".

L'utilisation du système de date 1904 (options Excel) permet l'emploi des valeurs négatives, mais avec toutes les réserves vues au chapitre 1 ; le Numéro de série 1 correspond alors au 02/01/1904 sous ce système ! L'exemple des dates ci-dessus devient :

Il est recommandé de se référer à l'aide Excel, chaque fois qu'on fait appel à une fonction ou à une manipulation qu'on ne maîtrise pas, le temps ainsi passé étant toujours largement " récupéré " par la suite.

II-E. Synthèse rapide

Même si, pour une utilisation courante et aisée des heures et dates, il est important d'avoir intégré les notions explicitées dans l'intégralité de ce tutoriel, l'urgence demande parfois quelques raccourcis ; c'est ce que nous allons tenter de faire dans cette synthèse qui n'aborde que l'interface feuille d'Excel Windows (attention il existe des différences importantes avec le VBA).

 Les fondamentaux à retenir sont que : 

• Les dates et heures dans Excel sont représentées par des nombres à virgule appelés numéros de série dont la partie entière représente les jours et la partie décimale les heures.
• Les valeurs formatées qui apparaissent dans les cellules ne sont que la représentation graphique d'une mise en forme appliquée à ce Numéro de série sous-jacent qui reste constant pour une même date-heure.
• Pour visualiser le Numéro de série, il suffit d'appliquer à la cellule date-heure voulue le format Standard. C'est sur lui que seront basés tous les calculs relatifs aux dates-heures.
• Le calendrier Excel couvre toutes les dates du 1er janvier 1900 au 31 décembre 9999.
• Les numéros de série représentent le nombre de jours et la fraction horaire écoulés depuis le 1er janvier 1900.
• Les numéros de série commencent donc avec le 1 qui représente le 1er janvier 1900 à 00 :00 :00 heures. Ensuite un Numéro de série tel que 3,8 représentera le 03/01/1900 à 19:12:00, et 40137 représentera le 20/11/2009 à 00:00:00.
• Excel, qui ne sait pas afficher des dates ou heures négatives dans ses cellules, affiche systématiquement à la place une série de #, mais on peut cependant utiliser ces valeurs négatives dans des calculs, cette limitation concernant seulement l'affichage et non la valeur contenue.

 

II-E-1. Représentation décimale et calculs sur base de salaire horaire

Nous sommes appelés à rencontrer souvent des durées de travail exprimées en journées et fractions de journée, ou en heures et fractions d'heure comme 2 jours ½ ou 3 h ¼.
Pour les jours, pas de problème, la base unitaire des numéros de séries étant le jour ; si on ajoute ; 1.5 à une date, Excel ajoutera 1 jour et 12 h automatiquement (bien formater la cellule).
Pour les heures, le tableau suivant va éclairer la situation...

Il faut bien se rappeler que l'on parle ici de durée et non de date ! Vouloir travailler sur une date seule reviendrait à considérer la durée entre le 01/01/1900 et la date considérée (comme en ligne 11 ci-dessous) !

Le tableau ci-dessus est disponible en téléchargement, (voir à la fin du tutoriel).

Que se passe-t-il ?

L'unité du Numéro de série étant basée sur la journée dans Excel, le Numéro de série et la représentation décimale de la durée qu'il représente sont identiques. Si on multiplie le Numéro de série par 24, on passe alors en unités de base horaire et donc dans un système qui représente des portions horaires de jours. On peut donc multiplier une cellule contenant une donnée date-horaire directement par 24 pour obtenir sa représentation décimale puisqu'on travaille en fait sur le Numéro de série sous-jacent.
Dans la colonne I, on fait l'inverse : on récupère le Numéro de série et par voie de conséquence les dates-heures en divisant la colonne F par 24. Attention d'appliquer le bon format, sinon la cellule I 11 retournerait 12:00:00 en format heure de base....

Une fois compris ce principe, les données temporelles sont bien plus aisées à manier ; ainsi la rémunération de la ligne 8 revient à l'opération suivante : =D8*24*8.86 au lieu du calcul :
= (8,86*8)+((8,86/60)*16)+((8,86/3600)*9) qui conduit bien au même résultat, mais est bien moins commode, surtout si l'on tient compte du fait que la séparation en heures-minutes-secondes nécessite d'appliquer encore d'autres formules…

II-E-2. Isoler la partie horaire d'une date-heure

On peut aussi avoir besoin d'isoler la partie horaire d'une date-heure comme par exemple : le 20/11/2009 08:30:00 situé en D26. Avec la notion de Numéro de série, c'est facile :

Appliquons à la cellule cible la formule suivante : =(D26-ENT(D26))*24

Que se passe-t-il ?

Le Numéro de série sous-jacent de D26 est le : 124,354166666667 ; c'est sur lui que s'effectueront les calculs.
Comme indiqué plus haut, la partie intéressante ici est la partie décimale qui représente la fraction horaire.

ENT étant une fonction Excel qui arrondit un nombre à l'entier immédiatement inférieur, elle va retourner la partie entière : 124. Ensuite, on soustrait du Numéro de série cette valeur entière et il reste la partie " fractionnaire " : 0,354166666667, soit le Numéro de série correspondant à la partie horaire seule qui, multiplié par 24, nous donnera le nombre décimal voulu à savoir : 8,5.

II-E-3. Afficher des données horaires cumulées

II-E-4. Ajouter (ou retrancher) des Jours, des Heures, des Minutes ou des Secondes au moyen de formules

Pour les jours, c'est simple, car on a vu que le jour est l'unité de base de temps d'Excel ; il suffit donc de les ajouter sous forme de nombres comme en lignes 36 et 37.
Pour les heures, minutes ou secondes, il suffit de les ajouter, entre guillemets, dans la formule avec le format voulu.

II-E-5. Mise en application simple sur un format date non homogène après import de données

Mon Ami Benoit Timer a récupéré à son boulot un fichier Excel résultant d'un import de données en csv et il se retrouve avec un colonne de date qui lui pose le problème suivant :
Certaines dates sont en format texte, d'autres en format date, et la colonne comporte des cellules vierges. Ceci l'empêche notamment d'appliquer des filtres automatiques. Il voudrait pouvoir faire un traitement rapide qui mettrait toutes ces dates au bon format, sachant qu'il a 20 000 lignes dans son fichier.
Aussi je lui ai donc proposé de mettre en application le tutoriel que je lui avais soumis…

Sub AppliTuto() With Columns("D:D") .Insert Shift:=xlToRight '.select '' ne pas mettre dans le code surtout, ''à activer pour compréhension de la suite si besoin... ''le with column("D:D") pointe vers la colonne E apparente suite à l'insertion, ''mais qui représente toujours l'objet d'origine à savoir la colonne pointée via l'instruction With... .Offset(0, -1).FormulaR1C1 = "=IF(RC[1]<>"""",RC[1]*24/24,"""")" .Offset(0, -1).NumberFormat = "m/d/yyyy" .Offset(0, -1).Copy .Offset(0, -1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False .Delete End With End Sub

Dans ce code, on insert une colonne à côté de notre colonne date hétérogène, puis on insère dans ses cellules une fonction " Si " qui si, la cellule n'est pas vide va multiplier et diviser par 24 les cellules" dates ".
On joue sur le pouvoir d'interprétation d'Excel qui lors de l'opération reconnaît une donnée qu'il peut interpréter comme une date (un string qui présente le bon format, qu'il va interpréter en Numéro de série) et on récupère la valeur initiale en la divisant par 24.
On met le format de cellule en " date 14/03/2001 " , puis comme notre colonne contient des formules relatives à l'ancienne que nous allons supprimer, on la copie sur elle-même en ne prenant que les valeurs.
On a donc un code simple et court avec une vitesse d'exécution optimisée.

Pour visualiser le processus, vous pouvez créer le tableau suivant :

Dans la colonne D la ligne du dessus est au format texte, visible pas son alignement par défaut à gauche. La colonne E est au format Standard pour afficher le Numéro de série.

II-F. Remarques sur l'emploi des fonctions

Je n'aborde pas ici les fonctions telles que CONVERT car de telles fonctions sont sujettes à bien des débats selon les environnements. Par exemple, CONVERT est native dans Excel 2007 (buggée avant la SP2 /sauvegardes formats antérieurs), mais elle appartient toujours à l'utilitaire d'analyse. L'utilitaire d'analyse qui était un complément jusqu'en 2003 est souvent cité comme natif dans 2007, ce qui est faux pour partie, certaines fonctions, moins courantes nécessitant encore son installation. Sur des versions françaises, on est appelé à rentrer les paramètres en anglais… Donc l'utilitaire d'analyse pourrait faire l'objet d'un tutoriel à lui tout seul, ce qui n'est pas le sujet ici.

Il y a aussi des fonctions basiques bien utiles comme DATEVAL, TEMPSVAL, TEMPS etc. mais elles ont chacune leurs limitations, et avoir compris le fonctionnement du système de dates vous permettra de mieux les appréhender ou mieux encore de construire la fonction dont vous avez précisément besoin avec un maximum de portabilité.
Exemples : Pour une date " 06/02/1900 ", DATEVAL vous retournera le Numéro de série 37 ; pour une date-heure " 06/02/1900 12 :00 :00 ", DATEVAL vous retournera encore 37 alors que le Numéro de série correspondant est : 37,5. DATEVAL ne bogue pas, mais cette fonction ne renvoie des numéros de série que si la date est formatée en texte et elle ne prend pas en compte les informations horaires. S'il y a problème, c'est parce qu'on utilise la fonction à mauvais escient. Il en sera de même avec TEMPSVAL.

Il n'est pas question d'évincer les fonctions, mais de rappeler, comme déjà indiqué et pour ne pas avoir de surprises, de ne pas hésiter à consulter en détail l'aide avant de les employer.

III. Les dates en VBA

III-A. Préambule VBA

Pour lever toute ambiguïté au niveau de l'affichage ou du comportement réactif mentionné, voici les options activées pour l'éditeur VBA qui nous sert d'exemple, Option Explicit étant de rigueur !

Ainsi que les Options Système date courte / longue.

Les plages de validité des dates reconnues comme valides varient selon les systèmes d'exploitation. Sous Microsoft Windows, la plage de validité s'étend du 1er janvier 100 au 31 décembre 9999 du calendrier grégorien. Il en est de même pour VBA.

III-B. Généralités

 En VBA : 

• Le calendrier débute au 31 décembre 1899 (son Numéro de série 1 ) mais il sait aussi prendre en compte toutes les dates allant du 1er janvier 100 au 31 décembre 9999.
• Les dates "positives" débutent avec le 31 décembre 1899 (au lieu du 1er janvier 1900 pour les feuilles de calcul).
• La date nulle (son Numéro de série 0 ) correspond bien au 30 décembre 1899 mais, bizarrement, VBA ne sait pas l'afficher (il retourne 00:00:00, ce qui doit correspondre au point origine de l'axe du temps) !
• Quant aux dates "négatives", elles commencent au 29 décembre 1899 et vont, en reculant dans le temps, jusqu'au 1er janvier 100.
• Les variables de type date affichent les dates au format de date abrégé (date courte) reconnu par votre ordinateur.

Une variable date contient implicitement une date et une heure, mais il est possible de ne renseigner qu'un seul de ces 2 éléments en saisie. (il n'existe d'ailleurs pas de Type de variable Heure).

De la particularité du 29/02/1900 (dans l'affichage des feuilles Excel) et du décalage de calendriers (visant peut-être d'ailleurs à combler cette différence) signalés précédemment, il résulte que les dates antérieures au 1er mars 1900 ne possèdent pas, sous Excel et en VBA, le même Numéro de série, mais qu'à partir du 1er mars 1900, les numéros de série sont identiques.

En VBA, dans la partie date (donc avant la virgule) :

• Le 0 correspond au 30 Décembre 1899 et est affiché 00 :00 :00 (c'est le point-origine qui sépare les numéros de série négatifs et positifs.
• Les dates antérieures seront représentées par des valeurs négatives.
• Le 1 correspond au 31 Décembre 1899.
• Le 2 correspond au 01 Janvier 1900 etc…

Avec ce code :

Option Explicit Sub PlaceDateDansCell() Dim dtmPass As Date Dim I As Long, J As Long, K As Byte J = -10 K = Abs(J) + 2 Worksheets(1).Columns(3).NumberFormat = "@" ' pour éviter une interprétation en date. For I = J To 62 dtmPass = I If I > 0 Then Cells(I + K, 1) = dtmPass '(la feuille refuse l'affichage des dates négatives) Cells(I + K, 2) = I ' If I = -1 Then Stop ''en mettant la valeur voulue, on peut surveiller un instant " t " Cells(I + K, 3) = CStr(dtmPass) 'renvoie une chaîne selon le format date courte de vos options système. Next I End Sub

on obtient le tableau suivant (où la colonne C est en format texte car on veut justement éviter une interprétation feuille ).

Contrairement aux feuilles, VBA refuse bien l'affectation du 29 février 1900 à une date (car l'année 1900 n'est pas bissextile).

Il est généralement conseillé d'utiliser le # en délimiteur, pour s'assurer une notation sans ambigüité et une bonne portabilité internationale. Mais, si ceci est sans problème pour un environnement US, chez nous cela s'avère plus délicat car l'éditeur VBA étant " interfacé US ", il nous faut réfléchir en notation mois/jour/an (au lieu de jour/mois/an), ce qui ne nous est pas naturel ; en outre, cela peut entraîner des risques d'erreurs si l'on n'est pas explicite (sans compter que la lecture du code est plus complexe).

Avantage immédiat : avec cette saisie, VBA nous signale, dès la validation de ligne un problème !

Dans l'exemple précédent, c'est au lancement du code que l'erreur surgit, ce qui fait une grosse différence.

Inconvénients immédiats : on est pratiquement sûr d'aller au devant de gros problèmes (on va voir pourquoi dans les 2 exemples suivants).
Avec une saisie comme ci-dessous, qui retourne un résultat correct, tout semble aller.

... mais c'est un " faux ami ", comme le montre l'exemple suivant (très voisin du précédent) :

Si on veut utiliser cette notation, il faut donc s'astreindre à saisir les mois par leurs noms, ce qui lèvera les ambiguïtés d'interprétation, mais il faudra alors les saisir en anglais (attention à ne pas mettre de voyelle accentuée).

On l'a dit, l'éditeur vba est en anglais.

Stratégies possibles :

Attention, pour les jours la valeur par défaut est le point initial du compteur : soit le 1. dtmPass = #december 2009# donnerait dtmPass = #12/1/2009# une fois validée.

III-C. Exemples de différentes saisies et leurs conséquences

III-D. Correspondance des notations des mois en noms entiers ou abrégés en français et en US.

III-E. Visualisation des problèmes d'interprétation

Pour bien visualiser les problèmes d'interprétation, il est facile de construire un Userform de ce style :

On construit un Userform comprenant un contrôle Monthview et 8 Labels. Au contrôle Monthview, on associe le code suivant :

Private Sub MonthView1_DateClick(ByVal DateClicked As Date) lblDateChoisie.Caption = DateClicked lblNumSerieVBA.Caption = CDec(DateClicked) If DateClicked > #12:00:00 AM# Then Cells(1, 5) = DateClicked Else Cells(1, 5) = "Refusé" 'la date #30/12/1899# n'existe pas en tant que telle... lblAffichageCell.Caption = Cells(1, 5).Text 'la preuve par 5 des dangers d'un code ambigü à tout niveau Labelxx.Caption = Cells(1, 5) 'donnera par défaut le résultat de la ligne suivante.... lblInterpretationVBA.Caption = Cells(1, 5).Value ' La "value" de Cells(1,5) est la date correspondante au Numéro de série sous-jacent ' appliqué au calendrier de l'environnement contextuel, ici nous sommes en vba.... End Sub

Avec un contrôle Calendar, on obtient le même effet, mais il plafonne au 01/01/1900 alors que le Monthview permet de remonter jusqu'au 01/01/1753. Le classeur avec cet UserForm (USF) est disponible en téléchargement (voir à la fin du tutoriel) .

III-F. Gymnastique sur la gestion des Calculs Horaires et Mise en application

Mon ami Benoît Tatillon, qui est travailleur à la tâche mais débutant sous Excel, fait des relevés de temps passés par client, en ayant la bonne idée d'inscrire les données de son téléphone chrono dans 3 colonnes : Heures, Minutes et Secondes. En procédant ainsi, il n'a aucun problème pour faire les totaux par colonne, mais, obtenant des valeurs cumulées, il ne sait pas ensuite comment les facturer.
De plus, il a une deuxième série de colonnes où il rentre des données négatives pour des raisons qui lui sont propres.
Il voudrait aussi simuler le coût de certaines durées en fonction de taux horaires spécifiques; aussi je lui ai donc proposé de mettre en application le tutoriel que je lui avais soumis…

En heures positives (H1), il avait 585 heures, 12846 minutes et 719 secondes, et en heures négatives (H2 affecté d'un signe moins) 138 heures, 712 minutes et 19 secondes, le tout devant être évalué au montant du smig brut de janvier 2010.
On a donc contruit un classeur avec l'Userfom suivant (disponible en téléchargement,voir à la fin du tutoriel) :


La Durée convertie en hh:mm:ss a été reportée en A7 et est utilisable ainsi ; quant au montant, il a été reporté en A10.

Ensuite, Benoît avait d'autres données (avec cette fois H1 inférieur à H2)


La durée étant négative n'est pas supportée comme telle dans la feuille (pour info on la note en clair en A8). Le montant lui figure toujours en A10.

On peut aussi, en sélectionnant Feuille dans Source de Données, récupérer les valeurs saisies en A3 et A4...



Si on inverse le signe en gardant les valeurs..


Code de l'USF :

Option Explicit Private Sub cmdRun_Click() Dim vnttbo_H1 As Variant Dim dtm_H1 As Date Dim vnttbo_H2 As Variant Dim dtm_H2 As Date Dim dblDurGlobalS As Double Dim lngSecond As Long Dim lngMin As Long Dim lngHeure As Long Dim dblMontant As Double 'voir l'aide vba pour les détails sur les fonctions utilisées... On Error GoTo Errare If opbFeuille.Value = True Then txt_H1 = CDate(Range("A3")) txt_H2 = CDate(Range("A4")) End If 'on profite du fait que les textbox ne renvoient que des strings pour utiliser Split vnttbo_H1 = Split(txt_H1, ":") 'Split qui nous retourne un tableau dtm_H1 = TimeSerial(vnttbo_H1(0), vnttbo_H1(1), vnttbo_H1(2)) 'dont on applique les éléments comme arguments à TimeSerial (dont on utillise 'la réaction face aux dépassements de capacité) qui nous retourne une variable date, ce qu'on cherchait. vnttbo_H2 = Split(txt_H2, ":") dtm_H2 = TimeSerial(vnttbo_H2(0), vnttbo_H2(1), vnttbo_H2(2)) 'Ensuite on fait un soustraction sur nos 2 variables dates If opbPlus.Value = False Then dtm_H2 = -dtm_H2 'Ci-dessus, on utilise le comportement des option button dans un frame (idem pour source Données) 'pour faire un interrupteur logique et choisir l'addition ou la soustraction. dblDurGlobalS = Round((dtm_H1 + dtm_H2) * 86400, 0) ' durée globale en secondes 'on extrait ensuite les diverses unités classiquement. lngSecond = Round((dblDurGlobalS / 60 - Fix(dblDurGlobalS / 60)) * 60, 0) 'nb de secondes lngMin = ((Fix(dblDurGlobalS / 60) / 60) - Fix(Fix(dblDurGlobalS / 60) / 60)) * 60 ' nb de minutes lngHeure = Fix(Fix(dblDurGlobalS / 60) / 60) ' nb d'heures txt_RepDeci = (dtm_H1 + dtm_H2) * 24 ' ce qui nous sert à afficher notre "heure reconstituée" lisible par tous. If lngHeure < 0 Or lngMin < 0 Or lngSecond < 0 Then txt_H3 = "- " & Abs(lngHeure) & ":" & Abs(lngMin) & ":" & Abs(lngSecond): _ Range("A7") = "Durée Négative" & vbCrLf & "non affichable" Range("A8") = "moins " & Abs(lngHeure) & "h " & Abs(lngMin) & "mn " & Abs(lngSecond) & "s" & " pour info..." Else txt_H3 = Abs(lngHeure) & ":" & Abs(lngMin) & ":" & Abs(lngSecond): Range("A7") = txt_H3.Text End If ' on utilise ensuite un simple test pour la couleur d'affichage. dblMontant = Round(txt_TauxHor * txt_RepDeci, 2) If dblMontant >= 0 Then txt_Montant = dblMontant & " €": txt_Montant.ForeColor = &H80000012 _ Else txt_Montant = dblMontant & " €": txt_Montant.ForeColor = &HFF& Range("A10") = CDbl(txt_Montant) Exit Sub Errare: Call rezet End Sub Private Sub Label8_Click() Application.ThisWorkbook.FollowHyperlink Label8.Caption End Sub Private Sub opbFeuille_Change() Call rezet End Sub Sub rezet() If opbFeuille.Value = True Then With txt_H1 .Locked = True .MousePointer = fmMousePointerNoDrop End With With txt_H2 .Locked = True .MousePointer = fmMousePointerNoDrop End With Else txt_H1.Locked = False txt_H2.Locked = False txt_H1.MousePointer = fmMousePointerDefault txt_H2.MousePointer = fmMousePointerDefault End If txt_H1.Value = "00:00:00" txt_H2.Value = "00:00:00" txt_RepDeci.Value = "" txt_H3.Value = "" txt_TauxHor.Value = "8,86" txt_Montant.Value = "" Range("A7") = "" Range("A8") = "" Range("A10") = "" End Sub Private Sub UserForm_Initialize() Call rezet Label5.Caption = "Corrrespond à H1 + ou - H2 sachant que qu'on affiche ici la durée en représentation" _ & "décimale des heures et fraction d'heures." Label7.Caption = "Montant en noir ou en rouge selon le contexte." ''Montant à régler (en noir) ou à récupérer (en rouge).. ou l'inverse selon le contexte. Label9.Caption = vbCrLf & "Gymnastique sur la" & vbCrLf & "GESTION DE CALCULS HORAIRES" & vbCrLf & _ " vous pouvez calculer des horaires négatifs et rentrer des cumuls* d'heures, minutes ou secondes" _ & vbCrLf & "en H1 et H2." Label10.Caption = "L'objet de cet Useform n'est pas le contrôle de saisie, il comporte un " _ & "traitement d'erreur sommaire, seules les saisies de forme : chiffre(s) : chiffre(s) :chiffre(s) sont valides" & _ " ou un nombre à virgule pour le taux horaire." Label12.Caption = "Astuce : Pour la saisie, vous pouvez double-cliquer sur un binôme." _ & vbCrLf & "Astuce : Pour réinitialiser à 0 rentrez des lettres et faites Run." Label19.Caption = "Cet USF est non Modal, vous pouvez modifier les valeurs de H1 & H2 / Feuille en direct" End Sub Private Sub UserForm_Terminate() Call rezet End Sub

Dans le code précédent ou ceux des fichiers en téléchargement, vous pouvez vous amuser à changer le typage des variables, pour mesurer, si besoin, les conséquences qui en découlent.

III-G. Savoir s'adapter

Si vous ne voulez pas y perdre votre latin, il vous faut vous adapter aux circonstances, et donc construire éventuellement, dans vos traitements qui iraient dans les plages particulières vues ci-dessus, le code approprié, pour retomber sur vos pieds de façon fiable..
Voir par exemple les liens suivants :

http://support.microsoft.com/default.aspx?scid=kb%3Bfr%3B466618

http://support.microsoft.com/kb/245104/fr

(ce n'est pas du latin, mais c'est de la traduction automatique…)

IV. Pour les curieux...

IV-A. Les calendriers... Pas si simple !

Les calendriers, ce n'est pas si simple… Entre le bug de l'année 1900 bissextile dans des tableurs, le calcul du jour de Pâques, le fait qu'il n'existe pas d'An zéro dans le calendrier Grégorien, que celui-ci a tout bonnement effacé 10 jours en 1582 (qui font que le lendemain du jeudi 4 octobre 1582 est le vendredi 15 octobre 1582) et que l'adoption de ce calendrier par de nombreux pays (pas tous) s'est échelonné depuis 1582 (1582 pour la France, mais entre le 16^e et 17^e siècle pour le Canada français) ne facilite pas la vocation des Excelliens historiens, astronomes ou généalogiciens en herbe !

Le calendrier grégorien
Absence d'An zéro

IV-B. L'énigme du jour zéro en vba : le 30 décembre 1899

Nous avons noté plus haut que le vba ne savait pas afficher le 30 décembre 1899 (qui figure et réagit sans souci dans le contrôle Monthview de notre USF) et qu'il retourne à la place 00 :00 :00. Ce qui n'influe d'ailleurs pas sur les calculs de dates dans cette zone, comme nous le montre le code suivant.

Sub CalulDatesDG() 'calcul de dates à cheval du 0 = OK Dim dtmPass1DG As Date Dim dtmPass2DG As Date Dim dtmPass3DG As String dtmPass1DG = #1/1/1900# dtmPass2DG = #12/29/1899# dtmPass3DG = dtmPass1DG + dtmPass2DG End Sub

dtmPass3DG aura comme valeur : 31/12/1899 soit le Numéro de série 1.
On ajoute donc au Numéro de série 2 (01/01/1900) le Numéro de série -1 et on obtient fort mathématiquement 1.

Pour regarder d'un peu plus près ce qui se passe entre le 31 décembre 1899 et le 29 décembre 1899, nous allons construire une routine qui nous permettra de constater la valeur retournée par le vba en faisant régresser la date-heure à partir du 01/01/1900 par tranche de 6 heures.
On se servira pour cela de la ligne de code suivante, après avoir initialisé D3 au 01/01/1900.
dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1.
I nous fait passer à la ligne suivante à chaque ligne de code avec I=I+1.
Notre variable date est décrémentée de 6 heures, on affiche la date dans la cellule de la colonne D, puis son Numéro de série dans la E.
Sur le côté nous avons un tableau espion qui donne pour certaines lignes les détails de la valeur date concernée, en affichage, ici, nous avons la correspondance au 28/12/1899, l'intérêt est d'avancer pas à pas sur le code bien sûr.


Que se passe-t-il ?

Là je ne pourrai vous en dire plus, je dirai pour le moment que constate-t-on ?
La journée du 30/12/1899 semble comporter 48 heures, soit 24 heures positives et 24 heures négatives. Les journées du 31/12/1899 en couleur bleu clair et du 29/12/1899 en brun ayant bien leur propre fenêtre de 24 heures en tendant vers 0+ et 0-.
Si on inscrit ces points sur un axe d'abscisse, on aurait le 31/12/1899 à minuit placé en +1 et le 29/12/1899 en -1 avec l'espace entre deux correspondant au 30/12/1899. Ci-dessous le code du tableau précédent :

Sub EtudeDuJourZéro() Dim dtmPassDG As Date Dim I as long Range(Cells(3, 4), Cells(19, 6)).ClearContents I = 3 dtmPassDG = 2: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 Call Espion_DatePart(dtmPassDG) Stop dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 Call Espion_DatePart(dtmPassDG) Stop dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 Call Espion_DatePart(dtmPassDG) Stop dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 Call Espion_DatePart(dtmPassDG) Stop dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 Call Espion_DatePart(dtmPassDG) Stop dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 Call Espion_DatePart(dtmPassDG) Stop dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 dtmPassDG = dtmPassDG - #6:00:00 AM#: Cells(I, 4) = CStr(dtmPassDG): Cells(I, 5) = CDec(dtmPassDG): I = I + 1 Call Espion_DatePart(dtmPassDG) End Sub Sub Espion_DatePart(dtmPassDG) Range(Cells(3, 6), Cells(9, 6)).ClearContents Cells(3, 6) = DatePart("s", dtmPassDG) Cells(4, 6) = DatePart("n", dtmPassDG) Cells(5, 6) = DatePart("h", dtmPassDG) Cells(6, 6) = DatePart("d", dtmPassDG) Cells(7, 6) = DatePart("y", dtmPassDG) Cells(8, 6) = DatePart("m", dtmPassDG) Cells(9, 6) = DatePart("yyyy", dtmPassDG) End Sub

 

V. Notion Rapide du Numéro de série sous Excel

Ceci est destiné à donner une approche rapide aux plus pressés, l'intelligibilité de cette approche ne sera globale qu'après la compréhension des divers éléments de ce tutoriel.
Pour comprendre son fonctionnement, il faut savoir qu''Excel stocke :

• Les dates sous forme de nombres de jours entiers (depuis " l'origine" ).
• Les heures sous forme de fractions décimales de jour.

On obtient un nombre décimal, qu''Excel désigne sous le terme de Numéro de série, dont la partie gauche (entière) concerne la date et la partie droite (décimale) l'heure.

Pour s'en convaincre, il suffit de taper une date ou une heure dans une cellule, puis de changer le format de celle-ci en Standard => on obtient alors un nombre " le Numéro de série ". Ce système de numéros de série permet d'effectuer des opérations mathématiques et comparatives sur les dates.
آخر مواضيع المنتدى منتدى نقاش المغرب العربي
ازياء اطفال غاية في الجمال
فساتين للبنوتات الحلوين
صناديق لحفض العاب اطفالك
التسنين ( اعراضه - آلامه - علاجه ) + تسوس الرضاعه
كولكشن اطفال جونااان
ساعات للاطفال من دزني،،
أطفال وكل شي حلو منهم
اهم الاعتبارات عند شراء ملابس طفلك الرضيع
علمي طفلك اصول كتم الاسرار العائليه
برنامج لكيفية الاستفاده واستغلال الاجازه الصيفيه للاطفال بشكل اكبر
ما هي الأعراض المبكرة للحمل
ما التأثير الضار للكافيين على الحامل؟
مشكلة التبول اللارادي عند الأطفال
ما هى الصفات التي يتلقاها طفلك منك؟
كيف تحفّزي التطور البدني للأطفال بعمر 8 أشهر