«

»

Format des dates et des heures

Le format des dates et des heures dans Excel est très important car ce sont de faux-amis.

Trois erreurs souvent commises avec les dates

  1. Ajouter une date dans une cellule et voir un chiffre (aux alentours de 42000) apparaître
  2. Ecrire du texte (jour ou mois en lettres) dans une date
  3. Vouloir afficher des heures et voir s’afficher 00:00:00 dans une cellule

Toutes ces erreurs s’expliquent par une méconnaissance du format des dates dans Excel.

Présentation des formats de date

Les dates sont des nombres

Quand vous écrivez une date dans une cellule – ou que le résultat d’une formule doit retourner une date – et que le résultat affiche un nombre (aux alentours de 42000) au lieu d’une date, pour beaucoup d’utilisateurs c’est un bug d’Excel. Or pas du tout !

Une date étant un nombre il est tout à fait normal qu’Excel puisse afficher une date juste sous la forme d’un nombre réel, sans le présenter avec des / ou des –

format-date-1A titre d’exemple, inscrivez une date dans une cellule. Par défaut, vous devez voir votre date s’afficher avec des séparateurs de date

format-date-2Maintenant si vous changez le format de la cellule et que vous mettez la cellule en format Standard la cellule affiche dès lors non plus une date mais le nombre 42370.

Ce chiffre représente l’écart en nombre de jours entre la date 01/01/1900 (qui est le point de départ des dates dans Excel) et la date que vous avez saisie. Il est également possible de commencer une date au 01/01/1904 dans Excel ; ce point sera étudié dans l’article sur les heures négatives.




Ecrire une date en texte

Avec les dates, l’une des erreurs les plus souvent commises c’est d’écrire sous forme de texte une partie de la date (1 janvier 2016 par exemple).

Ce n’est absolument pas à faire car en jouant sur le format d’une date – comme nous le verrons dans le paragraphe qui suit – il est possible d’obtenir le même résultat d’affichage mais en gardant la date sous forme d’un nombre.

Dans Excel un texte est cadré à gauche et un nombre est cadré à droite (pour plus d’explication, reportez-vous à l’article sur la différence entre texte et nombre).

 

Si vous voulez afficher exactement Samedi 1 Janvier 2011 dans une cellule, vous écrivez en fait une date mais dans un format particulier (voir le paragraphe Format ci-dessous). Parce que cette donnée est cadrée à droite dans la cellule, il s’agit bien d’un chiffre – dans le langage de Excel – bien que du texte s’affiche dans la cellule.

 

Dans Excel, différence entre Heure et Date

Cette règle est très importante pour comprendre comment faire des calculs autour d’une date et aussi comment travailler avec les heures.

Par exemple, si vous avez la valeur 8 dans une cellule et que vous appliquez le format ‘Heure‘, votre cellule affiche 00:00:00. Ce n’est pas une erreur.

Excel affiche dans ce cas, le 8ème jour à partir du 01/01/1900 (et non pas la 8ème heure).

Règle 3 : Les heures sont toujours une fraction d’une journée.

Si vous voulez afficher 08:00:00 dans une cellule, il faut diviser ce chiffre par le nombre d’heures dans une journée (soit 24).  Ensuite, en appliquant le format Heure, vous affichez 08:00 et non pas 0,33333333.

Il existe une technique très simple avec le collage spécial pour convertir facilement un tableau qui ne contient que des chiffres entier, en valeur décimale pour les transformer en heures.

 



Format des dates

Quand vous avez une date dans une cellule, vous pouvez l’afficher de différentes manières grâce aux options de formatage. Depuis le ruban, vous avez l’option date courte et date longue.

 

Vous pouvez également personnaliser chaque composante d’une date (jour/mois/année) en utilisant la boîte de dialogue de personnalisation des nombres. Pour afficher la boîte de dialogue de personnalisation des nombres, vous cliquez sur la flèche juste en dessous du ruban ou encore en activant le raccourci clavier Ctrl + Shift + 1.

 

 

 

Dans cette boîte de dialogue, vous sélectionnez dans la partie de droite ‘Personnalisée’ et dans ‘Type’ vous allez vous-même définir comment afficher votre date.

Chaque composant d’une date peut être modifié :

  • j pour les jours
  • m pour les mois
  • a pour les années

