Formules pour calculer des heures : De jour ; De nuit ; et Supplémentaires

Bonjour,

Afin de pouvoir de calculer des coût de prestation pour des intérimaires, j'ai besoin d'une calculatrice Google Sheet fonctionnelle.

La logique des périodes d'heure est la suivante :

1. Heures de jour : les heures travaillées entre 08:00 et 22:00, même si l'heure de fin dépasse minuit ou si l'heure de début est avant 08:00.
2. Heures de nuit : les heures entre 22:00 et 08:00, en gérant les cas où l'heure de début ou de fin se situe entre minuit et 08:00.
3. Heures supplémentaires : si le Total d'heures travaillées > 9 heures
4. Total heure : le total des heures travaillées

Pour l'instant, seuls les formules suivantes fonctionnent parfaitement :

- Total Heures :

=MOD(heures de fin - heures de début; 1) * 24

- Heures supplémentaires :

=MAX(0; (total heures - 9)

Cependant pour les formules des heures de jour et des heures de nuit, cela ne fonctionne pas. Voici un exemple des formules que j'ai utilisé pour l'instant :

- Heures de jour :

=IF(heure de fin < heure de début;(MAX(0; MIN(TIME(22;0;0); heure de fin + 1) - MAX(TIME(8;0;0); heure de début))) * 24; (MAX(0; MIN(TIME(22;0;0); heure de fin) - MAX(TIME(8;0;0); heure de début))) * 24)

- Heures de nuit :

=IF(heure de fin < E3;(MAX(0; TIME(24;0;0) - MAX(TIME(22;0;0); E3)) + MIN(heure de fin; TIME(8;0;0))) * 24; (MAX(0; heure de fin - TIME(22;0;0)) * 24 + IF(heure de fin <= TIME(8;0;0); heure de fin * 24; 0)))

Voici un exemple des résultats que j'obtiens avec ces formules :

Je ne sais pas si mes formules sont trop complexe ou si il manques des éléments qui empêchent par exemple de calculer correctement les heures de nuit une fois que les heures de début et les heures de fin sont séparé par minuit.

Je vous remercie d'avance pour votre temps !

Bonjour,

La gestion des heures est en effet très simplifiée dans Google Sheets

Voici une formule pour tes heures de jour : (F8:F = heure de début / G8:G = heure de fin) cette formule est à mettre dans la 1ere cellule sous ton en-tête)

=map(F8:F;G8:G;lambda(heureDebut;heureFin;SI(OU(heureDebut="";heureFin="");"";SI(heureDebut<=heureFin;MAX(0;MIN(heureFin;TEMPS(22;0;0))-MAX(heureDebut;TEMPS(8;0;0)));MAX(0;TEMPS(22;0;0)-MAX(heureDebut;TEMPS(8;0;0)))+MAX(0;MIN(heureFin;TEMPS(22;0;0))-TEMPS(8;0;0)))*24)))

Et pour les heures de nuits, même principe :

=map(F8:F;G8:G;lambda(heureDebut;heureFin;SI(OU(heureDebut="";heureFin="");"";SI(heureDebut<=heureFin;((heureFin-heureDebut)-MAX(0;MIN(heureFin;TEMPS(22;0;0))-MAX(heureDebut;TEMPS(8;0;0))))*24;((heureFin+1)-heureDebut-MAX(0;TEMPS(22;0;0)-MAX(heureDebut;TEMPS(8;0;0)))-MAX(0;MIN(heureFin;TEMPS(22;0;0))-TEMPS(8;0;0)))*24))))

Merci beaucoup pierre, cela semble fonctionner sur google sheet !

Comme je ne m'attendais pas à une formule aussi complexe je n'ai pas préciser que l'utilisation est pour l'application ODOO dont le spreadsheet est basé sur Google Sheet mais qui ne reconnais pas la fonction map que tu as utilisé ici.

Est- ce qu'il existe des alternatives ?

Alors, j'ai utilisé MAP et LAMBDA afin d'automatiser la formule à tes colonnes.

Mais si tu veux la formule simplifiée, à faire glisser, la voici pour les heures jours :

=SI(OU(F8="";G8="");"";SI(F8<=G8;MAX(0;MIN(G8;TEMPS(22;0;0))-MAX(F8;TEMPS(8;0;0)));MAX(0;TEMPS(22;0;0)-MAX(F8;TEMPS(8;0;0)))+MAX(0;MIN(G8;TEMPS(22;0;0))-TEMPS(8;0;0)))*24)

Et les heures de nuit :

=SI(OU(F8="";G8="");"";SI(F8<=G8;((G8-F8)-MAX(0;MIN(G8;TEMPS(22;0;0))-MAX(F8;TEMPS(8;0;0))))*24;((G8+1)-F8-MAX(0;TEMPS(22;0;0)-MAX(F8;TEMPS(8;0;0)))-MAX(0;MIN(G8;TEMPS(22;0;0))-TEMPS(8;0;0)))*24))

Concernant Oddo, je croyais qu'ils proposaient plusieurs outils dans le but de répondre à tous les besoins "basiques" des entrepreneurs, il n'y a pas ce genre de gestion déjà prévu ?

Un infini merci Pierre, ces formules semblent vraiment bien fonctionner !