Formule perso de comparaison Intervalle de Dates. Critiquez / Testez / Améliorez !

[Charly88]

Bonjour à tous,
Blabla :
Parmi les innombrables fonctionnalités "basiques" qui manquent à Excel, j'ai toujours été étonné par l'absence de formules de temps permettant de jouer, facilement, avec des intervalles de temps.
Bloqué par un rhume particulièrement coriace, j'ai utilisé mon congé pour réfléchir à nouveau au problème (auquel je m'étais confronté en VBA) pour créer quelques formules sur le sujet, en voici le résultat en PJ.

L'objet :
Il me semble très fréquent en entreprise / assoc' / autre, d'avoir une situation impliquant une période de temps définie (un déplacement, lequel est sujet à modifications) et le besoin de savoir combien de jours (calendaires ou ouvrés) cette période représente sur différents mois.


Ce que j'espère / attends en général de vos contributions :
- Tester le schmilblick.
La première personne (office 2013 avec des sécurités vba activées) qui l'a testé rapidement a eu un "freeze" direct. De mon côté, le fichier fonctionne sans problème sur deux Office 2013 et un Office 2007.
- Les résultats : M'indiquer/corriger toute erreur de code qui m'aurait échappée et amènerait à un résultat faux.
Malgré plusieurs expériences passées, j'ai eu maintes fois l'impression de réinventer la roue.
- Bien entendu, toute suggestion, amélioration, variante, etc est plus que bienvenue.
Je pense par exemple aux "types" des données avec lesquels je ne suis pas 100% à l'aise. Sur un autre plan (quoique) j'aimerais bien sûr que mes formules soient les plus économes possibles en terme de calcul.

Ce que j'aimerais tout particulièrement :
- pouvoir rajouter des informations contextuelles pour l'utilisateur tapant la formule.
Excel VBA semble (très) bêtement limité à ce niveau, je n'ai pas trouvé moyen d'indiquer en "caption" à l'utilisateur, tapant la formule, quel est l'élément est attendu à quel moment (c'est-à-dire une présentation identique à celle d'une formule native).
Actuellement, on ne peut même pas voir combien d'éléments sont attendus.
- Renvoyer facilement 5 solutions prédéfinies... = résolu en tapant ce sujet, F2.1
- Vient ensuite le partage pour lequel j'aurais peut-être dû créer un topic dédié... Comment proposer facilement une ces formules à quiconque ?
J'ai un peu exploré comment transmettre une formules "perso" mais j'ai très vite arrêté en voyant que le chemin classique consiste à créer une sorte de greffon puis à demander à l'utilisateur de l'importer. C'est inenvisageable pour les personnes autour de moi.
Faute d'autre solution, j'envisage d'entretenir un fichier "best-of" de formules perso, que l'utilisateur n'aurait plus qu'à copier-coller lorsqu'il démarre quelque chose où ces formules seraient utiles.



Quelques remarques :
- Le fichier manque de commentaires : oui, j'ai avant tout essayé d'atteindre le résultat, j'espère pouvoir clarifier les points nécessaires sur ce fil.
Néanmoins, j'ai bon espoir que le tout soit assez propre pour être compréhensible pour qui désire y jeter un oeil sérieux.

- Les deux intervalles de base : Comparer deux intervalles revient à comparer deux paires de dates. Mon intervalle de temps défini (celui qui peut varier) est nommé "IntervalID" avec IntervalID_Deb et _Fin pour les dates le définissant. Ma grille de référence est le mois, définie par Mois_Deb et _Fin.

- On peut aller plus loin : J'ai cherché tout d'abord à identifier tous les cas possibles, y compris quelques cas particuliers. Je suis convaincu que l'on peut aller encore plus loin dans les possibilités (intervalle à 1 jour) et variantes (échelle de temps en année, semaine, autre), cet exercice, a priori très simple, donne le vertige quant à la multiplicité des cas de figures. Je me suis concentré sur le cas le plus fréquent avec grille de référence = les mois.

- Si, une fois le fichier passé en .xls, le tout fonctionne, très bien. Mais après 12 ans de loyaux services, je considère le format .xls / Office 2003 comme obsolète et ne chercherai pas à l'entretenir.

 

[Charly88]

Re : Formule perso de comparaison Intervalle de Dates. Critiquez / Testez / Améliorez

