Microsoft 365 Décaler une date au premier jour ouvré suivant si le résultat tombe un samedi, dimanche ou un jours férié

MelissaMrtl · 11 Avril 2025

Bonjour,

Je voudrais faire une formule pour décaler ma date au premier jour ouvré suivant, si le résultat tombe un samedi, dimanche ou un jour férié.

Je dois faire un fichier de process avec des délais à respecter, par exemple me donner une date à +70 jours donc j'utilise soit ma date du jour en fonction du besoin soit une date plus lointaine :
- ="15/04/2025"+70 ==> 24/06/2025
OU
-=aujourdhui()+70 ==> 20/06/2025

Bon aucun résultat tombe sur un samedi dimanche ou un jour férié, mais imaginons que c'est tombé sur le 09/06/2025 qui est férié, comment je peux reporter la date au premier jour ouvré qui suit, soit par exemple dans ce cas au 10/06/2025 ?

Merci pour votre aide

sylvanu · 11 Avril 2025

Bonjour MelissaMrtl, et bienvenu sur XLD,
Comme un jour férié peut tomber un vendredi, il faut faire J, J+1,J+2 ou J+3.
Un essai en PJ avec :

VB:

=SI(OU(JOURSEM(C5;2)>5;NB.SI(Férié;C5)>0);SI(OU(JOURSEM(C5+1;2)>5;NB.SI(Férié;C5+1)>0);SI(OU(JOURSEM(C5+2;2)>5;NB.SI(Férié;C5+2)>0);C5+3;C5+2);C5+1);C5)

Une feuille contient les jours fériés.
( à noter que le lundi de Pentecôte est férié ou non suivant les conventions de votre entreprise, donc il faut le conserver ou le supprimer )

mapomme · 11 Avril 2025

Bonjour MelissaMrtl et bienvenue sur XLD 😀,

Un petit classeur représentatif aurait été le bienvenu aussi😉comme la charte le recommande. Comme c'est votre première intervention sur XLD 😉, j'ai fait un petit classeur de démo.

Dans le classeur joint :

une feuille nommée "FeriesCol" qui liste les jours fériés de 2001 à 2082. Choisissez en cellule F1 si la liste des fériés inclut (VRAI) ou non (FAUX) les jours fériés en Alsace/Moselle. La liste des dates des jours fériés a aussi été nommée : FeriesCol
Dans la feuille "Feuil1" en colonne H, la formule : =SERIE.JOUR.OUVRE(F2-1;1;FeriesCol) qui donne le premier jour ouvrable.

nota : il y a aussi des jours fériées spécifiques à chaque tom-dom pas pris en compte dans la liste des jours fériés😟.

edit : Pas rafraichi donc pas vu tu avais répondu. J'en profite pour te saluer @sylvanu😃. Je l'avais oublié le cas de la Pentecôte. Dans ma boite, c'était non travaillé mais ça nous coûtait un jour de RTT (jour de RTT imposé par l'entreprise).

Remarque : d'aucuns disent que ma date de Pâques n'est pas correcte pour 2079. J'ai donc changé de formule pour le calcul du jour de Pâques (en fait je n'ai pas trouvé vraiment de démonstration pour montrer qu'une formule est juste ou non et si elle est juste alors jusqu'à quelle date) . J'en ai profité aussi pour ajouter la possibilité d'inclure ou non le lundi de Pentecôte.

Le fichier joint a donc été changé pour la version v2.

AtTheOne · 11 Avril 2025

Bonsoir à toutes & à tous, donc aussi à @sylvanu , @mapomme , @MelissaMrtl

Je me suis posé un petit chalenge : intégrer dans la liste des jours fériés en donnant l'année de début et le nombre d'années à prendre en compte et en intégrant à la formule l'algorithme de Butcher-Meeus. Warf ! Warf ! (pourquoi faire simple ?)
La Formule n'est valable qu'avec les versions 2024 et 365 (pourquoi faire utilisable par tous ?)

On s'accroche :

VB:

