Gestion des heures supplémentaires

Name: Apprenez à gérer le temps Concevez planning, échéanciers, tableaux d'absence avec Excel (versions Office 365, 2019 et 2016)
Rating: 5 (3 reviews)

Énoncé du projet

Énoncé

Nous sommes dans une entreprise dont les salariés effectuent régulièrement des heures supplémentaires. Pour faciliter le suivi de ces heures, il a été demandé par le responsable de paie qu’un tableau de suivi des heures soit rempli quotidiennement par le salarié. Ce dernier devra saisir la date du jour, les heures d’arrivée et de départ, le temps de pause, préciser les jours d’absence.

En fin de mois, le même responsable aimerait avoir un récapitulatif lui indiquant par salarié le nombre total d’heures supplémentaires, le nombre d’heures supplémentaires par taux de majoration, le nombre d’heures d’absence, le nombre d’heures travaillées.

Voici quelques informations utiles à la création du tableau :

L’horaire collectif de travail est de 35 heures par semaine, et l’horaire journalier est de 7 heures.

Les salariés absents toute la journée, devront saisir à la place de l’heure d’arrivée le mot "Abs" ou éventuellement ne rien saisir. Dans le cas où l’absence est inférieure à une journée, ils devront saisir l’heure d’arrivée et l’heure de départ

Les heures supplémentaires étant calculées...

Étape 1 : analyse du projet

Les questions-réponses

Quel est l’objectif principal à atteindre ? Suivi des heures supplémentaires effectuées par chaque salarié.

Quelles sont les contraintes et/ou objectifs secondaires ?

Automatiser les calculs.
Simplifier la saisie.
Vérifier à quel mois les heures supplémentaires sont liées.
Obtenir des synthèses hebdomadaires.
Déterminer le nombre d’heures par taux.
Obtenir un tableau mensuel de synthèse pour le gestionnaire de paie.

À qui le résultat final est-il destiné ? Aux salariés et au gestionnaire de paie.

Sur quel support doit-il être présenté ? Sur Excel.

Le concepteur est-il l’utilisateur ? Non.

Qui devra saisir ou exploiter les données ? Les salariés, le responsable de paie.

Ce tableau servira-t-il une ou plusieurs fois ? Plusieurs fois, autant de fois qu’il y a de salariés.

Est-ce une matrice ? Oui. Le tableau sera un modèle.

Les données à saisir :

Pour le suivi journalier des heures supplémentaires : Nom du salarié, date du jour, heure début, heure fin, durée pause, les absences.

Pour les tableaux de synthèse : heures supplémentaires par semaine, par taux, heures travaillées par mois, heures d’absence.

Les données faisant partie de la structure du tableau...

Étape 2 : choix des commandes Excel

Avant de continuer, si vous ne l’avez pas déjà fait, nous vous conseillons de prendre le temps de lire le chapitre Introduction à la notion de temps, pour connaître les bases nécessaires à l’utilisation des heures (écriture, format, conversion, etc.).

Feuille de suivi des heures supplémentaires

Cette feuille sera utilisée en tant que modèle pour chaque salarié et pour chaque année.

Les dates, les heures de début et fin de journée, les heures de pause, les absences seront à saisir par le salarié.

Les heures travaillées, les heures d’absence, les heures supplémentaires, le numéro de semaine, le mois civil, le mois de paie devront être calculés automatiquement.

Déterminer le numéro de la semaine

Norme européenne du calcul de la 1ère semaine

Contrairement à ce que nous pourrions croire, la semaine 1 de l’année n’est pas la même dans tous les pays.

La norme européenne considère la semaine 1 comme étant celle qui est composée d’au moins 4 jours de la nouvelle année.

Excel nous propose la fonction NO.SEMAINE pour déterminer en premier lieu le numéro de la semaine correspondant à la date du jour selon la norme américaine. Cette fonction a été modifiée à partir de la version 2010 pour tenir compte de la norme européenne.

La version 2013 voit arriver une nouvelle fonction spécifique à la norme européenne : NO.SEMAINE.ISO.

Nous avons donc maintenant deux fonctions pour déterminer le numéro de la semaine.

Fonction NO.SEMAINE

Cette fonction est basée sur deux systèmes :

Système 1 : la semaine 1 contient le premier jour de l’année.

Système 2 : norme européenne, la semaine 1 est celle qui contient le premier jeudi de l’année et non le mardi comme il est indiqué dans l’aide en ligne de Microsoft.

Sa syntaxe est la suivante :

NO.SEMAINE(numero_de_serie;méthode)

Numero de serie : correspond à une date qui peut être représentée par un numéro de série, une référence, un calcul, une fonction.

Méthode : correspond aux valeurs précisées...

Étape 3 : gestion des heures supplémentaires pas à pas

Création du modèle de suivi des heures

Ouvrez le fichier Heures_supp_debut.

Calcul du numéro de la semaine

Cliquez enA6puis saisissez la formule suivante :

=NO.SEMAINE.ISO(B6)

Vous obtenez 1.

Recopiez la formule jusqu’en A11.

Calcul des heures travaillées

Cliquez en F6 puis saisissez la formule suivante :

=SI(OU(ESTVIDE(C6);ESTTEXTE(C6));0;D6-C6-E6)

Si la cellule C6 (heure début) est vide ou contient du texte, afficher 0, sinon calculer : D6 (heure fin) moins C6 (heure début) moins E6 (temps de pause).

Vous obtenez, une fois la formule validée, 0.125.

Cliquez en F6 puis appliquez le format HEURE.

Vous obtenez 03:00:00.

Recopiez la formule jusqu’en F11.

Calcul des heures supplémentaires

Cliquez en G6 puis saisissez la formule suivante :

=SI(F6=0;0;F6-$J$1)

Si F6 (heures travaillées) est égal à 0 alors afficher 0 sinon calculer les heures supplémentaires par F6 (heures travaillées) moins $J$1 (horaire journalier).

Vous obtenez -0,166... si votre cellule est au format standard ou #### si elle est au format heure. Rappelez-vous qu’un temps ne peut pas être négatif, or 09:00 moins 12:00 donne une valeur négative.

Pour éviter des heures supplémentaires négatives, écrivez en G6 la formule comme ci-dessous :

=SI(F6=0;0;SI(F6<=$J$1;0;F6-$J$1))

Cliquez en G6 puis appliquez le format HEURE.

Recopiez jusqu’à G11.

Calcul des heures d’absence

Cliquez en H6 puis saisissez la formule suivante :

=SI(F6=0;$J$1;SI(F6>$J$1;0;$J$1-F6))

Si F6 (heures travaillées) est égal à 0 alors afficher l’horaire journalier (07:00) sinon, si les heures travaillées (F6) sont supérieures à l’horaire journalier ($J$1) alors afficher 0 sinon calculer les heures d’absence par $J$1 (horaire journalier) moins F6 (heures travaillées).

Validez puis appliquez le format HEURE.

Vous obtenez 04:00:00.

Recopiez jusqu’en H11.

Calcul du mois civil

Cliquez en I6 puis saisissez la formule suivante :

=MOIS(B6)