XL 2021 Planning chantier avec SERIE.JOUR.OUVRE.INTL

Créer une discussion
Répondre
  • Initiateur de la discussion Initiateur de la discussion kdet
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
kdet

kdet

XLDnaute Occasionnel
Bonsoir à tous, le forum,

J'aurai besoin de votre aide sur la fonction SERIE.JOUR.OUVRE.INTL
Dans mon planning, le chantier fonctionne du lundi au vendredi.
En G10 j'ai utilisé cette formule : LET(deb;[@DEBUT];dur;[@DUREE];fer;Fériés;weekend;11;SI(OU(ESTVIDE(deb);ESTVIDE(dur));"";SERIE.JOUR.OUVRE.INTL(deb;dur-1;1;fer))) et il y a deux résultats incohérents. Je ne sais pas ce qui cloche sur la formule.
J'aimerais apporter quelques précisions : le chantier peut exceptionnellement travailler le Samedi, Dimanche et Jous Fériés si on devrait finir le chantier en avance.
Je voudrais les inclure dans la formule mais je ne sais comment faire.
Je ne sais pas si c'est possible, sinon auriez-vous une solution.

Vous remerciant par avance de votre aide.
kdet
 

Pièces jointes

Trier par date Trier par votes
Bonjour kdet, le forum,

Si je comprends bien vous voulez pouvoir avancer quand c'est possible la date de FIN de la feuille "Planning".

Pour cela le plus simple est d'ajouter la colonne AVANCE et d'utiliser en colonne G cette fonction VBA :
VB: 
Function FIN(deb As Range, duree As Range, fer As Range, avance As Range)
Dim jour&, n&
On Error GoTo 1
For jour = deb To deb + 10000
    If Weekday(jour, 2) < 6 And Application.CountIf(fer, jour) = 0 Then n = n + 1
    If n = duree Then FIN = jour - avance: Exit For
Next
1 If FIN = 0 Then FIN = ""
End Function
Le remplissage manuel des colonnes E F H du tableau est piloté par cette macro :
VB: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fer As Range, avance&, n&, com$, jour&
With ListObjects(1).Range 'tableau structuré
    Set Target = Intersect(Target, .Cells)
    If Target Is Nothing Then Exit Sub
    Set Target = Intersect(Target.EntireRow, .Columns(7))
End With
Application.EnableEvents = False 'désactive les évènements
Target.Offset(, -1).ClearComments 'RAZ
Set fer = [Fériés]
For Each Target In Target 'si entrées ou effacements multiples
    If IsDate(Target.Offset(, -1)) Then
        avance = Abs(Int(Val(Target))): Target = IIf(avance, avance, "")
        If Target.Offset(, -1) < Target.Offset(, -3) + Target.Offset(, -2) - 1 Then
            Target.Select
            MsgBox "L'avance est trop grande et va être effacée !", 48
            Target = ""
        End If
        If Target > 0 Then
            n = 0: com = "Travail :"
            For jour = Target.Offset(, -1) + avance To 1 Step -1
                If Weekday(jour, 2) > 5 Or Application.CountIf(fer, jour) Then
                    n = n + 1
                    com = com & vbLf & "- " & Format(jour, "ddd dd/mm/yyyy")
                End If
                If n = avance Then Exit For
            Next jour
            With Target(1, 0).AddComment(com).Shape.TextFrame
                .Characters.Font.Name = "Courier New" 'chasse fixe
                .AutoSize = True 'ajuste la taille
            End With
        End If
    End If
Next Target
Application.EnableEvents = True 'réactive les évènements
End Sub
Pour la tester entrez 1 (ou plus) en cellules H14 et H23 et voyez les commentaires en colonne G.

A+
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
job75 à dit:
Bonjour kdet, le forum,

Si je comprends bien vous voulez pouvoir avancer quand c'est possible la date de FIN de la feuille "Planning".

