Fonction cachée Excel DATEDIF

Cet article vous apporte des connaissances pertinentes sur excel. Il présente principalement les problèmes liés aux fonctions cachées, principalement la fonction DATEDIF. Examinons-le ensemble, j'espère qu'il sera utile à tout le monde.

Recommandations d'apprentissage associées : Tutoriel Excel

Il existe un type de fonction dans Excel appelé fonctions cachées. Vous ne pouvez pas les trouver dans la liste des fonctions d'Excel, et elles ne sont même pas pertinentes dans la description des fichiers d'aide de Microsoft. , mais ils sont non seulement puissants, mais également largement utilisés dans le travail.

DATEDIF est une fonction magique cachée qui existe dans Excel, mais elle est introuvable dans le fichier d'aide d'Excel.

Aujourd'hui, nous allons jeter un œil à cette fonction magique cachée.

1. Explication détaillée de la fonction DATEDIF

La syntaxe de base de DATEDIF est :

DATEDIF (date de début, date de fin, type d'intervalle)

Parmi eux, les paramètres start_date et end_date sont deux dates, et le le premier ne doit pas être plus grand que le second qui.

l'unité possède les 6 paramètres suivants, qui sont utilisés pour calculer différentes différences, comme indiqué dans le tableau ci-dessous.

Paramètres de DATEDIF

Dans l'orthographe quotidienne, certaines personnes manqueront le D au milieu du nom de la fonction et le changeront en DATEIF. C'est faux et le système Excel ne vous demandera pas lors des erreurs de frappe.

Ce mot de fonction a un moyen simple de se souvenir : DATEDIF est abrégé en Date Different, qui se traduit par des dates différentes.

2. Calculez les intervalles d'année, de mois et de jour entre deux dates

Comme le montre la figure ci-dessous, il s'agit de l'utilisation conventionnelle de DATEDIF. Nous pouvons expliquer la signification réelle de ces six paramètres en fonction des données de la figure. .

Utilisation générale de DATEDIF

Tout d'abord, entrez les formules suivantes dans les cellules D16 et D24 respectivement, et copiez-les respectivement dans les cellules D21 et D29 pour calculer la différence des différents paramètres :

=DATEDIF(E16, F16 ,C16)

=DATEDIF(E24,F24,C24)

Cellule D24, paramètre "Y", en regardant seulement 2017 et 2020, le nombre d'années devrait être de 3, mais du 2017/7/28 au 2020/2 /8, 2 ans se sont écoulés jusqu'au 2019/7/28, et il n'a pas atteint le 2020/2/8 requis, si une autre année passe, il atteindra le 2020/7/28, ce qui dépassera la date de fin, donc le le résultat renvoie 2 , ne peut pas renvoyer 3.

Cellule D25, paramètre "M", 30 mois après le 28/7/2017, elle atteint le 28/01/2020, puis 1 mois plus tard, elle atteint le 28/02/2020, dépassant la date de fin du 2/2020/ 8, donc le résultat ne peut être que 30, pas 31. Il faut bien comprendre le sens du mot « entier » dans « le nombre d'années entières » et « le nombre de mois entiers ».

Cellule D26, le paramètre "D" équivaut à soustraire directement deux dates pour calculer la différence en jours.

Cellule D27, paramètre "MD", ce calcul ignore le mois et l'année, ce qui équivaut à tirer start_date à la date la plus proche avant end_date. C'est-à-dire rapprocher le 28/7/2017 de la date la plus proche avec une date de 28 avant le 8/2020, soit le 28/01/2020, puis calculer la distance entre le 28/01/2020 et 2020. /2/8 La différence en jours est de 11 jours.

Cellule D28, paramètre "YM", ignorez le jour et l'année pour calculer le nombre de mois entiers, ce qui équivaut à ramener le 28/7/2017 au 28 juillet le plus proche avant le 8/2/2020, devenant 7/2019 28, puis calculez la différence du mois "entier" entre celui-ci et le 8/2/2020, soit 6 mois.

Cellule D29, paramètre "YD", ignore la différence de jours dans le calcul de l'année, ce qui équivaut à tirer start_date à la date la plus proche du même mois et du même jour avant end_date. C'est-à-dire rapprocher le 28/7/2017 du 28/7/2019, puis calculer la différence en nombre de jours entre le 28/7/2019 et le 8/2020, qui est de 195 jours.

Lors de l'utilisation des paramètres « MD » et « YD » pour calculer la différence de jour, en raison de l'existence d'années bissextiles, il y aura parfois une différence d'un jour par rapport à la valeur idéale. Cette situation n'affecte généralement pas notre utilisation quotidienne. .

3. La différence entre l'année entière, le mois et le jour

Comme le montre la figure ci-dessous, la comparaison entre le 28/7/2017 et le 27/7/2020 et le 28/7/2017 au 28/7/2020 est répertorié, bien que end_date uniquement. La différence est de 1 jour, mais les résultats sont assez différents. Le principe de calcul est le même et il faut comprendre le sens du mot « tout ».

La différence entre l'année entière, le mois et le jour

Comment devez-vous mémoriser autant de paramètres ? Dans un premier temps, il faut connaître le rôle de cette fonction et comprendre le principe de calcul de chaque paramètre. Si vous avez souvent besoin de calculer des dates au travail, vous pouvez l'imprimer et le coller à côté de votre bureau pour référence immédiate.

4. Cas : Calcul de l'ancienneté

Supposons que nous soyons aujourd'hui le 28/7/2019 et que la date à laquelle chaque employé a commencé à travailler est celle indiquée dans la colonne C de la figure ci-dessous. personne? L'ancienneté peut être exprimée en m années et n mois. Le

Le calcul de l'ancienneté

peut être effectué étape par étape. Calculez d’abord le nombre d’« années entières », puis le nombre de « mois entiers ». Lors du calcul du nombre de mois, vous devez noter que la valeur maximale du nombre de mois ne dépassera pas 11, car 12 mois feront un an, c'est-à-dire que l'existence de l'année doit être ignorée pour calculer le nombre de mois. . Alors quel paramètre utiliser pour le calcul ?

Comme le montre le tableau de comparaison des paramètres de DATEDIF mentionné dans la section précédente, le paramètre « Y » est utilisé pour calculer le nombre d'années entières, tandis que le paramètre « YM » est utilisé pour calculer le nombre de mois entiers indépendamment de l'année. Ainsi, la formule de fonction de la cellule D51 peut être écrite comme suit :

(faites glisser vers la gauche et la droite pour afficher la formule complète)

=DATEDIF(C51, "2019/7/28″", "Y")&"Year"&DATEDIF( C51, "2019/ 7/28", "YM") et "mois"

Jetons un coup d'œil à la plage de cellules D54 : D56. Il n'y a qu'une différence de 1 jour, donc les résultats du calcul sont différents. Par conséquent, lorsque vous utilisez DATEDIF, vous devez toujours avoir un concept « global » à l’esprit.

De plus, les paramètres Y, M et D dans DATEDIF peuvent être en majuscules ou en minuscules.

5. Cas : Calcul des jours de congé annuel

Le « Règlement sur les congés annuels payés des salariés » stipule que si un salarié a travaillé 1 an mais moins de 10 ans au total, le congé annuel est de 5 jours ; le salarié a travaillé 10 ans mais moins de 20 ans, le congé annuel est de 5 jours ; si le salarié a travaillé 10 ans mais moins de 20 ans, le congé annuel est de 10 jours ; pendant 20 ans ou plus, le congé annuel est de 15 jours.

De même, en supposant que nous soyons le 28/7/2019, à combien de jours de congé annuel chaque employé a-t-il droit ?

En fait, cette question est plus simple que le cas de la section précédente. Il suffit de connaître le nombre d'années travaillées par chaque employé.

Comme le montre l'image ci-dessous, entrez la formule suivante dans la cellule D66 pour calculer le nombre d'années de travail de chaque employé :

=DATEDIF(C66,DATE(2019,7,28),"Y")

Calcul des jours de congés annuels

Je souligne encore ici que si vous utilisez la méthode de saisie raccourcie pour exprimer la date dans la formule, vous devez ajouter des guillemets doubles, tels que "DATEDIF(C51,"2019/7/28″, "Y")" dans la section précédente. Si vous ne maîtrisez pas l'utilisation des guillemets doubles, utilisez simplement la fonction DATE de manière disciplinée pour vous assurer de ne pas commettre d'erreurs.

En fonction du nombre d'années dans la colonne D, vous pouvez calculer le nombre de jours de congé annuel légal Entrez la formule suivante dans la cellule E66 :

=RECHERCHE(D66,{0,1,10,20},{. 0,5,10,15} )

Recommandations d'apprentissage associées : tutoriel Excel

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!