ci-joint un classeur avec fonctions dates en remplacement de Datedif

Roland_M · 9 Février 2014

Bien le bonjour à tous,

Ci-joint un classeur pour personnes intéressées.
avec une fonction de calcul date(sans DateDif)
qui à fait l'objet, récemment, de discutions sur DateDif(md)
évidemment comme toujours on hésite pas à faire des remarques toujours pour améliorer, rectifier ...

F_EcartDates(DateHreDeb, DateHreFin, A_HrePile0Non1Oui, Date_0Text1Ann2Mois3Jour, Aff_0Num1Abrege2Entier)

Options et Formats:
-------------------
* A_HrePile0Non1Oui : (0/1)=tenir compte ou pas de l'heure pour comptabiliser le dernier jour
* Date_0Text1Ann2Mois3Jour : (0)="Année Mois Jour" (1)=Année (2)=Mois (3)=Jour
selon choix ci-dessus(0/1/2/3):
(0)Aff_0Num1Abrege2Entier : (0)=0 0 0 (1)="0000 A 00 M 00 J" (2)="0000 Années 00 Mois 00 Jours"
(1)Aff_0Num1Abrege2Entier : (0)=0 (1)="0000 A" (2)="0000 Années"
(2)Aff_0Num1Abrege2Entier : (0)=0 (1)="00 M" (2)="00 Mois"
(3)Aff_0Num1Abrege2Entier : (0)=0 (1)="00 J" (2)="00 Jours"

EDIT: je mets la dernière (V3)

R@chid · 9 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

Bonsoir @ tous,
Salut Roland_M,
Je me demande si la fonction DIFFDATE() de la macro complémentaire MoreFunc n'a pas résolu le problème de DATEDIF().

@ + +

Staple1600 · 9 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

Bonsoir R@chid

Puisque tu en parles, je mets le lien
Ce lien n'existe plus

Comme cela on pourra comparer les résultats avec ceux des fonctions de Roland_M

PS: L'avantage des fonctions de Roland_M c'est qu'on peut lire le code VBA.
Ce qu'on ne peut pas faire avec morefunc

ROGER2327 · 10 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

Bonjour à tous.

À Roland_M :
L'incohérence de la numérotation des jours dans Excel est difficile à corriger ! Il me semble qu'il y a encore à faire...
Comme vous le demandez, je joins quelques exemples qui coincent.

