Tutoriel Excel : Calcul de la date et de l'heure (calculer la différence, l'âge, additionner/soustraire)
Dans Excel, le calcul de la date et de l'heure est fréquemment utilisé, par exemple pour calculer la différence entre deux dates/heures, ajouter ou soustraire la date et l'heure, obtenir l'âge en fonction de la date de naissance, etc. Ici, dans ce didacticiel, il répertorie presque les scénarios sur le calcul de la date et de l'heure et vous fournit les méthodes associées.
Dans ce tutoriel, je crée quelques exemples pour expliquer les méthodes, vous pouvez modifier les références dont vous avez besoin lorsque vous utilisez ci-dessous le code VBA ou les formules
1. Calculer la différence entre deux dates/heures
Le calcul de la différence entre deux dates ou deux heures peut être le problème le plus normal de calcul de la date et de l'heure que vous rencontrez dans le travail quotidien d'Excel. Les exemples ci-dessous fournis peuvent vous aider à améliorer l'efficacité lorsque vous rencontrez les mêmes problèmes.
1.11 Calculer la différence entre deux dates en jours/mois/années/semaines
La fonction Excel DATEDIF 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 DATEIF fonction
Différence de jours entre deux dates
Pour obtenir la différence de jours entre deux dates dans les cellules A2 et B2, veuillez utiliser la formule comme ceci
=DATEDIF(A2,B2,"d")
Actualités Entrer clé pour obtenir le résultat.
Différence de mois entre deux dates
Pour obtenir la différence de mois entre deux dates dans les cellules A5 et B5, veuillez utiliser la formule comme ceci
=DATEDIF(A5,B5,"m")
Actualités Entrer clé pour obtenir le résultat.
Différence d'années entre deux dates
Pour obtenir la différence d'années entre deux dates dans les cellules A8 et B8, veuillez utiliser la formule comme ceci
=DATEDIF(A8,B8,"y")
Actualités Entrer clé pour obtenir le résultat.
Semaines d'écart entre deux dates
Pour obtenir la différence de semaines entre deux dates dans les cellules A11 et B11, veuillez utiliser la formule comme ceci
=DATEDIF(A11,B11,"d")/7
Actualités Entrer clé pour obtenir le résultat.
Remarque:
1) Lorsque vous utilisez la formule ci-dessus pour obtenir la différence de semaines, elle peut renvoyer un résultat au format date, vous devez 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 de semaines, elle peut revenir à un nombre décimal. Si vous souhaitez obtenir le numéro de semaine entier, vous pouvez ajouter la fonction ROUNDDOWN avant, comme indiqué ci-dessous, pour obtenir la différence de semaines entière :
=ROUNDDOWN(DATEDIF(A11,B11,"d")/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 de 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")
Actualités 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 de jours en ignorant les années et les mois entre deux dates, comme illustré ci-dessous, voici une formule qui peut vous aider.
=DATEDIF(A5,B5,"md")
Actualités Entrer clé pour obtenir le résultat.
A5 est la date de début et B5 est la date de fin.
1.14 Calculer la différence entre deux dates et retourner les années, les mois et les jours
Si vous souhaitez obtenir la différence entre deux dates et retourner xx ans, xx mois et xx jours comme le montre la capture d'écran ci-dessous, voici également une formule fournie.
=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"
Actualités Entrer clé 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, changez simplement end_date dans les formules ci-dessus en TODAY(). Ici, prenez pour calculer la différence de jours entre une date passée et aujourd'hui comme exemple.
=DATEDIF(A11,TODAY(),"d")
Actualités Entrer clé pour obtenir le résultat.
Notes: si vous voulez calculer la différence entre une date future et aujourd'hui, remplacez start_date par aujourd'hui et prenez la date future comme end_date comme ceci :
=DATEDIF(TODAY(),A14,"d")
Notez que start_date doit être inférieur à end_date dans la fonction DATEDIF, sinon, il reviendra à #NUM ! valeur d'erreur.
1.16 Calculer les jours ouvrés avec ou sans congés entre deux dates
Parfois, vous devrez peut-être compter le nombre de jours ouvrés avec ou sans les jours fériés entre deux dates données.
Dans cette partie, vous utiliserez la fonction NETWORKDAYS.INTL :
Cliquez NETWORKDAYS.INTL connaître ses arguments et son utilisation.
Compter les jours ouvrés avec les jours fériés
Pour compter les jours ouvrés avec jours fériés entre deux dates dans les cellules A2 et B2, veuillez utiliser la formule suivante :
=NETWORKDAYS.INTL(A2,B2)
Actualités Entrer clé pour obtenir le résultat.
Compter les jours ouvrés sans jours fériés
Pour compter les jours ouvrés avec des jours fériés entre deux dates dans les cellules A2 et B2, et en excluant les jours fériés dans la plage D5 : D9, veuillez utiliser la formule suivante :
=JOURSOUVRES.INTL(A5,B5,1,D5:D9)
Actualités Entrer clé pour obtenir le résultat.
Remarque:
Dans les formules ci-dessus, ils prennent le samedi et le dimanche comme week-end, si vous avez des jours de week-end différents, veuillez modifier l'argument [week-end] 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 :
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Actualités Entrer clé pour obtenir le résultat.
1.18 Calculer un jour de semaine spécifique entre deux dates
Pour compter le nombre d'un jour de semaine spécifique comme le lundi entre deux dates, la combinaison des fonctions INT et WEEKDAY peut vous aider.
Les cellules A15 et B15 sont les deux dates entre lesquelles vous souhaitez compter lundi, veuillez utiliser la formule suivante :
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Actualités Entrer clé pour obtenir le résultat.
Modifiez le numéro du jour de la semaine dans la fonction WEEKDAY pour compter un autre jour de la semaine :
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 en mois/année
Parfois, vous voudrez peut-être 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 du mois en cours
Cliquez EOMONTH connaître l'argument et l'usage.
Pour obtenir les jours restants du mois en cours dans la cellule A2, veuillez utiliser la formule suivante :
=EOMONTH(A2,0)-A2
Actualités Entrer et faites glisser la poignée de remplissage automatique pour appliquer cette formule à d'autres cellules si nécessaire.
Conseil: les résultats peuvent être affichés sous forme de date, il suffit de les modifier 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(YEAR(A2),12,31)-A2
Actualités Entrer 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 faire la différence entre deux temps, voici deux formules simples qui peuvent vous aider.
Supposons que les cellules A2 et B2 contiennent start_time et end_time séparément, en utilisant les formules suivantes :
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Actualités Entrer clé 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(end_time-first_time,"time_format"), entrez le format d'heure dans lequel vous voulez que le résultat s'affiche dans la formule, par exemple TEXT(end_time-first_time,"h") renvoie 16.
- Si end_time est inférieur à start_time, les deux formules renvoient des valeurs d'erreur. Pour résoudre ce problème, vous pouvez ajouter ABS devant ces formules, telles que ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")), puis formater le résultat en temps.
1.22 Calculer la différence entre deux temps 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 des heures de différence entre deux heures
Pour obtenir la différence d'heures entre deux heures en A5 et B5, veuillez utiliser la formule suivante :
=INT((B5-A5)*24)
Actualités Entrer , puis formatez le résultat du format d'heure en général ou en nombre.
Si vous souhaitez obtenir la différence d'heures décimales, utilisez (end_time-start_time)*24.
Obtenir des minutes de différence entre deux heures
Pour obtenir la différence de minutes entre deux temps en A8 et B8, veuillez utiliser la formule suivante :
=INT((B8-A8)*1440)
Actualités Entrer , puis formatez le résultat du format d'heure en général ou en nombre.
Si vous souhaitez obtenir la différence de minutes décimales, utilisez (end_time-start_time)*1440.
Obtenir la différence de secondes entre deux heures
Pour obtenir la différence de secondes entre deux temps en A5 et B5, veuillez utiliser la formule suivante :
=(B11-A11)*86400)
Actualités Entrer , 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 heures (ne pas dépasser 24 heures)
Si l'écart entre deux heures n'excède pas 24 heures, la fonction HOUR permet d'obtenir rapidement l'écart d'heures entre ces deux heures.
Cliquez HEURE pour plus de détails sur cette fonction.
Pour obtenir la différence d'heures entre les heures dans les cellules A14 et B14, veuillez utiliser la fonction HOUR comme suit :
=HOUR(B14-A14)
Actualités Entrer clé pour obtenir le résultat.
L'heure de début doit être inférieure à l'heure de fin, sinon la formule renvoie #NUM ! valeur d'erreur.
1.24 Calculer la différence de minutes uniquement entre deux heures (ne pas dépasser 60 minutes)
La fonction MINUTE peut obtenir rapidement les seules minutes de différence entre ces deux heures et ignorer les heures et les secondes.
Cliquez 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 suit :
=MINUTE(B17-A17)
Actualités Entrer clé pour obtenir le résultat.
L'heure de début doit être inférieure à l'heure de fin, sinon la formule renvoie #NUM ! valeur d'erreur.
1.25 Calculer la différence de secondes uniquement entre deux temps (ne pas dépasser 60 secondes)
La fonction SECONDE peut obtenir rapidement la seule différence de secondes entre ces deux heures et ignorer les heures et les minutes.
Cliquez DEUXIÈME 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 SECOND comme suit :
=SECOND(B20-A20)
Actualités Entrer clé pour obtenir le résultat.
L'heure de début doit être inférieure à l'heure de fin, sinon la formule renvoie #NUM ! valeur d'erreur.
1.26 Calculer la différence entre deux heures et retourner les heures, les minutes, les secondes
Si vous souhaitez afficher la différence entre deux heures sous forme de xx heures xx minutes xx secondes, veuillez utiliser la fonction TEXTE comme indiqué ci-dessous :
Cliquez TEXTE pour comprendre 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 comme suit :
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
Actualités Entrer clé pour obtenir le résultat.
Remarque:
Cette formule ne calcule également que la différence d'heures ne dépassant pas 24 heures, et le end_time doit être supérieur à start_time, sinon, elle renvoie #VALUE ! valeur d'erreur.
1.27 Calculer la différence entre deux datetimes
S'il y a deux heures au format mm/jj/aaaa hh:mm:ss, pour calculer la différence entre elles, vous pouvez utiliser l'une des formules ci-dessous selon vos besoins.
Obtenir la différence de temps entre deux dates et heures et renvoyer le résultat au format hh: mm: ss
Prenez deux dates/heures dans les cellules A2 et B2 comme exemple, veuillez utiliser la formule comme suit :
=B2-A2
Actualités Entrer key, renvoyant un résultat au format datetime, puis formatez ce résultat comme [h]: mm: ss dans la catégorie personnalisée sous le Numéro onglet Format de cellule dialogue.
Obtenez la différence entre deux dates et heures et retournez les jours, heures, minutes, secondes
Prenez deux dates/heures dans les cellules A5 et B5 comme exemple, veuillez utiliser la formule comme suit :
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
Actualités 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 en millisecondes
Tout d'abord, vous devez savoir comment formater la cellule pour afficher les millisecondes :
Sélectionnez les cellules que vous souhaitez afficher en millisecondes et à droite pour sélectionner Format de cellule pour activer le Format de cellule dialogue, sélectionnez Coutume dans l' Catégories liste sous l'onglet Nombre, et tapez ceci hh: mm: ss.000 dans la zone de texte.
Utilisez la formule:
Ici, pour calculer la différence entre deux heures dans les cellules A8 et B8, utilisez la formule suivante :
=ABS(B8-A8)
Actualités Entrer clé pour obtenir le résultat.
1.29 Calculer les heures de travail entre deux dates hors week-end
Parfois, il peut être nécessaire de compter les heures de travail entre deux dates, hors week-end (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 :
=NETWORKDAYS(A16,B16) * 8
Actualités 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 visiter Obtenir des heures de travail entre deux dates dans Excel
Si vous avez Kutools for Excel installé dans Excel, 90% des calculs de différence datetime peuvent être résolus rapidement sans se souvenir des formules.
1.31 Calculer la différence entre deux dates et heures par Data & Time Helper
Pour calculer la différence entre deux dates et heures dans Excel, il suffit de Assistant de date et d'heure est assez.
1. Sélectionnez une cellule dans laquelle vous placez le résultat calculé, puis cliquez sur Kutools > Aide à la formule > Assistant date et heure.
2. Dans le popping Assistant de date et d'heure boîte de dialogue, suivez les paramètres ci-dessous :
- En cliquant La différence l'option;
- Sélectionnez la date/heure de début et la date/heure de fin dans Entrée d'arguments section, vous pouvez également entrer directement la date et l'heure manuellement 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 ;
- Prévisualisez le résultat dans Résultat .
3. Cliquez Ok. Le résultat calculé est généré et faites glisser la poignée de remplissage automatique sur les cellules que vous devez également calculer.
Conseil:
Si vous souhaitez obtenir la différence entre deux datetime et afficher le résultat sous forme de jours, d'heures et de minutes avec Kutools for Excel, procédez comme ci-dessous :
Sélectionnez une cellule où vous souhaitez placer le résultat, puis cliquez sur Kutools > Aide à la formule > Date & Heure > Compter les jours, les heures et les minutes entre deux dates.
Puis dans le Aide aux formules boîte de dialogue, spécifiez la date de début et la date de fin, puis cliquez sur Ok.
Et le résultat de la différence sera affiché en jours, heures et minutes.
Cliquez Assistant de date et d'heure pour en savoir plus sur l'utilisation de cette fonctionnalité.
Cliquez Kutools for Excel pour connaître toutes les fonctionnalités de ce complément.
Cliquez Téléchargement gratuit pour obtenir un essai gratuit de 30 jours de Kutools for Excel
Si vous souhaitez compter rapidement le week-end, les jours ouvrables ou un jour de semaine spécifique entre deux dates/heures, Kutools for Excel's Aide à la formule groupe peut vous aider.
1. Sélectionnez la cellule qui placera le résultat calculé, cliquez sur Kutools > Statistique > Nombre de jours chômés entre deux dates/Nombre de jours ouvrés entre deux dates/Compter le nombre de jours spécifiques de la semaine.
2. Dans le popping out Aide aux formules boîte de dialogue, spécifiez la date de début et la date de fin, si vous postulez Compter le 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 pour indiquer dimanche-samedi.
3. Cliquez Ok, puis faites glisser la poignée de remplissage automatique sur les cellules qui doivent compter le nombre de week-end/jour de travail/jour de semaine spécifique si nécessaire.
Cliquez Kutools for Excel pour connaître toutes les fonctionnalités de ce complément.
Cliquez Téléchargement gratuit pour obtenir un essai gratuit de 30 jours de Kutools for Excel
2. Ajouter ou soustraire la date et l'heure
À l'exception du calcul de la différence entre deux dates-heures, l'ajout ou la soustraction est également le calcul de date-heure normal dans Excel. Par exemple, vous souhaiterez peut-être 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.
En supposant que vous ajoutiez 21 jours à une date dans la cellule A2, veuillez choisir l'une des méthodes ci-dessous pour la résoudre,
Méthode 1 date+jours
Sélectionnez une cellule et saisissez la formule :
=A+21
Actualités Entrer clé pour obtenir le résultat.
Si vous voulez soustraire 21 jours, changez simplement le signe plus (+) en signe moins (-).
Méthode 2 Collage spécial
1. Tapez le nombre de jours que vous souhaitez ajouter dans une cellule supposant dans la cellule C2, puis appuyez sur Ctrl + C pour le copier.
2. Sélectionnez ensuite les dates auxquelles vous souhaitez ajouter 21 jours, cliquez avec le bouton 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 (Si vous souhaitez soustraire des jours, cochez Soustraire option). Cliquez sur OK.
4. Maintenant, les dates d'origine sont remplacées par des nombres à 5 chiffres, formatez-les en tant que dates.
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 EDATE étudier ses arguments et ses usages.
En supposant que vous ajoutiez 6 mois à la date dans la cellule A2, utilisez la formule comme suit :
=EDATE(A2,6)
Actualités Entrer clé 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.
En supposant que vous ajoutiez 3 ans à la date dans la cellule A2, utilisez la formule comme suit :
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Actualités Entrer clé pour obtenir le résultat.
Si vous voulez 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
En supposant que vous ajoutiez 4 semaines à la date dans la cellule A2, utilisez la formule comme suit :
=A2+4*7
Actualités 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 incluant ou excluant les jours fériés
Dans cette section, il présente comment utiliser la fonction WORKDAY pour ajouter ou soustraire des jours ouvrables à une date donnée hors jours fériés ou y compris les jours fériés.
Visite JOURNEE DE TRAVAIL 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 est la date que vous utilisez, dans la cellule B2 contient le nombre de jours que vous souhaitez ajouter, veuillez utiliser la formule comme ceci :
=WORKDAY(A2,B2)
Actualités Entrer clé pour obtenir le résultat.
Ajouter des jours ouvrés hors jours fériés
Dans la cellule A5 est la date que vous utilisez, dans la cellule B5 contient le nombre de jours que vous souhaitez ajouter, dans la plage D5 : D8 répertorie les jours fériés, veuillez utiliser la formule comme suit :
=WORKDAY(A5,B5,D5:D8)
Actualités Entrer clé pour obtenir le résultat.
Remarque:
La fonction WORKDAY prend le samedi et le dimanche comme week-ends, si vos week-ends sont le samedi et le dimanche, vous pouvez appliquer la fonction WOKRDAY.INTL, qui prend en charge la spécification des week-ends.
Visite JOUR OUVRABLE.INTL pour plus de détails.
Si vous souhaitez soustraire des jours ouvrables à une date, changez simplement 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 spécifique, des jours de mois à une date, la formule qui combine les fonctions DATE, ANNÉE, MOIS et JOURS peut vous rendre service.
Pour ajouter 1 an 2 mois et 30 jours à une date en A11, veuillez utiliser la formule suivante :
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Actualités 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/heure
Voici quelques formules pour ajouter ou soustraire des heures, des minutes ou des secondes à une date/heure.
Ajouter ou soustraire des heures à une date/heure
En supposant que vous ajoutiez 3 heures à une date/heure (peut également être une heure) dans la cellule A2, veuillez utiliser la formule suivante :
=A2+3/24
Actualités Entrer clé pour obtenir le résultat.
Ajouter ou soustraire des heures à une date/heure
En supposant que vous ajoutiez 15 minutes à une date/heure (peut également être une heure) dans la cellule A5, veuillez utiliser la formule suivante :
=A2+15/1440
Actualités Entrer clé pour obtenir le résultat.
Ajouter ou soustraire des heures à une date/heure
En supposant que vous ajoutiez 20 secondes à une date/heure (peut également être une heure) dans la cellule A8, veuillez utiliser la formule comme suit :
=A2+20/86400
Actualités Entrer clé pour obtenir le résultat.
2.22 Somme des temps sur 24 heures
En supposant qu'il existe un tableau Excel enregistrant le temps de travail de tous les membres du personnel en une semaine, pour additionner le temps de travail total pour calculer les paiements, vous pouvez utiliser SOMME (gamme) pour obtenir le résultat. Mais en général, le résultat additionné sera affiché sous la 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 comme [hh]:mm:ss.
Faites un clic droit sur la cellule de résultat, choisissez Format de cellule dans le menu contextuel, et dans le popping Format de cellule dialogue, choisissez Coutume de la liste déroulante et tapez [hh]:mm:ss dans la zone de texte dans la section de droite, cliquez sur OK.
Le résultat additionné s'affichera correctement.
2.23 Ajouter des heures de travail à une date hors week-end et jours fériés
Ici fournit une formule longue pour obtenir la date de fin basée sur l'ajout d'un nombre spécifique d'heures de travail à une date de début et exclut les week-ends (samedi et dimanche) et les jours fériés.
Dans un tableau Excel, A11 contient la date et l'heure de début, et B11 contient les heures de travail, dans les cellules E11 et E13 sont les heures de début et de fin de travail, et la cellule E15 contient les vacances qui seront exclues.
Veuillez utiliser la formule comme ceci:
=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))
Actualités Entrer clé pour obtenir le résultat.
Si Kutools pour Excel est installé, un seul outil – Aide sur la date et l'heurer peut résoudre la plupart des calculs sur l'ajout et la soustraction de datetime.
1. Cliquez sur une cellule dont vous souhaitez afficher le résultat et appliquez cet outil en cliquant sur Kutools > Aide à la formule > Assistant de date et d'heure.
2. dans le Assistant de date et d'heure boîte de dialogue, vérifier Ajouter Option ou Soustraire selon vos besoins, puis choisissez la cellule ou tapez directement la date et l'heure que vous souhaitez utiliser dans Entrée d'arguments section, puis spécifiez les années, mois, semaines, jours, heures, minutes et secondes que vous souhaitez ajouter ou soustraire, puis cliquez sur Ok. Voir la capture d'écran:
Vous pouvez prévisualiser le résultat calculé dans le Résultat .
Maintenant que le résultat est généré, faites glisser la poignée automatique sur d'autres cellules pour obtenir les résultats.
Cliquez Assistant de date et d'heure pour en savoir plus sur l'utilisation de cette fonctionnalité.
Cliquez Kutools for Excel pour connaître toutes les fonctionnalités de ce complément.
Cliquez Téléchargement pour obtenir un essai gratuit de 30 jours de Kutools for Excel
2.41 Vérifier ou mettre en surbrillance si une date est expirée
S'il existe une liste des dates d'expiration des produits, vous pouvez vérifier et mettre en surbrillance les dates qui ont expiré en fonction d'aujourd'hui, comme le montre la capture d'écran ci-dessous.
En fait, le Mise en forme conditionnelle peut gérer rapidement ce travail.
1. Sélectionnez les dates que vous souhaitez vérifier, puis cliquez sur Accueil > Mise en forme conditionnelle > Nouvelle règle.
2. dans le Nouvelle règle de formatage dialogue, sélectionnez Utilisez une formule pour déterminer les cellules à formater dans l' Sélectionnez un type de règle section et type =B2 dans la zone de saisie (B2 est la première date que vous souhaitez vérifier), puis cliquez sur Format apparaître Format de cellule boîte de dialogue, puis choisissez une mise en forme différente pour dépasser les dates d'expiration selon vos besoins. Cliquez sur OK > OK.
2.42 Renvoie la fin du mois en cours/le premier jour du mois suivant/a>
Les dates d'expiration de certains produits sont à la fin du mois de production ou le premier jour du mois de production suivant, 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 :
=EOMONTH(B13,0)
Actualités Entrer clé pour obtenir le résultat.
Obtenez le 1er jour du mois prochain
Voici une date de production dans la cellule B18, veuillez utiliser la formule comme suit :
=EOMONTH(B18,0)+1
Actualités Entrer clé pour obtenir le résultat.
3. Calculer l'âge
Dans cette section, il répertorie les méthodes permettant de résoudre le calcul de l'âge en fonction 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 en nombre décimal en fonction de la date de naissance
Cliquez Annéefrac pour plus de détails sur ses arguments et son utilisation.
Par exemple, pour obtenir les âges basés sur la liste des dates de naissance dans la colonne B2 : B9, veuillez utiliser la formule suivante :
=YEARFRAC(B2,TODAY())
Actualités Entrer , puis faites glisser la poignée de remplissage automatique vers le bas jusqu'à ce que tous les âges soient calculés.
Conseil:
1) Vous pouvez spécifier la décimale selon vos besoins dans le Format de cellule dialogue.
2) Si vous souhaitez calculer l'âge à une date spécifique en fonction d'une date de naissance donnée, remplacez TODAY() par la date spécifique entourée de guillemets tels que =YEARFRAC(B2,"1/1/2021")
3) Si vous souhaitez obtenir l'âge de l'année suivante en fonction de la date de naissance, ajoutez simplement 1 dans la formule telle que =YEARFRAC(B2,TODAY())+1.
Obtenir l'âge en nombre entier en fonction de la date de naissance
Cliquez DATEIF 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 comme suit :
=DATEDIF(B2,TODAY(),"y")
Actualités Entrer , puis faites glisser la poignée de remplissage automatique vers le bas jusqu'à ce que tous les âges soient calculés.
Conseil:
1) Si vous souhaitez calculer l'âge à une date spécifique en fonction d'une date de naissance donnée, remplacez TODAY() par la date spécifique entourée de guillemets tels que =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, ajoutez simplement 1 dans la formule telle que =DATEDIF(B2,TODAY(),"y")+1.
3.12 Calculer l'âge en années, mois et jours en fonction de l'anniversaire donné
Si vous souhaitez calculer l'âge en fonction d'une date de naissance donnée et afficher le résultat sous la forme xx ans, xx mois, xx jours, comme le montre la capture d'écran ci-dessous, voici une longue formule qui peut vous aider.
Pour obtenir l'âge en années, mois et jours en fonction de la date de naissance dans la cellule B12, veuillez utiliser la formule suivante :
=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"
Actualités Entrer pour obtenir l'âge, puis faites glisser la poignée de remplissage automatique vers d'autres cellules.
Conseil:
Si vous souhaitez calculer l'âge à une date spécifique en fonction d'une date de naissance donnée, remplacez TODAY() par la date spécifique entourée de guillemets tels que = =DATEDIF(B12,"1/1/2021","Y")& " Années, "&DATEDIF(B12,"1/1/2021","YM")&" Mois, "&DATEDIF(B12,"1/1/2021","MD")&" Jours".
3.13 Calculer l'âge par date de naissance avant le 1/1/1900
Dans Excel, la date antérieure au 1/1/1900 ne peut pas être saisie comme date/heure ou calculée correctement. Mais si vous souhaitez calculer l'âge d'une personne célèbre en fonction de sa date de naissance (avant 1/11900) et de sa date de décès, seul un code VBA peut vous aider.
1. presse autre + F11 clés pour activer Microsoft Visual Basic pour applications fenêtre, et cliquez sur insérer onglet et choisissez Module pour créer un nouveau module.
2. Ensuite, copiez et collez le code ci-dessous dans le nouveau module.
VBA: Calculez 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 placer l'âge calculé, tapez =AgeFunc(birthdate,deathdate), dans ce cas, =FonctionAge(B22,C22), appuyez sur la touche Entrée pour obtenir l'âge. Et utilisez la poignée de remplissage automatique pour appliquer cette formule à d'autres cellules si nécessaire.
Si vous avez Kutools for Excel installé dans Excel, vous pouvez appliquer le Assistant de date et d'heure outil pour calculer l'âge.
1. Sélectionnez une cellule dans laquelle vous souhaitez placer l'âge calculé, puis cliquez sur Kutools > Aide à la formule > Assistant date et heure.
2. dans le Assistant de date et d'heure dialogue,
- 1) Vérifier Âge l'option;
- 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 Aujourd'hui Si vous souhaitez calculer l'âge actuel, choisissez Date spécifiée option et entrez la date si vous souhaitez calculer l'âge dans le passé ou dans le futur ;
- 4) Spécifiez le type de sortie dans la liste déroulante ;
- 5) Prévisualisez le résultat de sortie. Cliquez sur Ok.
Cliquez Assistant de date et d'heure pour en savoir plus sur l'utilisation de cette fonctionnalité.
Cliquez Kutools for Excel pour connaître toutes les fonctionnalités de ce complément.
Cliquez Téléchargement gratuit pour obtenir un essai gratuit de 30 jours de Kutools for Excel
3.31 Obtenir l'anniversaire à partir du numéro d'identification
S'il existe une liste de numéros d'identification qui utilisent les 6 premiers chiffres pour enregistrer la date de naissance, par exemple 920315330 signifie que la date de naissance est le 03/15/1992, comment pouvez-vous obtenir rapidement 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 comme suit :
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Actualités Entrer clé. Faites ensuite glisser la poignée de remplissage automatique vers le bas pour obtenir d'autres résultats.
Remarque:
Dans la formule, vous pouvez modifier la référence selon votre besoin. Par exemple, si le numéro d'identification affiché sous la forme 13219920420392, l'anniversaire est le 04/20/1992, vous pouvez modifier la formule en =MID(C2,8,2)&"/"&MID(C2,10,2)&"/ "&MID(C2,4,4) pour obtenir le bon résultat.
3.32 Calculer l'âge à partir du numéro d'identification
S'il existe une liste de numéros d'identification qui utilisent les 6 premiers chiffres pour enregistrer la date de naissance, par exemple 920315330 signifie que la date de naissance est le 03/15/1992, comment pouvez-vous calculer rapidement 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 comme suit :
=DATEDIF(DATE(IF(LEFT(C2,2)>TEXT(TODAY(),"YY"),"19"&LEFT(C2,2),"20"&LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),"y")
Actualités Entrer clé. Faites ensuite glisser la poignée de remplissage 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ée 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 didacticiels Excel :
Combinez plusieurs classeurs/feuilles de travail en un seul
Ce didacticiel répertorie presque tous les scénarios de combinaison auxquels vous pouvez être confronté et vous propose des solutions professionnelles relatives.
Fractionner les cellules de texte, de nombre et de date (séparer en plusieurs colonnes)
Ce didacticiel est divisé en trois parties : les cellules de texte fractionnées, les cellules de nombre fractionnées et les cellules de date fractionnées. Chaque partie fournit des exemples différents pour vous aider à savoir comment gérer le travail de fractionnement lorsque vous rencontrez le même problème.
Combinez le contenu de plusieurs cellules sans perdre de données dans Excel
Ce didacticiel réduit l'extraction à une position spécifique dans une cellule et rassemble différentes méthodes pour aider à extraire du texte ou des nombres d'une cellule par position spécifique dans Excel.
Comparez deux colonnes pour les correspondances et les différences dans Excel
Ici, cet article couvre 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é de bureau
Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%
- Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée
- Fusionner les cellules / lignes / colonnes et conservation des données; Contenu des cellules divisées; Combiner les lignes en double et la somme / moyenne... Empêcher les cellules en double; Comparer les gammes
- Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
- Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
- Formules favorites et insérer rapidement, Plages, graphiques et images; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
- Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules
- Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
- Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF
- Regroupement du tableau croisé dynamique par numéro de semaine, jour de la semaine et plus encore ... Afficher les cellules déverrouillées et verrouillées par différentes couleurs; Mettre en évidence les cellules qui ont une formule / un nom
- Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez 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!
