tri mois dates jours ouvrés

[almas]

Bonjour à tous

j 'ai trouvé déjà pas mal de post sur le tri par mois mais aucune ne m' a permis de faire ce que je souhaite🙁

je souhaiterai pouvoir trié les jours ouvrés extrait d 'une plage de dates par mois et voir en faire la somme😉

un petit fichier joint pour bien expliquer

merci d' avance si quelqu' un peut m éclairer sur la formule

édit: remplacement du fichier joint car le décompte de l exemple 1 n 'était pas bon (décompte fait manuellement ^^))

Récapitulatif​

Formule pour une plage de date seulement:
Jocelyn: #10

=SOMMEPROD((MOIS(LIGNE(INDIRECT($B$4&":"&$C$4)))=LIGNES($1:1))*(JOURSEM(LIGNE(INDIRECT($B$4&":"&$C$4));2)<6)*(ESTERREUR(EQUIV(LIGNE(INDIRECT($B$4&":"&$C$4));feriés;0))))

David: #16

=SI(NB.JOURS.OUVRES(B$4;MIN(C$4;DATE(An;LIGNES($1:2);));feriés)<0;0;NB.JOURS.OUVRES(B$4;MIN(C$4;DATE(An;LIGNES($1:2);));feriés)-SOMME(H$2:H2))

Formule multi plage :

Job : formule matricielle #46

=SOMME((date>=TRANSPOSE(deb))*(date<=TRANSPOSE(fin))*(MOIS(date)=LIGNES(F$3:F3))*(JOURSEM(date;2)<6)*NON(NB.SI(feriés;date)))

Roger : formule matricielle#34 (version sup a xls 2003)

=SOMME(NB.JOURS.OUVRES(fin/2+deb/4+F3/4+ABS(deb/4-F3/4)-ABS(fin/2-deb/4-F3/4-ABS(deb-F3)/4);deb/2+fin/4+MOIS.DECALER(F3;1)/4-0,25-ABS(fin-MOIS.DECALER(F3;1)+1)/4+ABS(deb/2-fin/4-MOIS.DECALER(F3;1)/4+0,25+ABS(fin-MOIS.DECALER(F3;1)+1)/4);feriés)*(deb<MOIS.DECALER(F3;1))*(F3<=fin))

Formule + Macro:

mapomme : #45 fichier avec le code et la fonction NBJourOuvreMois

 

[ROGER2327]

Re : tri mois dates jours ouvrés

Re...

non Roger malheureusement pour moi ça marche toujours pas

et toujours les {} au début et à la fin de la formule

donc peut être ton idée de trop de parenthèses imbriquées ? (pourtant cela parais pas énorme en nombres)

la fonction MOIS.DECALER n 'est pas la cause

Dommage que ça ne fonctionne toujours pas...

En ce qui concerne les accolades, elles sont indispensables. On les obtient en saisissant la formule sans les accolades, puis on valide avec la combinaison de touches Ctrl Maj Entrée.

À propos des parenthèses, je ne sais plus quel est le nombre de niveaux autorisé dans les différentes versions anciennes. À discuter avec les amateurs de vintage...​

ℝOGER2327
#7010

Mercredi 25 As 141 (Saint Lewis Caroll, profesSainteur - fête Suprême Quarte)
7 Frimaire An CCXXII, 6,6222h - chou-fleur
2013-W48-3T15:53:36Z

 

[almas]

Re : tri mois dates jours ouvrés

Merci Roger

Ta formule aura au moins le mérite d'apporter une solution direct à ceux qui ont des versions supérieur à 2003

Moi je vais surement devoir passé par un tableau intermédiaire avec les formules de Jocelyn ou David, et faire les somme après🙁

A moins qu un génie trouve une solution d'ici la ^^😀

 

[david84]

Re : tri mois dates jours ouvrés

Bonsoir,
un essai effectué sur le 1er fichier fourni par Roger mais à vérifier scrupuleusement car les résultats ne sont pas les mêmes...

=SOMME(SI((MOIS(F3)=MOIS(deb))*(deb<>"");NB.JOURS.OUVRES(SI(deb<>"";deb);DATE(An;MOIS(F3)+1;);feriés)))+SOMME(SI((MOIS(F3)=MOIS(fin))*(fin<>"");NB.JOURS.OUVRES(DATE(An;MOIS(F3);1);SI(fin<>"";fin);feriés)))

Validation matricielle.
A+

 

[ROGER2327]

Re : tri mois dates jours ouvrés

Suite...

Un essai avec moins de niveaux de parenthèses.​

ℝOGER2327
#7012

Mercredi 25 As 141 (Saint Lewis Caroll, profesSainteur - fête Suprême Quarte)
7 Frimaire An CCXXII, 6,9915h - chou-fleur
2013-W48-3T16:46:46Z

 

[david84]

Re : tri mois dates jours ouvrés

Suite