=LET(Nb;LIGNES(TS_Événements);
          A;_An_Début+TRONQUE(SEQUENCE(Nb*_Nb_An;;0)/Nb);
          Dt;INDEX(TS_Événements[Date];MOD(SEQUENCE(Nb*_Nb_An;;0);Nb)+1);
Pour_Butcher_Début;;
          n;MOD(A;19);
          c;ENT(A/100);
          u;MOD(A;100);
          s;ENT(c/4);
          t;MOD(c;4);
          p;ENT((c+8)/25);
          q;ENT((c-p+1)/3);
          e;MOD(19*n+c-s-q+15;30);
          b;ENT(u/4);
          d;MOD(u;4);
          l;MOD(32+2*t+2*b-e-d;7);
          h;ENT((n+11*2+22*l)/451);
          m;ENT((e+l-7*h+114)/31);
          j;MOD(e+l-7*h+114;31);
Pour_Butcher_Fin;;
         Pâques;SI((A>=1900)*(A<=9999);DATE(A;m;j)+1;"Hors domaine");
         Evt;INDEX(TS_Événements[Célébration];MOD(SEQUENCE(Nb*_Nb_An;1;0);Nb)+1);
         ListeDate;SI(Dt="";"";SI(ESTNUM(Dt);Pâques+CHOISIR(Dt;0;1;39;49;50;-2;-24);DATEVAL(Dt&" "&A)));
TRIER(FILTRE(ASSEMB.H(ListeDate;Evt);ListeDate<>"");1;))

