Excel et les dates (Partie 2)

La suite de l'article Excel et les dates (Partie 1)

Parlons un peu du temps, non pas de la météo mais les heures / minutes / secondes !

Comment sont gérés les heures

Tout comme les dates qui sont des nombres de jours (numéro de série), les heures sont aussi des nombres (numéro de série aussi). Ce sont des 24^èmes de jour.

Pour Excel Dates et Heures, c'est la même chose, des numéros de série ! Ce qui change c'est la manière d'afficher l'information.

Ainsi :

• 1 heure soit 1/24 de jour ( = 0,04167), c'est le 00/01/1900 à 1h 00
• 12 heures soit 0,5 jour ( = 0,5), c'est le 00/01/1900 à 12h 00
• 24 heures soit 1 jour ( = 1), c'est le 01/01/1900 à 0h 00
• 48 heures soit 2 jours ( = 2), c'est le 02/01/1900 à 0h 00

Le 09/01/2025 à 18h correspond au nombre 45 666,75 => Entre le 0/1/1900 et le 09/01/2025 il y a eu 45 666 jours + les 18h soit 0,75 (3/4 de jour).

Saisir des heures / minutes / secondes

A la validation, Excel va convertir la saisie en numéros de série puis appliquer un format d'affichage.

On utilise le caractère " : " pour séparer les heures, minutes, seconde.

• "6:" => 6 h.
• "5:3" => 5 h 3 min.
• "4:5:7" => 4 h 5 min 7 sec.
• "8:0 pm" => 20 h.
• "6:70" => Ne sachant pas comment afficher cette saisie, Excel va afficher le numéros de série correspondant à 7 h 10 (70 minutes convertis en 1 h et 10 min).
• "2:30:20,4238 => A la validation on aura un arrondissement réel à 2:30:20,424.

Si la limite des millièmes de seconde est trop juste, je vous propose une solution : Dépasser la limitation du millième de seconde avec Excel !).

Information : Pour saisir l'heure actuelle (heure système), il y a le raccourcis ctrl + :

Formatage des cellules en forme de dates/heures

On utilise les codes "h", "m" et "h" (le même m que pour les mois !).

Conversion des heures

Voir l'article Trucs et astuces pour convertir les types de valeurs

2:30 en heures  > 2:30 * 24      =   2,5
2:30 en minutes > 2:30 * 24 * 60 = 150

Fonctions liées aux heures

Obtenir l'heure

= MAINTENANT() => La date du jour et lʹheure

Cette fonction est "volatile", elle se mets à jour à chaque saisie dans Excel et par effet dominos toutes les cellules qui en dépendes. Elle est donc gourmande en temps de calculs.

Décomposer une date

L'intérêts de ces fonctions est de faire un test sur une partie de l'heure (dans des fonctions SI, RECHERCHEX, FILTRE... par exemple) ou d'en modifier une partie (fonction TEMPS).

B2 : 01/01/1900   06:30:09,124 ( soit 1,270938935)

= HEURE(B2)   =>  6
= MINUTE(B2)  => 30
= SECONDE(B2) =>  9

Récupérer les secondes avec les centièmes

=LET(c ; B2 ; (MOD(c;1)*24 - HEURE(c))*3600 - MINUTE(c)*60) => 9,124

• On retire les jours avec MOD : Ne garder que la partie décimale soit la partie heure : 0,270938935.
• On retire les heures converties en secondes :
  • * 24 pour passer les heures en centièmes.
  • * 3 600 pour passer en secondes.
• On retire les minutes converties en secondes :
  • -MINUTE(c)*60

Autre solution :

=LET(c ; B2 ; MOD(c;1) -TEXTE(c;"h:m"))*24*3600

• On retire les jours avec MOD : Ne garder que la partie décimale soit la partie heure : 0,270938935.
• On retire les heures et minutes en les récupérant avec TEXTE
  • Le - devant TEXTE convertit son résultat "6:30" en numérique.
• *24 * 3 600 pour convertir le numéro de série.

Composer une heure

Exemple : Décaler une heure de 3 heures et 30 secondes.

B2 : 2:00:40

= TEMPS( HEURE(B2)+3 ; MINUTE(B2) +3 ; SECONDE(B2) +30 ) => 5:01:10

ou

=B2+3/24+30/24/3600

Autre fonction disponible

La fonction TEMPSVAL permet de convertir du texte en heure en supprimant la partie date. Tout comme la fonction DATEVAL je n'ai jamais trouvé un intérêt à cette fonction.

Problèmes classiques avec les heures

La somme des heures est fausse

Si la somme dépasse 24h (soit un jour), le heures représentant des jours (multiple de 24) sont retirés de l'affichage.
Ainsi 51 heures sera affiché 3 heures ( 2 *24 + 3 ).

Il suffit de modifier le format d'affichage.

Gestion de heures négatives

6h dans Excel cela correspond au 0/01/1900 à 6h soit 0,25 (numéros de série). Si l'on retire 12h on va obtenir dans la cellule : (- 0.25) représentant -6 heures.

Or un numéro de série (date/heure) ne peut être négatif donc si l'on applique un format d'affichage Date/Heure on obtiendra ######## à l'infini (toute la largeur de la cellule) !

Cet affichage ne gêne pas les calculs car derrière les ######, il y a bien les -6 heures (-0.25) donc si on ajoute 10h, les ###### disparaitrons et retrouvera 4 heures. Ouf !

Comment pallier ce problème d'affichage ?

Solution 1 : (classique)

Ne pas avoir d'heures négatives. Facile 😁 !

On va devoir utiliser 2 cellules :

• Cellule "Heures positives" : Affiche le calcul si le résultat est positif sinon on affiche rien.
• Cellule "Heures négative" : Affiche le calcul en inversant le signe (* -1) si le résultat est négatif sinon on affiche rien.

Voir exemples Solde version 1 et Solde version 2 (voir image plus bas).

Solution 2 :

Ne pas avoir d'heures. Facile 😁 !

On va générer un résultat en texte (alphanumérique) et non un numéro de série.
Ce résultat sera pour l'affichage mais sin on doit l'utiliser, le plus simple c'est de repartir des valeurs de départs utiliser pour son calcul ou sinon il faut supprimer le signe "-" avec = STXT(la cellule ; 2 ; 5) par exemple.

Exemples

Solution 3 : (à vos risques)

Dans les options d'Excel, on peut activer l'option Calendrier depuis 1904.

Cette option est spécifique au classeur.

À partir de ce moment-là, Excel accepte les heures négatives.

• 0 correspond au 01/01/1904.
• Les valeurs négatives correspondent aux mêmes dates que les valeurs positives mais avec un signe moins devant.
  • 1 => 02/01/1904
  • -1 => - 02/01/1904
• Si des dates étaient déjà saisies elles seront décalées de 4 ans et 1 jours ! (Activation à faire avant toutes saisies de date)
• Limite au 31/12/9999.

Merci pour votre attention bienveillante.