On peut légèrement raccourcir :

=SOMME(SI((MOIS(F3)=MOIS(deb))*(deb<>"");NB.JOURS.OUVRES(SI(deb<>"";deb);DATE(An;MOIS(F3)+1;);feriés));SI((MOIS(F3)=MOIS(fin))*(fin<>"");NB.JOURS.OUVRES(DATE(An;MOIS(F3);1);SI(fin<>"";fin);feriés)))

A+

 

[ROGER2327]

Re : tri mois dates jours ouvrés

Bonjour à tous.

(...)
un essai effectué sur le 1er fichier fourni par Roger mais à vérifier scrupuleusement car les résultats ne sont pas les mêmes...
(...)

Nous avons effectivement de notables divergences...​

ℝOGER2327
#7014

Jeudi 26 As 141 (Saint Mensonger, évêque - fête Suprême Quarte)
8 Frimaire An CCXXII, 0,8055h - miel
2013-W48-4T01:56:00Z

 

[david84]

Re : tri mois dates jours ouvrés

Bonjour,

Nous avons effectivement de notables divergences...

Merci Roger pour ces tests. D'après ce que je constate à première vue ma proposition pose problème lorsque les mois des dates de début et de fin sont les mêmes. La solution serait donc d'exclure cette possibilité dans le calcul du nombre de jours ouvrés comportant la date de fin.
Je n'ai pas le temps de vraiment tester ce matin mais à première vue (je dis bien à première vue !), les résultats entre nos 2 propositions seraient alors concordants.

=SOMME(SI((MOIS(F3)=MOIS(deb))*(deb<>"");NB.JOURS.OUVRES(SI(deb<>"";deb);DATE(An;MOIS(F3)+1;);feriés));SI((MOIS(F3)=MOIS(fin))*(fin<>"")*(MOIS(deb)<>MOIS(fin));NB.JOURS.OUVRES(DATE(An;MOIS(F3);1);SI(fin<>"";fin);feriés)))

A tester cependant plus avant pour s'en assurer !
A+

Edit : après quelques tests portant sur une durée comportant plusieurs mois je m'aperçois que ma proposition ne convient pas.
Si le demandeur n'arrive toujours pas à utiliser les formules que vous lui avez proposées je regarderai à nouveau de mon côté si je peux trouver une autre solution.
Attendons sa réaction.
Bravo à vous mon cher Roger et bonne journée !

 

[almas]

Re : tri mois dates jours ouvrés

Bonjour a tous le forum
Bonjour Roger, David, Jocelyn et les autres....

Je vois que ça a pas chaumé pendant mon absence ^^

Roger je vois que tu a encore amélioré ta formule bravo!

Malheureusement pour moi toujours un problème avec excel 2003

Dès que j essais de rentrer une date ,tous par en saucisses et met des "valeurs" partout ( c 'est rageant car c 'est vraiment ce que je cherche à faire )

Bon faire un tableau intermédiaire devrai pas trop charger mon classeur bien que mon classeur final fera plus de 80 onglets fiches!
En tous ça merci beaucoup à tous ...et si quelqu' un avais une illumination pour trouver "LA" formule magique qui marche sous 2003 je suis toujours preneur 😛

 

[mapomme]

Re : tri mois dates jours ouvrés

Bonjour almas, kingfadhel, david84, Jocelyn, ROGER2327,

Pour le fun, un essai avec une fonction en VBA. Sous un vrai Excel 2003+Windows XP, la fonction donnait, à priori, des résultats corrects, en tout cas identiques à ceux de ROGER2327.

Il faut une référence à Microsoft Scripting Runtime. Cette référence devrait être montée automatiquement à l'ouverture de ce fichier (1ière fois que j'utilise cette méthode 😕)

La fonction est appelée par NBJourOuvreMois(PlageDates, xJoursFeries, Mois) où:
PlageDates est une plage à deux colonnes des périodes sources.
xJoursFeries est une plage contenant les dates des jours fériées.
Mois est le numéro du mois dont on désire connaître le nombre de jours ouvrés présents dans PlageDates.

Le code de la fonction:

Function NBJourOuvreMois(xPlageDates As Range, xJoursFeries As Range, xMois&) As Long
  Dim dicoPlage As New Dictionary, dicoFerie As New Dictionary
  Dim i&, i0&, i1&, xc As Range, xlig As Range

  For Each xc In xJoursFeries: dicoFerie.Add xc.Value, "": Next xc
  For Each xlig In xPlageDates.Rows
    i0 = xlig.Cells(1, 1)
    i1 = xlig.Cells(1, 2)
    For i = i0 To i1
      If i > 1 Then
        If Not (Weekday(i) = vbSunday Or Weekday(i) = vbSaturday) Then
          If Month(i) = xMois Then
            If Not dicoFerie.Exists(i) Then
              dicoPlage(Month(i)) = dicoPlage(Month(i)) + 1
            End If
          End If
        End If
      End If
    Next i
  Next xlig
  If dicoPlage.Exists(xMois) Then NBJourOuvreMois = dicoPlage(xMois)
