Tutoriel Excel : Calcul de date et heure (calculer la différence, l'âge, ajouter/soustraire)
Dans Excel, le calcul de date et d'heure est utilisé fréquemment, par exemple pour calculer la différence entre deux dates/heures, ajouter ou soustraire des valeurs de date et d'heure, obtenir un âge en fonction de la date de naissance donnée, etc. Dans ce tutoriel, il répertorie presque tous les scénarios liés au calcul de date et d'heure et fournit les méthodes correspondantes pour vous aider.
Dans ce tutoriel, je crée quelques exemples pour expliquer les méthodes. Vous pouvez modifier les références selon vos besoins lorsque vous utilisez le code VBA ou les formules ci-dessous.
1. Calculer la différence entre deux dates/heures
Calculer la différence entre deux dates ou deux heures peut être le problème le plus courant de calcul de date et d'heure que vous rencontrez dans votre travail quotidien sur Excel. Les exemples fournis ci-dessous peuvent vous aider à améliorer l'efficacité lorsque vous êtes confronté aux mêmes problèmes.
1.11 Calculer la différence entre deux dates en jours/mois/années/semaines
La fonction DATEDIF d'Excel peut être utilisée pour calculer rapidement la différence entre deux dates en jours, mois, années et semaines.
Cliquez pour plus de détails sur la fonction DATEDIF
Différence en jours entre deux dates
Pour obtenir la différence de jours entre deux dates dans les cellules A2 et B2, veuillez utiliser la formule suivante :
=DATEDIF(A2,B2,"d")
Appuyez sur Entrer touche pour obtenir le résultat.
Différence en mois entre deux dates
Pour obtenir la différence en mois entre deux dates dans les cellules A5 et B5, veuillez utiliser la formule suivante :
=DATEDIF(A5,B5,"m")
Appuyez sur Entrer clé pour obtenir le résultat.
Différence en années entre deux dates
Pour obtenir la différence en années entre deux dates dans les cellules A8 et B8, veuillez utiliser la formule suivante :
=DATEDIF(A8,B8,"y")
Appuyez sur Entrer touche pour obtenir le résultat.
Différence en semaines entre deux dates
Pour obtenir la différence en semaines entre deux dates dans les cellules A11 et B11, veuillez utiliser la formule suivante :
=DATEDIF(A11,B11,"d")/7
Appuyez sur Entrer clé pour obtenir le résultat.
Remarque :
1) Lorsque vous utilisez la formule ci-dessus pour obtenir la différence en semaines, le résultat peut parfois s'afficher au format date. Vous devez alors formater le résultat en général ou en nombre selon vos besoins.
2) Lorsque vous utilisez la formule ci-dessus pour obtenir la différence en semaines, le résultat peut être un nombre décimal. Si vous souhaitez obtenir le nombre de semaines en entier, vous pouvez ajouter la fonction ARRONDI.INF comme indiqué ci-dessous pour obtenir la différence en semaines entières :
=ARRONDI.INF(DATEDIF(A11;B11;"j")/7;0)
1.12 Calculer les mois en ignorant les années et les jours entre deux dates
Si vous souhaitez simplement calculer la différence en mois en ignorant les années et les jours entre deux dates, comme le montre la capture d'écran ci-dessous, voici une formule qui peut vous aider.
=DATEDIF(A2,B2,"ym")
Appuyez sur Entrer clé pour obtenir le résultat.
A2 est la date de début et B2 est la date de fin.
1.13 Calculer les jours en ignorant les années et les mois entre deux dates
Si vous souhaitez simplement calculer la différence en jours en ignorant les années et les mois entre deux dates, comme le montre la capture d'écran ci-dessous, voici une formule qui peut vous aider.
=DATEDIF(A5,B5,"md")
Appuyez sur Entrer clé pour obtenir le résultat.
A5 est la date de début et B5 est la date de fin.
1.14 Calculez la différence entre deux dates et retournez les années, les mois et les jours
Si vous souhaitez obtenir la différence entre deux dates et retourner xx années, xx mois, et xx jours comme le montre la capture d'écran ci-dessous, voici également une formule qui est proposée.
=DATEDIF(A8, B8, "y") & " années, " & DATEDIF(A8, B8, "ym") & " mois, " & DATEDIF(A8, B8, "md") & " jours"
Appuyez sur Entrer touche pour obtenir le résultat.
A8 est la date de début et B8 est la date de fin.
1.15 Calculer la différence entre une date et aujourd'hui
Pour calculer automatiquement la différence entre une date et aujourd'hui, il suffit de remplacer la date_de_fin dans les formules ci-dessus par AUJOURDHUI(). Ici, prenons l'exemple du calcul de la différence en jours entre une date passée et aujourd'hui.
=DATEDIF(A11,AUJOURDHUI(),"j")
Appuyez sur la touche Entrée pour obtenir le résultat.
Remarque : si vous souhaitez calculer la différence entre une date future et aujourd'hui, changez la date_de_début à aujourd'hui et prenez la date future comme date_de_fin de cette manière :
=DATEDIF(AUJOURDHUI(),A14,"j")
Notez que la date_de_début doit être inférieure à la date_de_fin dans la fonction DATEDIF, sinon, elle retournera la valeur d'erreur #NUM!.
1.16 Calculer les jours ouvrables avec ou sans jour férié entre deux dates
Parfois, vous pouvez avoir besoin de compter le nombre de jours ouvrables avec ou sans les jours fériés entre deux dates données.
Dans cette partie, vous utiliserez la fonction NETWORKDAYS.INTL :
Cliquez sur NETWORKDAYS.INTL pour en connaître ses arguments et son utilisation.
Compter les jours ouvrables avec les jours fériés
Pour compter les jours ouvrables avec jours fériés entre deux dates dans les cellules A2 et B2, veuillez utiliser la formule suivante :
=JOURS.OUVRES.INTL(A2,B2)
Appuyez sur Entrer clé pour obtenir le résultat.
Compter les jours ouvrables sans jours fériés
Pour compter les jours ouvrables avec jours fériés entre deux dates dans les cellules A2 et B2, tout en excluant les jours fériés de la plage D5:D9, veuillez utiliser la formule suivante :
=JOURS.OUVRES.INTL(A5,B5,1,D5:D9)
Appuyez sur Entrer touche pour obtenir le résultat.
Remarque :
Dans les formules ci-dessus, elles considèrent le samedi et le dimanche comme le week-end. Si vos jours de week-end sont différents, veuillez modifier l'argument [weekend] selon vos besoins.
1.17 Calculer les week-ends entre deux dates
Si vous souhaitez compter le nombre de week-ends entre deux dates, les fonctions SOMMEPROD ou SOMME peuvent vous rendre service.
Pour compter les week-ends (samedi et dimanche) entre deux dates dans les cellules A12 et B12 :
=SOMMEPROD(--(JOURSEM(LIGNE(INDIRECT(A12&":"&B12));2)>5))
Ou
=SOMME(ENT((JOURSEM(A12-{1,7})+B12-A12)/7))
Appuyez sur Entrer clé pour obtenir le résultat.
1.18 Calculer un jour de la semaine spécifique entre deux dates
Pour compter le nombre d'un jour de la semaine spécifique, comme lundi, entre deux dates, la combinaison des fonctions ENT et JOUR.SEM peut vous aider.
Les cellules A15 et B15 sont les deux dates entre lesquelles vous souhaitez compter les lundis. Veuillez utiliser la formule suivante :
=ENT((JOUR.SEM(A15-2)-A15+B15)/7)
Appuyez sur Entrer clé pour obtenir le résultat.
Modifiez le nombre du jour de la semaine dans la fonction JOUR DE LA SEMAINE pour compter un jour de la semaine différent :
1 est Dimanche, 2 est Lundi, 3 est Mardi, 4 est Mercredi, 5 est Jeudi, 6 est Vendredi et 7 est Samedi)
1.19 Calculer les jours restants dans le mois/année
Parfois, vous pouvez vouloir connaître les jours restants dans le mois ou l'année en fonction de la date fournie, comme le montre la capture d'écran ci-dessous :
Obtenir les jours restants dans le mois en cours
Cliquez sur EOMONTH pour connaître les arguments et l'utilisation.
Pour obtenir les jours restants du mois en cours dans la cellule A2, veuillez utiliser la formule suivante :
=FIN.MOIS(A2;0)-A2
Appuyez sur Entrer touche, et faites glisser la poignée de remplissage automatique pour appliquer cette formule à d'autres cellules si nécessaire.
Astuce : les résultats peuvent être affichés au format de date, il suffit de les changer en format général ou numérique.
Obtenir les jours restants dans l'année en cours
Pour obtenir les jours restants de l'année en cours dans la cellule A2, veuillez utiliser la formule suivante :
=DATE(ANNEE(A2),12,31)-A2
Appuyez sur Entrer touche, et faites glisser la poignée de remplissage automatique pour appliquer cette formule à d'autres cellules si nécessaire.
1.21 Calculer la différence entre deux heures
Pour obtenir la différence entre deux heures, voici deux formules simples qui peuvent vous aider.
Supposons que les cellules A2 et B2 contiennent respectivement l'heure de début et l'heure de fin, en utilisant les formules suivantes :
=B2-A2
=TEXTE(B2-A2,"hh:mm:ss")
Appuyez sur Entrer touche pour obtenir le résultat.
Remarque :
- Si vous utilisez end_time-start_time, vous pouvez formater le résultat dans un autre format d'heure selon vos besoins dans la boîte de dialogue Format de cellule.
- Si vous utilisez TEXT(heure_fin-heure_début,"format_heure"), entrez le format d'heure dans lequel vous souhaitez que le résultat soit affiché dans la formule, par exemple TEXT(heure_fin-heure_début,"h") renvoie 16.
- Si l'heure de fin est inférieure à l'heure de début, les deux formules renvoient des valeurs d'erreur. Pour résoudre ce problème, vous pouvez ajouter ABS au début de ces formules, par exemple ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")), puis formater le résultat en tant qu'heure.
1.22 Calculer la différence entre deux heures en heures/minutes/secondes
Si vous souhaitez calculer la différence entre deux heures en heures, minutes ou secondes comme le montre la capture d'écran ci-dessous, veuillez suivre cette partie.
Obtenir la différence en heures entre deux horaires
Pour obtenir la différence en heures entre deux horaires dans A5 et B5, veuillez utiliser la formule suivante :
=ENT((B5-A5)*24)
Appuyez sur Entrer touche, puis formater le résultat du format d'heure en général ou en nombre.
Si vous souhaitez obtenir la différence en heures décimales, utilisez (heure_fin - heure_début)*24.
Obtenir la différence en minutes entre deux heures
Pour obtenir la différence en minutes entre deux heures dans A8 et B8, veuillez utiliser la formule suivante :
=ENT((B8-A8)*1440)
Appuyez sur Entrer touche, puis formatez le résultat du format d'heure en général ou en nombre.
Si vous souhaitez obtenir la différence en minutes décimales, utilisez (heure_fin - heure_début) * 1440.
Obtenir la différence en secondes entre deux heures
Pour obtenir la différence en secondes entre deux heures dans A5 et B5, veuillez utiliser la formule suivante :
=(B11-A11)*86400)
Appuyez sur Entrer touche, puis formatez le résultat du format d'heure en général ou en nombre.
1.23 Calculer la différence d'heures uniquement entre deux horaires (ne pas dépasser 24 heures)
Si la différence entre deux heures ne dépasse pas 24 heures, la fonction HEURE peut rapidement obtenir la différence en heures entre ces deux horaires.
Cliquez sur HEURE pour plus de détails sur cette fonction.
Pour obtenir la différence en heures entre les heures dans les cellules A14 et B14, veuillez utiliser la fonction HEURE comme ceci :
=HEURE(B14-A14)
Appuyez sur Entrer touche pour obtenir le résultat.
L'heure_de_début doit être inférieure à l'heure_de_fin, sinon, la formule renvoie la valeur d'erreur #NOMBRE!.
1.24 Calculer la différence en minutes uniquement entre deux heures (ne pas dépasser 60 minutes)
La fonction MINUTE peut rapidement obtenir la différence en minutes uniquement entre ces deux heures, en ignorant les heures et les secondes.
Cliquez sur MINUTE pour plus de détails sur cette fonction.
Pour obtenir uniquement la différence en minutes entre les heures dans les cellules A17 et B17, veuillez utiliser la fonction MINUTE comme ceci :
=MINUTE(B17-A17)
Appuyez sur Entrer clé pour obtenir le résultat.
L'heure_de_début doit être inférieure à l'heure_de_fin, sinon la formule renvoie la valeur d'erreur #NOMBRE!.
1.25 Calculez uniquement la différence en secondes entre deux heures (ne dépassant pas 60 secondes)
La fonction SECONDE peut rapidement obtenir la différence en secondes entre ces deux heures, en ignorant les heures et les minutes.
Cliquez sur SECOND pour plus de détails sur cette fonction.
Pour obtenir uniquement la différence en secondes entre les heures dans les cellules A20 et B20, veuillez utiliser la fonction SECONDE comme ceci :
=SECONDE(B20-A20)
Appuyez sur Entrer clé pour obtenir le résultat.
L'heure_de_début doit être inférieure à l'heure_de_fin, sinon la formule renvoie la valeur d'erreur #NOMBRE!.
1.26 Calculer la différence entre deux heures et retourner les heures, minutes, secondes
Si vous souhaitez afficher la différence entre deux heures au format xx heures xx minutes xx secondes, veuillez utiliser la fonction TEXTE comme indiqué ci-dessous :
Cliquez sur TEXT pour découvrir les arguments et l'utilisation de cette fonction.
Pour calculer la différence entre les heures dans les cellules A23 et B23, utilisez la formule suivante :
=TEXTE(B23-A23,"h"" heures ""m"" minutes ""s"" secondes""").
Appuyez Entrer clé pour obtenir le résultat.
Remarque :
Cette formule calcule également uniquement la différence en heures ne dépassant pas 24 heures, et l'heure de fin doit être supérieure à l'heure de début, sinon elle renvoie la valeur d'erreur #VALEUR!.
1.27 Calculer la différence entre deux dates et heures
S'il y a deux horaires au format mm/jj/aaaa hh:mm:ss, pour calculer la différence entre eux, vous pouvez utiliser l'une des formules ci-dessous selon vos besoins.
Obtenir la différence de temps entre deux dates et heures et retourner le résultat au format hh:mm:ss
Prenons deux dates-heures dans les cellules A2 et B2 comme exemple, veuillez utiliser la formule suivante :
=B2-A2
Appuyez sur Entrer clé, en renvoyant un résultat au format datetime, puis formater ce résultat comme [h]:mm:ss dans la catégorie personnalisée sous le Nombre onglet dans Format de cellule boîte de dialogue.
Obtenir la différence entre deux dates et heures et retourner les jours, heures, minutes, secondes
Prenons deux dates-heures dans les cellules A5 et B5 comme exemple, veuillez utiliser la formule suivante :
=ENT(B5-A5) & " Jours, " & HEURE(B5-A5) & " Heures, " & MINUTE(B5-A5) & " Minutes, " & SECONDE(B5-A5) & " Secondes "
Appuyez sur Entrer clé pour obtenir le résultat.
Remarque : dans les deux formules, end_datetime doit être supérieur à start_datetime, sinon les formules renvoient des valeurs d'erreur.
1.28 Calculer la différence de temps avec des millisecondes
Tout d'abord, vous devez savoir comment formater la cellule pour afficher les millisecondes :
Sélectionnez les cellules dans lesquelles vous souhaitez afficher les millisecondes, puis faites un clic droit pour sélectionner. Format de cellule pour activer le Format de cellule boîte de dialogue, sélectionner Personnalisé dans le Catégorie liste sous l'onglet Nombre, et tapez ceci hh:mm:ss.000 dans la zone de texte.
Utiliser la formule :
Pour calculer la différence entre deux heures dans les cellules A8 et B8, utilisez la formule suivante :
=ABS(B8-A8)
Appuyez sur Entrer clé pour obtenir le résultat.
1.29 Calculer les heures de travail entre deux dates, hors week-ends
Parfois, vous pouvez avoir besoin de calculer les heures de travail entre deux dates, en excluant les week-ends (samedi et dimanche).
Ici, les heures de travail sont fixées à 8 heures par jour, et pour calculer les heures de travail entre deux dates fournies dans les cellules A16 et B16, veuillez utiliser la formule suivante :
=JOURS.OUVRES(A16,B16) * 8
Appuyez sur Entrer clé, puis formatez le résultat en tant que général ou nombre.
Pour plus d'exemples sur le calcul des heures de travail entre deux dates, veuillez consulter Obtenir les heures de travail entre deux dates dans Excel
Si vous avez installé Kutools pour Excel dans Excel, 90 % des calculs de différence de date et d'heure peuvent être résolus rapidement sans avoir à mémoriser aucune formule.
1.31 Calculez la différence entre deux dates et heures à l'aide de l'Assistant Date et Heure
Pour calculer la différence entre deux dates et heures dans Excel, il suffit de Assistant de date et heure suffit.
1. Sélectionnez une cellule où vous souhaitez placer le résultat calculé, puis cliquez Kutools > Assistant de formule > Assistant de date et heure.
2. Dans la boîte de dialogue Date et heure Assistant qui apparaît, suivez les paramètres ci-dessous :
- Cochez l'option Différence ;
- Sélectionnez la date et l'heure de début et la date et l'heure de fin dans la section Entrée d'argument, vous pouvez également entrer manuellement la date et l'heure directement dans la zone de saisie, ou cliquer sur l'icône du calendrier pour sélectionner la date.
- Sélectionnez le type de résultat de sortie dans la liste déroulante ;
- Aperçu du résultat dans la section Résultat.
3. Cliquez sur OK. Le résultat calculé est affiché, puis faites glisser la poignée de recopie automatique sur les cellules que vous devez également calculer.
Astuce :
Si vous souhaitez obtenir la différence entre deux dates et heures et afficher le résultat en jours, heures et minutes avec Kutools pour Excel, veuillez procéder comme suit :
Sélectionnez une cellule où vous souhaitez placer le résultat, puis cliquez Kutools > Assistant de formule > Date et heure > Compter les jours, heures et minutes entre deux dates.
Ensuite, dans le Assistant de formule dans la boîte de dialogue, spécifiez la date de début et la date de fin, puis cliquez D'accord.
Et le résultat de la différence sera affiché en jours, heures et minutes.
Cliquez sur Date et heure Assistant pour en savoir plus sur l'utilisation de cette fonctionnalité.
Cliquez sur Kutools pour Excel pour découvrir toutes les fonctionnalités de ce complément.
Cliquez sur Téléchargement gratuit pour obtenir un essai gratuit de 30 jours de Kutools pour Excel
Si vous souhaitez compter rapidement les week-ends, les jours ouvrables ou un jour de la semaine spécifique entre deux dates, Kutools pour Excel’s Assistant de formule Le groupe peut vous aider.
1. Sélectionnez la cellule où le résultat calculé sera placé, puis cliquez Kutools > Compter > Nombre de jours non ouvrables entre deux dates/Nombre de jours ouvrables entre deux dates/Nombre de jours de la semaine entre deux dates.
2. Dans la boîte de dialogue Formules Helper qui apparaît, spécifiez la date de début et la date de fin. Si vous appliquez le comptage du nombre de jours spécifiques de la semaine, vous devez également spécifier le jour de la semaine.
Pour compter le jour de la semaine spécifique, vous pouvez vous référer à la note pour utiliser 1-7 afin d'indiquer Dimanche-Samedi.
3. Cliquez sur OK, puis faites glisser la poignée de recopie automatique sur les cellules où vous devez compter le nombre de week-ends/jours ouvrables/jours de la semaine spécifiques, si nécessaire.
Cliquez sur Kutools pour Excel pour découvrir toutes les fonctionnalités de ce complément.
Cliquez sur Téléchargement Gratuit pour obtenir un essai gratuit de 30 jours de Kutools pour Excel
2. Ajouter ou soustraire des dates et heures
Outre le calcul de la différence entre deux dates et heures, l'ajout ou la soustraction est également un calcul de date et d'heure courant dans Excel. Par exemple, vous pourriez vouloir obtenir la date d'échéance en fonction de la date de production et du nombre de jours de conservation d'un produit.
2.11 Ajouter ou soustraire des jours à une date
Pour ajouter ou soustraire un nombre spécifique de jours à une date, voici deux méthodes différentes.
Supposons que vous souhaitiez ajouter 21 jours à une date dans la cellule A2, veuillez choisir l'une des méthodes ci-dessous pour résoudre ce problème.
Méthode 1 date+jours
Sélectionnez une cellule et tapez la formule :
=A+21
Appuyez sur Entrer clé pour obtenir le résultat.
Si vous souhaitez soustraire 21 jours, il suffit de remplacer le signe plus (+) par le signe moins (-).
Méthode 2 Collage spécial
1. Tapez le nombre de jours que vous souhaitez ajouter dans une cellule, par exemple en C2, puis appuyez sur Ctrl + C pour la copier.
2. Ensuite, sélectionnez les dates auxquelles vous souhaitez ajouter 21 jours, faites un clic droit pour afficher le menu contextuel, et sélectionnez Collage spécial....
3. Dans le Collage spécial boîte de dialogue, vérifier Ajouter option (Si vous souhaitez soustraire des jours, cochez Soustraire option). Cliquez OK.
4. Maintenant, les dates d'origine sont converties en nombres à 5 chiffres, appliquez-leur un format de date.
2.12 Ajouter ou soustraire des mois à une date
Pour ajouter ou soustraire des mois à une date, la fonction EDATE peut être utilisée.
Cliquez sur EDATE pour étudier ses arguments et son utilisation.
Supposons que vous souhaitiez ajouter 6 mois à la date dans la cellule A2, utilisez la formule suivante :
=EDATE(A2,6)
Appuyez sur Entrer touche pour obtenir le résultat.
Si vous souhaitez soustraire 6 mois à la date, changez 6 en -6.
2.13 Ajouter ou soustraire des années à une date
Pour ajouter ou soustraire n années à une date, une formule combinant les fonctions DATE, ANNÉE, MOIS et JOUR peut être utilisée.
Supposons que vous souhaitiez ajouter 3 ans à la date dans la cellule A2, utilisez la formule comme suit :
=DATE(ANNÉE(A2) + 3, MOIS(A2), JOUR(A2))
Appuyez sur Entrer clé pour obtenir le résultat.
Si vous souhaitez soustraire 3 ans à la date, changez 3 en -3.
2.14 Ajouter ou soustraire des semaines à une date
Pour ajouter ou soustraire des semaines à une date, la formule générale est
Supposons que vous souhaitiez ajouter 4 semaines à la date dans la cellule A2, utilisez la formule comme suit :
=A2+4*7
Appuyez sur Entrer clé pour obtenir le résultat.
Si vous souhaitez soustraire 4 semaines à la date, remplacez le signe plus (+) par le signe moins (-).
2.15 Ajouter ou soustraire des jours ouvrables, y compris ou excluant les jours fériés
Dans cette section, il est expliqué comment utiliser la fonction JOUR.OUVRE pour ajouter ou soustraire des jours ouvrables à une date donnée, en excluant les jours fériés ou en les incluant.
Visitez WORKDAY pour en savoir plus sur ses arguments et son utilisation.
Ajouter des jours ouvrables, y compris les jours fériés
Dans la cellule A2 se trouve la date que vous utilisez, et la cellule B2 contient le nombre de jours que vous souhaitez ajouter. Veuillez utiliser la formule suivante :
=JOUR.OUVRE(A2,B2)
Appuyez sur Entrer clé pour obtenir le résultat.
Ajouter des jours ouvrables en excluant les jours fériés
Dans la cellule A5 se trouve la date que vous utilisez, la cellule B5 contient le nombre de jours que vous souhaitez ajouter, et la plage D5:D8 liste les jours fériés. Veuillez utiliser la formule suivante :
=JOUR.OUVRE(A5,B5,D5:D8)
Appuyez sur Entrer touche pour obtenir le résultat.
Remarque :
La fonction JOUR.OUVRE considère le samedi et le dimanche comme les jours du week-end. Si vos week-ends tombent le samedi et le dimanche, vous pouvez appliquer la fonction JOUR.OUVRE.INTL, qui permet de spécifier les jours du week-end.
Visiter JOUR.OUVRE.INTL pour plus de détails.
Si vous souhaitez soustraire des jours ouvrables à une date, il suffit de changer le nombre de jours en négatif dans la formule.
2.16 Ajouter ou soustraire une année, un mois, des jours spécifiques à une date
Si vous souhaitez ajouter une année, un mois ou des jours spécifiques à une date, la formule qui combine les fonctions DATE, ANNÉE, MOIS et JOURS peut vous aider.
Pour ajouter 1 an, 2 mois et 30 jours à une date en A11, veuillez utiliser la formule suivante :
=DATE(ANNÉE(A11)+1,MOIS(A11)+2,JOUR(A11)+30)
Appuyez sur Entrer clé pour obtenir le résultat.
Si vous souhaitez soustraire, remplacez tous les signes plus (+) par des signes moins (-).
2.21 Ajouter ou soustraire des heures/minutes/secondes à une date et heure
Voici quelques formules pour ajouter ou soustraire des heures, minutes ou secondes à une date et heure.
Ajouter ou soustraire des heures à une date et heure
Supposons que vous souhaitiez ajouter 3 heures à une date et heure (cela peut également être une heure) dans la cellule A2, veuillez utiliser la formule suivante :
=A2+3/24
Appuyez sur Entrer clé pour obtenir le résultat.
Ajouter ou soustraire des minutes à une date et heure
Supposons que vous souhaitiez ajouter 15 minutes à une date et heure (cela peut également être une heure) dans la cellule A5, veuillez utiliser la formule suivante :
=A2+15/1440
Appuyez Entrer touche pour obtenir le résultat.
Ajouter ou soustraire des secondes à une date et heure
Supposons que vous souhaitiez ajouter 20 secondes à une date et heure (cela peut également être une heure) dans la cellule A8, veuillez utiliser la formule suivante :
=A2+20/86400
Appuyez sur Entrer clé pour obtenir le résultat.
2.22 Somme des heures au-delà de 24 heures
Supposons qu'il y ait un tableau Excel enregistrant le temps de travail de tous les employés sur une semaine, pour additionner le temps total de travail afin de calculer les paiements, vous pouvez utiliser SOMME(plage) pour obtenir le résultat. Cependant, en général, le résultat additionné sera affiché sous forme d'une durée ne dépassant pas 24 heures, comme le montre la capture d'écran ci-dessous. Comment pouvez-vous obtenir le résultat correct ?
En fait, il vous suffit de formater le résultat en [hh]:mm:ss.
Faites un clic droit sur la cellule de résultat, puis choisissez Format de cellules dans le menu contextuel, et dans la fenêtre qui s'affiche Format de cellule boîte de dialogue, choisir Personnalisé à partir de la liste noire, et tapez [hh]:mm:ss dans la zone de texte dans la section de droite, cliquez OK.
Le résultat additionné sera affiché correctement.
2.23 Ajouter des heures de travail à une date en excluant le week-end et les jours fériés
Voici une formule longue pour obtenir la date de fin en ajoutant un nombre spécifique d'heures ouvrables à une date de début, tout en excluant les week-ends (samedi et dimanche) ainsi que les jours fériés.
Dans un tableau Excel, la cellule A11 contient la date et l'heure de début, et la cellule B11 contient les heures de travail. Les cellules E11 et E13 indiquent respectivement les heures de début et de fin du travail, et la cellule E15 contient le jour férié qui sera exclu.
Veuillez utiliser la formule comme ceci :
=JOUR.OUVRE(A11;ENT(B11/8)+SI(HEURE(A11)+MINUTE(A11)/1440+SECONDE(A11)/86400+HEURE(MOD(B11;8))+MINUTE(MOD(MOD(B11;8);1)*60)/1440> $E$13;1;0);$E$15)+SI(HEURE(A11)+MINUTE(A11)/1440+SECONDE(A11)/86400+HEURE(MOD(B11;8))+MINUTE(MOD(MOD(B11;8);1)*60)/1440>$E$13;$E$11 +HEURE(A11)+MINUTE(A11)/1440+SECONDE(A11)/86400+HEURE(MOD(B11;8))+MINUTE(MOD(MOD(B11;8);1)*60)/1440-$E$13;HEURE(A11)+MINUTE(A11)/1440+SECONDE(A11)/86400 +HEURE(MOD(B11;8))+MINUTE(MOD(MOD(B11;8);1)*60)/1440)
Appuyez sur Entrer clé pour obtenir le résultat.
Si vous avez installé Kutools pour Excel, un seul outil – Assistant Date et Heurer peut résoudre la plupart des calculs sur l'ajout et la soustraction de datetime.
1. Cliquez sur une cellule où vous souhaitez afficher le résultat, puis appliquez cet outil en cliquant Kutools > Assistant de formule > Assistant de date et heure.
2. Dans la boîte de dialogue Assistant Date et heure, cochez l'option Ajouter ou Soustraire selon vos besoins, puis choisissez la cellule ou tapez directement la date et l'heure que vous souhaitez utiliser dans la section Entrée d'argument, puis spécifiez les années, mois, semaines, jours, heures, minutes et secondes que vous souhaitez ajouter ou soustraire, puis cliquez sur OK. Voir capture d'écran :
Vous pouvez prévisualiser le résultat calculé dans le Résultat section.
Le résultat est maintenant affiché, faites glisser la poignée automatique sur d'autres cellules pour obtenir les résultats.
Cliquez sur Date et heure Assistant pour en savoir plus sur l'utilisation de cette fonctionnalité.
Cliquez sur Kutools pour Excel pour découvrir toutes les fonctionnalités de ce complément.
Cliquez sur Téléchargement gratuit pour obtenir un essai gratuit de 30 jours de Kutools pour Excel
2.41 Vérifier ou mettre en évidence si une date est expirée
S'il y a une liste de dates d'expiration de produits, vous souhaiterez peut-être vérifier et mettre en évidence les dates qui sont expirées en se basant sur la date d'aujourd'hui, comme le montre la capture d'écran ci-dessous.
En fait, la mise en forme conditionnelle peut rapidement gérer cette tâche.
1. Sélectionnez les dates que vous souhaitez vérifier, puis cliquez Accueil > Mise en forme conditionnelle > Nouvelle règle.
2. Dans le Nouvelle règle de mise en forme boîte de dialogue, sélectionner Utiliser une formule pour déterminer les cellules à formater dans le Sélectionnez un type de règle section, et tapez =B2
2.42 Retourne la fin du mois en cours/premier jour du mois suivant
Les dates d'expiration de certains produits se situent à la fin du mois de production ou le premier jour du mois suivant la production. Pour lister rapidement les dates d'expiration en fonction de la date de production, veuillez suivre cette partie.
Obtenir la fin du mois en cours
Voici une date de production dans la cellule B13, veuillez utiliser la formule comme suit :
=FIN.MOIS(B13,0)
Appuyez sur Entrer clé pour obtenir le résultat.
Obtenir le 1er jour du mois suivant
Voici une date de production dans la cellule B18, veuillez utiliser la formule comme suit :
=FIN.MOIS(B18;0)+1
Appuyez sur Entrer clé pour obtenir le résultat.
3. Calculer l'âge
Dans cette section, sont listées les méthodes pour calculer l'âge à partir d'une date donnée ou d'un numéro de série.
3.11 Calculer l'âge en fonction de la date de naissance donnée
Obtenir l'âge sous forme de nombre décimal en fonction de la date de naissance
Cliquez sur YEARFRAC pour plus de détails sur ses arguments et son utilisation.
Par exemple, pour obtenir les âges en fonction de la liste des dates de naissance dans la colonne B2:B9, veuillez utiliser la formule suivante :
=YEARFRAC(B2,AUJOURDHUI())
Appuyez sur Entrer touche, puis faites glisser la poignée de recopie automatique vers le bas jusqu'à ce que tous les âges soient calculés.
Astuce :
1) Vous pouvez spécifier le nombre de décimales selon vos besoins dans le Format de cellule boîte de dialogue.
2) Si vous souhaitez calculer l’âge à une date spécifique en fonction d’une date de naissance donnée, remplacez AUJOURD’HUI() par la date spécifique entourée de guillemets doubles, par exemple =YEARFRAC(B2,"1/1/2021")
3) Si vous souhaitez obtenir l’âge de l’année suivante en fonction de la date de naissance, il suffit d’ajouter 1 à la formule, par exemple =YEARFRAC(B2,TODAY())+1.
Obtenir l'âge en nombre entier basé sur la date de naissance
Cliquez sur DATEDIF pour plus de détails sur ses arguments et son utilisation.
En utilisant l'exemple ci-dessus, pour obtenir l'âge en fonction des dates de naissance dans la liste en B2:B9, veuillez utiliser la formule suivante :
=DATEDIF(B2,AUJOURDHUI(),"y")
Appuyez sur Entrer touche, puis faites glisser la poignée de remplissage automatique vers le bas jusqu'à ce que tous les âges soient calculés.
Astuce :
1) Si vous souhaitez calculer l’âge à une date spécifique en fonction d’une date de naissance donnée, remplacez AUJOURDHUI() par la date spécifique entourée de guillemets doubles, par exemple =DATEDIF(B2,"1/1/2021","y").
2) Si vous souhaitez obtenir l’âge de l’année suivante en fonction de la date de naissance, il suffit d’ajouter 1 à la formule, par exemple =DATEDIF(B2,TODAY(),"y")+1.
3.12 Calculer l'âge en années, mois et jours à partir de la date d'anniversaire donnée
Si vous souhaitez calculer l'âge en fonction d'une date de naissance donnée et afficher le résultat sous la forme xx années, xx mois, xx jours comme le montre la capture d'écran ci-dessous, voici une formule longue qui peut vous aider.
Pour obtenir l’âge en années, mois et jours à partir de la date de naissance dans la cellule B12, veuillez utiliser la formule suivante :
=DATEDIF(B12,AUJOURDHUI(),"Y")&" Années, "&DATEDIF(B12,AUJOURDHUI(),"YM")&" Mois, "&DATEDIF(B12,AUJOURDHUI(),"MD")&" Jours"
Appuyez sur Entrer pour obtenir l'âge, puis faites glisser la poignée de recopie automatique vers les autres cellules.
Astuce :
Si vous souhaitez calculer l'âge à une date spécifique en fonction d'une date de naissance donnée, remplacez AUJOURDHUI() par la date spécifique entourée de guillemets doubles, comme ceci : =DATEDIF(B12,"1/1/2021","Y")&" Ans, "&DATEDIF(B12,"1/1/2021","YM")&" Mois, "&DATEDIF(B12,"1/1/2021","MD")&" Jours".
3.13 Calculer l'âge par la date de naissance avant le 1/1/1900
Dans Excel, la date antérieure au 1/1/1900 ne peut pas être saisie en tant que date ou calculée correctement. Cependant, si vous souhaitez calculer l’âge d’une personne célèbre à partir de sa date de naissance (avant le 1/1/1900) et de sa date de décès, seul un code VBA pourra vous aider.
1. Appuyez sur les touches Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic for Applications, puis cliquez sur l'onglet Insertion et choisissez Module pour créer un nouveau module.
2. Ensuite, copiez et collez le code ci-dessous dans le nouveau module.
VBA : Calculer l'âge avant le 1/1/1900
Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
Dim xSMonth As Integer
Dim xSDay As Integer
Dim xSYear As Integer
Dim xEMonth As Integer
Dim xEDay As Integer
Dim xEYear As Integer
Dim xAge As Integer
If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
xAge = xEYear - xSYear
If xSMonth > xEMonth Then
xAge = xAge - 1
ElseIf xSMonth = xEMonth Then
If xSDay > xEDay Then xAge = xAge - 1
End If
If xAge < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = xAge
End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
Dim I As Long
Dim K As Long
Y = 0
M = 0
D = 0
GetDate = True
On Error Resume Next
I = InStr(1, DateStr, "/")
M = CLng(Left(DateStr, I - 1))
D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
GetDate = False
End If
End Function
3. Enregistrez le code, revenez à la feuille et sélectionnez une cellule pour afficher l’âge calculé, puis saisissez =AgeFunc(date de naissance,date de décès), dans ce cas, =AgeFunc(B22,C22), appuyez sur la touche Entrée pour obtenir l'âge. Utilisez ensuite la poignée de remplissage automatique pour appliquer cette formule à d'autres cellules si nécessaire.
Si vous avez Kutools pour Excel installé dans Excel, vous pouvez appliquer le Assistant de date et heure outil pour calculer l'âge.
1. Sélectionnez une cellule où vous souhaitez placer l'âge calculé, puis cliquez Kutools > Assistant de formule > Assistant de date et heure.
2. Dans la boîte de dialogue Assistant Date et heure,
- 1) Cochez l'option Âge ;
- 2) Choisissez la cellule de la date de naissance ou entrez directement la date de naissance, ou cliquez sur l'icône du calendrier pour sélectionner la date de naissance.
- 3) Choisissez l'option Aujourd'hui si vous souhaitez calculer l'âge actuel, choisissez l'option Date spécifique et saisissez la date si vous souhaitez calculer l'âge dans le passé ou le futur.
- 4) Spécifiez le type de sortie dans la liste déroulante ;
- 5) Aperçu du résultat de sortie. Cliquez sur OK.
Cliquez sur Date et heure Assistant pour en savoir plus sur l'utilisation de cette fonctionnalité.
Cliquez sur Kutools pour Excel pour découvrir toutes les fonctionnalités de ce complément.
Cliquez sur Téléchargement gratuit pour obtenir un essai gratuit de 30 jours de Kutools pour Excel
3.31 Obtenir la date d'anniversaire à partir du numéro d'identification
S'il y a une liste de numéros d'identification qui utilisent les 6 premiers chiffres pour enregistrer la date de naissance, comme 920315330, ce qui signifie que la date de naissance est le 15/03/1992, comment pouvez-vous rapidement obtenir la date de naissance dans une autre colonne ?
Prenons maintenant la liste des numéros d'identification commençant dans la cellule C2 comme exemple, et utilisons la formule suivante :
=STXT(C2;5;2)&"/"&STXT(C2;3;2)&"/"&STXT(C2;1;2)
Appuyez sur Entrer key. Ensuite, faites glisser la poignée de recopie automatique vers le bas pour obtenir d'autres résultats.
Remarque :
Dans la formule, vous pouvez modifier la référence selon vos besoins. Par exemple, si le numéro d'identification affiché est 13219920420392, la date de naissance est le 20/04/1992, vous pouvez changer la formule en =MID(C2,8,2)&"/"&MID(C2,10,2)&"/"&MID(C2,4,4) pour obtenir le résultat correct.
3.32 Calculer l'âge à partir du numéro d'identification
S'il y a une liste de numéros d'identification qui utilisent les 6 premiers chiffres pour enregistrer la date de naissance, comme 920315330, ce qui signifie que la date de naissance est le 15/03/1992, comment pouvez-vous rapidement calculer l'âge en fonction de chaque numéro d'identification dans Excel ?
Prenons maintenant la liste des numéros d'identification commençant dans la cellule C2 comme exemple, et utilisons la formule suivante :
=DATEDIF(DATE(SI(GAUCHE(C2,2)>TEXTE(AUJOURDHUI(),"AA"),"19"&GAUCHE(C2,2),"20"&GAUCHE(C2,2)),STXT(C2,3,2),STXT(C2,5,2)),AUJOURDHUI(),"a")
Appuyez sur Entrer Clé. Ensuite, faites glisser la poignée de recopie automatique vers le bas pour obtenir d'autres résultats.
Remarque :
Dans cette formule, si l'année est inférieure à l'année en cours, l'année sera considérée comme commençant par 20, par exemple 200203943 sera considéré comme l'année 2020 ; si l'année est supérieure à l'année en cours, l'année sera considérée comme commençant par 19, par exemple 920420392 sera considéré comme l'année 1992.
Plus de tutoriels Excel :
Combinez plusieurs classeurs/feuilles de calcul en un seul
Ce tutoriel répertorie presque tous les scénarios de combinaison auxquels vous pourriez être confronté et propose des solutions professionnelles adaptées.
Diviser les cellules de texte, de nombre et de date (séparer en plusieurs colonnes)
Ce tutoriel est divisé en trois parties : diviser les cellules de texte, diviser les cellules de nombres et diviser les cellules de dates. Chaque partie fournit différents exemples pour vous aider à comprendre comment gérer la tâche de division lorsque vous rencontrez le même problème.
Combinez le contenu de plusieurs cellules sans perdre de données dans Excel
Ce tutoriel se concentre sur l'extraction à une position spécifique dans une cellule et regroupe différentes méthodes pour aider à extraire du texte ou des nombres d'une cellule selon une position précise dans Excel.
Comparer deux colonnes pour les correspondances et les différences dans Excel
Cet article couvre ici la plupart des scénarios possibles de comparaison de deux colonnes que vous pourriez rencontrer, et j'espère qu'il pourra vous aider.
Les meilleurs outils de productivité pour Office
Kutools pour Excel résout la plupart de vos problèmes et augmente votre productivité de 80 %
- Barre de formule avancée (éditez facilement plusieurs lignes de texte et de formules); Mode Lecture (lisez et éditez facilement un grand nombre de cellules); Coller dans une plage filtrée...
- Fusionner des cellules/lignes/colonnes tout en conservant les données; Diviser le contenu des cellules; Consolider des lignes en double et faire la somme/moyenne... Empêcher les doublons dans les cellules; Comparer des plages...
- Sélectionner des lignes en double ou uniques; Sélectionner des lignes vides (toutes les cellules sont vides); Super Recherche et recherche floue dans de nombreux classeurs; Sélection aléatoire...
- Copie exacte de plusieurs cellules sans changer les références de formule; Créer automatiquement des références vers plusieurs feuilles; Insérer des puces, des cases à cocher et plus encore...
- Ajouter et insérer rapidement des formules, des plages, des graphiques et des images; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails...
- Extraire du texte, ajouter du texte, supprimer par position, supprimer des espaces; Créer et imprimer des statistiques de page; Convertir entre le contenu des cellules et les commentaires...
- Super Filtre (enregistrer et appliquer des schémas de filtre à d'autres feuilles); Tri avancé par mois/semaine/jour, fréquence et plus; Filtre spécial par gras, italique...
- Consolider des classeurs et feuilles de calcul; Fusionner des tables basées sur des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots de fichiers xls, xlsx et PDF...
- Regroupement dans les tableaux croisés dynamiques par numéro de semaine, jour de la semaine et plus... Afficher les cellules verrouillées/déverrouillées avec différentes couleurs; Mettre en évidence les cellules contenant des formules/noms...
- Activer l'édition et la lecture à onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrir et créer plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et réduit des centaines de clics de souris chaque jour !