(C'est pour cela que, dans les discussions précédentes, je précisais que mes propositions ne s'appliquaient qu'à des dates postérieures au 1[SUP]er[/SUP] mars 1900.)

ℝOGER2327
#7148

Lundi 16 Gueules 141 (Saint Münchhausen, baron - fête Suprême Quarte)
22 Pluviôse An CCXXII, 1,3831h - thyméle
2014-W07-1T03:19:10Z

Roland_M · 10 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

Bien le bonjour à tous et merci de vous intéresser !

Rectificatif suite à la remarque très juste de ROGER(merci!)
remarque déjà faite et que j'avais complètement oubliée ! Impardonnable !

à suivre . . .

quand à la macro complémentaire c'est plus gênant !
en ce sens qu'il faut l'avoir sur son PC !?
et si on passe le classeur il faut aussi passer cette macro !?

EDIT: voici donc le classeur (après edit (V3)

pour confirmer les dires de ROGER2327,
vous remarquerez un comportement pour le moins étrange entre le transfert de date Excel vers vbe
une date sur excel <"01/03/1900" ici elle est = - 1jour
ex 01/01/1900 ici = 31/12/1899
ex 02/01/1900 ici = 01/01/1900

il suffit pour s'en convaincre d'afficher les variables dans un sub
exemple dans celui-ci j'ai mis en rem un MsgBox pour vous voir ces fameuses dates !?

F_NbrDeJours(DateHreDeb As Date, DateHreFin As Date, A_HrePile0Non1Oui)

'MsgBox DateHreDeb & " " & DateHreFin

Roland_M · 10 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

re

un petit up pour vous signaler que j'ai mis le classeur qui était en attente !

celui-ci(V3) avec une petite correction d'affichage !

ROGER2327 · 10 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

Bonjour à tous.
Re...

Roland_M à dit:
re

un petit up pour vous signaler que j'ai mis le classeur qui était en attente !

celui-ci(V3) avec une petite correction d'affichage !

Ça m'a l'air correct.

Une autre proposition dans le classeur joint, avec la fonction personnalisée roDATEDIF revue pour gérer les dates Excel du 1[SUP]er[/SUP] janvier au 28 février 1900 :

VB:

Function roDATEDIF(Date1 As Date, Date2 As Date, Incrément$) As Long 'ROGER2327
Dim Fct&, Tmp&, D1&, D2&, dH#, dM&
Dim x&, y&
'  Application.Volatile 'Facultatif, en fonction des besoins.
  dH = Round(Date2 - Int(Date2) - Date1 + Int(Date1), 6)            'Excédent horaire (<1 jour)
  D1 = Int(Date1) - (Date1 < 61)                                    'Conversion date Excel -> date VB
  D2 = Int(Date2) - (Date2 < 61) - (dH >= 0)                        'Conversion date Excel -> date VB
  Select Case LCase(Incrément)                                      '
  Case "s": Fct = 86400 * (dH - (dH < 0)) Mod 60                    'Secondes
  Case "n": Fct = 86400 * (dH - (dH < 0)) \ 60 Mod 60               'Minutes
  Case "h": Fct = 86400 * (dH - (dH < 0)) \ 3600                    'Heures
  Case "d": Fct = D2 - D1 - 1                                       'Jours
  Case Else                                                         '
    Tmp = 12 * (Year(D2) - Year(D1)) + Month(D2) - Month(D1)        'Calcul du nombre de mois
    GoSub DECM                                                      '         (suite)
    Tmp = Tmp + (dM >= D2)                                          '          (fin)
    Select Case LCase(Incrément)                                    '
    Case "md": GoSub DECM: Fct = D2 - dM - 1                        'Excédent jours (<1 mois)
    Case "yd": Tmp = 12 * (Tmp \ 12): GoSub DECM: Fct = D2 - dM - 1 'Excédent jours (<1 an)
    Case "m": Fct = Tmp                                             'Mois
    Case "ym": Fct = Tmp Mod 12                                     'Excédent mois (<1 an)
    Case "y": Fct = Tmp \ 12                                        'Années
    End Select                                                      '
  End Select                                                        '
  roDATEDIF = Fct                                                   '
Exit Function                                                      '
DECM:                                                              'Gestion des "fins de mois" :
  x = DateSerial(Year(D1), Month(D1) + Tmp, 1)                      'Cette routine remplace la fonction
  y = Day(DateSerial(Year(x), Month(x) + 1, 0))                     'native WorksheetFunction.EDate,
  If y < Day(D1) Then dM = x + y - 1 Else dM = x + Day(D1) - 1      'beaucoup trop lente.
Return
End Function

Date1 ne doit pas être postérieure à Date2.
Incrément prend l'une des valeurs des paramètres classiques d'Excel : "y", "ym", "yd", "md", "m", "d", "h", "n" ou "s".
La fonction n'appelle aucune fonction auxiliaire.

N'oubliez pas de signaler les éventuelles (ou probables ?) erreurs ! Merci.

Bonne soirée.

ℝOGER2327
#7149

Lundi 16 Gueules 141 (Saint Münchhausen, baron - fête Suprême Quarte)
22 Pluviôse An CCXXII, 6,9334h - thyméle
2014-W07-1T16:38:24Z

Roland_M · 10 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

Bonsoir à tous,

Très BEAU Travail ROGER !

ROGER2327 · 11 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

Suite...

La même chose sans VBA.

Une seule formule :

VB:

=SI($A3*$B3;   SI(D$2="y";ENT((12*(ANNEE(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-ANNEE($A3))
                                   +MOIS(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-MOIS($A3)
            -(MOIS.DECALER($A3;12*(ANNEE(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-ANNEE($A3))
                                   +MOIS(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-MOIS($A3))
                                       >=ENT($B3)+(MOD($B3;1)>=MOD($A3;1))))/12);
               SI(D$2="ym";MOD(12*(ANNEE(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-ANNEE($A3))
                                   +MOIS(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-MOIS($A3)
            -(MOIS.DECALER($A3;12*(ANNEE(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-ANNEE($A3))
                                   +MOIS(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-MOIS($A3))
                                       >=ENT($B3)+(MOD($B3;1)>=MOD($A3;1)));12);
                    SI(D$2="m";12*(ANNEE(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-ANNEE($A3))
                                   +MOIS(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-MOIS($A3)
            -(MOIS.DECALER($A3;12*(ANNEE(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-ANNEE($A3))
                                   +MOIS(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-MOIS($A3))
                                       >=ENT($B3)+(MOD($B3;1)>=MOD($A3;1)));
                             SI(D$2="yd";ENT($B3)+(MOD($B3;1)>=MOD($A3;1))
-MOIS.DECALER(ENT($A3);12*ENT((12*(ANNEE(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-ANNEE($A3))
                                   +MOIS(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-MOIS($A3)
            -(MOIS.DECALER($A3;12*(ANNEE(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-ANNEE($A3))
                                   +MOIS(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-MOIS($A3))
                                       >=ENT($B3)+(MOD($B3;1)>=MOD($A3;1))))/12))-($A3<61)*(59<$B3)*($B3<367)-1;
                             SI(D$2="md";ENT($B3)+(MOD($B3;1)>=MOD($A3;1))
        -MOIS.DECALER(ENT($A3);12*(ANNEE(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-ANNEE($A3))
                                   +MOIS(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-MOIS($A3)
            -(MOIS.DECALER($A3;12*(ANNEE(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-ANNEE($A3))
                                   +MOIS(ENT($B3)+(MOD($B3;1)>=MOD($A3;1)))-MOIS($A3))
                                       >=ENT($B3)+(MOD($B3;1)>=MOD($A3;1))))-1;
                              SI(D$2="d";ENT($B3-$A3)-($A3<61)*(59<$B3);
SI(D$2="h";HEURE($B3-$A3);SI(D$2="n";MINUTE($B3-$A3);SI(D$2="s";SECONDE($B3-$A3);0)))))))));"")

Ça rame un peu, mais c'est rigolo.

Bonne nuit.

ℝOGER2327
#7150

Mardi 17 Gueules 141 (Le Bétrou - fête Suprême Quarte)
23 Pluviôse An CCXXII, 1,7133h - chiendent
2014-W07-2T04:06:43Z

Roland_M · 11 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

re:

ci-joint mes macros et les mêmes en formules de ROGER2327 !
tout ceci fonctionne correctement et facile à utiliser !

ROGER2327 · 11 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

Re...

Roland_M à dit:
re:

ci-joint mes macros et les mêmes en formules de ROGER2327 !
tout ceci fonctionne correctement et facile à utiliser !

Tant qu'à spécialiser les formules (i.e. écrire les formules sans les paramètres "y", "md", etc.), autant utiliser les plus courtes. Par exemple, en O3 de l'onglet FonctionsDatesRoland, vous utilisez :

Code:

=SI(A3*B3;ENT(B3)+((ARRONDI(B3-ENT(B3)-A3+ENT(A3);6))>=0)-MOIS.DECALER(ENT(A3);12*(ANNEE(ENT(B3)+((ARRONDI(B3-ENT(B3)-A3+ENT(A3);6))>=0))-ANNEE(A3))+MOIS(ENT(B3)+((ARRONDI(B3-ENT(B3)-A3+ENT(A3);6))>=0))-MOIS(A3)-(MOIS.DECALER(A3;12*(ANNEE(ENT(B3)+((ARRONDI(B3-ENT(B3)-A3+ENT(A3);6))>=0))-ANNEE(A3))+MOIS(ENT(B3)+((ARRONDI(B3-ENT(B3)-A3+ENT(A3);6))>=0))-MOIS(A3))
>=(ENT(B3)+((ARRONDI(B3-ENT(B3)-A3+ENT(A3);6))>=0))))-1;"")

soit 422 caractères.

À partir de la nouvelle formule que j'ai proposée plus haut, on peut écrire :

Code:

=SI(A3*B3;ENT(B3)+(MOD(B3;1)>=MOD(A3;1))-MOIS.DECALER(ENT(A3);12*(ANNEE(ENT(B3)+(MOD(B3;1)>=MOD(A3;1)))-ANNEE(A3))+MOIS(ENT(B3)+(MOD(B3;1)>=MOD(A3;1)))-MOIS(A3)-(MOIS.DECALER(A3;12*(ANNEE(ENT(B3)+(MOD(B3;1)>=MOD(A3;1)))-ANNEE(A3))+MOIS(ENT(B3)+(MOD(B3;1)>=MOD(A3;1)))-MOIS(A3))>=ENT(B3)+(MOD(B3;1)>=MOD(A3;1))))-1;"")

soit seulement 317 caractères.

Je joins un classeur augmenté de deux onglets.
Dans l'onglet Onglet 2 (3), j'ai mis les formules spécialisées déduite de la formule paramétrée.
Dans l'onglet Onglet 2 (4), j'ai écrit des formules plus courtes en utilisant une colonne auxiliaire.

ℝOGER2327
#7151

Mardi 17 Gueules 141 (Le Bétrou - fête Suprême Quarte)
23 Pluviôse An CCXXII, 5,8367h - chiendent
2014-W07-2T14:00:29Z

Roland_M · 11 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

re

Franchement LE ROGER il est Trop Fort !!!

je ne cache pas qu'en formule je ne suis pas doué !
je me suis mis à Excel très tardivement je crois aux environs de 2002 !?
avant je bidouillé sous QuickBasic 4.5 et j'ai gardé cette habitude de tout faire moi même.
tout doucement avec le temps et surtout avec ce forum exceptionnel, disons le franchement,
j'utilise de plus en plus les fonctions Excel !

Mais, je n'arrive toujours pas à comprendre comment on s'y prends et par quel bout commencer,
pour arriver à réaliser des formules aussi longues, comme vous, comme Monique, et bien d'autres ici !?

Il doit bien y avoir une méthode !?

On ne peut absolument pas commencer à taper une formule pareille dans la barre des formules
on doit la préparer en format texte, mais il y a bien un début !? enfin ... un jour peut être ...!?
mais je commence à me faire vieux !!!

quand au classeur je le garde précieusement !

ROGER2327 · 12 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

Re...

Roland_M à dit:
(...)
avant je bidouillé sous QuickBasic 4.5 et j'ai gardé cette habitude de tout faire moi même.
(...)

Excellente idée ! C'est en forgeant qu'on devient forgeron.

Roland_M à dit:
(...)
tout doucement avec le temps et surtout avec ce forum exceptionnel, disons le franchement,
j'utilise de plus en plus les fonctions Excel !

Mais, je n'arrive toujours pas à comprendre comment on s'y prends et par quel bout commencer,
pour arriver à réaliser des formules aussi longues, comme vous, comme Monique, et bien d'autres ici !?

Il doit bien y avoir une méthode !?

On ne peut absolument pas commencer à taper une formule pareille dans la barre des formules
on doit la préparer en format texte, mais il y a bien un début !? enfin ... un jour peut être ...!?
(...)

C'est certain, lorsqu'on ouvre le tableur, on sait à peu près ce qu'on va y écrire. Autrement, inutile de l'ouvrir.
Je crois fermement que les ingrédients primaires sont : du papier, des crayons à papier, des gommes, du jus de cervelle, de la sueur et des larmes. Cela dit, je ne me risquerai pas à proposer une méthode. Peut-être quelques éléments ?
Décomposer le problème en éléments simples.
Aller du général au particulier. (Concevoir un algorithme dans ses grandes lignes avant de traiter les ramifications.)
Mais j'arrête, car ce genre de conseils est connu de tous et ne donne guère de prise...

Dans le cas qui nous occupe, j'ai rédigé ma démarche :

Je me place d'abord dans le cas où on ne traite que des dates postérieures au 28 février 1900, réservant le traitement des erreurs induites par la croyance persistante de Microsoft en l'existence d'un 29 février 1900.
Dans un premier temps, je ne considère que des dates à 0 heure, c'est-à-dire des dates repérées par un nombre entier dans Excel®.
D'autre part, je suppose toujours (et, je pense, logiquement) que la date de fin (que je noterai Date2 dans la suite) n'est pas antérieure à la date de début (désormais désignée par Date1).

Pour ce qui est de la méthode que je retiens, elle diffère notablement de la méthode généralement utilisée par ceux qui s'attaquent à ce problème.

En général, les gens utilisent une méthode que je qualifierai d'« anthropocentrée » : comme on le fait « à la main », ils cherchent d'abord combien d'années séparent les deux dates ; puis, le cas échéant, combien de mois résiduels doit-on ajouter à ces années ; enfin, si besoin est, le nombre de jours pour compléter l'écart.
Cette méthode a l'inconvénient de multiplier les problèmes dus au fait que ni l'année ni le mois ne sont des unités au sens métrologique du terme. D'où des considérations compliquées pour jongler avec les années bissextiles ou communes et les mois de 28, 29, 30 ou 31 jours.

C'est pourquoi j'ai abandonné cette approche. Remarquant que, si ni l'année ni le mois ne sont des unités, il n'en est pas moins vrai qu'on considère que l'année et le mois sont dans un rapport constant de un à douze, j'ai choisi de privilégier le décompte des mois séparant les deux dates. Ce décompte étant fait, une simple division euclidienne par douze me donne le nombre d'années (quotient de la division) et le nombre de mois résiduels (reste de la division) séparant les deux dates.
Cette approche m'a semblé d'autant plus intéressante qu'on dispose d'une fonction native qui semble assez sûre : MOIS.DECALER.

Etc.
Le texte étant un peu long (un peu plus de mille mots), je le joins au format .pdf. (C'est un document éditable et modifiable à souhait avec LibreOffice Writer.)
J'y donne la construction pas à pas de la « grrrande formule paramétrée » qui n'est en réalité que la juxtaposition un peu répétitive de petits éléments pas très-difficiles à construire.

Étant un piètre dactylographe, il se peut que j'ai laissé quelques fzutes de frqppe et quelques fantaisis orthotrafiques. Veuillez m'en excuser. Merci.

Roland_M à dit:
(...)

mais je commence à me faire vieux !!!

(...)

Arrêtez ! Je ne suis votre cadet que de moins de mille jours : vous me faites peur !

Bonne journée.

ℝOGER2327
#7152

Mercredi 18 Gueules 141 (Nativité de Deibler, prestidigitateur - fête Suprême Quarte)
24 Pluviôse An CCXXII, 1,0766h - trainasse
2014-W07-3T02:35:01Z

Roland_M · 13 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

re et dern je crois !?

ROGER, encore une fois, un GRAND merci à vous pour ce BEAU travail !

j'ai repris le principe du total des mois qui est de toutes évidences plus logique et beaucoup plus simple.
et j'ai fais ma petite formule pour les jours !
ça éclairci et allège assez bien le code.

il est vrai que je pratique comme vous le dites ( une méthode que je qualifierai d' "anthropocentrée" )

et pour ceux que ça intéressent,
je met mon classeur à jour plus une page avec vos formules dans des zones de texte.

au plaisir

EDIT:
remplacement du classeur par le nouveau mise à jour formules(ROGER2327) au poste suivant !

ROGER2327 · 13 Février 2014

Re : ci-joint un classeur avec fonctions dates en remplacement de Datedif

Re...

Roland_M à dit:
re et dern je crois !?
(...)

On verra...
(J'ai sous le coude un projet de synthèse, mais il n'est pas terminé.)

Roland_M à dit:
(...)
et pour ceux que ça intéressent,
je met mon classeur à jour plus une page avec vos formules dans des zones de texte.

au plaisir

Vu la fréquence des problèmes avec DATEDIF, j'espère bien que ça en intéressera plus d'un !

Quant aux compliments que vous me faites, ils me touchent, mais vous en méritez aussi : de toutes les solutions plus ou moins ingénieuses, plus ou moins compliquées que j'ai vues, la vôtre est la première qui semble fiable.
(Je prends la précaution du "il me semble" car, sait-on jamais ? quelqu'un va peut-être nous sortir l'exemple qui ne marche pas...)

Une remarque toutefois : les formules que vous citez dans l'onglet FormulesROGER ne sont pas optimisées. Je joins une copie de votre classeur avec la dernière version de mes formules, nettement plus courtes que les anciennes.

Quoi qu'il en soit, merci encore pour votre travail de synthèse.

Cordialement,

ℝOGER2327
#7159

Jeudi 19 Gueules 141 (Saint Sade ès Liens - fête Suprême Quarte)
25 Pluviôse An CCXXII, 7,4377h - lièvre
2014-W07-4T17:51:01Z

ci-joint un classeur avec fonctions dates en remplacement de Datedif

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

Discussions similaires

Partager cette page

Statistiques des forums