Bon bon bon... Pas de succès par ici, je tente une autre approche :
Ô Dieux de ce forum, Saints Barbatrucs, je vous en conjure, écoutez ma requête et donnez-moi un signe ! :-D

Je suis un peu surpris et frustré car cela fait des années que je viens chercher, et trouver, des solutions sur ce forum. Alors je me faisais une joie de pouvoir enfin renvoyer, modestement, la balle à Misange, Boisgontier, PierreJean, MJ13, Brigitte, Pierrot, Bruno... (et vous nommer c'est forcément en oublier donc je m'arrête là) en venant d'abord avec une solution plutôt qu'un problème.

Je me doute que plusieurs points de mon poste n'ont pas de solution mais quelques confirmations seraient bienvenues là où c'est possible. Dans tous les cas, merci !

 

[MJ13]

Re : Formule perso de comparaison Intervalle de Dates. Critiquez / Testez / Améliorez

Bonjour Charly

J'avoue que j'ai ouvert ton fichier quand tu l'as posté, en janvier, mais j'ai vite déchanté, n'y comprenant rien . Après je n'ai pas répondu en pensant que d'autres Xldiens plus pointus que moi viendraient t'aider. Après cela m'est sorti de la tête .

En effet, je dois te l'avouer, je ne suis pas très doué, juste un utilisateur Lambda, qui utilise l'enregistreur de macros pour faire les codes dont j'ai besoin .

Je me demande quand même si tu ne te compliques pas un peu la vie .

Donc, Wait and see .§

 

[gosselien]

Re : Formule perso de comparaison Intervalle de Dates. Critiquez / Testez / Améliorez

idem...

sorry mais rien de clair et pas pour mon niveau mais je vais suivre ce post...

 

[Charly88]

Re : Formule perso de comparaison Intervalle de Dates. Critiquez / Testez / Améliorez

Merci déjà pour ces premiers retours !
Je comprends que le long baratin et les 2 premières feuilles soient un peu obscurs mais, sur le plan strictement fonctionnel, si vous regardez la feuille Test et les fonctions F3 et F4 avec des résultats concrets, cela ne vous parle pas plus ?
Lesquelles indiquent respectivement le nombre de jours calendaires et ouvrés, dans les intervalles-Mois définis en ligne 1 et 2.

 

[MJ13]

Re : Formule perso de comparaison Intervalle de Dates. Critiquez / Testez / Améliorez

Re Charly, Gosselien

Si j'essaye de comprendre, il y a tellement de cas, que je n'y comprend pas grand chose .

Peut-être que si Roger passe par là, il est plus au fait de ces problèmes de dates.

Moi, cela me dépasse et je n'en ai pas besoin plus que çà .

 

[ROGER2327]

Re : Formule perso de comparaison Intervalle de Dates. Critiquez / Testez / Améliorez

Bonjour à tous.

Comme les autres, j'avais reculé devant la complexité du classeur de Charly88.
C'est un peu plus clair avec ceci :​

(...) si vous regardez la feuille Test et les fonctions F3 et F4 avec des résultats concrets, cela ne vous parle pas plus ?
Lesquelles indiquent respectivement le nombre de jours calendaires et ouvrés, dans les intervalles-Mois définis en ligne 1 et 2.

C'est un problème très-souvent traité sur le forum, par exemple ici (solution par formules Excel).

Voici une proposition en VisualBasic :​

Public Function interDates(d1, f1, d2, f2, Optional we As Boolean, Optional fe As Range, Optional g& = 1)

'|¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯|
'|Ajouter la référence à la bibliothèque Microsoft Scripting Runtime (scrrun.dll) au projet ! |
'|____________________________________________________________________________________________|

'Mode d 'emploi :
'                     Paramètres :
'
' d1, f1, d2, f2 :  Dates (ou numéros de série) tels que d1 > 0, d2 > 0, f1 >= d1 et f2 >= d2.
' we :              FAUX ou 0 ou omis -> suppression des samedis et dimanches ;
'                   VRAI ou diférent de 0 -> conservation des samedis et dimanches.
' fe :              Plage (uni-colonne ou uni-ligne) facultative de cellules vides ou
'                   contenant des dates à supprimer (par exemple, jours fériés, congés...).
' g :               1 ou omis -> les dates f1 et f2 sont prises en compte ;
'                   0 (zéro) -> la dates f2 n'est pas prise en compte.

