Problème calcul entre 2 dates avec année de 360 jours [Résolu]

Signaler
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020
-
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020
-
Bonjour,

Je me permets de vous contacter car j'ai un souci sur Excel. Je travaille dans un hôpital et suis amenée à calculer l'ancienneté des salariés, puis à les positionner sur une grille selon leur ancienneté. Ici nous devons prendre en compte une année de 360 jours et 1 mois de 30 jours. Ma collègue qui fait son calcul à la main trouve toujours la bonne date contrairement à moi qui travaille sur Excel. J'utilise la fonction JOURS360 mais cela ne fonctionne pas.

Exemple : Embauche au 01/04/2019. Calculer l'ancienneté d'un salarié du 01/01/2017 au 31/03/2019 inclus. Avec la fonction JOURS360, je trouve 809 jours et une date d'ancienneté au 12/01/2017. Ma collègue trouve une date d'ancienneté au 22/12/2016 et ma responsable est d'accord avec ma collègue. Pourriez-vous me dire ce que vous trouvez svp ? Merci par avance pour votre aide qui me sera précieuse pour mes futures reprises d'ancienneté. Bien cordialement,
Naï

14 réponses

Messages postés
25172
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 juillet 2020
5 352
Bonjour
comment peut on calculer l'ancienneté du 1/1/17 au 31/3/19 d'un embauché le 1/4/19?
il doit manquer quelque chose dans vos infos?
à vous lire
crdlmnt


Messages postés
25172
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 juillet 2020
5 352
pour déposer un fichier:
sur ce lien:
http://mon-partage.fr
en haut de page : "parcourir" sélectionnez votre fichier
en bas de page "uploader" cliquez
après le chargement, revenez en haut de page "lien de téléchargement"
copiez le lien
revenez le coller ici dans un prochain message
à vous lire
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Je vous remercie. Je ne peux pas aller sur ce site car mon établissement l'a bloqué. Je vous l'enverrai ce soir de chez moi. Merci.

Cdt
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Bonsoir,

Désolée je n'ai pas eu le temps de me reconnecter avant et entre temps j'ai trouvé la solution. Je vous joins tout de même le tableau afin que vous puissiez comprendre le charabia que je vous ai raconté plus haut :) et pour que vous me disiez si mes formules sont correctes svp.

Voici le lien : https://mon-partage.fr/f/Hk3dbnDw/

Donc pour grâce à vous, la date d'ancienneté trouvée est du 22/12/2016 (date rose).

J'étais ensuite à la recherche de la date de la case coloriée en bleu. Logiquement mon salarié ayant moins de 3 ans d'ancienneté, je l'ai positionné à l'échelon 2. Je dois tout de même lui ajouter ses 1 an d'ancienneté acquis. 22/12/2016 + 1 an = 22/12/2017. Et je n'arrivais pas à trouver la formule adéquate pour tomber sur cette date. J'ai l’impression que mon nombre de jours total de 819 est faux et que Excel est toujours sur la base de 365 / an. Du coup si vous regardez la formule, j'ai juste ajouté la durée du 1er échelon, soit 365 jours. J'avoue que j'ai du mal à comprendre pourquoi parfois il y a 360 et d'autres fois 365 sur mon tableau.

En tout cas je tenais vraiment à vous remercier de m'avoir aidée. Ma collègue et ma responsable étaient persuadées que je n'y arriverai pas et que je devrai m'y prendre à la main... Puis c'est vous qui avez réussi pour moi.

Merci pour votre précieuse aide.

Cdt
Messages postés
25172
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 juillet 2020
5 352
Bonjour
j'ai moi aussi du mal à faire le tri dans vos données entre le 365 et le 360
les seules choses que je peux proposer sont
  • la formule en B40 pour décaler la date d'un an (donc 12 mois) jour pour jour incluant les bissextiles
  • les formules de calcul direct en M19/M20/M21 (sur base 360, et 12 mois de 30 jours) avec le formatage qui affiche le texte sans modifier la valeur numérique des cellules