Pour cela le plus simple est d'ajouter la colonne AVANCE et d'utiliser en colonne G cette fonction VBA :
VB: 
Function FIN(deb As Range, duree As Range, fer As Range, avance As Range)
Dim jour&, n&
On Error GoTo 1
For jour = deb To deb + 10000
    If Weekday(jour, 2) < 6 And Application.CountIf(fer, jour) = 0 Then n = n + 1
    If n = duree Then FIN = jour - avance: Exit For
Next
1 If FIN = 0 Then FIN = ""
End Function
Le remplissage manuel des colonnes E F H du tableau est piloté par cette macro :
VB: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fer As Range, avance&, n&, com$, jour&
With ListObjects(1).Range 'tableau structuré
    Set Target = Intersect(Target, .Cells)
    If Target Is Nothing Then Exit Sub
    Set Target = Intersect(Target.EntireRow, .Columns(7))
End With
Application.EnableEvents = False 'désactive les évènements
Target.Offset(, -1).ClearComments 'RAZ
Set fer = [Fériés]
For Each Target In Target 'si entrées ou effacements multiples
    If IsDate(Target.Offset(, -1)) Then
        avance = Abs(Int(Val(Target))): Target = IIf(avance, avance, "")
        If Target.Offset(, -1) < Target.Offset(, -3) + Target.Offset(, -2) - 1 Then
            Target.Select
            MsgBox "L'avance est trop grande et va être effacée !", 48
            Target = ""
        End If
        If Target > 0 Then
            n = 0: com = "Travail :"
            For jour = Target.Offset(, -1) + avance To 1 Step -1
                If Weekday(jour, 2) > 5 Or Application.CountIf(fer, jour) Then
                    n = n + 1
                    com = com & vbLf & "- " & Format(jour, "ddd dd/mm/yyyy")
                End If
                If n = avance Then Exit For
            Next jour
            With Target(1, 0).AddComment(com).Shape.TextFrame
                .Characters.Font.Name = "Courier New" 'chasse fixe
                .AutoSize = True 'ajuste la taille
            End With
        End If
    End If
Next Target
Application.EnableEvents = True 'réactive les évènements
End Sub
Pour la tester entrez 1 (ou plus) en cellules H14 et H23 et voyez les commentaires en colonne G.

A+
Cliquez pour agrandir...
Bonjour job75, le forum,
Merci pour ce code VBA. Je ne suis pas pro de VBA mais je vais m'inspirer avec.
kdet
 
Vote positif 0 Vote négatif
Bonjour kdet, le forum,

Pour peaufiner on peut ajouter cette macro qui crée une liste de validation en colonne H :
VB: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim n&
Set Target = ActiveCell
With ListObjects(1).Range.Columns(7) 'tableau structuré
    .Cells.Validation.Delete 'RAZ
    If Intersect(Target, .Cells) Is Nothing Then Exit Sub
End With
If Not IsDate(Target.Offset(, -1)) Then Exit Sub
n = Target.Offset(, -1) + Target - Target.Offset(, -3) - Target.Offset(, -2) + 1
If n = 0 Then Exit Sub
Columns(1).ClearContents 'RAZ colonne A auxiliaire
Cells(1) = n
Cells(1).Resize(n).DataSeries Step:=-1
Target.Validation.Add xlValidateList, Formula1:="=A1:A" & n
CreateObject("WScript.Shell").SendKeys "%{DOWN}" 'déroule la liste de validation
End Sub
A+
 

Pièces jointes

Vote positif 0 Vote négatif
re, et juste pour m'amuser, un autre approach,
les 2 dernières colonnes servent à expliquer ce qui se passe.
la colonne "type" montre avec un chiffre chaque jour à partir du debut au fin son utilisation. Après chaque dimanche,on a une virgule et pour l'explication des chiffres, voir tableau "Tabel4"
Puis la colonne "Commentaire" explique le nombre de jours dans chaque catégorie. Si on dit par exemple 5 samedis, la macro les met au début, mais vous pouvez les encore déplacer, si vous voulez. Mais dès qu'on commence avec une nouvelle catégorie, on ne sait plus faire cela. Par exemple, si on a aussi quelque dimanches, cela veut dire que tous les samedis sont utilisés, donc on ne sait plus les déplacer.
Si on veut avancer une date mais on dépasse les possibilités, une MFC rouge indique cela.
 

