insertion heures après minuit dans une formule

[NatD]

Bonjour,

malgré plein de recherches dans plusieurs forum, et divers tests pas vraiment concluant, je viens vous demander de l'aide avant d'avoir la tête qui explose !

je travaille sur un tableur de planning horaire, qui peut contenir des heures de fin de travail après minuit. J'ai réussi à adapter ma formule pour obtenir mon nombre d'heures travaillées, mais je bloque sur une autre formule.
Je souhaite faire apparaître dans une case une alerte si le temps de repos quotidien est inférieur à 11h.
Cette alerte fonctionne si il n'y a pas d'heures après minuit en jeu (exemple case P9 dans l'onglet septembre de ma pièce jointe), mais ne fonctionne pas si on dépasse minuit (case P8 l'alerte devrait s'afficher).
Je précise également que je ne souhaite pas devoir saisir les heures après minuit sous le format "01/01/1900 heure:minutes:secondes" comme c'était le cas à la base dans ce tableur.

En espérant que vous pourrez m'aider

Merci

 

[crefieu]

Re : insertion heures après minuit dans une formule

Bonjour NatD,

Un essai rapide...

 

[Victor21]

Re : insertion heures après minuit dans une formule

Bonjour, NatD.

Une piste : sur ton fichier :

MOD(E9-J8;1)

renvoie 8:00

 

[NatD]

Re : insertion heures après minuit dans une formule

Bonjour et merci pour vos réponses rapides !

@ crefieu : ça marche car l'alerte fonctionne, et le système de menu déroulant pour choisir l'heure est pratique, sauf que dans le menu déroulant ça affiche la version en "1,04" et autres valeurs numériques, donc il faut savoir à quoi elles correspondent our pouvoir choisir l'heure (ça risque de ne pas être pratique pour mes collaborateurs)

@ Victor 21 : après quelques recherches sur le fonctionnement de cette fonction MOD, je comprends qu'il faudrait que je l’intègre dans ma formule en P8, mais là franchement je sèche , étant donné que le calcul de différence entre E9 et J8 est apparemment éclaté en deux partie dans la formule existante... encore un petit indice ?

 

[Victor21]

Re : insertion heures après minuit dans une formule

Re,

Essaie en P9 à recopier vers le bas :

=SI(OU(K8=0;K9=0);"";SI(MOD(SI(E9="";SI(G9="";I9;G9);E9)-SI(J8="";SI(H8="";F8;H8);J8);1)*24<'Limites conventionnelles'!F$14;'Limites conventionnelles'!M$14;""))

 

[crefieu]

Re : insertion heures après minuit dans une formule

Bonjour le fil,

C'est bizarre car sur mon fichier cela s'affiche correctement...
quels sont tes formats en zone de liste pour les menus deroulants, et dans ta grille?

 

[NatD]

Re : insertion heures après minuit dans une formule

Bonjour !

@Victor21 : ça m'affiche "Err :509", comme à chaque fois que j'essaie une formule différente...je commence à être désespérée à force d'essayer plein de combinaisons possibles !

@Crefieu : j'ai téléchargé et ouvert ton fichier, le format de cellule où on entre l'heure est en "Heure 13:37", et ma liste en colonne AE est en Format défini par l'utilisateur : [$-409]H:MM\ AM/PM;@

merci encore pour votre aide

 

[Victor21]

Re : insertion heures après minuit dans une formule

Re.

Vous avez dit bizarre... Comme c'est bizarre !!!

 

[NatD]

Re : insertion heures après minuit dans une formule

@ Victor21 : Merci beaucoup !! effectivement ça marche nikel ! (j'ai trouvé pourquoi ça bugait, il y avait "g 9" au lieu de "G9" à un endroit dans la formule), c'est quand même un peu galère ces histoires d'heures après-minuit...

pour ma curiosité personnelle et si à l'occasion tu as 5mn à perdre je veux bien que tu m'explique la formule (j'ai essayé de la décortiquer mais je ne comprends toujours pas)

en tout cas, ça a débloqué mon problème, encore un peu de boulot sur mon tableur (corriger la formule en W, ou remettre la formule en X aux lignes de dimanche) mais je devrait m'en sortir, au pire je vous poste un petit message !

Merci beaucoup !

 

[Victor21]

Re : insertion heures après minuit dans une formule

Re,

pour ma curiosité personnelle et si à l'occasion tu as 5mn à perdre je veux bien que tu m'explique la formule (j'ai essayé de la décortiquer mais je ne comprends toujours pas)

De mémoire, j'ai calculé dans une cellule(a) l'heure de fin maxi sur les 3 plages possibles, puis l'heure de début mini le lendemain dans une autre cellule(b). J'ai effectué la soustraction de (b)-(a) dans une 3° cellule(c), puis remplacé les références aux cellules (a) et (b) par les formules qu'elles contenaient. J'ai ajouté ta condition, et supprimé les cellules (a) et (b).

C'est la méthode que j'utilise souvent : diviser un problème en sous-problèmes, (éventuellement continuer les divisions) et les résoudre un à un.

 

[NatD]

Re : insertion heures après minuit dans une formule

Re-bonjour,

dernière ligne droite pour mon tableau, avec encore 2 petits soucis que je n'arrive pas à résoudre :

1°) un problème de format pour le calcul des heures supplémentaires (en colonne P pour le calcul chaque semaine et en N562 pour le total annuel), j'ai ajouté *24 dans ma formule ce qui fait le bon calcul mais j'ai essayé plusieurs format heure sans succès, le seul affichage qui fonctionne est en format Nombre standard, ce qui me pose problème pour le calcul en N563...

2°) le total d'heures par jour se remplit automatiquement à 7h si le type de jour est FOR, sans remplir d'heures de travail réelles (exemple ligne 18), mais ça me pose souci dans cette fameuse formule pour les 11h de repos quotidien en R18.
j'ai essayé d'inclure dans la formule une condition SI supplémentaire du type SI(D18=FOR;"";la formule actuelle) mais j'ai un message d'erreur...

je remets mon fichier actualisé en pièce jointe,
si quelqu'un a une idée je suis preneuse !

Merci d'avance

 

[Victor21]

Re : insertion heures après minuit dans une formule

Re,

Dans la colonne P, remplace :

=SI(L14*24>'Limites conventionnelles'!F$36;L14*24-'Limites conventionnelles'!F$36;"")

par :

=SI(L14*24>'Limites conventionnelles'!F$36;L14*24-'Limites conventionnelles'!F$36;0)

 

[NatD]

Re : insertion heures après minuit dans une formule

Bonjour,

j'ai changé la formule, si je laisse en format Nombre standard le calcul est toujours bon mais ça ne me l'affiche pas en heures, si je le mets en format heure "876613:37", ça m'affiche 66:00 et pas 2:45... une idée d'où ça peut venir ?

 

[Victor21]

Re : insertion heures après minuit dans une formule

Re,

Si tu affiches en heures, sur lesquelles tu dois faire des calculs, ton nombre ne pourra pas être négatif (Excel n'accepte pas des heures négatives, sauf à passer en calendrier 1904 via Outils, options, calcul).
PS : 66:00/24=2:45

 

[NatD]

Re : insertion heures après minuit dans une formule

ok merci pour l'explication, j'ai rajouté des parenthèses et /24 à la condition vrai et ça fonctionne
(c'est quand même bizarre de devoir mettre *24 ET /24 dans le même calcul non ? )

et pour le calcul du repos hebdomadaire en cas de formation ? est-ce qu'il y a une solution ou est-ce que dois enlever ce remplissage automatique et rentrer des heures ?

Merci !