Liste de dates automatiques avec alternance de 1-2-3 semaine

Bonjour,

Dans le fichier ci-joint, j'essaie de créer une liste ou un tableau automatique de dates, en fonction de paramètres introduits par l'utilisateur...

Ces paramètres vont générer "une liste de dates" qui seront prises en compte dans le calcul des jours de maladies.

De la même manière que l'on peut calculer via formule le nombre de jours ouvrés d'une période X en retirant les jours fériés qui se trouvent dans un autre tableau, je voudrais également retirer les jours réguliers où le travailleur est en congé.

Exemple (voir fichier ci-joint):

Le travailleur est absent du 01/04/14 au 30/04/14.

Cette année-là, le lundi de pâques est tombé le 21, donc ça ferait:

  • 30 jours calendriers
  • 22 jours ouvrés
  • 21 jours prestés

Oui, mais là où ça se corse (pour moi), c'est que le travailleur pour une période de 1 an est en 4/5 ou mi-temps.

1 - Le 4/5 implique que toutes semaines, il a son jour de congé qui devrait être retiré aussi des jours ouvrés comme les fériés.

2 - Il se peut que le 4/5ème se fasse en deux semaines (ou plus): La 1ère semaine complète de la période de 1 an, il a congé le lundi; la 2ème, c'est le vendredi. Et ainsi de suite en alternance.

3 - De la même manière que le point 2, le mi-temps implique que la 1ère semaine, il travaille 3 jours et la 2ème, seulement 2 jours. Ce qui lui donne son mi-temps sur 2 semaines.

4 - Si la période de congé commence un mardi, ça implique que la 1ère semaine complète est le lundi qui suit. Ca doit comprendre que le début de la période de congé étant une semaine non-complète, il s'agit d'une semaine n°2 et donc, comprendre les non-prestés de la semaine 2.

Les paramètres de ces 4 points sont date de début, date de fin, date du lundi 1ère semaine complète, nombre de semaine et les jours non-prestés.

A partir de là, j'avoue, je ne suis pas très créatif, je ne sais pas du tout le chemin à emprunter dans excel...

Qu'en pensez-vous? Merci d'avance.

Cordialement,

Thomas

NB: Je me débrouille relativement bien en excel, mais pas du tout en vba. Et comme il s'agit d'un outil professionnel dont je voudrais me servir, j'essaie encore plus d'éviter le VBA. Sauf si...

18mal-test.xlsx (33.82 Ko)

Bonjour,

sujet intéressant mais complexe

quelques formules utiles dans le fichier simplifié joint

ce n'est qu'un début ... il faut encore coupler la recherche des mercredis avec les jours fériés, je poursuis

malheureusement NB.JOURS.OUVRES.INTL n'est pas dispo dans la version XL2013

8mal-test.xlsx (15.16 Ko)

Oui, mais là où ça se corse (pour moi), c'est que le travailleur pour une période de 1 an est en 4/5 ou mi-temps.

1 - Le 4/5 implique que toutes semaines, il a son jour de congé qui devrait être retiré aussi des jours ouvrés comme les fériés.

exemple pour le mercredi :

=NB.JOURS.OUVRES(A2;B2;Tableau1[JOUR])-SOMMEPROD(N(JOURSEM(LIGNE(INDIRECT([@[Début
période]]&":"&[@[Fin
période]]));2)=3))+SOMMEPROD((JOURSEM(Tableau1[JOUR];2)=3)*(Tableau1[JOUR]>=[@[Début
période]])*(Tableau1[JOUR]<=[@[Fin
période]]))

voici des formules plus complètes, il "suffit" de piocher dedans et combiner

12mal-test.xlsx (15.58 Ko)

Merci Steelson, je vais jeter un coup d'oeil à tes exemples demain.

Et la semaine prochaine, je pourrai tenter/tester une approche nouvelle grâce à toi avec un cas réel.

Je reste preneur de toute proposition supplémentaire.

Encore merci Steelson.

Vive le diables rouges ...

en fin de compte, toute la difficulté consiste à compter le nombre de jours off non fériés

j'ai du reste modifié la formule pour qu'elle soit plus intuitive

Nombre de mercredis non fériés :

=SOMMEPROD(N(JOURSEM(LIGNE(INDIRECT([@[Début
période]]&":"&[@[Fin
période]]));2)=mercredi))-SOMMEPROD((JOURSEM(Tableau1[JOUR];2)=mercredi)*(Tableau1[JOUR]>=[@[Début
période]])*(Tableau1[JOUR]<=[@[Fin
période]]))

