Quatrième vidéo consacrée au calendrier dynamique sur Excel. Cet article est destiné aux salariés qui utilisent leur véhicule pour se rendre à leur lieu de travail. Nouvelle année veut aussi dire faire le bilan de l’année précédente. C’est le moment de faire le compte des kilomètres effectués dans l’année. Le but est de s’en servir pour les frais réels de votre prochaine déclaration de revenus.
Récupération du calendrier dynamique
Afin d’éviter une perte de temps, si vous découvrez cet article sans avoir lu les précédents, je vous invite à lire les articles :
- Excel : créer un calendrier
- Excel : suivre ses congés et RTT
- Améliorer son calendrier dynamique Excel en 2025
Pour que tous soient sur un pied d’égalité, on va commencer à partir du fichier disponible à la fin du 3e article.
Libre à vous d’utiliser votre propre calendrier si vous l’avez personnalisé mais vous pourriez rencontré des problèmes.
Modification des bases
Pour optimiser notre future rubrique de frais kilométriques, on va effectuer une petite modification dans la feuille base.
Tout d’abord, dans la colonne E, on liste les types d’événements pour notre feuille saisies. J’y ajoute Formation. Sachez que c’est facultatif.
Comme c’est un nouveau type d’événement, si je fais des saisies avec, cela sera affiché dans mon calendrier. Donc, je vous invite à créer une mise en forme conditionnelle supplémentaire.
Pour les frais kilométriques, certains événements ne sont pas concernés par le calcul du kilométrage (ex : télétravail). Il faut donc une 2nde colonne pour distinguer ceux qui seront pris en compte dans notre futur calcul.
- Tout d’abord, donnez un titre Frais en F1.
- Sélectionnez la colonne Frais du tableau structuré puis allez dans Données > Validation de données.
- Autoriser Liste puis dans le champ source il nous faut 2 choix Oui et Non. J’ai fait le choix de mettre seulement O;N
- Cliquez sur OK
Avec notre exemple, il faut faire le choix du O pour les 3ers types et le dernier puis N pour Télétravail.
Ajout d'une section frais kilométriques
1ère partie : jours calendaires, repos, fériés et événements hors indemnités
Dans la feuille calendar, nous avons déjà des tableaux pour le calcul des congés et des RTT. C’est à la suite de ces derniers que nous allons ajouter une rubrique consacrée au kilométrage.
Commençons à saisir les éléments suivants comme la capture d’écran ci-dessous :
- AB18 : un titre
- AB19 : le nombre de jours par an
- AB20 : Repos
- AB21 : Férié
Tant qu’à faire autant afficher la liste des types événements qui ne seront pas affectés par le calcul des indemnités kilométriques (bien que ce ne soit pas obligatoire).
Vous vous souvenez la colonne Frais créée dans notre feuille base. Voilà son utilité !
En AB22, je saisis la formule :
=FILTRE(Tabl_type_evt[Type];Tabl_type_evt[Frais]="N")
Hormis la 1ère ligne (AB19), il est important que les valeurs soient écrites exactement comme elles apparaissent dans le calendrier.
Passons aux calculs de cette partie.
On pourrait faire le décompte des jours présents pour chaque mois du calendrier mais je ne trouve pas la formule optimale et agréable aux yeux.
=NBVAL(Y4#;W4#;U4#;S4#;Q4#;O4#;M4#;K4#;I4#;G4#;E4#;C4#)
Je vais appliquer 2 fonctions que l’on a découvert dans l’article Excel : les fonctions indispensables sur les dates : JOURS et FIN.MOIS.
Pour utiliser la fonction JOURS, nous avons besoin des dates de départ et de fin de la période. Pour la 1ère, on va simplement utiliser la cellule D1. Même si janv est affiché, la valeur de cellule est le 01/01/202x.
Sur le même principe, on peut partir de la cellule Z1 pour le dernier mois de la période. Or, la valeur est le 01/12/202x. C’est pour cela qu’on va utiliser la fonction FIN.MOIS.
N’oublions pas que la fonction JOURS retranche 1 jour, il faudra donc en ajouter un dans la formule de calcul.
Au final, on obtient :
=JOURS(FIN.MOIS(Z1;0);D1)+1
Il est possible que le résultat affiche un format de cellule date. Passez le en Nombre sans décimales.
Concernant Repos et Férié, on va faire un décompte des termes dans notre calendrier. Pour cela, on utilise la fonction NB.SI avec la formule ci-dessous :
=NB.SI($C$4:$Z$34;$AB20)
On finit cette colonne avec les événements hors frais kilométriques. Or, La fonction FILTRE est utilisée pour la liste des types d’événements.
Je vais modifier le dernier argument pour rendre les décomptes dynamiques.
=NB.SI($C$4:$Z$34;$AB22#)
Ainsi, pour chaque nouvelle ligne, j’aurai le décompte du calendrier.
2e partie : événements pour calcul des indemnités
On va maintenant décompter les événements de notre feuille saisies qui sont impactés par le calcul des indemnités kilométriques.
Vous la connaissez maintenant puisqu’on l’a utilisé sur AB22.
Cela donne :
=FILTRE(Tabl_type_evt[Type];Tabl_type_evt[Frais]="O")
Pour leur calcul, on va appliquer la même méthode que pour les événements hors calcul des indemnités.
Calcul du kilométrage
On finit avec la dernière partie : le calcul des kilomètres. Pour cela il nous faut :
- Le nombre de jours pour lesquels on se rend à son lieu de travail.
- La distance entre votre logement et l’entreprise. L’Administration fiscale impose que la distance soit la plus courte (et non la plus rapide). De plus, elle ne peut excéder 40 kms aller.
Au sujet des libellés, vous pouvez les saisir à votre convenance. Pour le calcul du nombre de jours, je vais utiliser une simple soustraction et la fonction SOMME.
Informations nécessaires :
- Nombre annuel de jours
- Nombre de jours Férié et Repos
- Nombre de jours avec un événement sans déplacement à son lieu de travail
On obtient la formule :
=$AC$19-SOMME($AC$20:$AC$21;AF19#)
Pour la distance, il suffit de la saisir manuellement. La cellule AI20 ne contient donc aucune formule. Pour information, c’est la distance aller (non aller-retour) à saisir.
On va calculer la distance parcourue en AI21 via une fonction SI.
=SI(AI20>40;AI19*40*2;AI20*AI19*2)
Dorénavant, la distance parcourue est la valeur que l’on renseignera dans notre déclaration de revenus.
Elle sera calculée automatiquement si vous remplissez au fil de l’eau votre calendrier dynamique avec tous les événements survenus durant chaque année. Vous n’aurez plus à calculer manuellement chaque année ce chiffre.
Fichier Téléchargeable
Je rappelle que c’était un projet personnel pour obtenir un calendrier dynamique, utilisable sur plusieurs années, qui permet de déterminer les congés à prendre avant la fin de période prévue (mai ou décembre) et le kilométrage pour ma déclaration de revenus. Je l’utilise depuis quatre ans et je ne pourrais plus m’en passer.
J’espère qu’il vous soit utile. N’hésitez pas à me contacter si vous souhaitez proposer des améliorations.
