Calculer avec des dates dans Excel et les autres tableurs
Pour gagner du temps et éviter les erreurs dans vos calculs avec les dates, utilisez ces fonctions très pratiques et disponibles à la fois sur Microsoft Excel, Google Sheets, LibreOffice Calc et OnlyOffice Spreadsheets.
Manipuler des dates peut sembler être une opération triviale, que nous effectuons quotidiennement sans même y penser. Cependant, notre calendrier est un système plus retord qu'il n'y paraît. S'il n'est nul besoin de sortir une calculatrice pour compter le nombre de jours entre le 12 et le 28 mai, il arrive fréquemment d'être confronté à des calculs un peu plus pénibles. Combien de jours y a-t-il exactement entre le 8 février et 17 novembre ? Et de jours ouvrés uniquement entre ces deux dates ? Ou bien de jours calendaires, à l'exclusion des mercredis et des jours fériés par exemple ?
Heureusement, les outils informatiques peuvent grandement nous faciliter la tâche dans ce genre de cas, et les logiciels tableurs comme Microsoft Excel, Google Sheets, LibreOffice Calc ou OnlyOffice Spreadsheets, disposent tous de fonctions prêtes à l'emploi pour nous aider dans ces calculs. Parmi la multitude de fonctions disponibles dans ces logiciels pour effectuer des calculs sur les dates, nous en avons sélectionné quatre qui peuvent se révéler particulièrement utiles et vous faire gagner un temps considérable dans vos tâches personnelles ou professionnelles, en plus de vous éviter quelques nœuds au cerveau.
Par ailleurs, ces fonctions ont l'avantage d'exister à l'identique dans les quatre tableurs précités, qui sont les plus répandus et les plus utilisés. Leur syntaxe et leur fonctionnement sont les mêmes d'une application à l'autre, ce qui permet de les utiliser peu importe le logiciel à votre disposition.
Dans ce guide pratique, les captures d'écran ont été réalisées à partir de Microsoft Excel, mais toutes les fonctions ont également été testées sur Googles Sheets, LibreOffice Calc et OnlyOffice Spreadsheets.
Comment compter les jours entre deux dates avec Excel ?
Commençons par le cas de figure le plus simple : compter le nombre exact de jours calendaires qui sépare deux dates. Pour cela, vous pouvez utiliser la fonction =JOURS(date_fin;date_debut) :
- Dans la cellule A2, écrivez 01/01/2023
- Dans la cellule B2, écrivez 31/12/2023
- Dans la cellule C2, écrivez =JOURS(B2;A2)
Après avoir validé votre saisie en appuyant sur Entrée, la cellule C2 devrait afficher la valeur 364.
- Dans la cellule A3, écrivez 08/02/2023
- Dans la cellule B3, écrivez 17/11/2023
- Dans la cellule C3, écrivez =JOURS(B3;A3)
Après avoir validé votre saisie en appuyant sur Entrée, la cellule C3 devrait afficher la valeur 282.
Vous serez peut-être surpris par le résultat de la première formule, qui indique 364 jours entre le 1er janvier et le 31 décembre 2023, alors qu'on s'attend Intuitivement à obtenir un résultat de 365 jours. Cela vient de la manière dont Excel et la plupart des tableurs traitent les dates, qui sont stockés comme des numéros de série séquentiels : le 01/01/2023 correspond au numéro 44927 et le 31/12/2023 au numéro 45291. Pour compter le nombre de jours entre deux dates, la fonction JOURS() calcule l'écart entre le deuxième et le premier numéro, soit 45291 - 44927, dont le résultat est bien 364.
Pour remédier à cela, vous avez deux possibilités. Vous pouvez saisir une date de fin égale au lendemain du dernier jour de la période dont vous souhaitez compter le nombre de jour, le 01/01/2024 et le 18/11/2023 dans notre exemple :
Cette première méthode fonctionne, mais peut créer de la confusion lors de la saisie des données, par un autre utilisateur que vous-même par exemple, qui n'aurait pas en tête cette subtilité. L'autre solution est de modifier la formule de calcul pour tenir compte du dernier jour de la période dont vous souhaitez compter le nombre de jour :
- Dans la cellule C2, écrivez =JOURS(B2+1;A2)
- Dans la cellule C3, écrivez =JOURS(B3+1;A3)
Après avoir validé votre saisie en appuyant sur Entrée, les cellules C2 et C3 devraient désormais afficher 365 et 283, ce qui est plus conforme au résultat attendu dans la plupart des cas.
Vous pouvez également écrire les dates de début et de fin directement dans la fonction JOURS(), entre les parenthèses, mais cette pratique est déconseillée. Les valeurs écrites de cette façon seront stockées "en dur" dans la formule, et il sera nécessaire de la modifier manuellement pour changer le calcul, ce qui n'est pas pratique et source d'erreur, surtout dans le cas d'un grand tableau de dates à traiter.
Si vous souhaitez tout de même écrire les dates de début et de fin directement dans la fonction JOURS(), pour un calcul ponctuel par exemple, vous devez alors respecter une syntaxe particulière, en écrivant les valeurs de dates entre guillemets :
- Dans la cellule A2, écrivez =JOURS("31/12/2023";"01/01/2023")
- Dans la cellule A3, écrivez =JOURS("17/11/2023";"08/02/2023")
Après avoir validé votre saisie en appuyant sur Entrée, les cellules A2 et A3 devraient afficher 364 et 282.
N'omettez surtout pas les guillemets autour des dates, car la fonction JOURS() ne pourrait pas identifier ces valeurs comme des dates, et renverrait alors systématiquement le résultat 0.
Comment compter les jours ouvrés avec Excel ?
Il existe de nombreux cas où il est utile de compter le nombre de jours ouvrés entre deux dates, comme calculer un délai de livraison, de réponse ou le nombre de jours travaillés par exemple. Le problème est un peu plus complexe que précédemment, car il faut déduire certains jours du résultat, comme les week-ends, les jours fériés ou les jours chômés contractuellement, et ces jours peuvent varier en fonction des pays ou des situations.
Commençons par le cas de figure le plus courant, avec des week-ends composés du samedi et du dimanche, et les jours fériés en France de l'année 2023. Pour compter le nombre de jours ouvrés entre deux dates dans ces conditions, vous pouvez utiliser la fonction =NB.JOURS.OUVRES(date_départ;date_fin;[jours_fériés]).
Tout d'abord, vous remarquerez sûrement que l'ordre des arguments "date_départ" et "date_fin" est inversé dans la fonction NB.JOURS.OUVRES() par rapport à la fonction JOURS(), soyez donc vigilants lors de l'écriture de votre formule si vous utilisez régulièrement les deux fonctions.
Ensuite, la fonction NB.JOURS.OUVRES() contient un troisième argument, facultatif, qui s'appelle [jours_fériés]. Cet argument permet d'indiquer les jours fériés à exclure du calcul. Comme il est facultatif, vous pouvez ne rien indiquer à cet endroit, la fonction considèrera alors qu'il n'y aucun jours fériés sur la période.
Enfin, la fonction NB.JOURS.OUVRES() n'exclut pas le date de fin du nombre de jours ouvrés calculé, contrairement à la fonction JOURS(). Il ne sera donc pas nécessaire d'ajouter +1 à l'argument date_fin comme dans l'exemple précédent.
- Dans la cellule A2, écrivez 01/01/2023
- Dans la cellule B2, écrivez 31/01/2023
- Dans la cellule C2, écrivez =NB.JOURS.OUVRES(A2;B2)
Après avoir validé votre saisie en appuyant sur Entrée, la cellule C2 devrait afficher 22. Pour vous entraîner, vous pouvez remplir les lignes suivantes en indiquant le premier et le dernier jour de chaque mois de l'année 2023 dans les colonnes A et B, puis recopiez la formule de la cellule C2 sur les autres lignes, afin d'obtenir le résultat suivant :
Pour le moment, la fonction NB.JOURS.OUVRES() nous indique le nombre de jours entre deux dates, en déduisant uniquement les samedis et les dimanches. Pour déduire également les jours fériés, ou tout autre jour non ouvré, il faut les saisir dans un petit tableau à part qui servira de référence à la fonction.
- Dans la cellule E1, copiez-collez la liste suivante :
Jours fériés 2023
01/01/2023
10/04/2023
01/05/2023
08/05/2023
18/05/2023
29/05/2023
14/07/2023
15/08/2023
01/11/2023
11/11/2023
25/12/2023 - Dans la cellule C2, écrivez =NB.JOURS.OUVRES(A2;B2;$E$2:$E$12)
Faîtes bien attention aux symboles $ dans la formule ci-dessus, ils permettent d'indiquer que la plage de cellules E2:E12 est une référence absolue, ce qui vous permettra de copier-coller la formule n'importe-où sans risque de perdre le lien avec la liste des jours fériés. - Copiez-collez la formule de la cellule C2 dans les cellules C3 à C13.
Après avoir validé votre saisie, vous obtiendrez le résultat suivant :
La colonne C affiche désormais le nombre de jours ouvrés pour chaque mois de l'année 2023, donc sans les samedis, les dimanches et les jours fériés indiqués dans la colonne E. Si vous devez ajouter de nouveaux jours fériés dans votre liste, n'oubliez pas de mettre à jour la formule de calcul dans la colonne C, afin que les nouveaux éléments soient bien pris en compte par la fonction NB.JOURS.OUVRES.
Comment compter les jours ouvrés avec weekends personnalisés sur Excel ?
Si vous avez besoin de compter le nombre de jours ouvrés entre deux dates avec des jours de weekends ou de repos autres que le samedi ou le dimanche, vous devez alors utiliser la fonction NB.JOURS.OUVRES.INTL(date_depart;date_fin;[week-end];[jours_feries]). Elle fonctionne exactement de la même façon que la fonction NB.JOURS.OUVRES, mais permet en plus d'indiquer des jours de weekends personnalisés.
On retrouve les mêmes arguments que précédemment, avec la date de départ, la date de fin et les jours fériés, mais avec un nouvel argument appelé [week-end] en troisième position. C'est lui qui va permettre de définir des jours de weekends ou de repos personnalisés, autres que les classiques samedi et dimanche.
Sa syntaxe est un peu particulière : il doit être écrit sous la forme d'un code ou d'une chaîne de caractères qui représente les jours récurrents à exclure du nombre de jours à compter entre deux dates. Voici un tableau de correspondance entre les différents codes possibles et les jours déduits du comptage :
| CODE | JOURS DEDUITS DU COMPTAGE |
|
1 ou omis |
samedi, dimanche |
Pour illustrer le fonctionnement de la syntaxe avec code, voici quelques exemples :
- La formule =NB.JOURS.OUVRES.INTL(A2;B2;1;) renvoi le nombre de jours entre les dates contenues en A2 et B2, en déduisant les samedis et dimanches (code "1" en troisième position)
- La formule =NB.JOURS.OUVRES.INTL(A3;B3;2;) renvoi le nombre de jours entre les dates contenues en A2 et B2, en déduisant les dimanches et lundis (code "2" en troisième position)
- La formule =NB.JOURS.OUVRES.INTL(A4;B4;3;$E$2:$E$12) renvoi le nombre de jours entre les dates contenues en A2 et B2, en déduisant les lundis et mardis (code "3" en troisième position) et les jours fériés indiqués dans la plage de cellules E2:E12
Le système en code est pratique mais ne permet d'indiquer que des jours de weekends ou de repos consécutifs. Pour définir des jours non ouvrés et non consécutifs, comme le mercredi et le dimanche par exemple, il faut utiliser l'argument [week-end] avec la syntaxe sous forme de chaîne de caractère.
Dans ce cas, l'argument doit être écrit entre guillemets, comme un chaîne de sept caractères, qui peuvent prendre les valeurs 0 ou 1. Chaque caractère représente un jour de la semaine, le premier pour le lundi et le septième pour le dimanche. Un 0 indique que le jour en question est ouvré et un 1 que le jour n'est pas travaillé. Voici quelques exemples pour y voir plus clair :
- "0010001" signifie que le mercredi et le dimanche ne sont pas travaillés
- "1000010" signifie que le lundi et le samedi ne sont pas travaillés
- "1111111" signifie qu'aucun jour n'est travaillé"
- "0000000" signifie que tous les jours sont travaillés
Pour illustrer le fonctionnement de la syntaxe avec chaîne de caractère, voici quelques exemples :
- La formule =NB.JOURS.OUVRES.INTL(A2;B2;"0010001";$E$2:$E$12) renvoie le nombre de jours entre les dates contenues en A2 et B2, en déduisant les mercredis et les dimanches (chaîne "0010001") et les jours fériés indiqués dans la plage de cellules E2:E12
- La formule =NB.JOURS.OUVRES.INTL(A3;B3;"1000001";$E$2:$E$12) renvoie le nombre de jours entre les dates contenues en A3 et B3, en déduisant les lundis et les dimanches (chaîne "0010001") et les jours fériés indiqués dans la plage de cellules E2:E12
- La formule =NB.JOURS.OUVRES.INTL(A4;B4;"0000000";) renvoie le nombre de jours entre les dates contenues en A4 et B4, sans déduire aucun jour de repos (chaîne "0000000") et sans aucun jour férié
- La formule =NB.JOURS.OUVRES.INTL(A5;B5;"1111111";) renvoie le nombre de jours entre les dates contenues en A5 et B5, en déduisant tous les jours de la semaine (chaîne "1111111")
Au premier abord, la syntaxe en chaîne de caractères de l'argument [week-end] est plus déroutante que celle en code, mais elle se montre beaucoup plus puissante et flexible à l'usage, car elle permet de définir n'importe quel ensemble de jours de repos, qu'ils soient consécutifs ou non et peu importe leur nombre dans la semaine.
Comment compter les jours avec des mois de 30 jours sur Excel ?
Les mois de notre calendrier ne sont pas réguliers, certains comportent 30 jours, d'autres 31, et l'un d'entre eux a même la facheuse tendance à en compter 28 ou 29 selon les années. Pour contourner cette irrégularité, de nombreux systèmes s'appuient sur un calendrier "virtuel" composés de mois d'une durée égale : les salaires, les abonnements ou les loyers par exemple sont calculés sur une base de mois à 30 jours. C'est pour cela que vous payez votre forfait téléphonique le même montant en janvier et en février.
Pour compter le nombre de jours entre deux date sur une base de mois à 30 jours dans Excel, vous pouvez utiliser la fonction JOURS360. Sa syntaxe est la suivante =JOURS360(date_début;date_fin;[méthode]) et comporte trois arguments :
- date_début et date_fin, qui sont obligatoires et représentent les dates de début et de fin de la période dont vous souhaitez compter le nombre de jour. Ils peuvent contenir des dates écrites directement ou des références à des cellules contenant des dates.
- [méthode], qui est facultatif et indique si l'on utilise la méthode américaine ou européenne pour les calculs avec des mois à 30 jours. Si cet argument est vide ou égal à FAUX, c'est la méthode américaine qui est retenue, si il est égale à VRAI, c'est la méthode européenne.
Pour information, voici la définition de chaque méthode donnée par l'aide intégrée à Excel :
- Méthode américaine (FAUX ou omis) : Méthode États-Unis (NASD). Si la date de début est le dernier jour du mois, la date de début devient le 30 du même mois. Si la date de fin est le dernier jour du mois et que la date de début est avant le 30 du mois, la date de fin devient le 1er du mois suivant ; sinon, la date de fin devient le 30 du même mois.
- Méthode européenne : Méthode européenne. Les dates de début et de fin correspondant au 31 du mois deviennent le 30 du même mois.
Dans les faits, il est probable que vous utilisiez toujours la méthode européenne pour vos calculs, veillez donc à toujours renseigner l'argument [méthode] avec la valeur VRAI. En conséquence, vos formules devraient toujours ressembler à cela : =JOURS360("01/01/2023";"31/12/2023";VRAI) ou =JOURS360(A2;B2;VRAI)
Par ailleurs, notez bien que la fonction JOURS360 exclut la date de fin de la période du calcul du nombre de jours , il faut donc indiquer une date de fin au lendemain de la période voulue ou ajouter +1 à l'argument date_fin.
Voici quelques exemples pour appréhender le fonctionnement de la fonction JOURS360 :
- Dans n'importe quelle cellule, écrivez =JOURS360("01/01/2023";"31/12/2023";VRAI). Le résultat de cette formule est 359.
- Dans n'importe quelle cellule, écrivez =JOURS360("01/01/2023";"31/12/2023"+1;VRAI). Le résultat de cette formule est 360.
- Ecrivez 01/01/2023 dans la cellule A4, 31/01/2023 dans la cellule B4, puis =JOURS360(A4;B4;VRAI) dans la cellule C4. Le résultat de cette formule est 29.
- Ecrivez 01/01/2023 dans la cellule A5, 31/01/2023 dans la cellule B5, puis =JOURS360(A5;B5+1;VRAI) dans la cellule C5. Le résultat de cette formule est 30.
- Ecrivez 01/02/2023 dans la cellule A6, 28/02/2023 dans la cellule B6, puis =JOURS360(A6;B6;VRAI) dans la cellule C6. Le résultat de cette formule est 27.
- Ecrivez 01/02/2023 dans la cellule A7, 28/02/2023 dans la cellule B7, puis =JOURS360(A7;B7+1;VRAI) dans la cellule C7. Le résultat de cette formule est 30.
- Ecrivez 15/01/2023 dans la cellule A8, 28/02/2023 dans la cellule B8, puis =JOURS360(A8;B8+1;VRAI) dans la cellule C8. Le résultat de cette formule est 46.
Ces exemples illustrent bien le mode de calcul particulier de la fonction JOURS360. Pour le mois de janvier, le calcul du nombre de jours sur un mois complet donne comme résultat 30 et non 31. Inversement, pour le mois de février, la fonction retourne un résultat de 30 jours au lieu de 28 pour le mois complet.