Pièces jointes

Vote positif 0 Vote négatif
bsalv à dit:
re, et juste pour m'amuser, un autre approach,
les 2 dernières colonnes servent à expliquer ce qui se passe.
la colonne "type" montre avec un chiffre chaque jour à partir du debut au fin son utilisation. Après chaque dimanche,on a une virgule et pour l'explication des chiffres, voir tableau "Tabel4"
Puis la colonne "Commentaire" explique le nombre de jours dans chaque catégorie. Si on dit par exemple 5 samedis, la macro les met au début, mais vous pouvez les encore déplacer, si vous voulez. Mais dès qu'on commence avec une nouvelle catégorie, on ne sait plus faire cela. Par exemple, si on a aussi quelque dimanches, cela veut dire que tous les samedis sont utilisés, donc on ne sait plus les déplacer.
Si on veut avancer une date mais on dépasse les possibilités, une MFC rouge indique cela.
Cliquez pour agrandir...
Re-bonjour bsalv, job75, le forum,
Merci pour l'explication car ça m'aide énormément.

kdet
 
Vote positif 0 Vote négatif
Bonsoir le forum,

Sur mes fichiers précédents avec l'avance 12 en H14 le commentaire en G14 liste des jours de travail postérieurs au 16/05/2026.

Pour y remédier il faut remplacer dans la macro Worksheet_Change :
VB: 
com = com & vbLf & "- " & Format(jour, "ddd dd/mm/yyyy")
par ;
VB: 
If jour <= Target.Offset(, -1) Then com = com & vbLf & "- " & Format(jour, "ddd dd/mm/yyyy")
Utilisez ce fichier (3).

A+
 

Pièces jointes

Vote positif 0 Vote négatif
job75 à dit:
Bonsoir le forum,

Sur mes fichiers précédents avec l'avance 12 en H14 le commentaire en G14 liste des jours de travail postérieurs au 16/05/2026.

Pour y remédier il faut remplacer dans la macro Worksheet_Change :
VB: 
com = com & vbLf & "- " & Format(jour, "ddd dd/mm/yyyy")
par ;
VB: 
If jour <= Target.Offset(, -1) Then com = com & vbLf & "- " & Format(jour, "ddd dd/mm/yyyy")
Utilisez ce fichier (3).

A+
Cliquez pour agrandir...
Bonjour job75, le forum,

Je vous suis reconnaissant pour cette correction et votre collaboration bienveillante.

kdet
 
Vote positif 0 Vote négatif
bonjour job75, kdet,
j'ai ajouté une feuille "schema" pour mieux visualiser le fonctionnement.
Dans les cellules B10:B12 vous ajoutez vos 3 paramètres et vous voyez le résultat dans les cellules B13:B15.
Mais dans les lignes 2:7, vous voyez aussi les 6 catégories de jours et leur utilisation dépendant de la date dans la ligne 1.
Donc, si on n'a pas d' "Avance", on n'a que des cellules vertes dans la ligne 2. Puis on ajoute quelque jours d'avance et les samedis normaux commencent à se remplir à partir de gauche (vous voyez leur nombre dans B3 et vous pouvez les déplacer, si cela vous convient mieux). Dès qu'on a utilisé toutes les possibilités d'une ligne, on commence à la ligne suivante, donc, il y a une sorte de priorité qui me semble évident.
Et un moment donné, quand tout est plein, l'avance se met en rouge.
Vous pouvez utilisez cet outil avec vos propres dates dans B10:B12.
Avec cet outil, je suppose que les colonnes grises I:J de "planning" sont inutiles maintenant

