Formule pour Calculer les heures de nuit

[hectine]

Bonjour

Voila, j'utilise excel pour mes feuilles d'heures et tout est automatisé (le calcul des paniers, des heures faites dans la journée etc...) mais il y a un truc que je n'arrive pas à faire, c'est la formule pour calculer les heures de nuit.

Ces heures de nuit partent de 21h00 à 06h00.

Il faudrait donc que lorsque j'entre par exemple en colonne A (début de travail) et en colonne B (fin de travail), en C j'obtienne les heures de nuit.

Exemple :

A B C

18 03 6
19 21 0
16 22 1
02 08 4

etc...

Qui serait capable de me faire cette formule que j'insèrerais en colonne C (heures de nuit) ?
Merci.

 

[hectine]

j'utilise excel 2003

ci-joint un fichier vierge.

 

[Monique]

Re,

Tes heures de nuit sont en place.
L'heure de début et de fin des nuits est sur une feuille à part.
Cellules nommées D et F pour raccourcir la formule.

Le format des heures est changé, tu peux supprimer toutes les colonnes qui te donnaient "h" ou rien
J'ai enlevé le format texte partout, on se sert de vrais nombres.
Il y a un truc que je n'ai pas compris, c'est le total des heures de dimanche multiplié par 40
Dans ces totaux, la formule est aussi modifiée :
on fait le total de tout, tout, tout même les sous-totaux, et on divise par 2.

Tu as une colonne "Heures jours fériés"
Pour faire ce compte, il faudrait avoir de vraies dates en colonne B
et une plage de fériés quelque part dans ton classeur.
Est-ce que tu veux un compte de fériés ?
Si oui, il faudra enlever les lignes qui séparent les semaines et faire les totaux hebdo sur le côté et non sous chaque semaine.
Tu as un exemple ici (mais tu ne voudra peut-être pas le télécharger) :
La page "Calendrier Etat Heures" de Autres Formules III"
<http://www.excel-downloads.com/html/French/fichiers/programmation-date_maj-1.htm>

 

[hectine]

Salut.

Merci pour la formule. Mais est il possible de l'adapter sans rien changer dans la feuille ? Je veux dire, en gardant l'exemple que je t'ai envoyé dans lequel j'insère la deuxième page. De sorte qu'on obtiendrait un chiffre rond comme 20 pour 20h ou 20,5 pour 20h30. Car si je reprends mon exemple, que je colle ta deuxième page et que je copie ta formule et la place dans la colonne appropriée, j'ai des ####.

Penses tu pouvoir l'adapter ?

=SI(ET(D2>=E2;D2<=D;D2<>0;E2<>0);MOD(F-D;24)-SI(E2<=F;F-E2)+SI(D2<=F;F-D2);SI(ET(D2>=E2;D2>D;D2<>0;E2<>0);MOD(F-D;24)-(D2-D)+SI(E2>=D;E2-D)-SI(E2<F;F-E2);SI(ET(D2<E2;ESTNUM(D2);E2<>0);0+SI(ET(D2<=F;E2<=F);E2-D2)+SI(ET(D2<=F;E2>F);F-D2)+SI(E2>=D;E2-D2-SI(D2<=D;D-D2));0)))+SI(ET(I2>=J2;I2<=D;I2<>0;J2<>0);MOD(F-D;24)-SI(J2<=F;F-J2)+SI(I2<=F;F-I2);SI(ET(I2>=J2;I2>D;I2<>0;J2<>0);MOD(F-D;24)-(I2-D)+SI(J2>=D;J2-D)-SI(J2<F;F-J2);SI(ET(I2<J2;ESTNUM(I2);J2<>0);0+SI(ET(I2<=F;J2<=F);J2-I2)+SI(ET(I2<=F;J2>F);F-I2)+SI(J2>=D;J2-I2-SI(I2<=D;D-I2));0)))

 

[hectine]

C'est bon !!

Formule finalisée. J'insère deux colonnes entre << TOTAL vacation >> et << dont nuit >>. Dans la première je mets :