End Function

Le code dans Workbook_Open:

Private Sub Workbook_Open()
'référence à la library micrisoft scripting runtime
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGUID "{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0
On Error GoTo 0
End Sub

 

[job75]

Re : tri mois dates jours ouvrés

Bonjour à tous,

Pour info, j'ai testé la formule =NB.JOURS.OUVRES({41275;41276;41277};41640) sur mes 2 ordis :

- renvoie #VALEUR! sur Excel 2003 (XP)

- renvoie bien la matrice {262;261;260} sur Excel 2010 (Win7)

La fonction n'est donc pas adaptée au calcul matriciel sur Excel 2003.

A+

 

[job75]

Re : tri mois dates jours ouvrés

Re,

Une solution qui fonctionne sans problème sur toute version.

Formule en H3 à tirer vers le bas :

 =SOMMEPROD((MOIS(LIGNE(INDIRECT(B$4&":"&C$4)))=LIGNES(F$3:F3))*(JOURSEM(LIGNE(INDIRECT(B$4&":"&C$4));2)<6)*NON(NB.SI(feriés;LIGNE(INDIRECT(B$4&":"&C$4)))))+SOMMEPROD((MOIS(LIGNE(INDIRECT(B$5&":"&C$5)))=LIGNES(F$3:F3))*(JOURSEM(LIGNE(INDIRECT(B$5&":"&C$5));2)<6)*NON(NB.SI(feriés;LIGNE(INDIRECT(B$5&":"&C$5)))))

Fichier joint.

Nota : sur Excel 2003 (65536 lignes) cette formule ne fonctionnera plus au-delà du 05/06/2079.

On pourrait reculer facilement cette limite mais avec une formule alourdie.

A+

 

[almas]

Re : tri mois dates jours ouvrés

re et Bonjour a mapomme et job75

merci a tous les 2 d'avoir relever le défit!

job d’après ce que je vois ta formule marche parfaitement mais puis je l 'tendre a une plage de saisie
car sinon j 'ai peur que cela fasse une formule a rallonge....

sinon mapomme je vais tester ta macro
edit
ça marche bien mais je la comprend pas 😎
le problème c 'est que tu associe formule et macro et qu il y a aussi les nom et formule de Roger et David sur la même feuille ^^
j 'ai essayer de reproduire ça sur le fichier propre d origine mais tous ça a tous planter et même plus moyen de quitter excel !^^😱
control/alt/sup pour quitter.

je joint le fichier vierge du départ si tu peut me dire quelles autre opération a a fait en plus du code?

 

[ROGER2327]

Re : tri mois dates jours ouvrés

Bonjour à tous.

Bonjour à tous,

Pour info, j'ai testé la formule =NB.JOURS.OUVRES({41275;41276;41277};41640) sur mes 2 ordis :

- renvoie #VALEUR! sur Excel 2003 (XP)

- renvoie bien la matrice {262;261;260} sur Excel 2010 (Win7)

La fonction n'est donc pas adaptée au calcul matriciel sur Excel 2003.

A+

Merci pour cette explication.
Il ne reste plus qu'à écrire une fonction équivalente à NB.JOURS.OUVRES avec des fonctions moins capricieuses. C'est ce que vous avez fait (il me semble) dans le message #41. Bravo.​

Bonne journée.


ℝOGER2327
#7015

Jeudi 26 As 141 (Saint Mensonger, évêque - fête Suprême Quarte)
8 Frimaire An CCXXII, 5,7252h - miel
2013-W48-4T13:44:26Z

 

[almas]

Re : tri mois dates jours ouvrés

oui Roger mais sur 2 plages seulement

sur 20 plages je peut allonger la formule indéfiniment?

 

[mapomme]

Re : tri mois dates jours ouvrés

(re)

Voilà ton fichier avec le code et la fonction NBJourOuvreMois

En H3, la formule =NBJourOuvreMois($B$4:$C$23;ferié;LIGNES($1:1))

J'ai pris comme plage de dates (début/Fin) la plage $B$4:$C$23, comme plage des fériés la plage de nom ferié.
Le dernier terme LIGNES($1:1)) renvoie 1 pour le mois de janvier.

Ensuite, il faut tirer la cormule vers le bas:
la plage $B$4:$C$23 reste constante dans la formule (normal, il s'agit toujours de la même plage en entrée)
la plage férié reste constante dans la formule (normal, il s'agit toujours des mêmes jours fériés)
le terme LIGNES($1:1)) va s'incrémenter en LIGNES($1:2)) puis LIGNES($1:3)) ... jusqu'à LIGNES($1:12)). Ces termes vont renvoyer 1, 2, 3 ... jusqu'à 12 pour chacun des mois de janvier à décembre.