où mercredi est le nom d'une zone qui comporte le chiffre 3

idem

Cas du lundi semaines paires et vendredi semaines impaires off :

=SOMMEPROD((MOD(NO.SEMAINE.ISO(LIGNE(INDIRECT([@[Début
période]]&":"&[@[Fin
période]])));2)=paire)*(JOURSEM(LIGNE(INDIRECT([@[Début
période]]&":"&[@[Fin
période]]));2)=lundi))-SOMMEPROD((JOURSEM(Tableau1[JOUR];2)=lundi)*(Tableau1[JOUR]>=[@[Début
période]])*(Tableau1[JOUR]<=[@[Fin
période]])*(MOD(NO.SEMAINE.ISO(Tableau1[JOUR]);2)=paire))+SOMMEPROD((MOD(NO.SEMAINE.ISO(LIGNE(INDIRECT([@[Début
période]]&":"&[@[Fin
période]])));2)=impaire)*(JOURSEM(LIGNE(INDIRECT([@[Début
période]]&":"&[@[Fin
période]]));2)=vendredi))-SOMMEPROD((JOURSEM(Tableau1[JOUR];2)=vendredi)*(Tableau1[JOUR]>=[@[Début
période]])*(Tableau1[JOUR]<=[@[Fin
période]])*(MOD(NO.SEMAINE.ISO(Tableau1[JOUR]);2)=impaire))

L'inconvénient ici de s'appuyer sur la parité du n° de semaines et que, si une année se termine avec la semaine 53 (impaire), la semaine suivante est forcément impaire aussi.

4mal-test.xlsx (17.51 Ko)

Bonjour,

Bonjour Steelson,

La fonction NB.JOUR.OUVRES.INTL() est apparue avec Excel 2010 !...

ce n'est qu'un début ... il faut encore coupler la recherche des mercredis avec les jours fériés, je poursuis

malheureusement NB.JOURS.OUVRES.INTL n'est pas dispo dans la version XL2013

Ma petite contribution du jour.

Cdlt.

7mal-test.xlsx (22.50 Ko)

La fonction NB.JOUR.OUVRES.INTL() est apparue avec Excel 2010 !...

Fabuleux ! je vais investiguer car excel me donne #NOM?


edit : ok, il manquait le S

NB.JOURS.OUVRES.INTL()

En attendant d'y voir plus clair avec NB.JOURS.OUVRES.INTL (mais à ce stade je ne suis plus sûr que cela soit indispensable et il faudrait que henryth valide le fait que sa version excel mac l'accepte) ...

voici quelques exemples, j'ai mis les jours et les semaines en paramètre (fond jaune) !

fais quand même une vérification précise avec un calendrier car je finissais par ne plus avoir les yeux en face des trous

9mal-test-tout.xlsx (20.73 Ko)

Oula, oula, du mouvement il y a eu.

Alors, coup d'oeil rapide, plus complexe que ce que je m'attendais. Je ne connais pas le fonctionnement de tes fonctions, Steelson...

Mais c'est peut-être l'occasion ou jamais.

Ceci dit, je crois comprendre l'idée.

A l'arrache avec un exemple de fiche d'un travailleur, il y a déjà pas mal d'incohérences même dans les formules les plus simples. Mais c'est justement une fiche qui pose problème.

Je testerai donc entre 2 dossiers (ou 200, ça dépendra du jour) la semaine prochaine.

Je suis un peu inquiet par rapport à la remarque sur la semaine 1 qui pourrait suivre une semaine 53...

En tout cas, merci pour le temps de réflexion et les fichiers attachés !!!!!!!

NB:

Chez moi sur mon mac, j'ai accès à NB.JOURS.OUVRES.INTL avec la version d'Office 15.33.

Au boulot (windows), mon office est 2013 pro, je pense. Certains autres pc et portables, où je pense que c'est 2010.

NB:

Chez moi sur mon mac, j'ai accès à NB.JOURS.OUVRES.INTL avec la version d'Office 15.33.

Au boulot (windows), mon office est 2013 pro, je pense. Certains autres pc et portables, où je pense que c'est 2010.

Très bien, on pourra reprendre cela avec des formules plus simples, voire avec celles de Jean-Eric.

Et réfléchir à la solution des semaines ... plutôt que paires/impaires, on fera le calcul modulo 7 ou 14 à partir d'une date de référence début de cycle.

Je suis en train de compléter les jours fériés et inclure un calendrier, histoire de pouvoir vérifier les calculs.