Dans la zone ‘Type‘, il vous suffit d’indiquer la manière dont vous voulez que la date s’affiche. Regardez le tableau suivant qui reprend la liste des codes existant et le résultat que cela entraine sur le format des dates.

Vous pouvez aussi combiner tous ces paramètres pour afficher vos dates de différentes manières. Ici par exemple, nous avons un classeur intégré qui présente la date du jour et plusieurs affichages différents de cette date (le code du format est dans la première colonne).



Format des Heures

De la même manière que pour les dates, il est aussi possible de changer le format des heures (h), des minutes (m) et des secondes (s). Pourtant il existe des particularités.

  • Pour afficher des heures au-delà de 24, il faut écrire le code des heures entre crochets [h]. Dans l’exemple ci-dessous, Excel présente le nombre d’heures depuis le 01/01/1900
  • Il n’est pas possible d’écrire les minutes sans les heures ou sans les secondes car sinon le format est interprété comme le mois
  • Pour les dixièmes, centièmes et au-delà, il suffit d’ajouter des .0 (c’est un point, pas une virgule) à votre format pour afficher ces informations dans votre cellule

Vous trouverez un exemple de calcul en dixième, centième et millième dans cet article.

Le tableau suivant vous présente comment afficher un temps avec les différents codes possibles.




Lien Permanent pour cet article : https://www.excel-exercice.com/format-dates-heures/

(37 commentaires)

2 pings

