Calculer des heures négatives sous Excel

Calculer des heures sous Excel est un vrai casse-tête… quand le solde des heures est positif, une astuce permet de corriger le format des heures. Quand le solde est négatif, la solution se cache dans les options d’Excel… Prêt/en à la découvrir ? GO !


Activ’Formation | Excel toute version | format heure, format personnalisé, formule TEMPS | Initié(e)**


La situation : heures effectuées, heures positives ou négatives

Dans une entreprise, l’horaire journalier a été fixé à 7 heures 42.

Comment exploiter les heures pour afficher les heures en plus, les heures en moins et les heures à récupérer ? Mieux… comment ajouter les signes + et – devant les valeurs ?

calcul des heures supplementaires avec heures negatives


Autoriser le calcul des heures négatives

Dans les formules de calcul, Excel remplacer les heures négatives par une série de ###### (et ce, même si la colonne est assez large pour afficher les valeurs).

Pour afficher les heures négatives : activer l’onglet FICHIER > cliquer sur Options > cliquer sur Options avancées > repérer la zone Lors du calcul de ce classeur > cocher l’option Utiliser le calendrier depuis 1904 > cliquer sur OK pour valider.

Attention ! Toutes les dates affichées dans votre classeur « avancent » de 4 ans ! Il faudra les saisir à nouveau. Entre deux « maux », il faut choisir le moindre : re-saisir les jours (facile grâce à la poignée de recopie), ou ne pas afficher les heures négatives.


Déterminer le nombre d’heures effectuées par jour

En D4, la formule est classique : heure de fin moins heure de début égal heures travaillées

Note : dans cet exemple, les temps de repas sont assimilés à du temps de travail car les agents déjeunent ou dînent avec les patients. Sinon, il conviendrait de saisir les heures par demi-journée (ou déduire le temps de pause à mi-journée).

1-Saisie ou importation des heures


Cumuler les heures de la semaine (addition)

Pour additionner les heures, procéder de façon classique (sélection puis clic sur l’icône ∑ somme).

Pour afficher les heures au bon format : sélectionner la cellule de réception > effectuer un clic-droit > cliquer sur l’option Format de cellule > activer l’onglet Nombre > cliquer sur Personnalisée > cliquer sur le format [H]:mm:ss et supprimer les secondes (:ss) > valider en cliquant sur le bouton OK.

2-cumul des heures au dela de 24 heures


Afficher le différentiel d’heures à l’aide de la fonction =TEMPS

Pour déterminer le différentiel d’heures travaillées : partir de la durée effectuée et déduire le temps à effectuer.

Ici, la formule est =D4-TEMPS(7;42;0). C’est-à-dire : 10 heures moins 7 heures 42.

3-Excel calculer differentiel avec fonction temps


Personnaliser le format des heures positives (+), négatives (-) ou nulles

Pour simplifier la lecture des heures effectuées en plus ou en moins : sélectionner les cellules concernées > effectuer un clic-droit > cliquer sur Format de cellule > activer l’onglet Nombre > cliquer sur Personnalisée > saisir dans la zone Type la syntaxe : +[h]:mm;[Rouge]-[h]:mm;[Bleu][h]:mm > cliquer sur le bouton OK pour valider.

format personnalise heures plus moins couleurs


Voilà, votre tableau est terminé !

Attention ! Si l’affichage des jours est en format tronqué (ex. : 17-nov) affichez l’année pour la corriger si elle « avance » de quatre ans !

0-heures effectuees et heures supplementaies


Pour en savoir plus sur les couleurs et les formats personnalisés : c’est ici !

Pour calculer des heures : c’est par là !


©Gianluca Rasile Fotolia