Avec la formule NB.JOURS.OUVRES.INTL, oublie tout ce qui précède pour la cas simple d'une semaine répétitive.

Pour le cas d'un jour fixe par semaine :

7mal-test-v2.xlsx (99.04 Ko)

Pour les alternances d'une semaine sur l'autre, je ne peux pas utiliser NB.JOURS.OUVRES.INTL (Jean-Eric a peut-être une idée).

Par contre, je peux revoir semaine paire/impaire pour réellement partir sur une semaine sur 2 à compter d'un lundi de référence.

Nouvelle version avec semaines A et B (définies dans le calendrier (en-tête)).

9nb-de-jours.xlsx (136.15 Ko)

Bonjour Steelson,

Wowowow! J'ai le sentiment que tu aimes bien la problématique que j'expose.

Jamais on ne m'avait répondu autant en si peu de temps.

Je suis au boulot, j'ai trop de travail que pour me concentrer là-dessus.

Alors, pour plus de facilité, je vais reproduire un cas pratique et y incluant les différentes "difficultés".

A très vite...

Pour tout avouer, entre autres "métiers" exercés (ingénierie, qualité, production, contrôle de gestion, gérant SCI, auto-entrepreneur) je me suis retrouvé aux ressources humaines ... et les dates, les heures, les jours fériés, les ponts etc. j'en passe et des meilleurs. Et ton sujet est très très intéressant pour moi ...

J'attends un exercice pratique (si nécessaire en mp, mais mieux vaut l'anonymiser).

Au passage, c'est dommage d'avoir un onglet par année ...

Pour tout avouer, entre autres "métiers" exercés (ingénierie, qualité, production, contrôle de gestion, gérant SCI, auto-entrepreneur) je me suis retrouvé aux ressources humaines ... et les dates, les heures, les jours fériés, les ponts etc. j'en passe et des meilleurs. Et ton sujet est très très intéressant pour moi ...

Evidemment...

Alors... J'ai ajouté un onglet au fichier.

La séparation en onglet partait du principe que chaque année, un octroi est fait à une date X (1-juil), mais sur la fiche, c'est noté comme dans le tableau. Cet octroi se fait un peu au cas par cas. Dans un premier temps, j'envisageais surtout d'automatiser le décompte par période et par total. C'est le plus fastidieux.

Les données sont fictives et j'y ai intégré un mi-temps et un 4/5ème. Dans un cas réel, il pourrait avoir depuis 2014 et au-delà de 2018, plusieurs 4/5ème et/ou mi-temps distincts. Chacun d'entre eux devrait être encodé au moins une fois. Sinon, ça "plantera" les totaux...

(+voir suite dans le fameux onglet)

14nb-de-jours.xlsx (141.48 Ko)

J'ai pris "semaine 1" = première semaine de 2014 ... à corriger si besoin dans la cellule intitulée REF

J'ai pris toutes les valeurs proposées dans les 2 tableaux et je les ai appliquées aux cas 4/5ème et mi-temps.

13nb-de-jours-1.xlsx (157.07 Ko)

Bonjour,

as-tu pris le temps de vérifier les valeurs trouvées ?

Bonjour,

as-tu pris le temps de vérifier les valeurs trouvées ?

Bonjour Steelson,

Je regrette d'avoir à dire que non ( ), je n'ai pas encore eu le temps de vérifier pleinement. Je ne vais pas dire que je croule sous le travail (et mes collègues aussi), mais... on n'en est pas loin. (Voilà pourquoi, je cherche des outils pour me faciliter la tâche.)

J'ai juste eu le temps de regarder et, au vu des 2 onglets (4/5 et mi-tps), je me demandais comment intégrer le tout en un seul? Dans l'exemple à la main que j'ai implémenté, c'est le cas.

En vérifiant que la date de début de maladie se trouve entre date de début et date de fin d'un période de prestations réduites qui se trouverait dans une autre liste/tableau contenant toute les périodes de prestations réduites distinctes? ou quelque chose comme ça?

EDIT: On n'en pas encore là, n'est-ce-pas! Ne va pas croire que je suis fainéant! :-p Là, je pense plus que je ne réfléchis.

bàt, henryth

Pour avoir implémenté quelques outils excel, je peux dire que dans certains cas j'ai obtenu 95% de gain de temps (et ce n'est pas moi qui le disais), de quoi orienter les RH vers des tâches à v raie valeur ajoutée.

Je vais voir pour rassembler le tout dans un seul et unique onglet.

Rechercher des sujets similaires à "liste dates automatiques alternance semaine"