J'avais un problème avec vos féries. Je ne sais pas pourquoi, on ne prend qu'une année. En décembre, vous aurez des travaux en cheveauchement sur 2 années et vos féries ne sont que celles de 2026. Donc, j'ai ajouté 4 années supplémentaires. Normallement, il y a des formules pour cela, mais apparament, elles ne sont pas correctes. Je ne les ai pas encore corrigé ... (et pour quelqu'unes je ne les connais pas).
La feuille est protégée sans mot de passe.
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
bsalv à dit:
bonjour job75, kdet,
j'ai ajouté une feuille "schema" pour mieux visualiser le fonctionnement.
Dans les cellules B10:B12 vous ajoutez vos 3 paramètres et vous voyez le résultat dans les cellules B13:B15.
Mais dans les lignes 2:7, vous voyez aussi les 6 catégories de jours et leur utilisation dépendant de la date dans la ligne 1.
Donc, si on n'a pas d' "Avance", on n'a que des cellules vertes dans la ligne 2. Puis on ajoute quelque jours d'avance et les samedis normaux commencent à se remplir à partir de gauche (vous voyez leur nombre dans B3 et vous pouvez les déplacer, si cela vous convient mieux). Dès qu'on a utilisé toutes les possibilités d'une ligne, on commence à la ligne suivante, donc, il y a une sorte de priorité qui me semble évident.
Et un moment donné, quand tout est plein, l'avance se met en rouge.
Vous pouvez utilisez cet outil avec vos propres dates dans B10:B12.
Avec cet outil, je suppose que les colonnes grises I:J de "planning" sont inutiles maintenant

J'avais un problème avec vos féries. Je ne sais pas pourquoi, on ne prend qu'une année. En décembre, vous aurez des travaux en cheveauchement sur 2 années et vos féries ne sont que celles de 2026. Donc, j'ai ajouté 4 années supplémentaires. Normallement, il y a des formules pour cela, mais apparament, elles ne sont pas correctes. Je ne les ai pas encore corrigé ... (et pour quelqu'unes je ne les connais pas).
La feuille est protégée sans mot de passe.
Cliquez pour agrandir...
Re-bonjour bsalv, job75, le forum,
Encore merci pour ce "fameux" schéma. J'avoue que j'ai du mal à choisir car les deux méthodes (job75 et bsalv) sont parfaites. J'ai vraiment appris beaucoup de choses grâce vous, le forum.
Avec cet outil, je suppose que les colonnes grises I:J de "planning" sont inutiles maintenant
Cliquez pour agrandir...
j'en ai quand même besoin.

Encore merci à vous , le forum.

kdet
 
Vote positif 0 Vote négatif
les 2 méthodes vous offrent pour la plupart, la même date "FIN", mais les dates proposées pour faire le "job"😉 sont différent.
Supposons debut 27/4, 22 jours et avance 5 alors Jobs vous propose Fin = 27/5 (jour férié) et ces 3 jours : 27/5 25/5 et 24/5 2 jours féries et 1 dimanche. Donc ces jours se trouvent toujours au bout, sans priorité samedi/dimanche/fériés.
Moi, je vous propose Fin=26/5 (=1 jour plus tôt,le jour avant ce férié) et 3 samedis 2/5, 9/5 et 16/5.
Je suppose quand on est assez tôt dans le planning, on préfère choisir ces dates, sinon, c'est "combattre un incendie alors que la maison est déjà en feu"
 
Vote positif 0 Vote négatif
bsalv à dit:
les 2 méthodes vous offrent pour la plupart, la même date "FIN", mais les dates proposées pour faire le "job"😉 sont différent.
Supposons debut 27/4, 22 jours et avance 5 alors Jobs vous propose Fin = 27/5 (jour férié) et ces 3 jours : 27/5 25/5 et 24/5 2 jours féries et 1 dimanche. Donc ces jours se trouvent toujours au bout, sans priorité samedi/dimanche/fériés.
Moi, je vous propose Fin=26/5 (=1 jour plus tôt,le jour avant ce férié) et 3 samedis 2/5, 9/5 et 16/5.
Je suppose quand on est assez tôt dans le planning, on préfère choisir ces dates, sinon, c'est "combattre un incendie alors que la maison est déjà en feu"
Cliquez pour agrandir...
Bonjour bsalv, le forum,
Vous avez tout à fait raison. J'en tiendrais compte de votre proposition.
Encore merci pour votre collaboration et vos précieux conseils.

