Les dates sont souvent une partie importante de l'analyse des données. Vous posez souvent des questions telles que la date d'achat du produit, combien de temps la tâche prendra-t-elle sur le projet ou quel est le revenu moyen pour le trimestre fiscal ? Il est impératif de saisir les dates correctes pour garantir des résultats précis. Cependant, pour bien comprendre ces résultats, vous devez formater les dates pour les rendre plus faciles à comprendre.
Important:Étant donné que les règles régissant l'interprétation des dates par tout programme de calcul sont complexes, vous devez être aussi précis que possible pour les dates que vous entrez. Cela garantira que les calculs de date sont aussi précis que possible.
Comprendre les calculs et les formats de date
Excel stocke les dates sous forme de nombres séquentiels appelés valeurs séquentielles. Par exemple, dans Excel pour Windows, le 1er janvier 1900 est le 1 ordinal et le 1er janvier 2008 est l'ordinal 39448 car il est 39 448 jours après le 1er janvier 1900.
Excel stocke les valeurs de temps sous forme de fractions décimales car le temps est traité comme une fraction de jour. Un nombre décimal est une valeur comprise entre 0 (zéro) et 0,999999999, représentant une heure comprise entre 0:00:00 (12:00:00) et 23:59:59 (23:59:59).
Étant donné que les valeurs de date et d'heure sont des valeurs, elles peuvent être ajoutées, soustraites et incluses dans d'autres calculs. Vous pouvez afficher une date sous forme de valeur ordinale et une heure sous forme de fraction décimale en modifiant le format d'une cellule contenant une date ou une heure en un format général.
Comprendre les deux systèmes de date
Excel pour Mac et Excel pour Windows prennent en charge les systèmes de date 1900 et 1904. Le système de date par défaut pour Excel pour Windows est 1900 ; et le système de date par défaut pour Excel pour Mac est 1904.
Excel pour Windows était à l'origine basé sur le système de date 1900 car il améliorait la compatibilité avec d'autres tableurs développés pour le système d'exploitation MS-DOS et Microsoft Windows et est ainsi devenu le système de date standard. Excel pour Mac était à l'origine basé sur le système de date de 1904 car il améliorait la compatibilité avec les premiers ordinateurs Macintosh qui ne prenaient pas en charge les dates antérieures au 2 janvier 1904, il est donc devenu le système de date par défaut.
Le tableau ci-dessous présente la première date et la dernière date de période pour chaque système de date et les valeurs séquentielles associées à chaque date.
Étant donné que les deux systèmes de date utilisent des jours de début différents, la même date est représentée par des numéros de série différents dans chaque système de date. Par exemple, le 5 juillet 2007 peut avoir deux valeurs consécutives différentes selon le système de date utilisé.
La différence entre les deux systèmes de date est de 1 462 jours ; Cela signifie que le numéro de série d'une date dans le système de date de 1900 est toujours inférieur de 1 462 jours à la valeur consécutive de la même date dans le système de date de 1904. Inversement, le numéro de série d'une date dans le système de date de 1904 est toujours de 1 462 jours. inférieur à la valeur consécutive des mêmes dates dans le système de date de 1900. 1462 jours équivaut à 4 ans et un jour (y compris un jour bissextile).
Changer la façon dont une année à deux chiffres est interprétée
Important: Pour interpréter correctement les valeurs de l'année, entrez quatre chiffres (par exemple, 2001, pas 01). En entrant des années à quatre chiffres, Excel n'interprétera pas ce siècle.
Si vous avez entré une date à deux chiffres de l'année dans une cellule de texte ou un argument de texte dans une fonction, tel que "= année" ("1/1/31"), Excel interprète l'année comme suit :
00 – 29 interprété comme les années 2000 à 2029. Par exemple, lors de la saisie d'une date 5/28/19 Excel pense que la date pourrait être le 28 mai 2019.
de 30 à 99 interprété comme les années 1930 à 1999. Par exemple, lors de la saisie d'une date 5/28/98 Excel pense que la date pourrait être le 28 mai 1998.
Dans Microsoft Windows, vous pouvez modifier la façon dont l'année à deux chiffres est interprétée pour tous les programmes Windows installés.
Windows 10
Panneau de commande puis sélectionnez Panneau de commande.
Au chapitre horloge, langue et région Cliquez sur.
Cliquez sur l'icône Langue et normes régionales.
Dans la boîte de dialogue Région appuie sur le bouton Options supplémentaires.
Ouvrir l'onglet Date .
Dans le champ
Cliquez sur le bouton d'accord.
Windows 8
Chercher Chercher), entrez dans le champ de recherche élément du panneau de commande Panneau de commande.
Au chapitre horloge, langue et région Cliquez sur.
Dans la boîte de dialogue Région appuie sur le bouton Options supplémentaires.
Ouvrir l'onglet Date .
Dans le champ après avoir entré deux chiffres de l'année, l'interpréter comme l'année entre cadres, en modifiant la limite supérieure de la paupière.
Lorsque la limite supérieure change, l'année passe automatiquement à la limite inférieure.
Cliquez sur le bouton d'accord.
Windows 7
Cliquez sur le bouton Démarrer et sélectionnez l'article Panneau de commande.
Sélectionnez un élément.
Dans la boîte de dialogue Région appuie sur le bouton Options supplémentaires.
Ouvrir l'onglet Date .
Dans le champ après avoir entré deux chiffres de l'année, l'interpréter comme l'année entre cadres, en modifiant la limite supérieure de la paupière.
Lorsque la limite supérieure change, l'année passe automatiquement à la limite inférieure.
Cliquez sur le bouton d'accord.
Modifiez le format de date par défaut pour afficher l'année en quatre chiffres
Par défaut, lorsque vous entrez des dates dans un livre, les dates sont formatées pour afficher deux chiffres pour l'année. Lorsque vous modifiez le format de date par défaut en un format différent à l'aide de cette procédure, l'affichage des dates précédemment saisies dans le classeur sera remplacé par le nouveau format jusqu'à ce qu'il soit formaté dans la boîte de dialogue. " Format de cellule"" (sur le domicile en groupe numéro appuie sur le bouton appel de la boite de dialogue).
Windows 10
Dans la zone de recherche de la barre des tâches, tapez Panneau de commande puis sélectionnez Panneau de commande.
Au chapitre horloge, langue et région Cliquez sur modifier les formats de date, d'heure et de nombre .
Cliquez sur l'icône Langue et normes régionales.
Dans la boîte de dialogue Région appuie sur le bouton Options supplémentaires.
Ouvrir l'onglet Date .
Dans une courte liste Format de date
Cliquez sur le bouton d'accord.
Windows 8
Faites glisser votre doigt depuis le bord droit de l'écran, appuyez sur le bouton Chercher(ou, si vous utilisez une souris, passez votre souris sur le coin supérieur droit de l'écran, déplacez-la vers le bas et cliquez sur Chercher), entrez dans le champ de recherche élément du panneau de commande puis appuyez sur ou Sélectionner Panneau de commande.
Au chapitre horloge, langue et région Cliquez sur modifier les formats de date, d'heure ou de nombre.
Dans la boîte de dialogue Région appuie sur le bouton Options supplémentaires.
Ouvrir l'onglet Date .
Dans une courte liste Format de date sélectionnez un format qui utilise quatre chiffres pour l'année (aaaa).
Cliquez sur le bouton d'accord.
Windows 7
Cliquez sur le bouton Démarrer et sélectionnez l'article Panneau de commande.
Sélectionnez l'élément normes linguistiques et régionales.
Dans la boîte de dialogue Région appuie sur le bouton Options supplémentaires.
Ouvrir l'onglet Date .
Dans une courte liste Format de date sélectionnez un format qui utilise quatre chiffres pour l'année (aaaa).
Cliquez sur le bouton d'accord.
Changer le système de date dans Excel
Le système de date change automatiquement lorsque vous ouvrez un document créé sur une autre plateforme. Par exemple, si vous travaillez dans Excel et ouvrez un document qui a été créé dans Excel pour Mac, le " système de date de 1904" est installé automatiquement.
Vous pouvez modifier le système de date en suivant les étapes ci-dessous.
Veuillez sélectionner Déposer > Paramètres > en outre.
Au chapitre lors du calcul de ce livre sélectionnez le livre que vous voulez puis cochez ou décochez la case utiliser le système de date de 1904 .
Problème : j'ai des problèmes avec les dates entre les classeurs utilisant différents systèmes de date
Vous pouvez rencontrer des problèmes lors de la copie et du collage de dates ou de la création de références externes entre des classeurs basés sur deux systèmes de date différents. Les dates peuvent être affichées quatre ans plus tôt ou plus tard que la date prévue. Ces problèmes peuvent se produire si vous utilisez Excel pour Windows, Excel pour Mac ou les deux.
Par exemple, si vous copiez la date du 5 juillet 2007 à partir d'un classeur qui utilise le système de date de 1900, puis que vous la collez dans un classeur qui utilise le système de date de 1904, la date apparaît comme le 5 juillet 2011, soit 1 462 jours plus tard. . En outre, si vous copiez la date du 5 juillet 2007 à partir d'un classeur qui utilise le système de date de 1904, puis que vous la collez dans un classeur qui utilise le système de date de 1900, la date sera le 4 juillet 2003, soit 1 462 jours plus tôt. ... Pour des informations générales, voir Systèmes de date dans Excel.
Corriger l'erreur de copier-coller
Pour régler la date sur quatre ans et un jour plus tard, ajoutez 1 462 à la date.
Feuille 1 ! $ A $ 1 + 1462
Pour définir la date à quatre ans et plus tôt, soustrayez-en 1 462. Exemple
Feuille 1 ! $ A $ 1-1462
Information additionnelle
Vous pouvez toujours poser une question à la communauté technique Excel, demander de l'aide à la communauté Answers ou suggérer une nouvelle fonctionnalité ou une amélioration sur le site Web Excel User Voice.
Le moyen le plus simple et le plus rapide d'entrer la date ou l'heure actuelle dans une cellule est d'appuyer sur la combinaison de touches CTRL + ";" (date actuelle) et CTRL + MAJ + ";" (heure actuelle).
Il est beaucoup plus efficace d'utiliser la fonction AUJOURD'HUI (). Après tout, non seulement il définit, mais met également à jour automatiquement la valeur de la cellule tous les jours sans intervention de l'utilisateur.
Comment mettre la date du jour dans Excel
Pour insérer la date actuelle dans Excel, utilisez la fonction AUJOURD'HUI (). Pour ce faire, sélectionnez l'outil "Formules" - "Date et heure" - "AUJOURD'HUI". Cette fonction n'a pas d'arguments, vous pouvez donc simplement taper dans la cellule : "= AUJOURD'HUI ()" et appuyer sur Entrée.
Date actuelle dans la cellule :
S'il est nécessaire que la cellule mette à jour automatiquement la valeur non seulement de la date actuelle, mais également de l'heure, il est alors préférable d'utiliser la fonction "= TDATA ()".
La date et l'heure actuelles dans la cellule.
Comment définir la date actuelle dans Excel sur les en-têtes et les pieds de page
L'insertion de la date du jour dans Excel s'effectue de plusieurs manières :
- En définissant les paramètres des en-têtes et pieds de page. L'avantage de cette méthode est que la date et l'heure actuelles sont estampillées sur toutes les pages à la fois.
- Utilisation de la fonction AUJOURD'HUI ().
- Utilisation de la combinaison de touches CTRL + ; - pour régler la date du jour et CTRL + SHIFT + ; - pour régler l'heure actuelle. Inconvénient - dans cette méthode, la valeur de la cellule ne sera pas automatiquement mise à jour avec les indicateurs actuels lors de l'ouverture du document. Mais dans certains cas, l'inconvénient des données est un avantage.
- Utilisation de macros VBA à l'aide des fonctions suivantes dans le code du programme : Date ( ); Heure ( ); Maintenant ().
Les en-têtes et pieds de page vous permettent de définir la date et l'heure actuelles en haut ou en bas des pages du document qui seront sorties vers l'imprimante. De plus, l'en-tête et le pied de page permettent de numéroter toutes les pages du document.
Pour créer la date actuelle dans Excel et la pagination à l'aide d'en-têtes et de pieds de page, procédez comme suit :
Les en-têtes et les pieds de page nous permettent de faire plus que simplement définir des dates et une pagination. Vous pouvez également ajouter un emplacement pour la signature de la personne responsable du rapport. Par exemple, modifions maintenant le coin inférieur gauche de la page dans la zone d'en-tête et de pied de page.
Chaque transaction est effectuée à un moment ou à une période donnée, puis liée à une date précise. Dans Excel, les dates sont des nombres entiers convertis. C'est-à-dire que chaque date a son propre entier, par exemple, 01/01/1900 est le numéro 1, et 02/01/1900 est le numéro 2, etc. La définition des années, des mois et des jours n'est rien de plus que le type de formatage correspondant pour les valeurs numériques successives. Pour cette raison, même les opérations de date les plus simples effectuées dans Excel (par exemple, le tri) sont très problématiques.
Trier dans Excel par date et mois
À l'aide de l'exemple du tableau présenté dans la figure ci-dessous, nous montrerons de quelle manière le préparer pour un tri confortable des dattes. Notre liste de transactions de date couvre la plage de cellules B1 : C15.
Tout d'abord, nous allons créer une colonne dans laquelle nous numéroterons toutes les transactions. Considérez cette action comme une bonne forme pour toute sorte de tri. En effet, dans de tels cas, si les données sont mal remaniées, nous avons toujours la possibilité de revenir à la vue d'origine de la table. Pour ça:
En conséquence, la colonne sera automatiquement remplie avec une séquence de numéros de transaction de 1 à 14.
Conseil utile! Dans Excel, la plupart des tâches ont plusieurs solutions. Pour normaliser automatiquement les colonnes dans Excel, vous pouvez utiliser le bouton droit de la souris. Pour ce faire, placez simplement le curseur sur le marqueur du curseur du clavier (dans la cellule A2) et maintenez uniquement le bouton droit de la souris enfoncé et faites glisser le marqueur le long de la colonne. Après avoir relâché le bouton droit de la souris, un menu contextuel apparaîtra automatiquement à partir duquel vous devez sélectionner l'option "Remplir". Et la colonne sera automatiquement remplie avec une séquence de nombres, similaire à la première méthode de saisie semi-automatique.
Il est maintenant temps de créer des colonnes auxiliaires qui aideront considérablement à trier la liste des transactions de manière de haute qualité. Il y a quelques étapes simples à suivre :
- Remplissez les cellules D1, E1, F1 avec les noms des rubriques : "Année", "Mois", "Jour".
- Pour chaque colonne, saisissez les fonctions appropriées sous les en-têtes et copiez-les le long de chaque colonne :
- D1 : = ANNÉE (B2) ;
- E1 : = MOIS (B2) ;
- F1 : = JOUR (B2).
En conséquence, nous devrions obtenir le résultat suivant :
Notre table est prête et offre la possibilité d'effectuer un tri multilatéral des transactions par date. Tout d'abord, faisons un tri test pour voir comment les choses fonctionnent.
Disons que nous voulons trier les dates de transaction par mois. Dans ce cas, l'ordre des jours et des années n'a pas d'importance. Pour ce faire, il suffit d'aller dans n'importe quelle cellule de la colonne Mois (E) et de sélectionner l'outil : DONNÉES - Trier & Filtrer - Trier par ordre croissant.
Maintenant, afin de réinitialiser le tri et de ramener les données du tableau à leur forme d'origine, accédez à n'importe quelle cellule de la colonne "Non" (A) et vous sélectionnerez à nouveau le même outil "Trier par ordre croissant".
Comment trier les dates selon plusieurs conditions dans Excel
Et maintenant, vous pouvez commencer un tri complexe de dates selon plusieurs conditions. La tâche est la suivante - les transactions doivent être triées dans l'ordre suivant :
- Années ascendantes.
- Mois dans la période de certaines années - décroissant.
- Jours dans des périodes de mois spécifiques - décroissant.
La manière de mettre en œuvre la tâche :
De ce fait, nous avons effectué un tri complexe de dates selon plusieurs conditions :
Pour trier les valeurs du tableau au format Date, Excel fournit des options de liste déroulante telles que Old to New et New to Old. Mais en pratique, lorsque l'on travaille avec de grandes quantités de données, le résultat n'est pas toujours à la hauteur des attentes. Étant donné que pour Excel, les dates sont des nombres entiers, il est plus sûr et plus efficace de les trier en utilisant la méthode décrite dans cet article.
Les feuilles de calcul Excel permettent de travailler avec différents types de texte et d'informations numériques. Le traitement des dates est également disponible. Dans ce cas, il peut être nécessaire d'isoler un nombre spécifique de la valeur totale, par exemple une année. Il existe des fonctions distinctes pour cela : ANNÉE, MOIS, JOUR et JOUR DE LA SEMAINE.
Exemples d'utilisation de fonctions pour le traitement des dates dans Excel
Les tableaux Excel stockent des dates qui sont présentées sous la forme d'une séquence de valeurs numériques. Il commence le 1er janvier 1900. Cette date correspondra au nombre 1. Parallèlement, le 1er janvier 2009 est inscrit dans les tableaux sous le nombre 39813. Il s'agit du nombre de jours entre deux dates désignées.
La fonction YEAR est utilisée de la même manière que les fonctions adjacentes :
- MOIS;
- JOURNÉE;
Ils affichent tous des valeurs numériques correspondant au calendrier grégorien. Même si le calendrier Hijri a été sélectionné dans la feuille de calcul Excel pour afficher la date saisie, lors de l'extraction de l'année et d'autres valeurs composites à l'aide de fonctions, l'application présentera un nombre équivalent dans le système de chronologie grégorien.
Pour utiliser la fonction ANNÉE, entrez la formule de fonction suivante avec un argument dans une cellule :
ANNÉE (adresse de cellule numérique avec date)
L'argument de fonction est obligatoire. Il peut être remplacé par "date_num_format". Dans les exemples ci-dessous, vous pouvez clairement le voir. Il est important de se rappeler que lors de l'affichage de la date sous forme de texte (orientation automatique le long du bord gauche de la cellule), la fonction ANNÉE ne sera pas exécutée. Cela se traduira par l'affichage #VALUE. Par conséquent, les dates formatées doivent être numériques. Les jours, les mois et les années peuvent être séparés par des points, des barres obliques ou des virgules.
Considérons un exemple de travail avec la fonction ANNÉE dans Excel. Si nous devons obtenir l'année à partir de la date d'origine, la fonction DROITE ne nous aidera pas, car elle ne fonctionne pas avec les dates, mais uniquement avec du texte et des valeurs numériques. Pour séparer l'année, le mois ou le jour de la date complète, Excel fournit des fonctions pour travailler avec les dates.
Exemple : Il y a un tableau avec une liste de dates et dans chacun d'eux il faut séparer la valeur de l'année seulement.
Entrons les données initiales dans Excel.
Pour résoudre le problème, vous devez entrer la formule dans les cellules de la colonne B :
ANNÉE (l'adresse de la cellule, à partir de la date dont vous voulez extraire la valeur de l'année)
Par conséquent, nous extrayons les années de chaque date.
Un exemple similaire de la fonction MOIS dans Excel :
Un exemple de travail avec les fonctions JOUR et JOUR SEMAINE. La fonction DAY calcule le nombre de n'importe quel jour à partir d'une date :
La fonction WEEKDAY renvoie le numéro du jour de la semaine (1er lundi, 2ème mardi...etc.) pour n'importe quelle date :
Dans le deuxième argument optionnel de la fonction WEEKDAY, vous devez spécifier le chiffre 2 pour notre format du jour de la semaine (du lundi-1 au dimanche-7):
Si vous omettez le deuxième argument facultatif, le format par défaut (Anglais dimanche-1 à samedi-7) sera utilisé.
Créons une formule à partir des combinaisons des fonctions INDEX et WEEKDAY :
Voyons plus clairement la mise en œuvre de cette fonction.
Exemples d'application pratique de fonctions pour travailler avec des dates
Ces fonctions primitives sont très utiles pour regrouper les données par année, mois, jour de la semaine et jour spécifique.
Disons que nous avons un rapport de vente simple :
Nous devons organiser rapidement les données pour une analyse visuelle sans utiliser de tableaux croisés dynamiques. Pour ce faire, nous présenterons le rapport dans un tableau où vous pourrez facilement et rapidement regrouper les données par années, mois et jours de la semaine :
Nous avons maintenant un outil pour travailler avec ce rapport de ventes. Nous pouvons filtrer et segmenter les données selon des critères temporels précis :
De plus, vous pouvez dresser un histogramme pour analyser les jours les plus vendus de la semaine, comprendre quel jour de la semaine a le plus de ventes :
Dans cette vue, il est très pratique de segmenter les rapports de vente sur des périodes longues, moyennes et courtes.
Il convient de noter tout de suite que pour obtenir la différence entre deux dates, aucune des fonctions ci-dessus ne nous aidera. Pour cette tâche, vous devez utiliser la fonction RAZDAT spécialement conçue :
Le type de valeurs dans les cellules "date" nécessite une approche particulière lors du traitement des données. Par conséquent, vous devez utiliser les fonctions appropriées pour ce type dans Excel.
Créons une séquence de dates et d'heures de différentes sortes :01.01.09, 01.02.09, 01.03.09, ..., Jan, Avr, Juil, ..., Lun, Mar, Mer, ..., Q1, Q2, ..., 09:00, 10: 00, 11h00, ... etc.
Parce que chaque valeur de date correspond à un certain nombre (voir article), alors les approches pour former des séquences décrites dans l'article sont applicables pour les dates. Cependant, il y a aussi quelques particularités.
Séquence 01.01.09, 01.02.09, 01.03.09 (premiers jours des mois) peut être formé par la formule = DATE CEE (B2, CHAINE (A1)), en cellule B2 il doit y avoir une date - le premier élément de la séquence ( 01.01.09 ).
La même séquence peut être formée en utilisant le bouton droit de la souris. Laisser entrer dans la cellule A2 valeur saisie 01.01.09 ... Sélectionnez la cellule A2 ... Tout en maintenant le bouton droit de la souris enfoncé, copiez la valeur de A2 dans les cellules ci-dessous. Après avoir relâché le bouton droit de la souris, un menu contextuel apparaîtra, dans lequel vous devez sélectionner l'élément Remplir par mois.
En changeant le format des cellules contenant la séquence 01.01.09, 01.02.09, 01.03.09, au MMM(voir article) on obtient la séquence janvier, février, mars, ...
La même séquence peut être saisie à l'aide de la liste de saisie semi-automatique Bouton Office / Options Excel / De base / Paramètres de base pour travailler avec Excel / Modifier les listes(Entrer Jan, alors Marqueur de remplissage recopie).
Les cellules ne contiendront pas de dates, comme dans le cas précédent, mais des valeurs textuelles.
De même, vous pouvez former une séquence de jours de la semaine. lun, mar, mer, ...
Séquence de trimestres 1 m², 2 m², ... peut être formé en utilisant les idées de l'article.
À l'aide de l'outil, seules des séquences de jours de la semaine peuvent être créées. Et aussi avec un pas d'un mois et d'un an.
Séquence des premiers mois des trimestres janv, avr, juil, oct, jan, ... peut être créé en saisissant dans deux cellules les deux premiers éléments de la séquence ( janv., avr.), puis (après les avoir sélectionnés) recopiez marqueur de remplissage... Les cellules contiendront des valeurs de texte. Pour que les cellules contiennent des dates, utilisez la formule = DATEEC ($ G $ 16, (LIGNE (A2) -LIGNE ($ A $ 1)) * 3) La séquence est supposée commencer à la cellule G16 , la formule doit être saisie dans la cellule G17 (voir fichier exemple).
Séquence temporelle 09:00, 10:00, 11:00, ... peut être formé en utilisant. Laisser entrer dans la cellule A2 valeur saisie 09 :00 ... Sélectionnez la cellule A2 ... copions Marqueur de remplissage, valeur de A2 dans les cellules ci-dessous. La séquence sera formée.
Si vous souhaitez générer une séquence temporelle par incréments de 15 minutes ( 09:00, 09:15, 09:30, ... ), alors vous pouvez utiliser la formule = B15 + 1/24/60 * 15 (On suppose que la séquence commence par la cellule B15 , la formule doit être saisie dans B16 ). La formule renverra le résultat au format date.
Une autre formule = TEXTE (B15 + 1/24/60 * 15; "hh: mm") renverra le résultat au format texte.
