«

»

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.

C'est quoi une date dans Excel ?

Erreur ? ⛔ Mais non c'est normal 😉

Dans Excel, il n'est pas rare que l'ajout d'une date dans une cellule s'affiche sous  la forme d'un nombre ; comme par exemple 42370.

Pour beaucoup d'utilisateurs cet affichage est un bug. Or pas du tout !

Les dates sont des nombres entiers

Voici une date dans une cellule format-date-1

 

format-date-2Maintenant, la valeur 42370 c'est aussi une date.

C'est la même valeur mais au format  Standard.

Changez une date entre le format Date et Standard pour voir la difference d'affichage 😉

En fait, un nombre entier représente l'écart en nombre de jours depuis le 01/01/1900 qui est le point de départ des dates dans Excel.

Si vous voulez qu'Excel interprète les dates avant le 01/01/1900, ce n'est pas possible. 😤😱⛔

Attention au texte dans une date ⚠

Avec les dates, l'une des erreurs les plus souvent commises c'est d'écrire du texte dans la date (1 janvier 2016 par exemple).

Ne jamais écrire une date comme cela ⛔⛔⛔

Car pour Excel, le contenu de la cellule est maintenant du Texte en non plus une Date.

  • Un texte est toujours cadré à gauche
  • Un nombre est toujours cadré à droite

Donc simplement en regardant la position de la donnée dans la cellule, vous savez si c'est du Texte ou un Nombre (ou Date)

Les bonnes pratiques

Excel vous permet d'afficher une date de plusieurs façon

  • 01/01/2016
  • 01 janvier 2016
  • vendredi 01/01/2016
  • ven 01 janvier 2016
  • ...

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 les options date courte et date longue.

 

 

 

 

 

 

 

Personnaliser le format d'une date

Vous pouvez personnaliser l'affichage d'une date en jouant sur les 3 composantes d'une date à savoir

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

Méthodologie

Pour personnaliser une date, il faut ouvrir la boîte de dialogue de personnalisation des nombres

  • Soit en cliquant sur la toute petite flèche oblique juste en bas de chaque section du ruban
  • Soit en appuyant sur le raccourci clavier Ctrl + Shift + 1
  • Ou encore en cliquant sur le menu Autres formats numériques (tout en bas de la liste des formats de nombre)

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.

Maintenant, il suffit d'écrire en paramètre les codes j, m ou a un certain nombre de fois. En inscrivant les codes, 1, 2, 3 ou 4 fois, l'affichage sera différent.

Le tableau suivant vous montre toutes les combinaisons possible et le résultat dans Excel.

Voici un tableau qui montre plusieurs résultat possible toujours à partir de la même date 😎😍😃

Différence fondamentale entre Date et Heure

Dans Excel, les heures sont toujours une fraction d'une journée.

Cette règle est fondamentale pour éviter les erreurs d'affichage et de calcul. JAMAIS un nombre entier ne sera interprété comme une heure ou une minute.

 

 

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. ⚠⚠⚠

Ce qui se passe ici c'est qu'Excel affiche le 8ème jour à partir du 01/01/1900.

C'est pour ça que le résultat est le 08/01/1900 comme le montre l'image ci-contre.

Convertir un nombre entier en Heure

Donc, si vous devez convertir un nombre entier en heure, vous devez diviser ce chiffre par 24 (soit le nombre d'heures dans une journée).

=8/24

Le résultat est 0,33333 mais en appliquant le format Heure, le résultat est de 08:00:00 dans la cellule 😉

 

Il existe une technique très simple avec le collage spécial pour transformer tous les nombres entiers d'une feuille en heure.

Format des heures

Personnalisation des Heures, Minutes et Secondes

De la même manière que pour les dates, il est aussi possible de personnaliser l'affichage

  • h pour le format des heures
  • m des minutes
  • s des secondes

Il n'est pas possible d'écrire les minutes sans les heures ou sans les secondes car sinon le paramètre m est interprété comme le mois

Les heures au-delà de 24H

Pour les heures au-delà de 24H, il y a une spécificité. Il faut écrire le code entre crochets

=[h]

Calcul avec des heures

Vous trouverez dans ces articles des exemples de calcul avec des heures

 

Synthèse

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

 

Articles complémentaires


Voici une liste d'articles qui pourrait également vous intéresser sur le même thème.

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


(37 commentaires)

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 http://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. Valerie

        bonjour, ayant rencontré le problème, cela a été résolu dans l'onglet FORMULES > AFFICHER LES FORMULES, et les affichages ont retrouvé leur forme habituelle.

  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

Laisser un commentaire

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