voir fichier joint en retour
Si votre problème est résolu, merci de pointer en résolu (menue roue dentée à droite du titre du fil)
https://mon-partage.fr/f/4n3mtOLF/
Bonne route, revenez si besoin
bien cordialement
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Bonjour Vaucluse,

Vous êtes au top ! Merci de m'avoir simplifié la vie :-D !

J'ai mis le problème en résolu :)

Merci encore.

Bien à vous,

Naïlou2509
Bonjour,
Par simple curiosité, pourquoi votre entreprise calcule-t-elle l'ancienneté de ses employés en prenant en compte des années de 360 jours et des mois de 30 jours ? Cette convention est (était) utilisée par les banques pour les calculs d'escompte, car cela simplifie les calculs, mais dans votre cas, je ne vois pas bien la raison...
Cordialement.

Ps : Ma calculatrice HP17BII effectue ce genre de calculs, soit en années réelles, soit en années de 365 jours, c'est à dire en ne tenant pas compte du jour supplémentaire des années bissextiles, soit en années de 360 jours et de 12 mois de 30 jours.
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Bonjour,

J'ai posé exactement la même réponse à ma responsable qui m'a simplement répondu que c'était comme ça depuis toujours à l'hôpital, sans aucune autre explication.

En cherchant une solution à mon problème, j'ai lu qu'il s'agissait d'une formule comptable...
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Bonjour Edgard,

J'ai reçu un mail me disant que vous m'aviez répondu. Dans ce mail j'ai le début de votre réponse, mais lorsque je souhaite accéder à la totalité de votre message et y répondre sur ccm, je ne retrouve pas votre 2nd message...
Bonjour Nailou,
Je n'ai posté qu'un message, le 25/09 à 9:33, puis un autre ce jour à 11:00 modifié à 11:09 à l'attention d'eriiic et enfin celui ci.
Cordialement.
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Rebonjour,

Je me permets de revenir vers vous car je me suis rendue compte qu'il y avait une erreur dans le calcul de ma date d'ancienneté. J'avais calculé le nombre de jours total à 819 jours, parce que j'avais fait la différence entre la date de début et la date de fin, en prenant des années de 365 jours... J'ai donc modifié la formule et suis partie sur des années de 360 jours.

Du coup, en calculant l'ancienneté du 01/01/2017 au 31/03/2019 inclus, toujours en restant sur une année de 360 jours, cela fait 810 jours, soit 2 ans et 3 mois pile poil (et plus 2 ans, 3 mois et 9 jours). La date d'ancienneté (case rose) devrait donc être au 01/01/2017 et non au 22/12/2016 (et la date d'ancienneté dans l'échelon, en bleu, devrait être au 01/01/2018). Je n'arrive pas à modifier la date... Pourriez-vous une nouvelle fois m'aider svp ?

Voici le fichier : https://mon-partage.fr/f/XEQbiQeB/

Merci bien.