kdet
 
Vote positif 0 Vote négatif
Bonjour kdet, le forum,

Je me suis attaqué à la colonne D (AVANCEMENT) avec cette 2ème fonction VBA :
VB: 
Function AVANCEMENT(deb As Range, duree As Range, fer As Range, celFIN As Range)
Dim com$, jour&, n&
If Not celFIN.Comment Is Nothing Then com = celFIN.Comment.Text
On Error GoTo 1
For jour = deb To celFIN
    If jour > Date Then Exit For
    If Weekday(jour, 2) < 6 And Application.CountIf(fer, jour) = 0 Or InStr(com, Format(jour, "dd/mm/yyyy")) Then n = n + 1
Next
AVANCEMENT = n / duree
1 If Err Then AVANCEMENT = ""
End Function
A la fin de la macro Worksheet_Change cette instruction force le recalcul des 2 fonctions VBA :
VB: 
        Target = Target.Value 'force le recalcul des 2 fonctions VBA
Je vous laisse tester ce fichier (4), vous pouvez par exemple ajouter le 16/04/2026 dans la liste des jours fériés.

A+
 

Pièces jointes

Vote positif 0 Vote négatif
job75 à dit:
Bonjour kdet, le forum,

Je me suis attaqué à la colonne D (AVANCEMENT) avec cette 2ème fonction VBA :
VB: 
Function AVANCEMENT(deb As Range, duree As Range, fer As Range, celFIN As Range)
Dim com$, jour&, n&
If Not celFIN.Comment Is Nothing Then com = celFIN.Comment.Text
On Error GoTo 1
For jour = deb To celFIN
    If jour > Date Then Exit For
    If Weekday(jour, 2) < 6 And Application.CountIf(fer, jour) = 0 Or InStr(com, Format(jour, "dd/mm/yyyy")) Then n = n + 1
Next
AVANCEMENT = n / duree
1 If Err Then AVANCEMENT = ""
End Function
A la fin de la macro Worksheet_Change cette instruction force le recalcul des 2 fonctions VBA :
VB: 
        Target = Target.Value 'force le recalcul des 2 fonctions VBA
Je vous laisse tester ce fichier (4), vous pouvez par exemple ajouter le 16/04/2026 dans la liste des jours fériés.

A+
Cliquez pour agrandir...
Bonjour job75, le forum,
... Je ne trouve plus les mots pour vous féliciter.
Sincèrement merci et encore pour ce super boulot.

kdet
 
Dernière édition:
Vote positif 0 Vote négatif
Bonjour kdet, le forum,

Encore une chose : pour que la colonne AVANCEMENT se mette à jour à l'ouverture du fichier il faut dans ThisWorkbook :
VB: 
Private Sub Workbook_Open()
Dim c As Range
For Each c In Sheets("Planning").ListObjects(1).Range.Columns(3).Cells
    If c < 1 And c.Offset(, 1) <= Date Then c = c.Formula
Next c
End Sub
A+
 

Pièces jointes

Vote positif 0 Vote négatif
job75 à dit:
Bonjour kdet, le forum,

Encore une chose : pour que la colonne AVANCEMENT se mette à jour à l'ouverture du fichier il faut dans ThisWorkbook :
VB: 
Private Sub Workbook_Open()
Dim c As Range
For Each c In Sheets("Planning").ListObjects(1).Range.Columns(3).Cells
    If c < 1 And c.Offset(, 1) <= Date Then c = c.Formula
Next c
End Sub
A+
Cliquez pour agrandir...
Bonjour job75, le forum,
Je n'y avais pas pensé à ce "p'tit détail" qui est vraiment utile et important.
Encore merci job75

kdet
 
Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD
Retour