=SI(D2>=21;SI(E2>=21;E2-D2;SI(E2<=6;E2+24-D2;6+24-D2));SI(D2>6;SI(E2>=21;E2-21;SI(E2>6;SI(E2<D2;6+24-21;0);E2+3));SI(E2>6;6-D2;E2-D2)))

Dans la seconde :

=SI(I2>=21;SI(J2>=21;J2-I2;SI(J2<=6;J2+24-I2;6+24-I2));SI(I2>6;SI(J2>=21;J2-21;SI(J2>6;SI(J2<I2;6+24-21;0);J2+3));SI(J2>6;6-I2;J2-I2)))

En fait ce sont les même formules décalées.

Puis dans Dont nuit, j'additionne les deux ci-dessus. Puis je rétrécis les deux colonnes à 0. Ainsi, on ne les voit pas. Et ça permet de calculer les heures de nuit sur deux vacations. )

Bonne soirée et merci.

 

[Monique]

Re,

Ce n'est pas la formule qui serait à adapter, mais l'affichage.
Tu as plusieurs solutions.
Est-ce que tu as supprimé les colonnes qui contenait le "h" et qui ne servent plus ?
Tu élargis la colonne.
Tu choisis une police plus petite.
Tu changes le format de la cellule :
[=0]"";0,00"h"
(un espace en moins)
[=0]"";0,0" h"
(un 0 en moins)
[=0]"";0,0"h"
(un 0 et un espace en moins)
Si tu veux imprimer, tu joues sur le zoom
Colonne des jours, tu peux écrire Lun, Mar, Mer, etc

 

[Monique]

Re,

Tu peux supprimer les colonnes F et K
et mettre en N2 :
=MOD(E2-D2;24)+MOD(J2-I2;24)

 

[hectine]

Merci pour cette petite formule pratique !

 

[Monique]

Re,

La formule dont tu parles pour les heures de nuit n'est pas valable dans tous les cas.
De 6:00 à 22:00 par exemple, le résultat est faux.
Celle qui est dans le classeur posté fait la somme des heures de nuit des 2 parties de la journée de travail, pas besoin de 2 colonnes masquées et d'un total dans une 3è colonne..
Et on peut avoir, avec cette formule, des amplitudes de 24 heures (gardes, astreintes, etc)

 

[Monique]

Re,

Colonne "Panier" :
=(MOD(E2-D2;24)>6,5)+(MOD(J2-I2;24)>6,5)
et tu supprimes les 2 colonnes intermédiaires.
On va y arriver.

 

[hectine]

eheh extra ! merci monic. ))

Pour la formule, là ça doit être bon :

=SI(A4>=21;SI(B4>=21;B4-A4;SI(B4<=6;B4+24-A4;6+24-A4));SI(A4>=6;SI(B4>=21;B4-21;SI(B4>6;SI(B4<A4;6+24-21;0);B4+3));SI(B4>6;6-A4;B4-A4)))

 

[Monique]

Re,

Non, elle n'est toujours pas exacte dans tous les cas.
Si elle correspond aux horaires que tu es susceptible d'avoir, c'est bien.
Mais fais beaucoup d'essais avant de l'utiliser.
Et ce sera une formule que l'on ne devra pas utiliser pour quelqu'un d'autre.

 

[hectine]

Par contre, lorsque j'ai :

=SOMME(N7:N13)

Comment faire pour que ça me mette 35 si le total est en dessous ?
En fait, ça totalise les heures. Mais si par exemple, ca donne 36, 39 etc... C'est bon. Mais si ça totalise 29, 25, 34, il faudrait que ça mette 35.

Que faire pour cela dans cette formule ? =SOMME(N7:N13)

 

[Monique]

Bonjour,

Tu tapes :
=MAX(35;SOMME(N7:N13))
Elle te donnera soit 35, soit davantage, mais pas moins que 35.

 

[hectine]

merciiiiiiiiiiii ))