Cdt,
Messages postés
25172
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 juillet 2020
5 352
????
ça demande des explications
je n e comprend pas l'utilité de cet empilage de calcul pour aboutir en fait au résultat qui dit que la date d'ancienneté et celle du premier jour de la colonne B soit donc à la date en B22
il suffirait dans tous les cas de placer en F29 la formule =B22
(B39 s'ajuste tout seul sur F29)
C'est en fait ce que vous aviez calculé au début et qui semblait ne pas convenir à votre collègue?

Messages postés
25172
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 juillet 2020
5 352
Moi je ne vois que ça
dans ce fichier trois solutions de calcul qui donne le même résultat
https://mon-partage.fr/f/nk0BdWA4/
(avec +1 pour inclure les limites)
je ne vois pas ce que l'on peut inventer d'autre
Bien cordialement
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Vaucluse : merci pour tes calculs. J'avoue que je ne comprends pas ta 1ère solution. Idem pour ton tableau "via un renvoi", où tu calcules le nombre de jours exacts. C'est effectivement la différence réelle entre les dates mais ce n'est pas arrondi à 360 jours.

Bravo pour les 2 autres formules. C'est épatant que tu puisses trouver ça... Je pense que je ne suis pas assez calée en Excel pour comprendre le calcul ... je ne connais pas la formule "MAX" :-/ sorry. Mais les 2 m'ont l'air correctes.

Je peux utiliser l'une ou l'autre du coup ? Elles aboutissent sur la même date.

Tu penses que si, pour un autre exemple, avec plusieurs dates non consécutives, j'utilise tes formules, je tomberai sur un compte juste ?

Merci encore.

Bien cdt
Messages postés
25172
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 juillet 2020
5 352
Le meilleurs moyen de savoir c'est d'essayer. En ce qui me concerne, j'ai bien du mal à comprendre ce que tu veux obtenir si les périodes sont séparées
Tu prends la formule que tu veux, elle donneront quoiqu'il en soit toujours le même résultat, c'est à dire:
  • prendre la date maxi des fins de périodes du tableau moins le total des nombres de jours de chaque période

Crdlmnt
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Merci ! J'ai tenté avec un autre exemple et ça a fonctionné :)
Messages postés
25172
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 juillet 2020
5 352
Alors on souhaite que vous ayez toutes les solutions maintenant!
pensez à repasser le sujet en résolu
Bonne route et au plaisir
Bien cordialement
Messages postés
23379
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 juillet 2020
6 229
Bonjour à tous,

Je ne vois pas pourquoi tu cherches à calculer une date fictive de début.
Il faut calculer l'ancienneté d'indice avec des mois 30 j à partir de l'ancienneté en j.

Pour =JOURS360() j'utiliserai plutôt la méthode européenne +1 j :
=JOURS360(B22;C22;1)+1

Et j'ai un gros doute sur le fait de prendre en compte le taux d'activité (?!?)
Quelqu'un à 50% resterait 2 ans à l'échelon 1. Tu es sûre de ton coup là ?
Regarde ce que tu en penses : https://www.cjoint.com/c/IIBiJuV5nv7
eric

En essayant continuellement, on finit par réussir. 
Donc plus ça rate, plus on a de chances que ça marche.(les Shadoks)
En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Date d'ancienneté dans l'échelon 2. Si vous reprenez mon tableau initial, j'avais mis une case en rose, avec la date d'ancienneté dans l'échelon (+ les 12 mois de l'échelon 1). C'est cette même date, mais avec ta formule je n'y parviens pas...
Messages postés
23379
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 juillet 2020
6 229
Et la réponse à 2nde question ?
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Non ce ne sera pas toujours des mois entiers.
Messages postés
23379
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 juillet 2020
6 229
Ah, dommage...
Un essai, ce n'est jamais évident avec les calculs de dates.
Contrôle avec des cas réels dont tu as la réponse sûre.
https://mon-partage.fr/f/TWa0SaPd/
eric
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Je n'ai pas accès au site mon-partage au boulot, je regarderai ce soir, merci :)
@eriiic
Bonjour,
Auriez vous une source concernant la norme européenne 360J +1 ?C
Merci d'avance.
Cordialement.
Messages postés
23379
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 juillet 2020
6 229
Je regarde juste l'aide sur cette fonction et je me dis que si un paramètre est prévu pour la méthode européenne, je vais privilégier celle-ci.
La différence entre les 2 est expliquée dans l'aide.
eric
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Merci à tous, je passe le sujet en résolu. Je reviendrai vous dire à l'avenir si tout fonctionne.

Bonne journée et bon week-end :)
Bonsoir tout le monde,

C'est encore moi avec mes questions bizarres :).

En faisant une nouvelle reprise d'ancienneté, je suis confrontée à un nouveau problème. Je ne sais pas si vous allez me comprendre...

Voici le lien vers mon tableau excel : https://www.cjoint.com/c/ILbxlNEAFfc