Passer au formulaire de commentaire

  1. kohen

    Bonjjour,
    Je vous prie de m’aider à poser une condition dans une formule: je dispose de colonnes: libellé- Date-Responsable-Résultat. Sachant que j’ai des dates sous format jj/mm/aa, je cherche par exemple le nombre de résultat obtenu (exemple: Réalisé, en cours, en retard…) par libellé (Inspection, visite, audit…) mais je n’arrive pas à rajouter le critère “DATE” pour calculer le résultat en fonction de l’année “en cours” sans tenir compte des jours et des mois? Merci pour votre précieuse collaboration
    exemple:

    Libellé Date Responsable Résultat

    Inspection 08/03/2017 AA Réalisé
    Audit 03/01/2017 BB En retard
    Inspection 25/12/2016 AA En retard

    1. Frédéric LE GUEN

      Il faut utiliser NB.SI.ENS https://www.excel-exercice.com/nb-si-ens/
      Un exemple montre comment trouver les salaires compris entre 50000 et 70000. Il faut faire pareil mais avec des dates

      1. kohen

        Merci pour la célérité de votre réponse.
        En effet c’est ce que j’utilise mais le problème est comment poser le critère sur l’année en cours sans tenir compte des jours et des mois. Voici la formule utilisée mais sans le critère dont j’ai besoin:
        =NB.SI.ENS($C$61:$C$1000;”Audit “;$F$61:$F$1000;”en cours”;$V$61:$V$1000;$A35)

        Merci encore

  2. Bakhali

    bonjour,je cherche a convertir une date écrite en année seulement en date écrite en jj/mm/aaaa et merci pour votre aide précieuse.

    1. Bakhali

      bonjour , merci pour m’avoir répondu et j’ai compris la difficulté .

  3. Jafran

    Bonsoir,

    Je ne parviens pas à afficher les dates dans le format que je choisis dans une colonne Exel 2007.
    Je tape des valeurs de type 04/04/1896 et c’est bien ce qui s’affiche. Je vais sélectionne la colonne et vais dans “nombre / date” ou je choisis un affichage type “14 mars 2001” OK. L’affichage ne change pas et il est impossible de trier la colonne.
    Curieusement, les deux dates de 01/07/1900 et 08/08/1904 sorte bien en 1er juillet 1900 et 8 août 1904. Compte tenu de vos explications ci-dessus, je flaire que l’on ne peut pas entrer aisément de dates antérieures à 1900. Mais ce serait pour le moins curieux car, en histoire, on a souvent besoin de classer par date.
    Y aurait-il un moyen d’y parvenir ?
    Merci d’avance pour votre aide.

    1. Frédéric LE GUEN

      Bonsoir,
      Alors les dates antérieures à 1900 s’affichent mais en texte (cadré à gauche dans une cellule). Donc comme c’est du texte, vous ne pourrez jamais les faire grouper par mois, années, trimestre, …
      Comme vous l’avez bien compris, la limite des dates dans Excel c’est le 01/01/1900 et il n’est pas du tout possible d’y déroger et ce n’est même pas une volonté de Microsoft de vouloir le modifier. Excel a été pensé pour aider dans les opérations comptables mais pas pour l’histoire ni la généalogie.
      Désolé, pas de solution

      1. Jafran

        Bonjour,
        Merci pour cette précision qui évite de perdre du temps à chercher une solution avec Excel sur cette fonction.
        Pour ceux qui voudraient quand même utiliser un tableur pour classer chronologiquement des dates dont certaines antérieures à 1900, j’ai trouvé la solution avec Calc de Libre-Office qui fait ça sans difficulté (tout comme les autres fonctions de Excel d’ailleurs). Ceci n’est pas une publicité pour Libre Office car j’utilise plus souvent Excel mais me rabats sur Calc lorsque Excel coince.

  4. TONSON LA TOUR

    Bonjour Frédéric, c’est Anne-Claire de Paname !
    Comment va ?
    J’ai un souci sur mon fichier, je tape le format de la date que je veux : DD/MM/AA dans format > Date etc mais il persiste à s’afficher en chiffres dans les cellule mais dans la case du haut fx (où on visualise la formule) je vois que Excel affiche la date comme je veux

    Ex : 10/04/2017 dans fx
    continue à s’afficher dans ma cellule en chiffres : 42835

    Bref, pour comparer un fichier erroné et un fichier à fusionner qui doit être le bon, c’est pas pratique.

    Merci pour ton aide et belle journée à toi !
    Ac

    1. Frédéric LE GUEN

      Bonjour Anne-Claire,
      Bizarre cette histoire. Et quand tu passes par la boite de dialogue “format personnalisé”, tu peux écrire jj/mm/aaaa

      1. Naly

        Je viens d’avoir le même problème. Solution: il suffit de copier/coller la feuille dans un nouvel onglet et tout s’affiche correctement….

  5. Aline

    Bonjour,

    J’aimerais utiliser Excel 2016 pour faire ma petite compta pro.
    Comment faire en sorte que sur le classeur janvier par exemple toutes les dates soient en janvier et que je n’ai plus qu’à rentrer le jour.
    Je m’explique: quelqu’un me fait un chèque le 16/01, je note 16 dans Excel et il met le /01 ou -janv tout seul.
    Et dans le classeur février il fasse de même en rajoutant -fev. ??

    Merci bcp pour votre aide!

  6. Jaline

    Bonjour, malgré les conseils donnés sur plusieurs forums, je ne réussis pas à additionner des heures en les affichant au bon format. Auriez-vous la solution.
    Merci pour le partage et les conseils.

    1. Frédéric LE GUEN

      Il n’y a pas de raison que ça ne marche pas. Si vos heures sont bien des valeurs décimales (tel que c’est expliqué dans l’article), l’addition doit fonctionner

  7. Christophe

    Bonjour,
    J’aimerai faire un suivi de chantier sur plusieurs feuilles ex : feuille 1 = Janvier 2016
    Dans cette feuille, une case marqué Lundi j’aimerai avoir automatiquement la date de ce jour.

    EX : Feuille Janvier

    case A1 -> 01/01/16
    case B1 -> Vendredi

    car le 01/01/16 tombe un vendredi

    Merci.

  8. Marco

    Bonjour.
    Je désire faire des moyennes de temps (format hh:mm:ss), mais ayant des heures en 00:00:15, il m’est impossible d’avoir une moyenne correcte (impossible de diviser par 0) ?
    Y-a-t-il une fonction capable de convertir les heures en décimal, de faire la moyenne (en décimal) et de repaser au format heure??

  9. Anonyme

    Bonjour,

  10. Anonyme

    bonjour j’ai une date avec des point 23.10.2015 vu excel ne comprend pas les points comment le convertir en date ?

    1. Frédéric LE GUEN

      Ca doit être des dates qui viennent d’un système allemand. En Allemagne les séparateurs autorisés pour les dates sont les / et les .
      Un simple Remplacer les . par des / fonctionne pour corriger votre erreur

  11. Danielle

    Bonjour,
    J’aimerais être capable d’insérer un calendrier dans une cellule pour que je puisse choisir une date spécifique, comment fait ont ??

  12. JF Boutigny

    un tableau excel issu d’une application sous le logiciel Manhattan génère les dates avec le format suivant : [$-1040C]jj/mm/aaaa.
    lors de l’utilisation du tableau dans une macro, les dates sont modifiées et traduites sous format américain lorsque la date du jour est comprise entre 1 et 12 ( exemple le 05/04/2015 devient 04/05/2014) leet restent sous format français si le n° du jour est supérieur à 12 (exemple le 13/05/2015 reste 13/05/2015 puisque le mois 13 n’existe pas).

  13. Hélène

    Bonjour, J’ai importé des données (pour info, d’un compte bancaire) sous CSV, dans un tableau excel où je tiens tout l’historique de ce compte. Or, cette fois-ci les dates, correctes sous le fichier importé, se décalent lorsque je les colle dans le grand tableau. Le décalage est de 4 ans et 1 jour, et je suppose qu’il s’agit de ce que vous mentionnez plus haut, de la date initiale au 1/1/1904. Par ex., le code 40724 correspond au 1e juillet 2015. Je n’ai pas trouvé la solution pour rétablir le bon code. Pouvez-vous m’aider ? Merci !

    1. JF Boutigny

      L’article ci-dessous vous intéresse peut-être

      Comment faire pour transférer des fichiers entre Microsoft Excel pour Mac et Excel pour Windows
      Par défaut, Excel pour Mac utilise le système de date Calendrier depuis 1904 et Excel pour Windows utilise le calendrier depuis 1900. Cela signifie que lorsque vous tapez le numéro de série 1 dans Excel pour Mac et mettez-le en forme comme une date, Excel l’affiche comme 02/01/1904 12:00 a.m. Microsoft Excel pour Windows affiche le numéro de série 1 comme 1/1/1900 12:00 a.m.

      Si vous transférez des fichiers à partir d’Excel pour Mac vers Excel pour Windows, cette différence dans les systèmes de date ne devrait pas provoquer de problème, car le système de date est enregistré dans chaque fichier. Toutefois, si vous copiez et collez entre des fichiers avec des systèmes de date différents provenant de plates-formes différentes, les dates peuvent être affichées quatre années et un jour après ou avant leur date correcte.

      Pour changer le système de date Calendrier depuis 1904 dans Excel pour Windows, procédez comme suit :

      Dans le menu Outils dans Excel 2003, cliquez sur Options. Aller à Options Exceldans Excel 2007 et versions ultérieur.
      Dans Excel 2003, cliquez sur l’onglet calcul , puis cliquez sur pour sélectionner la case à cocher système de date Calendrier depuis 1904 . Dans Excel 2007 et versions ultérieures, cliquez sur l’option avancée . Faites défiler la liste vers le bas à lors du calcul de ce classeur : et sélectionnez la case à cocher utiliser depuis 1904 .

      Pour changer le système de date Calendrier depuis 1904 dans Excel pour Mac, procédez comme suit :

      Dans le menu Excel , cliquez sur Préférences.
      Sous formules et cliquez sur listes calcul.
      Sous options de classeur , cliquez sur Désactiver la case à cocher système de date Calendrier depuis 1904

  14. Charles HONNORAT

    Je voudrais écrire des dates antérieures à 1900 et faire sur mes tableurs des tris par date (exemple 13 août 1745 ou 5 juillet 1630…) . D’après vos explications, cela semble impossible …

    1. Frédéric LE GUEN

      C’est tout à fait impossible.
      Excel ne peut pas être utilisé pour de la généalogie ou autres recherches historiques

  15. Virginia

    Bonjour. Dans un fichier excel, je souhaiterais qu’on puisse rentrer une date au format jj/mm/aaaa et que dans la case d’à côté, dans ma formule, je puisse avoir ma date au format jjmmaa.
    Par exemple, je rentre 08/07/2015 et ma formule ferait en sorte que j’obtienne “Bonjour, la date est : 080715”. Est-ce possible ? Si oui, comment faire ?

    1. Frédéric LE GUEN

      Bonjour,
      Il faut écrire dans la cellule d’à coté =”Bonjour, la date est : “&TEXTE(A1;”ddmmaa”)
      Bien sur votre date est en A1
      Allez faire un tour sur la page qui explique la fonction TEXTE dans ce site. Fonction>Fonction Date>Fonction TEXTE

  16. K'naille

    Bonjour, j’aimerais pouvoir saisir mes dates avec ce format : 23.03.2015 soit jj.mm.aaaa . Est-ce possible ? et si oui comment ? faut-il saisir les points dans la barre “Type” et lesquels ; celui du clavier alpha ( touche ; . ) ou celui du pavé numérique ( touche . suppr ). Dans les cellules Excel faut-il saisir les points si oui lesquels : alpha ( 23.03.2015 ) ou numérique ( 23,03,2015 ) ou uniquement les chiffres : ( 23032015 ). Merci de votre réponse, cordialement

  17. K'naille

    Bonjour, j’aimerais pouvoir entrer un format de date quelque peu différent de ceux existants à savoir : jj.mm.aaaa ; Est-ce possible et si oui, s’il fallait saisir le “.” avec le point du clavier alpha (majuscule 😉 ou celui du clavier numérique “. suppr” qui donne une “,” avec des chiffres ; et dans la cellule est-ce que je dois saisir 23032015 ou 23.03.2015 et là encore le point clavier alpha ou numérique. Merci de votre réponse, cordialement

  18. zoulai

    Bonjour,
    J’ai un souci de date dans un fichier excel que j’échange avec des utilisateurs étrangers.
    Je dois entrer mes dates au format dd/mm/aaaa pour qu’elles soient reconnues comme telle par Excel, et eux, doivent les entrer en format mm/dd/aaaa,
    D’ou cela vient il ? et si possible comment faire pour que nous entrions les dates dans le même format ?
    Merci d’avance !

    1. Anonyme

      Cela vient du fait que votre interlocuteur utilise une version anglo-saxonne d’Excel et que dans ces pays, le format de date n’est pas le même.
      Créer un format personnalisé de la forme YYYY/MM/DD. Vous contournerez ainsi la difficulté

  19. bisquette

    Bonjour
    je souhaite faire une liste de dates en y mettant uniquement les lundis. Par exemple pour 2015: le 05/01/2015, ligne en dessous le 12/01/2015, etc… et ce pour l’année entière
    Comment incrémenter d’une semaine sur l’autre ?
    (car mon fichier est énorme et pour avancer me collègues, je dois faire le même fichier pour plusieurs années …. très long à la main !

    Merci !

    1. Frédéric LE GUEN

      Il n’est pas nécessaire de faire des formules. Il suffit d’uilitser la poignée de recopie et ça se fait tout seul. Regardez la deuxième vidéo présente dans cette page http://www.excel-exercice.com/gestion-des-donnees/recopie-incrementee/

  20. bousquet matthieu

    Bonjours, je souhaite trouver une formule pour calculer précisément une date. je souhaite trouver a partir d’une date type( ex: 08/02/2014) qu’elle date nous serons 9 mois et 15 jours plus tard. pour l’exemple la réponse devrait être 23/11/2014. il faut compter 9 mois plein puis 15 jours a partir de cette date mais je ne sais pas comment faire.
    si vous pouvez m aider merci

  21. Frédéric LE GUEN

    Bonjour, Pouvez-vous mieux expliquer ce que vous voulez faire ? Sans exemple concret, je ne peux pas vous aidre

  22. PELLEAU

    Bonjour,
    Je ne comprends rien au explications pour additionner des heures. Les crochets je les mets où ? J’ai essayé plusieurs solutions, ça ne donne rien !

    1. Frédéric LE GUEN

      Très facile, Faites un tour à la page suivante http://www.excel-exercice.com/fonction/fonctions-dates/date-annee-mois-jour/ et vous verrez comment ajouter des mois et/ou des jours à une date donnée

  1. Comment créer un calendrier automatique dans Excel

    […] Format des dates dans Excel […]

  2. Calcul des dixièmes, centièmes et millièmes avec Excel - Excel Exercice

    […] cela l’a été évoqué dans cet article sur le format des heures, pour pouvoir ajouter des dixièmes, centièmes ou millièmes, il suffit de rajouter après le […]

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Advertisment ad adsense adlogger