Utiliser TODAY dans Excel pour calculer la prochaine occurrence d'une date dans un certain nombre de mois ?

Voici une solution facile à comprendre, basée sur une formule, sans tableau, qui fonctionne avec Excel 2010 (et les versions antérieures). Elle utilise des colonnes d'aide (qui peuvent être masquées).

Étant donné qu'il n'est pas toujours possible de satisfaire aux exigences de moins de 24 mois et de plus de 18 mois, et que l'exigence absolue est de moins de 24 mois, j'ai assoupli l'exigence à l'autre extrémité en la fixant à plus de 18 mois.

Pour une date donnée, il n'y a que trois dates cibles possibles : le 31 mars ou le 30 septembre de l'année qui suit de 18 mois la date, ou le 31 mars de l'année suivante. Il vous suffit de sélectionner la première d'entre elles qui répond aux critères.

La question précise les résultats basés sur TODAY. Je voulais également montrer comment cela se comporte pour d'autres "jours". La cellule A2 contient =TODAY() . Les autres cellules de la colonne A ne représentent que d'autres dates à titre d'illustration, en particulier les "dates limites" relatives aux 31 mars et 30 septembre. Les formules font référence à la cellule de date, mais TODAY() pourrait être codé en dur à la place.

Les colonnes I:J ne sont données qu'à titre d'illustration. Elles indiquent les dates de 18 et 24 mois à partir de la date de la colonne A pour aider à comprendre pourquoi les valeurs des résultats sont sélectionnées.

Les colonnes d'aide sont C:E. Elles contiennent les trois dates cibles candidates pour la date de la colonne A. La cible 1 de la colonne C2 contient :

=DATE(YEAR(EDATE(A2,18)),3,31)

Cela crée la date du 31 mars de l'année 18 mois après la date de la colonne A. La cible 2 de D2 contient :

=DATE(YEAR(EDATE(A2,18)),9,30)

Cela crée la date du 30 septembre de l'année 18 mois après la date de la colonne A. La cible 3 dans E2 contient :

=DATE(YEAR(EDATE(A2,18))+1,3,31)

Cela crée la date du 31 mars de l'année qui suit de 18 mois la date de la colonne A.

Le résultat se trouve dans la colonne G. La formule se trouve dans la colonne G2 :

=SUMPRODUCT((C2:E2<EDATE(A2,24))*(C2:E2>=EDATE(A2,18))*C2:E2)

En raison des exigences, une seule date cible peut être prise en compte. SUMPRODUCT traite les comparaisons de tableaux avec une formule normale (sans tableau).

C2:E2<EDATE(A2,24) renvoie VRAI/FAUX (1/0) pour chaque date cible selon que la date est inférieure ou non à 24 mois de la date de la colonne A.

C2:E2>=EDATE(A2,18) renvoie de la même manière 1/0 pour chaque date cible selon que la date est >= 18 mois par rapport à la date de la colonne A.

Une seule date cible remplira les deux conditions, de sorte que le produit de ces valeurs 1/0 sera le suivant 1 pour cette date et 0 pour les deux autres dates. Ce produit est multiplié par la valeur de chaque cellule de date cible. Les dates étant stockées sous forme de nombres, le résultat est le nombre représentant la date cible de qualification. Il suffit de le formater comme une date.

La fonction définie par l'utilisateur suivante crée d'abord une période de calendrier de 18 à 24 mois à partir d'aujourd'hui. Elle parcourt ensuite cette période en boucle jusqu'à ce qu'elle trouve une date correspondant à vos critères :

Public Function ProjDate() As Date
    Dim d1 As Date, d2 As Date, y As Long
    Dim dd As Date, d As Long, m As Long

    d = Day(Date)
    m = Month(Date)
    y = Year(Date)
    d1 = DateSerial(y, m + 18, d + 1)
    d2 = DateSerial(y, m + 24, d - 1)

    For dd = d1 To d2
        d = Day(dd)
        m = Month(dd)
        If (m = 3 And d = 31) Or (m = 9 And d = 30) Then
            ProjDate = dd
            Exit Function
        End If
    Next dd
End Function

Les fonctions définies par l'utilisateur (UDF) sont très faciles à mettre en œuvre. installer et l'utiliser :

1. ALT-F11 ouvre la fenêtre VBE
2. ALT-I ALT-M ouvre un nouveau module
3. coller les éléments et fermer la fenêtre du VBE

Si vous enregistrez le classeur, l'UDF sera enregistré avec lui. Si vous utilisez une version d'Excel postérieure à 2003, vous devez enregistrer le fichier au format .xlsm plutôt qu'au format .xlsm. le fichier en tant que .xlsm plutôt que .xlsx.

Pour supprimer l'UDF :

1. faire apparaître la fenêtre VBE comme ci-dessus
2. effacer le code
3. fermer la fenêtre VBE

Pour utiliser l'UDF à partir d'Excel :

\=myfonction(A1)

Pour en savoir plus sur les macros en général, voir :

http://www.mvps.org/dmcritchie/excel/getstarted.htm

y

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

et pour les spécificités des UDF, voir :

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Les macros doivent être activées pour que cela fonctionne !

Voici une formule. Telle qu'elle est écrite, pour les tests, elle fait référence à A1 . Toutefois, vous pouvez remplacer A1 con TODAY() si vous constatez qu'il renvoie les résultats escomptés :

=MAX((MONTH(EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))={4,10})*EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))-1

Ce qui précède est une réseau formule.

Comme il s'agit d'une formule de tableau, vous devez la "confirmer" en maintenant enfoncée la touche ctrl + shift tout en frappant enter . Si vous procédez correctement, Excel placera des accolades {...} autour de la formule telle qu'observée dans la barre de formule

Si vous voulez éviter le CSE vous pouvez essayer la procédure d'entrée légèrement plus longue :

=AGGREGATE( 14,4,(MONTH(EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))={4,10})*EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}),1)-1