Quelles sont les numéros de série Date et Heure dans Microsoft Excel, et pourquoi existent-ils?

Sommaire

Toggle

Résumé

• Les dates et les heures sont stockées sous forme de numéros de série dans Microsoft Excel, permettant des calculs et des comparaisons rapides.

• Les numéros de série pour les dates commencent le 1er janvier 1900, tandis que les temps sont des représentations décimalisées d'une période de 24 heures.

• La compréhension des numéros de série Date and Time d'Excel peut aider à calculer les différences entre les dates, l'ajout de jours, les temps d'ajustement, etc.

Dans Microsoft Excel, si vous sélectionnez des cellules contenant une date ou une heure et appliquer le format de numéro général, cela génère ce qui semble être des nombres aléatoires. En fait, ces nombres sont tout sauf aléatoires – ce sont des numéros de série séquentiels qui servent un objectif spécifique.

Les dates et les heures sont stockées en tant que numéros de série dans Excel

Dans Microsoft Excel, vous pouvez formater une cellule comme une date ou une heure. Dans cet exemple, la cellule F1 est formatée comme une date.

Excel reconnaît les dates tapées de diverses manières. Par exemple, vous pouvez saisir des dates avec des traits de tirets (tels que 2024-12-16 ou 16-Dec-2024), des barres obligées (comme 2024/12/16 ou 24/12/16), ou des espaces et des virgules (comme le 16 décembre 2024). Cependant, si vous utilisez une période pour séparer l'année, le mois et le jour, Excel le reconnaît comme du texte.

D'un autre côté, la valeur dans la cellule F2 est formatée en tant que temps.

Vous pouvez saisir des temps dans Excel en heures et minutes séparés par un côlon (comme 10h00), et vous pouvez également inclure les secondes (comme 10h00) si vous le souhaitez. Pour inclure AM ou PM, ajoutez simplement un espace et type SUIS ou PM Après avoir tapé le temps. Pour les heures uniquement, vous pouvez entrer dans l'heure suivie par AM ou PM (comme 10 h).

Le formatage par défaut des dates, des temps, des devises et d'autres éléments dans Excel dépend des paramètres de votre système. Vous pouvez modifier ces paramètres dans Windows 11 en modifiant votre région.

Cependant, sous la surface, les cellules avec des dates et des temps contiennent des numéros de série séquentiels cachés. Vous pouvez les voir en sélectionnant les cellules formatées sous forme de dates et heures, et en cliquant sur « Général » dans le groupe numérique de l'onglet Home sur le ruban.

Remarquez comment les numéros de série pour les dates sont des nombres entiers et les numéros de série pour les temps sont des décimales. En effet, le temps est considéré comme une partie d'une journée, et vous pouvez avoir une date et un temps ensemble dans la même cellule. Par exemple, si vous tapez 2024-12-16 10:00:00 Dans une cellule, le numéro de série représente à la fois l'entier de date et la décimale de temps.

Comment les numéros de série Date et Heure sont générés dans Excel

Les numéros de série pour les dates dans toutes les versions modernes de Microsoft Excel commencent à 1 pour le 1er janvier 1900, augmentant avec chaque jour suivant.

Par exemple, le numéro de série pour le 31 janvier 1900 est de 31 ans, car c'est 31 jours après le point de départ. Cependant, ce système comprend un numéro de série pour le 29 février 1900, même si 1900 n'était pas une année de saut. Ainsi, chaque numéro de série après cette date est à ce que vous vous attendez, mais cela n'est important que si vous avez affaire à des dates au premier trimestre de l'année 1900.

De plus, Excel ne reconnaît pas les dates avant le 1er janvier 1900. En conséquence, les dates antérieures n'ont pas de numéros de série, vous ne pouvez donc pas les utiliser dans les calculs.

Les numéros de série date dans les premières versions d'Excel pour Mac commencent le 1er janvier 1904. Pour éviter les problèmes de compatibilité si vous copiez les dates entre les versions Excel, vous pouvez modifier le système de date dans les options de calcul d'Excel en cliquant sur Fichier> Options> Avancé sur un PC Windows, ou menu Excel> Préférences> Formules et listes sur un Mac.

Pour les temps, 12 h a un numéro de série de 0, et cela augmente de 1/1440 à chaque minute suivante, car il y a 1440 minutes par jour. Ainsi, par exemple, le numéro de série pour 1 h du matin est de 0,041666666666667, ce qui équivaut à 60/1440.

Comment utiliser les numéros de série date et heure dans les calculs Excel

Maintenant que vous savez comment les numéros de série fonctionnent pour les dates et les heures d'Excel, vous êtes prêt à tirer le meilleur parti de ces connaissances dans les calculs.

Exemple 1: trouver la différence entre deux dates

Dans cet exemple, disons que vous souhaitez déterminer le nombre de jours entre le 1er janvier et le 31 mars à 2025.

Étant donné que les numéros de série date augmentent tous les jours, dans la cellule D2, vous pouvez simplement soustraire la date de fin de la date de début, mais assurez-vous que la cellule contenant le résultat est formatée en nombre, pas une date:

=C2-B2

Lors du calcul de la différence entre deux jours, le résultat n'inclut pas la date de début.

Exemple 2: Ajout de jours à une date

