Définir des heures de nuit

[pitou007]

Bonjour a tous,

J'ai cherché sur le forum comment définir des heures excel, mais rien ne colle à ce que je recherche.
Je vous explique:

Je vais renseigner dans une colonne A2 une heure (ex 21h30)
Je veux que dans une colonne B2 avoir le nombre d'heures de nuit (de 21h00 à 06h00).
Je procède comme suit pour tester:
=SI(b2>=21;b2-21)

Mais j'ai la notification Faux qui apparait pouvez vous me dire pourquoi et comment faire ?

Merci

 

[nolich]

Re : Définir des heures de nuit

Bonsoir Pitou007, bonsoir à tous,

J'aimerais bien voir un exemple car 21 est un nombre alors que 21:00:00 peut être une heure simple ou une heure datée. Par ailleurs, où doit être le résultat de ton calcul :
=SI(b2>=21;b2-21) car si le résultat est en B2, il y a une référence circulaire si je ne m'abuse.

Enfin bref, un petit exemple serait le bienvenu...

Cordialement

 

[abcd]

Re : Définir des heures de nuit

Bonjour,

En B1 l'heure de début : 21:30 au format h:mm ou 21h30 au format h"h"mm
En B2 l'heure de fin 4:30 (mêmes formats que ci-dessus)
Heures de nuit : de 21:00 à 6:00

=MOD(B2-MAX(B1;21/24;1) renvoie 7:00 (même format que ci-dessus)

abcd

 

[nolich]

Re : Définir des heures de nuit

Hi abcd, bonsoir à toutes et à tous,

Bonjour,

En B1 l'heure de début : 21:30 au format h:mm ou 21h30 au format h"h"mm
En B2 l'heure de fin 4:30 (mêmes formats que ci-dessus)
Heures de nuit : de 21:00 à 6:00

=MOD(B2-MAX(B1;21/24;1) renvoie 7:00 (même format que ci-dessus)

abcd

Juste un petit rectificatif si tu me le permets :

=MOD(B2-MAX(B1;21/24);1)

Cordialement

 

[abcd]

Re : Définir des heures de nuit

Re,

Merci pour la correction, j'ai retrouvé la ) manquante à côté de mon PC !

abcd

 

[pitou007]

Re : Définir des heures de nuit

Merci pour votre aide ca marche bien.

Serait il possible d'avoir un peu d'explication sur cette fonction (Mod) et de manière générale sur cette formule ?

Par ailleurs la formule se comporte bizarement lorsque B1 et B2 ne sont pas renseignés, il trouve 3h00. Si B1 =B2 il trouve 22h30

Une autre question bête, je veux calucler le retard donc Heure départ - Heure prevu. CA marche sans pb. Sauf que après 24h00 ma formule marche plus, je suis obligé de mettre 25h00 pour 01h00 du matin.
De quoi ca vient ?
Si quelqu'un peut m'expliquer, je serai un peu moins bête

Merci à vous

 

[nolich]

Re : Définir des heures de nuit

Bonjour à tous,

Pitou007, je te renvoie ton fichier un peu modifié. Je n'ai pas le temps de t'expliquer tout ce matin, mais la fonction MOD retourne le reste d'une division, donc MOD(21/24) est égal à 3 : 21/24 donne 0 avec un reste de 3. Par ailleurs, les cellules vides sont considérées, dans un calcul, comme étant nulle ( =0 ).

Cordialement

PS : il semble que je ne sache pas comment joindre un fichier, je réessaierai ce soir.

 

[abcd]

Re : Définir des heures de nuit

Bonjour,

Excel considère une cellule vide comme étant égale à 0, soit 00:00:00
Donc : =MOD(0/24-21/24;1) ou =MOD("0:00"-"21:00";1) renvoie 3:00
Pour contourner ce problème il faut mettre une condition prélimainaire dans la formule :
=SI(OU(D12="";G12=0);"";MOD(G12-MAX(D12;21/24);1))
Cette condition prélimniare devrait aussi être appliquée sur la formule de la colonne J afin d'éviter les #### au moment de la saisie.

Excel stocke les valeurs horaires sous un format nombre, et l'unité horaire étant le jour, toutes les heures en format nombre sont inférieures à 1
21:00 → 0.875 et 3:00 → 0.125
0.125-0.875 = -0.75
La fonction MOD divise le résultat de la soustraction par le 2ème argument (1 dans la formule proposée) et renvoie le reste de la division :
=1+(-0.75) = 0.25 → 6:00 en format horaire
De là : =MOD("3:00"-"21:00";1) ou =MOD(3/24-21/24;1) = 6:00 en format horaire.

J'espère avoir été clair.
Consulte également les anciens messages qui traitent le sujet.

abcd

 

[nolich]

Re : Définir des heures de nuit

re,re ;-)

Et pour illustrer les propos d'abcd, un petit fichier. Mais je préfère les "si(non(estvide(X99)));VRAI;FAUX)"

Cordialement

 

[abcd]

Re : Définir des heures de nuit

Re,

Si tu préfères NBVIDE, ceci est plus court de 21 caractères :
=SI(ESTVIDE(D12:G12)>0;"";MOD(G12-MAX(D12;21/24);1))

abcd

 

[nolich]

Re : Définir des heures de nuit

Re,

Tu as tout à fait raison, il faut savoir être court

Cordialement