Calculer des heures de nuit dans Numbers

[GLeb]

Bonjour, après quelques recherches, et ne trouvant pas la réponse je m'adresse à vous dans l'espoir d'une aide.
Je cherche à calculer le nombre d'heures de nuit sur une période entre deux dates.

Ici en exemple j'ai calculé à la main, mais j'aimerais qu'il soit automatique, si il y a des heures comprises entre 21:00 et 06:59, faire la somme de ces heures, sinon ne rien afficher.
J'aimerais ici que C3 m'affiche 1h 0min tout seul.
Merci.

 

[baron]

Indépendamment de la question du calcul sur des heures/durées, j'explorerais déjà la fonction SOMME.SI.

 

[zeltron54]

@GLeb
Bonjour,
Pour prendre en compte toutes les possibilités d'horaire: Début avant ou après 7H, fin avant ou après 21H et éventuellement début un jour, fin le lendemain, il faut une formule à rallonge ou bien la décomposer et faire des cellules intermédiaires que tu additionnes à la fin.
Ci-dessous je te mets la formule à mettre dans C3 pour coller à ton exemple, qui prend en compte toutes les possibilités.
Dis-moi si ça te convient .

=SI(JOUR(A3)=JOUR(B3); SI(HEURE(A3)<7;7−HEURE(A3);0)+ SI(HEURE(B3)>21;HEURE(B3)−21;0); SI(HEURE(A3)<7;7−HEURE(A3);0)+SI(HEURE(B3)>21;HEURE(B3)−21;0)+SI(HEURE(B3)<7;HEURE(B3);7)+SI(HEURE(A3)>21;24−HEURE(A3);3))

 

[zeltron54]

@GLeb
Après plus amples test, la formule comporte quelques cas ou il y a des erreurs, et de plus elle ne prend pas en compte les minutes.
Il faut la revoir complétement. Je regarde dés que j'ai un peu de temps ...

 

[zeltron54]

Pour résoudre ton problème, j'ai démissionné de faire ça avec une seule formule, trop longue et trop complexe.
J'ai donc opté pour des calculs intermédiaires, 5 pour être précis:

dans la cellule D3 : si le jour de fin = jour de début alors calcul des heures de nuit faite le matin.
dans la cellule E3 : si le jour de fin = jour de début alors calcul des heures de nuit faite le soir.
Dans la cellule F3 : si jour de fin est différent de jour de début alors calcul des heures de nuit faite le matin du jour de début.
Dans la cellule G3 : si jour de fin est différent de jour de début alors calcul des heures de nuit faite le soir du jour de début.
Dans la cellule H3 : si jour de fin est différent de jour de début alors calcul des heures de nuit faite le matin du jour de Fin.
Dans la cellule C3 : la somme des 5 cellules
Les colonnes D à H peuvent bien sur être masquées.

Ci-dessous les formules de chaque cellule:

en D3:

SI(JOUR(A3)=JOUR(B3);SI(HEURE(A3)<7;SI(HEURE(B3)>7;TEMPS(7−HEURE(A3);−MINUTE(A3);0) ;TEMPS(HEURE(B3);MINUTE(B3);0)−TEMPS(HEURE(A3);MINUTE(A3);0));TEMPS(0;0;0));TEMPS(0;0;0))

en E3:

SI(JOUR(A3)=JOUR(B3);SI(HEURE(B3)≥21;SI(HEURE(A3)<21;TEMPS(HEURE(B3)−21;MINUTE(B3);0);TEMPS(HEURE(B3);MINUTE(B3);0)−TEMPS(HEURE(A3);MINUTE(A3);0));TEMPS(0;0;0));TEMPS(0;0;0))

en F3:

SI(JOUR(A3)≠JOUR(B3);SI(HEURE(A3)<7;TEMPS(7−HEURE(A3);−MINUTE(A3);0);TEMPS(0;0;0));TEMPS(0;0;0))

enG3:

SI(JOUR(A3)≠JOUR(B3);SI(HEURE(A3)≥21;TEMPS(23−HEURE(A3);60−MINUTE(A3);0);TEMPS(3;0;0));TEMPS(0;0;0))

en H3:

SI(JOUR(A3)≠JOUR(B3);SI(HEURE(B3)<7;TEMPS(HEURE(B3);MINUTE(B3);0);TEMPS(7;0;0));TEMPS(0;0;0))

en C3:

TEMPS(HEURE(D3)+HEURE(E3)+HEURE(F3)+HEURE(G3)+HEURE(H3);MINUTE(D3)+MINUTE(E3)+MINUTE(F3)+MINUTE(G3)+MINUTE(H3);0)

Chez moi ça fonctionne!
Tiens moi au courant .

 

[GLeb]

Pour résoudre ton problème, j'ai démissionné de faire ça avec une seule formule, trop longue et trop complexe.
J'ai donc opté pour des calculs intermédiaires, 5 pour être précis…