Ici, votre objectif est d'ajouter 60 jours au 1er janvier 2025.

Pour ce faire, dans la cellule C2, ajoutez la cellule contenant la différence en jours à la date de début, en s'assurant que la cellule est formatée comme une date.

=B2+D2

Exemple 3: prendre des jours de congé

En plus d'ajouter des jours à une date, vous pouvez également utiliser un principe similaire pour soustraire les jours à partir d'une date. Par exemple, vous voudrez peut-être déterminer la date de 35 jours avant le 1er juillet 2025.

Dans la cellule B2, type:

=C2-D2

et formater la cellule comme une date.

Exemple 4: Trouver la différence entre deux fois le même jour

La première chose à noter lors de la recherche de la différence entre deux fois est que les temps doivent être dans le même jour. En effet, les numéros de série du temps se déroulent du début de la journée à la fin de la journée, avant de redémarrer à nouveau le lendemain.

Par exemple, si vous essayez de trouver la différence entre 19 h un jour et 7 h le lendemain, le numéro de série pour 19 h est supérieur au numéro de série pour 7 heures du matin, et Excel ne peut pas afficher des temps négatifs. En d'autres termes, le premier argument de la somme doit être un moment ultérieur que le deuxième argument.

Pour résoudre ce problème, vous devrez convertir toutes les données en formats à date de temps. Voir l'exemple 7 pour plus de détails.

Au lieu de cela, supposons que vous souhaitiez résoudre le décalage horaire entre 7:12:45 et 10:14:57 le même jour.

Pour ce faire, dans la cellule D2, type:

=C2-B2

Parce que la cellule D2 est formatée en tant que temps, elle a un numéro de série. Dans l'exemple ci-dessus, cela signifie qu'il a adopté le « AM » des cellules B2 et C2.

Ainsi, pour supprimer ces lettres, sélectionnez la cellule D2 et appuyez sur Ctrl + 1 pour lancer la boîte de dialogue des cellules du format. Ensuite, dans l'onglet Numéro, cliquez sur « temps » et choisissez un format de temps qui affiche uniquement les heures, les minutes et les secondes. Lorsque vous avez terminé, cliquez sur « OK ».

Maintenant, vous avez une représentation plus claire de la différence de temps entre les cellules B2 et C2, et le numéro de série correspondant confirme que le calcul est correct.

Exemple 5: Ajout d'heures et de minutes à une heure

Dans cet exemple, vous souhaitez découvrir l'heure après avoir ajouté 12,5 heures à 19 h.

Vous remarquerez que le décalage horaire dans la cellule D2 est formaté comme un temps. Cependant, vous savez que cela fonctionnera comme prévu, car le numéro de série représente un peu plus d'une demi-journée, ce que vous cherchez à ajouter à l'heure de début.

Cela dit, si vous voulez ranger cela, vous pouvez reformater la cellule D2 dans la boîte de dialogue des cellules du format, afin que seules les heures, les minutes et les secondes soient affichés. Voir l'exemple 4 pour plus de détails sur la façon de procéder.

Maintenant, dans la cellule C2, type:

=B2+D2

Le numéro de série pour l'heure de fin est supérieur à 1, car l'ajout de 12,5 heures à 19 h vous mène le lendemain. Cependant, parce que la cellule C2 est formatée pour afficher uniquement l'heure, seule la partie après le point décimal est retournée.

Exemple 6: prendre des heures et des minutes de congé un temps

Comme pour l'exemple 4, il est important de noter que la soustraction des heures et des minutes d'un temps ne fonctionnera que si le résultat atterrit le même jour que l'heure du calcul. Sinon, vous verrez une série de symboles de hachage, car Excel ne peut pas afficher un temps négatif. Dans ce cas, vous devrez saisir des valeurs de date à la date au lieu des valeurs de temps, comme dans l'exemple 7.

Imaginons que vous vouliez déterminer le temps où vous soustrayez cinq heures, 30 minutes et 20 secondes à partir de 9 h.

Pour ce faire, dans la cellule B2, type:

=C2-D2

Exemple 7: effectuer des calculs avec des valeurs de date

Dans un dernier exemple, disons que votre objectif est de régler le décalage horaire entre 7 h le 12 décembre 2023 et 9 h le 31 janvier 2024.

Tout d'abord, appliquez le format général ou numéro à la cellule D2. Ensuite, dans la même cellule, type:

=C2-B2

Excel peut gérer ce calcul, car le numéro de série pour la valeur dans la cellule C2 est supérieur au numéro de série pour la valeur dans la cellule B2.

Maintenant, pour rendre ces informations plus claires, vous pouvez diviser le résultat en jours et heures. Tout d'abord, dans la cellule E2, type:

=INT(D2)

pour arronser le nombre décimalisé jusqu'au numéro entier le plus proche. Cela vous donne le nombre de jours entiers.

Enfin, dans la cellule F2, type:

=SUM(MOD(D2,1)*24)

pour extraire la décimale et le transformer en une figure horaire.

---

En plus d'utiliser les numéros de série Date and Time pour effectuer ces types de calculs, Microsoft Excel propose également diverses fonctions de temps et de date, y compris Dadif et Networkdays – qui peuvent vous aider à obtenir les dates et les heures dont vous avez besoin.