Comprendre le Fonctionnement des Dates dans Excel
Les Dates : Des Nombres Déguisés
Excel stocke les dates sous forme de nombres. Plus précisément, il s'agit du nombre de jours écoulés depuis le 1er janvier 1900. Par exemple, le 1er janvier 1900 est représenté par le nombre 1, le 2 janvier 1900 par le nombre 2, et ainsi de suite. Cette représentation numérique permet à Excel d'effectuer des calculs sur les dates, comme calculer la différence entre deux dates ou ajouter un nombre de jours à une date.
Formats de Date
Bien qu'Excel stocke les dates en tant que nombres, il les affiche généralement dans un format plus lisible, tel que "jj/mm/aaaa" ou "mmmm aaaa". Le format d'affichage peut être modifié via l'onglet "Accueil" dans le groupe "Nombre", ou en faisant un clic droit sur la cellule et en sélectionnant "Format de cellule".
Erreurs Courantes et Comment les Éviter
Une erreur fréquente est d'entrer une date dans un format que Excel ne reconnaît pas. Par exemple, si vous tapez "1er janvier 2023" et que Excel ne le reconnaît pas comme une date, il le traitera comme du texte. Pour éviter cela, assurez-vous d'utiliser un séparateur de date valide (généralement "/" ou "-") et de respecter l'ordre jour/mois/année.
Formater les Dates dans Excel
Changer le Format d'Affichage
Pour modifier le format d'affichage d'une date, sélectionnez la cellule ou la plage de cellules contenant les dates. Ensuite, dans l'onglet "Accueil", allez dans le groupe "Nombre" et choisissez un format de date dans la liste déroulante. Vous pouvez également cliquer sur la petite flèche en bas à droite du groupe "Nombre" pour ouvrir la boîte de dialogue "Format de cellule".
Formats de Date Personnalisés
Si les formats de date prédéfinis ne vous conviennent pas, vous pouvez créer un format personnalisé. Dans la boîte de dialogue "Format de cellule", sélectionnez l'onglet "Nombre" et choisissez la catégorie "Personnalisée". Vous pouvez ensuite entrer un code de format personnalisé dans la zone "Type". Voici quelques exemples :
jj/mm/aaaa: Affiche la date au format jour/mois/année avec des chiffres.dd/mm/yyyy: Similaire au précédent, mais avec l'année sur 4 chiffres.jj-mmm-aaaa: Affiche la date au format jour-mois (abrégé)-année.jj mmmm aaaa: Affiche la date au format jour mois (complet) année.jjjj jj mmmm aaaa: Affiche la date au format jour de la semaine (complet) jour mois (complet) année.
Exemple Pratique
Imaginez que vous ayez une colonne de dates au format "aaaa-mm-jj". Pour les afficher au format "jj/mm/aaaa", sélectionnez la colonne, ouvrez la boîte de dialogue "Format de cellule", choisissez la catégorie "Personnalisée" et entrez le code jj/mm/aaaa dans la zone "Type".
Formules Excel pour Travailler avec les Dates
DATE : Créer une Date à Partir d'Année, Mois et Jour
La fonction DATE permet de créer une date à partir de trois arguments : l'année, le mois et le jour. Sa syntaxe est DATE(année;mois;jour). Par exemple, DATE(2023;10;26) renvoie la date du 26 octobre 2023.
AUJOURDHUI et MAINTENANT : Obtenir la Date et l'Heure Actuelles
La fonction AUJOURDHUI() renvoie la date actuelle. La fonction MAINTENANT() renvoie la date et l'heure actuelles. Ces fonctions sont mises à jour automatiquement chaque fois que la feuille de calcul est recalculée.
JOUR, MOIS et ANNEE : Extraire des Composantes d'une Date
Les fonctions JOUR, MOIS et ANNEE permettent d'extraire respectivement le jour, le mois et l'année d'une date. Par exemple, JOUR(A1) renvoie le jour de la date contenue dans la cellule A1.
JOURSEM : Déterminer le Jour de la Semaine
La fonction JOURSEM renvoie un nombre représentant le jour de la semaine d'une date. Par défaut, le dimanche est représenté par le nombre 1 et le samedi par le nombre 7. Vous pouvez modifier ce comportement en spécifiant un deuxième argument à la fonction. Par exemple, JOURSEM(A1;2) renvoie un nombre où le lundi est représenté par le nombre 1 et le dimanche par le nombre 7.
FIN.MOIS : Obtenir le Dernier Jour du Mois
La fonction FIN.MOIS renvoie la date du dernier jour du mois, un certain nombre de mois avant ou après une date spécifiée. Sa syntaxe est FIN.MOIS(date_départ;mois). Par exemple, FIN.MOIS("26/10/2023";0) renvoie la date du 31 octobre 2023.
Exemple Pratique : Calculer l'Âge
Pour calculer l'âge d'une personne à partir de sa date de naissance, vous pouvez utiliser la formule suivante :
=ENT( (AUJOURDHUI()-A1) / 365.25 )
Où A1 contient la date de naissance. Cette formule calcule la différence entre la date actuelle et la date de naissance, la divise par le nombre moyen de jours dans une année (365.25 pour tenir compte des années bissextiles), et utilise la fonction ENT pour arrondir le résultat à l'entier inférieur.
Calculs de Dates Avancés
Ajouter ou Soustraire des Jours, des Mois ou des Années
Pour ajouter ou soustraire des jours à une date, il suffit d'ajouter ou de soustraire un nombre à la date. Par exemple, =A1+7 ajoute 7 jours à la date contenue dans la cellule A1.
Pour ajouter ou soustraire des mois ou des années, vous pouvez utiliser la fonction DATE. Par exemple, pour ajouter un mois à une date, vous pouvez utiliser la formule suivante :
=DATE(ANNEE(A1);MOIS(A1)+1;JOUR(A1))
Calculer la Différence entre Deux Dates
Pour calculer la différence entre deux dates en nombre de jours, il suffit de soustraire la date la plus ancienne de la date la plus récente. Par exemple, =B1-A1 calcule la différence en jours entre les dates contenues dans les cellules A1 et B1.
Pour calculer la différence en mois ou en années, vous pouvez utiliser les fonctions MOIS et ANNEE combinées à la fonction DATEDIF.
La fonction DATEDIF est une fonction plus ancienne qui n'est pas documentée dans l'aide d'Excel, mais elle reste très utile. Sa syntaxe est DATEDIF(date_début;date_fin;unité). L'argument unité spécifie l'unité de temps dans laquelle vous souhaitez obtenir la différence : "y" pour les années, "m" pour les mois, "d" pour les jours, "ym" pour les mois restants après avoir soustrait les années complètes, "yd" pour les jours restants après avoir soustrait les années complètes, et "md" pour les jours restants après avoir soustrait les mois complets.
Par exemple, pour calculer la différence en années entre deux dates, vous pouvez utiliser la formule suivante :
=DATEDIF(A1;B1;"y")
Exemple Pratique : Calculer le Nombre de Jours Ouvrables
Pour calculer le nombre de jours ouvrables entre deux dates, vous pouvez utiliser la fonction NB.JOURS.OUVRES. Sa syntaxe est NB.JOURS.OUVRES(date_début;date_fin;[jours_fériés]). L'argument jours_fériés est facultatif et permet de spécifier une plage de cellules contenant les dates des jours fériés à exclure du calcul.
Par exemple, si vous avez les dates de début et de fin dans les cellules A1 et B1, et les dates des jours fériés dans la plage C1:C10, vous pouvez utiliser la formule suivante :
=NB.JOURS.OUVRES(A1;B1;C1:C10)
Bonnes Pratiques et Astuces
- Vérifiez le format de date par défaut de votre système d'exploitation : Excel utilise ce format par défaut. Des incohérences peuvent entraîner des erreurs.
- Utilisez la validation de données pour forcer les utilisateurs à entrer des dates valides : Cela peut éviter les erreurs de saisie.
- Soyez cohérent dans l'utilisation des formats de date : Cela facilite la lecture et l'interprétation des données.
- Utilisez des noms de plages pour rendre vos formules plus lisibles : Au lieu d'utiliser des références de cellules comme A1:A10, vous pouvez définir un nom de plage comme "Dates" et utiliser ce nom dans vos formules.
- Testez vos formules avec des exemples de données : Cela vous permet de vérifier qu'elles fonctionnent correctement.
- Documentez vos formules : Ajoutez des commentaires pour expliquer ce qu'elles font.
- Importer des données avec des dates: Lors de l'import de données depuis des fichiers CSV ou d'autres sources, assurez-vous que les dates sont correctement reconnues. Utilisez l'assistant d'importation de texte d'Excel pour spécifier le format des colonnes de dates.
- Tableaux structurés: Convertissez vos plages de données en tableaux structurés (Ctrl+T). Les tableaux structurés gèrent automatiquement les formules et les formats lorsque vous ajoutez de nouvelles lignes, ce qui est particulièrement utile pour les données de dates qui évoluent dans le temps.
- Power Query: Utilisez Power Query pour importer et transformer des données de dates provenant de diverses sources. Power Query offre des outils puissants pour nettoyer et normaliser les formats de dates avant de les importer dans Excel.
En maîtrisant ces techniques et en adoptant ces bonnes pratiques, vous serez en mesure de gérer efficacement les dates dans Excel et d'exploiter pleinement leur potentiel pour l'analyse de données.