Le résultat filtre les données vides et trie par ordre chronologique.
(il peut y avoir des données vides car copiant @mapomme , j'ai intégré des fêtes locales)
Le tableau structuré "TS_Événement" auquel se réfère la formule, liste toutes les fêtes sur une année en incluant les fêtes locales (mais leur date— Jour, Mois — n'est affichée que si la région concernée est cochée cf toujours @mapomme)

La cellule contenant la formule est baptisée "JoursFériés_Formule"
et le nom "JoursFériés" fait référence à la plage occupée par cette formule : JoursFériés_Formule#

On choisi l'année de départ et le nombre d'année et la liste suis automatiquement (chouettes les fonctions matricielles dynamique)

Maintenant que l'on a les jours fériés pour calculer la date arrivant après un certain délai, je vois 2 possibilités
(J'ai créé un TS avec une colonne Date_Début et Délai et 2 autres pour les 2 cas

On exclu du délai les week-ends et les jours fériés
utilisation de =SERIE.JOUR.OUVRE([@[Date début]];[@Délai];INDEX(JoursFériés;;1))
On n'exclue pas les week-ends ni les jours fériés du délai
utilisation de =SERIE.JOUR.OUVRE([@[Date début]]+[@Délai]-1;1;INDEX(JoursFériés;;1))
dans ce dernier cas SERIE.JOUR.OUVRE avec -1 ;1 sert à traiter le dernier jour (férié ou non)

Voilà je joins le fichier.
À bientôt

Jean-Eric · 12 Avril 2025

Bonjour,
@AtTheOne,
On va titiller un peu par ce beau matin d'avril. 😉
En 2049, le dimanche de Pâques est le 18 avril et non le 25 avril.
Maintenant, je peux me tromper et c'est dans 24 ans. !...
Cdlt.

AtTheOne · 12 Avril 2025

Bonjour à toutes & à tous, bonjour @Jean-Eric

Oups ! une erreur dans le terme de correction "h" un deux au lieu d'un "e" ça rime mais pour le calcul ça ne le fait pas !
La formule devient :

Enrichi (BBcode):

=LET(Nb;LIGNES(TS_Événements);
          A;_An_Début+TRONQUE(SEQUENCE(Nb*_Nb_An;;0)/Nb);
          Dt;INDEX(TS_Événements[Date];MOD(SEQUENCE(Nb*_Nb_An;;0);Nb)+1);
Pour_Butcher_Début;;
          n;MOD(A;19);
          c;ENT(A/100);
          u;MOD(A;100);
          s;ENT(c/4);
          t;MOD(c;4);
          p;ENT((c+8)/25);
          q;ENT((c-p+1)/3);
          e;MOD(19*n+c-s-q+15;30);
          b;ENT(u/4);
          d;MOD(u;4);
          l;MOD(32+2*t+2*b-e-d;7);
          h;ENT((n+11*e+22*l)/451);
          m;ENT((e+l-7*h+114)/31);
          j;MOD(e+l-7*h+114;31);
Pour_Butcher_Fin;;
         Pâques;SI((A>=1900)*(A<=9999);DATE(A;m;j)+1;"Hors domaine");
         Evt;INDEX(TS_Événements[Célébration];MOD(SEQUENCE(Nb*_Nb_An;1;0);Nb)+1);
         ListeDate;SI(Dt="";"";SI(ESTNUM(Dt);Pâques+CHOISIR(Dt;0;1;39;49;50;-2;-24);DATEVAL(Dt&" "&A)));
TRIER(FILTRE(ASSEMB.H(ListeDate;Evt);ListeDate<>"");1;))

Merci Jean-Eric pour ta vigilance
À bientôt

Jean-Eric · 12 Avril 2025

Re,
Et on re-titille.
C'est maintenant à @mapomme de "revoir sa copie" avec l'année 2079.
Le dimanche de Pâques est le 23 avril et non le 16 avril.
Bon, c'est dans 54 ans !... 😉😎
Bon weekend à tous.
Cdlt.

AtTheOne · 12 Avril 2025

re,
Les formules simplifiée du calcul de Pâques ne sont pas valable pour l'éternité !
L'algorithme, lui, de Butcher est réputé sans erreur (sauf si on écrit mal la formule 😉).
Enfin c'est sans compter sur les potentielles variations du comportement de la lune sur des grandes périodes. Le mois lunaire devrait quand même s'allonger d'une quarantaine de minutes d'ici 1 million d'année ! (Dixit ChatGPT) :

Quant à l'année terrestre, elle aura perdu 1 seconde !
Mais d'ici là je ne sais pas si Pâques sera toujours fêté.

À bientôt

AtTheOne · 12 Avril 2025

Bonjour @MelissaMrtl
Comme tu le vois, Nous sommes plusieurs à nous être penchés sur ton problème, ce serait gentil de nous dire ce que tu en penses 🙄 ...
À très bientôt (j'espère)

Staple1600 · 12 Avril 2025

Bonjour le fil

Histoire d'occuper mon après-midi dans VBA et PQ
1) macro VBA pour créer le jeu de données pour la requête PQ

VB:

Sub CREATION_TABLEAU_pour_PQ()
'A ne lancer qu'une fois
Dim r As Range
With ActiveSheet
    Set r = .Range("A2:A1177")
    .[A1] = "ANNEES": .[A2] = 2025: .[A3] = 2026: r.DataSeries: .ListObjects.Add(1, r.Offset(-1).Resize(1177), , 1).Name = "COL_ANS"
End With
End Sub

2) La requête PQ qui utilise l'algorithme de Gauss

PowerQuery:

et
    ETAPE_I = Table.AddColumn(Excel.CurrentWorkbook(){[Name="COL_ANS"]}[Content], "Date de Pâques", each
        let
            Year = [ANNEES],
            a = Number.Mod(Year, 19),
            b = Number.IntegerDivide(Year, 100),
            c = Number.Mod(Year, 100),
            d = Number.IntegerDivide(b, 4),
            e = Number.Mod(b, 4),
            f = Number.IntegerDivide(b + 8, 25),
            g = Number.IntegerDivide(b - f + 1, 3),
            h = Number.Mod(19 * a + b - d - g + 15, 30),
            i = Number.IntegerDivide(c, 4),
            k = Number.Mod(c, 4),
            l = Number.Mod(32 + 2 * e + 2 * i - h - k, 7),
            m = Number.IntegerDivide(a + 11 * h + 22 * l, 451),
            Month = Number.IntegerDivide(h + l - 7 * m + 114, 31),
            Day = Number.Mod(h + l - 7 * m + 114, 31) + 1
        in
            #date(Year, Month, Day)
    ),
    RESULTAT = Table.TransformColumnTypes(ETAPE_I,{{"Date de Pâques", type date}})
in
  RESULTAT

Apparemment la date de Pâques en 3200 est bonne 😉
(je ne sais pas ce qui m'a poussé à vérifier cette année en particular ?
Peut-être l'espoir qu'en 3200, XLD sera toujours en ligne ! 😉
source de vérification

Maintenant la question est : fallait-il que je le sache qu'en 3200, Pâques sera le 26 mars 😉

AtTheOne · 12 Avril 2025

Salut @Staple1600, sauf erreur de ma part, c'est l'algorithme de BUTCHER que tu utilises,

mais bon je peux me tromper (cf post #4😉 )

À bientôt

Staple1600 · 12 Avril 2025

Re

@AtTheOne
Tout ce que je sais que je suis en congés la semaine qui suit Pâques 2025
Bon bah , je retourne dans PQ et dans les Saintes-Ecritures 😉

PS: Au fait les dates renvoyées par PQ sont bonnes ou pas ?

Dans ce cas, j'aurais juste à éditer mon précédent message, alors 😉

Cousinhub · 12 Avril 2025

Hi,
JM
D'après la formule de Roger, les dates sont exactes.
Nice ! 👏

Staple1600 · 12 Avril 2025

Re

@Cousinhub
J'aurais du lire ton message avant @Cousinhub
Mais non , je me suis fait plaisir dans PQ pendant que ma soupe à l'oignon cuisant à feu doux 😉

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="COL_ANS"]}[Content],
    COL_BUTCH = Table.AddColumn(Source, "Date de Pâques (Butcher)", each 
        let
            Year = [ANNEES],
            a = Number.Mod(Year, 19),
            b = Number.IntegerDivide(Year, 100),
            c = Number.Mod(Year, 100),
            d = Number.IntegerDivide(b, 4),
            e = Number.Mod(b, 4),
            f = Number.IntegerDivide(b + 8, 25),
            g = Number.IntegerDivide(b - f + 1, 3),
            h = Number.Mod(19 * a + b - d - g + 15, 30),
            i = Number.IntegerDivide(c, 4),
            k = Number.Mod(c, 4),
            l = Number.Mod(32 + 2 * e + 2 * i - h - k, 7),
            m = Number.IntegerDivide(a + 11 * h + 22 * l, 451),
            Month = Number.IntegerDivide(h + l - 7 * m + 114, 31),
            Day = Number.Mod(h + l - 7 * m + 114, 31) + 1
        in
            #date(Year, Month, Day)
    ),
    COL_GAUSS = Table.AddColumn(COL_BUTCH, "Date de Pâques (Gauss)", each 
        let
            annee = [ANNEES],
            a = Number.Mod(annee, 19),
            b = Number.Mod(annee, 4),
            c = Number.Mod(annee, 7),
            k = Number.IntegerDivide(annee, 100),
            p = Number.IntegerDivide(13 + 8 * k, 25),
            q = Number.IntegerDivide(k, 4),
            M = Number.Mod(15 - p + k - q, 30),
            N = Number.Mod(4 + k - q, 7),
            d = Number.Mod(19 * a + M, 30),
            e = Number.Mod(2 * b + 4 * c + 6 * d + N, 7),
            mars = 22 + d + e,
            datePaques = if mars <= 31 then 
                             #date(annee, 3, mars) 
                         else 
                             #date(annee, 4, mars - 31)
        in
            datePaques
    ),
    FIN_B = Table.TransformColumnTypes(COL_GAUSS, {{"Date de Pâques (Butcher)", type date}}),
    FIN_G= Table.TransformColumnTypes(FIN_B, {{"Date de Pâques (Gauss)", type date}}),
    FIN = FIN_G,
    COMPARAISON = Table.AddColumn(FIN, "COMPARAISON", each if [#"Date de Pâques (Butcher)"] = [#"Date de Pâques (Gauss)"] then "OK" else "PAS OK"),
    FILTRAGE = Table.SelectRows(COMPARAISON, each ([COMPARAISON] = "PAS OK"))
in
    FILTRAGE

On trouve douze différences entre les deux algorithmes.

A+ pour de nouvelles aventures Exceliennes 😉 (et joyeuses Pâques 2025 à ceux qui les célèbrent 😉 )

mapomme · 15 Avril 2025

Bonjour à tous😉,

Je me suis, semble-t-il, planté sur la date de Pâques 2079. C'est loin.

La seule chose sûre que je sais, c'est qu'en 2079, je serai depuis longtemps à six pieds sous terre et que mes os auront été blanchis non pas par l'action du soleil mais par la voracité des petits vers, asticots, moisissures et autres petites bestioles visibles ou non dont je ne veux en aucune façon imaginer la forme, la couleur et le bruit dissonant et grinçant de leurs mandibules.

J'avais pensé arrêter mon calendrier des fériés à l'année 2047 (solution simple, rapide et astucieuse). Ainsi, plus de problème et en plus le fichier aurait été allégé.

Mais craignant par cette manœuvre fallacieuse d'être soumis à la vindicte des cadors de XLD et clouer au pilori pour y être sauvagement lapidé, j'ai préféré changer de formule pour les jours fériés🤪. J'en ai profité pour y ajouter la possibilité de considérer ou pas le lundi de Pentecôte comme un jour férié.

Aller sur le message d'origine pour le classeur v2.

Microsoft 365 Décaler une date au premier jour ouvré suivant si le résultat tombe un samedi, dimanche ou un jours férié

MelissaMrtl

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

AtTheOne

XLDnaute Accro

Pièces jointes

Jean-Eric

XLDnaute Occasionnel

AtTheOne

XLDnaute Accro

Pièces jointes

Jean-Eric

XLDnaute Occasionnel

AtTheOne

XLDnaute Accro

AtTheOne

XLDnaute Accro

Staple1600

XLDnaute Barbatruc

AtTheOne

XLDnaute Accro

Staple1600

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Discussions similaires

Microsoft 365 Décaler une date au premier jour ouvré suivant si le résultat tombe un samedi, dimanche ou un jours férié

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Accro

Pièces jointes

XLDnaute Occasionnel

XLDnaute Accro

Pièces jointes

XLDnaute Occasionnel

XLDnaute Accro

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

Discussions similaires

Privacy & Transparency

Privacy & Transparency