'                     Valeurs renvoyées :
'
'     - si we = VRAI, fe omis, g = 1, nombres de jours de l'intersection de [d1;f1] et [d2;f2].
'     - si we = VRAI, fe omis, g = 0, nombres de jours de l'intersection de [d1;f1[ et [d2;f2[.
'     - si we = VRAI, fe donné, g = 1, nombres de jours de l'intersection de [d1;f1] et [d2;f2]
' diminué du nombre de jours appartenant à fe et à l'intersection de [d1;f1] et [d2;f2].
'     - si we = VRAI, fe donné, g = 0, nombres de jours de l'intersection de [d1;f1[ et [d2;f2[
' diminué du nombre de jours appartenant à fe et à l'intersection de [d1;f1[ et [d2;f2[.
'     - si we = FAUX, fe omis, g = 1, nombres de jours de l'intersection de [d1;f1] et [d2;f2]
' diminué du nombre de samedis et dimanches appartenant à l'intersection de [d1;f1] et [d2;f2].
'     - si we = FAUX, fe omis, g = 0, nombres de jours de l'intersection de [d1;f1[ et [d2;f2[
' diminué du nombre de samedis et dimanches appartenant à l'intersection de [d1;f1[ et [d2;f2[.
'     - si we = FAUX, fe donné, g = 1, nombres de jours de l'intersection de [d1;f1] et [d2;f2]
' diminué du nombre de samedis et dimanches appartenant à l'intersection de [d1;f1] et [d2;f2]
' et diminué du nombre de jours appartenant à fe et à l'intersection de [d1;f1] et [d2;f2].
'     - si we = FAUX, fe donné, g = 0, nombres de jours de l'intersection de [d1;f1[ et [d2;f2[
' diminué du nombre de samedis et dimanches appartenant à l'intersection de [d1;f1[ et [d2;f2[
' et diminué du nombre de jours appartenant à fe et à l'intersection de [d1;f1[ et [d2;f2[.

Dim i&, d&, f&, l&, x, fer As New Scripting.Dictionary
    interDates = ""
    If d1 > 0 And d2 > 0 And f1 >= d1 And f2 >= d2 Then
        If d1 > d2 Then d = d1 Else d = d2
        If f1 < f2 Then f = f1 + (g = 0) Else f = f2 + (g = 0)
        If d <= f Then
            l = f - d + 1
            If Not we Then
                For i = d + (6 - (d - 1) Mod 7) Mod 6 To f: l = l - 1: fer.Add i, Empty: i = i + 5 * (i Mod 7): Next
            End If
            If Not fe Is Nothing Then
                For i = 1 To fe.Count
                    x = fe(i).Value2
                    If Not IsEmpty(x) Then If d <= x And x <= f Then If Not fer.Exists(x) Then fer.Add x, Empty: l = l - 1
                Next
            End If
            interDates = l
        End If
    End If
End Function

Je ne dispose plus d'Excel2003 mais, a priori, je pense que ce code est compatible. À vérifier !​

Bonne journée.

P. s. : Le code comportait une erreur (voir le message suivant). Il est maintenant corrigé.


ℝOGER2327
#7791

Mardi 10 Pédale 142 (Nativité de Saint Tancrède, jeune homme - fête Suprême Quarte)
14 Ventôse An CCXXIII, 6,0962h - vélar
2015-W10-3T14:37:51Z

 

[ROGER2327]

Re : Formule perso de comparaison Intervalle de Dates. Critiquez / Testez / Améliorez

Aïe !


Erreur : la ligne

For i = d + (7 - d Mod 7) Mod 6 To f: l = l - 1: fer.Add i, Empty: i = i + 5 * (i Mod 7): Next

est incomplète. Il faut

For i = d + (7 - d Mod 7) Mod 7 Mod 6 To f: l = l - 1: fer.Add i, Empty: i = i + 5 * (i Mod 7): Next

Trois Mod à la suite, c'est beaucoup ! On mettra

For i = d + (6 - (d - 1) Mod 7) Mod 6 To f: l = l - 1: fer.Add i, Empty: i = i + 5 * (i Mod 7): Next

Je corrige le message précédent de suite.


ℝOGER2327
#7792

Mardi 10 Pédale 142 (Nativité de Saint Tancrède, jeune homme - fête Suprême Quarte)
14 Ventôse An CCXXIII, 7,1553h - vélar
2015-W10-3T17:10:22Z