Ouah ! Ça fonctionne du feu de Dieu. Je comprend la logique employée, c'est déjà bien, je saurais donc la modifier au besoin, mais alors je n'aurais jamais réussi ce tour de force ! Chapeau !
J'étais partit sur la fonction SOMME.SI comme Baron, mais j'étais dans l'impasse, j'ai donc bien fait de poster ici.
Merci de la précieuse aide.
Une dernière question, Numbers me force la main sur le format, il met la Date, pas la Durée en C3, du coup j'ai plus le total des heures de nuit. Une idée ?

 

[zeltron54]

Je n'ai pas ce problème de format chez moi.

Sans format défini (Auto), j'ai 01:30, si je défini un format date et heure, je choisi date --> aucun et Heure 19h08 j'obtiens 1h30
Sinon, essai d'effacer toute la colonne C , afin d' être sur qu'il n'y ai pas de format restant, puis tu la récrées sans aucun format, normalement ça devrait être bon.

Content pour toi que cela fonctionne.

 

[zeltron54]

Je viens de comprendre que tu voudrais avoir la somme de la colonne (H de nuit).
Pour ce faire 2 possibilités

1- Tu utilises l'équivalent de la formule qui est en C3, en additionnant une à une les heures, puis les secondes.

2 - Tu crées 2 colonnes supplémentaires,( par exemple en D et E puis dans la 1ere (D) tu mets les heures avec la formule: =HEURE(C3) dans la 2ieme (E) les minutes: =MINUTE(C3)
En bas de ces colonnes tu fait les sommes avec la formule: =SOMME(D3x) x étant le numéro de la dernière ligne et =SOMME(E3:Ex)
Puis en bas de la colonne C pour avoir le total des H de nuit la formule: =TEMPS(Dx;Ex;0)

Je pense que la 2ieme solution est la plus pratique.

 

[Membre supprimé 1060554]

Je pense que la 2ieme solution est la plus pratique.

c'est aussi la plus désopilante : ... =SOMME(D3x) x ...​

 

[zeltron54]

Coucou
macomaniac

Je pense qu'il comprendra qu'il faut lire

=SOMME(D3:Dx)

Un peu d'humour dans ce monde de brute ... c'est parfois bon !

 

[Membre supprimé 1060554]

zeltron

Il suffit que tu encadres ton segment poilant par les balises [plain].....[/plain].

Ce qui donne : =SOMME(D3:Dx)

 

[GLeb]

Je tombe sur un chiffre qui n'a rien à voir, encore une fois Numbers me pousse dans la gorge Date, ok, mais pour le calcul en C3, il compte en jours et redémarre le compteur toute les 24 heures, si seulement je pouvais connaître ce fameux nombre de jours. Mais dans l'idéal, ce serais de connaître le nombre exacte d'heures, même passé 24h.

 

[zeltron54]

Désolé, mais je ne comprend pas ton problème.
donne moi déjà l'analyse d'une ligne (par exemple la 3 , en me disant ce qu'il y a dans chaque cellules de A à la fin de l'utilisation.
dis également quelle est la dernière ligne des colonnes (qui devrait faire les totaux) et ce qu'il y a dans chaque cellules.

Si possible une copie d'écran complète.
éventuellement, transmettre le fichier...

Tu parles peux être du calcul de la somme en colonne C, pas de la cellule C3 ?
Si c'est ça, confirme moi, je te donnerai le moyen d'obtenir un total heures minutes, il me faudra juste le nom des 2 cellules dans lesquelles tu as le total heure et le total minute (colonne D et E dans mon exemple précédent).

 

[GLeb]

Je te transmet ici le fichier complet, et c'est bien un total des heures qui m'intéresse sous la forme du type "139h 15min" ici dans la cellule J23, la colonne J étant le résultat de tes calculs. Merci.
https://www.icloud.com/numbers/0MXqeVkvef2jgb8EfNbxXUIkg#RES_GLC_BILAN_PLANNING_-_Mon_film

 

[zeltron54]

J'ai accès à ton tableau uniquement en lecture, donc je te mets les formules à modifier.

En G23 : Calcul du total heures de nuit

=DUREE(0;0;H$23;I$23;0)

En E23 ajouter la somme pour calcul du temps total de travail

=SOMME(D3:D22)

En D23 Calcul des heures de jour

=E23−G23

En espérant avoir bien compris ton besoin !
Dis moi si ça te convient.

 

[GLeb]

Tout fonctionne à merveille. Tu m'as bien aidé sur ce point, merci encore !

 

[zeltron54]

Vérifie, je viens d'éditer mon post avec des simplifications de formules

 

[GLeb]

Plus light la formule, j'ai juste dû supprimer les semaines et secondes qui c'était mise automatiquement. Même résultat mais plus clair.

 

[zeltron54]

Ok content pour toi !