En cumulant l'ancienneté de toutes les expériences, et en utilisant la formule de Vaucluse (JOURS360) j'ai trouvé une ancienneté de 11, ans, 9 mois et 27 jours. Jusqu'ici tout va bien. Ensuite, je dois positionner mon agent sur la grille, afin de le positionner sur un échelon et de lui donner une date d'ancienneté DANS cet échelon. Ce qui me pose problème, c'est que je n'arrive pas à calculer la date d'ancienneté dans l'échelon en prenant en compte des années de 360 jours.

Exemple : Un agent est mis en stage le 01/01/2020. Il faut 5 ans pour acquérir le 4ème échelon et 6 ans pour acquérir le 5ème échelon. L'agent en question a 5 ans et 6 mois d'ancienneté. Il est positionné au 4ème échelon avec 6 mois d'ancienneté. Je dois donc trouver la date d'ancienneté dans cet échelon, soit le 01/07/2019.

Avec ma formule actuelle (case rose B38), le souci, est qu'il déduit le nombre de jours acquis. Du coup, je me retrouve avec une fausse date. Car il ne tient pas compte des années à 360 jours.

Dans mon tableau excel, je dois normalement trouver la date de 03/12/2015 comme date d'ancienneté en B38 (calculé à la main avec ma collègue, et ça me paraît logique car elle a déjà acquis 3 ans, 9 mois et 27 jours, et pour acquérir les 4 ans nécessaires au passage au prochain échelon, il lui reste 2 mois & 3 jours, soit le 03/12/2019). Mais je n'y arrive pas.

Dites-moi s'il faut que je réexplique quelque chose.

Par ailleurs, pensez-vous qu'il soit possible d'informatiser les colonnes de B35 à B38 ? Car je la complète à la main mais je pense qu'il est possible que le calcul se fasse automatiquement.

Merci à tous pour votre aide.

Bien à vous,

Nailou
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Personne pour m'aider :) ?

Merci.

Nailou2509
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Je remets le lien du fichier, au cas où quelqu'un voudrait bien m'aider :) :

https://www.cjoint.com/c/JAco5wOkfdD

Merci !

Nailou2509
Messages postés
23379
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 juillet 2020
6 229
Bonjour,

avec la meilleur volonté du monde (arrondi.sup soustrait) je n'arrive qu'au 04/12/2015
=MOIS.DECALER(B14;-(B37/30))-ARRONDI.SUP(MOD(B37;30)/30*JOUR(FIN.MOIS(MOIS.DECALER(B14;-(B37/30))-MOD(B37;30);0));0)

Et avec
=JOURS360(B38;B14)
je retrouve bien 1377. A voir si tu maintiens ton -1

Sinon en partant du début du mois :
=MOIS.DECALER(B14;-(B37/30)-1)+30-MOD(B37;30)-1

on peut arriver au 03/12

Je m'étonne de ta façon de compter les durées.
Pour moi travailler du 17/11/2008 au 16/01/2009 ça fait 60 jours, pas 59.
eric


En essayant continuellement, on finit par réussir. 
Donc plus ça rate, plus on a de chances que ça marche.(les Shadoks)
En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Bonjour Eric,

Un grand merci pour ton aide.

Je m'étonne également de la façon de mon établissement de compter les durées... Honnêtement j'ai le même raisonnement que toi, mais ma manière de compter est a priori fausse selon eux. En gros je dois toujours raisonner en 360 jours par an.

Les 2 formules fonctionnent (même si je n'ai rien compris au calcul o_O ...). 04/12 ou 03/12, peu importe, pour un jour ça n'a pas d'importance. Je ne sais pas laquelle prendre du coup...

Merci encore :-D

Nailou2509
Messages postés
23379
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 juillet 2020
6 229
Bonjour,

je ne te parlais pas des années 360.
Mais quelqu'un qui travaille du 1 au 2 a pour moi 2 jours d'ancienneté, pas 1.

Prend la 2nde qui présentera moins souvent un écart.
eric
Messages postés
27
Date d'inscription
mardi 24 septembre 2019
Statut
Membre
Dernière intervention
3 janvier 2020

Merci à tous !

Nailou