Help ! Prescription d'un trimestre !

L

Law

XLDnaute Junior
Bonjour le forum,

Je travaille dans un organisme social et bute sur le problème suivant.

En fonction de la date du jour (et donc le trimestre en cours), j'aimerais connaître à tout moment la période potentiellement concernée par un contrôle, ceci sachant que :
a) les cotisations des trimestres civils sont exigibles au dernier jour du mois suivant la fin du trimestre (le 1er trimestre N est exigible le 30/04/N ; le 2ème le 31/07/N ; le 3ème le 30/09/N ; le 4ème le 31/01/N+1).
b) la période pouvant faire l'objet d'un contrôle ne peut excéder 3 ans.

3 exemples (pour être plus compréhensible) :

1. Aujourd'hui, nous sommes le 2 mai 2008. Il s'agit donc du 2ème trimestre 2008 (2T2008). Ce trimestre sera exigible le 31/07/2008. Et bien le trimestre le plus ancien sur lequel nous puissions remonter pour effectuer un contrôle est donc le 2ème trimestre 2005 (2T2005). Pour le 1er trimestre 2005 (qui était exigible le 30/04/2005), il est donc trop tard car le 30/04/2008 est dépassé.

2. Admettons que nous soyons le 28 avril 2008. Il s'agit toujours du 2ème trimestre 2008 (2T2008), trimestre qui est exigible le 31/07/2008. Là, le trimestre le plus ancien que nous pouvons contrôler sera le 1er trimestre 2005, celui-ci étant exigible le 30/04/2005, il ne sera prescrit que dans 2 jours (le 30 avril 2008).

3. Admettons que nous soyons le 15 janvier 2008. Cette date correspond au 1er trimestre 2008 (1T2008), exigible le 30/04/2008. La, le contrôle peut remontrer jusqu'au 4ème trimestre 2004, celui-ci étant exigible le 31/01/2005. Le 4ème trimestre 2004 pourra faire l'objet d'un contrôle jusqu'au 31/01/2008 inclu.

Je vous joins en PJ le début de mes laborieux travaux...
J'espère être compréhensible, et je vous remercie par avance pour votre aide ! :)
 

Pièces jointes

  • Test.xls
    33.5 KB · Affichages: 50
  • Test.xls
    33.5 KB · Affichages: 52
  • Test.xls
    33.5 KB · Affichages: 51
Trier par date Trier par votes
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : Help ! Prescription d'un trimestre !

Bonjour Law

regarde le code ci dessous, si j'ai bien compris les calculs à effectuer :

Code: 
Sub test2()
Dim t As Byte, madate As Date, madate1 As Date, madate2 As Date
madate = Range("C1").Value
t = DatePart("q", madate, 2, 2)
Range("C2").Value = t & "T" & Year(madate)
Select Case t
    Case 1
        madate1 = DateSerial(Year(Date), 1, 1)
        madate2 = DateSerial(Year(Date), 3, 31)
    Case 2
        madate1 = DateSerial(Year(Date), 4, 1)
        madate2 = DateSerial(Year(Date), 6, 30)
    Case 3
        madate1 = DateSerial(Year(Date), 7, 1)
        madate2 = DateSerial(Year(Date), 9, 30)
    Case 4
        madate1 = DateSerial(Year(Date), 10, 1)
        madate2 = DateSerial(Year(Date), 12, 31)
End Select
Range("C3").Value = madate1
Range("C4").Value = madate2
Range("C5").Value = DatePart("q", DateAdd("yyyy", -3, madate1), 2, 2) & "T" & Year(DateAdd("yyyy", -3, madate1))
Range("C6").Value = DateSerial(Year(madate2), Month(madate2) + 2, 0)
End Sub

bonne journée
@+
 
Vote positif 0 Vote négatif
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : Help ! Prescription d'un trimestre !

Re

attention, la date de référence doit être renseignée en C1, si tu veux que ce soit la date du jour, rajoute la ligne de code ci dessous :

Code: 
[B]Range("C1").Value = Date[/B]
madate = Range("C1").Value

@+
 
Vote positif 0 Vote négatif
L

Law

XLDnaute Junior
Re : Help ! Prescription d'un trimestre !

Pierrot93 à dit:
Bonjour Law

regarde le code ci dessous, si j'ai bien compris les calculs à effectuer :

Code: 
Sub test2()
Dim t As Byte, madate As Date, madate1 As Date, madate2 As Date
madate = Range("C1").Value
t = DatePart("q", madate, 2, 2)
Range("C2").Value = t & "T" & Year(madate)
Select Case t
    Case 1
        madate1 = DateSerial(Year(Date), 1, 1)
        madate2 = DateSerial(Year(Date), 3, 31)
    Case 2
        madate1 = DateSerial(Year(Date), 4, 1)
        madate2 = DateSerial(Year(Date), 6, 30)
    Case 3
        madate1 = DateSerial(Year(Date), 7, 1)
        madate2 = DateSerial(Year(Date), 9, 30)
    Case 4
        madate1 = DateSerial(Year(Date), 10, 1)
        madate2 = DateSerial(Year(Date), 12, 31)
End Select
Range("C3").Value = madate1
Range("C4").Value = madate2
Range("C5").Value = DatePart("q", DateAdd("yyyy", -3, madate1), 2, 2) & "T" & Year(DateAdd("yyyy", -3, madate1))
Range("C6").Value = DateSerial(Year(madate2), Month(madate2) + 2, 0)
End Sub

bonne journée
@+
Cliquez pour agrandir...

Bonjour Pierrot93,
Avant tout, merci de me venir en aide.
J'ai recopié ton code et l'ai adapté en transformant simplement les Ranges C en Range B pour coller à mon modèle (résultats dans la colonne "B").

Voilà ce que j'ai relevé :

1. Si en B1 (C1 dans ton code), on saisit par exemple en date du jour le 05/01/2008, en date de début du contrôle, la macro renvoie le 1T2005. Or, ce devrait être le 4T2004, celui-ci étant exigible le 31/01/2005, il reste encore 25 jours avant qu'il ne soit prescrit (il le sera le 31/01/2008).

2. Dans le même ordre d'idées, si en B1 (C1 dans ton code), on saisit par exemple en date du jour le 10/04/2008, en date de début du contrôle, la macro renvoie le 2T2005. La macro devrait renvoyer le 1T2005, car celui-ci est exigible le 30/04/2005, il reste encore 20 jours avant qu'il ne soit prescrit (il le sera le 30/04/2008).

3. Enfin, mais là c'est entièrement de ma faute car je ne m'y suis pas arrêté en expliquant mon problème, au niveau de la date de fin du contrôle (C6 dans ton code), la macro doit également tenir compte du fait qu'un trimestre devient exigible au dernier jour du mois suivant la fin du trimestre. Je m'explique en prenant l'exemple d'aujourd'hui. Nous sommes le 02/05/2008, il s'agit du 2T2008. Ce trimestre ne sera exigible que le 31/07/2008. Donc il ne peut pas entrer dans la période de contrôle. La macro, qui à l'heure actuelle renvoit "31/07/2008" (serait-il possible qu'elle renvoit un résultat au même format qu'en C5 ??) devrait dans l'exemple renvoyer le 1T2008 (qui est devenu exigible depuis le 30/04/2008).

J'espère que je me suis bien fait comprendre... :((
Si tu as besoin de davantage d'informations, surtout n'hésites pas ! :)
Et merci une nouvelle fois !
 
Vote positif 0 Vote négatif
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : Help ! Prescription d'un trimestre !

Re

code modifié pour C5 et C6, mais pas tout compris sur le calcul de la "fin étendue controle".... sans doute à revoir..

Code: 
Sub test2()
Dim t As Byte, madate As Date, madate1 As Date, madate2 As Date
madate = Range("C1").Value
t = DatePart("q", madate, 2, 2)
Range("C2").Value = t & "T" & Year(madate)

Select Case t
    Case 1
        madate1 = DateSerial(Year(Date), 1, 1)
        madate2 = DateSerial(Year(Date), 3, 31)
    Case 2
        madate1 = DateSerial(Year(Date), 4, 1)
        madate2 = DateSerial(Year(Date), 6, 30)
    Case 3
        madate1 = DateSerial(Year(Date), 7, 1)
        madate2 = DateSerial(Year(Date), 9, 30)
    Case 4
        madate1 = DateSerial(Year(Date), 10, 1)
        madate2 = DateSerial(Year(Date), 12, 31)
End Select
Range("C3").Value = madate1
Range("C4").Value = madate2
Range("C5").Value = DatePart("q", DateAdd("yyyy", -3, DateAdd("m", -1, madate1)), 2, 2) & "T" & Year(DateAdd("yyyy", -3, DateAdd("m", -1, madate1)))
Range("C6").Value = DatePart("q", DateAdd("m", 1, madate2), 2, 2) & "T" & Year(DateAdd("m", 1, madate2))
End Sub

@+
 
Vote positif 0 Vote négatif
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : Help ! Prescription d'un trimestre !

Re

j'ai re modifié le code comme suit, il restait à tort des appels à la fonction "date" :

Code: 
Sub test()
Dim t As Byte, madate As Date, madate1 As Date, madate2 As Date
madate = Range("C1").Value
t = DatePart("q", madate, 2, 2)
Range("C2").Value = t & "T" & Year(madate)

Select Case t
    Case 1
        madate1 = DateSerial(Year(madate), 1, 1)
        madate2 = DateSerial(Year(madate), 3, 31)
    Case 2
        madate1 = DateSerial(Year(madate), 4, 1)
        madate2 = DateSerial(Year(madate), 6, 30)
    Case 3
        madate1 = DateSerial(Year(madate), 7, 1)
        madate2 = DateSerial(Year(madate), 9, 30)
    Case 4
        madate1 = DateSerial(Year(madate), 10, 1)
        madate2 = DateSerial(Year(madate), 12, 31)
End Select
Range("C3").Value = madate1
Range("C4").Value = madate2
Range("C5").Value = DatePart("q", DateAdd("yyyy", -3, DateAdd("m", -1, madate1)), 2, 2) & "T" & Year(DateAdd("yyyy", -3, DateAdd("m", -1, madate1)))
Range("C6").Value = DatePart("q", DateAdd("m", 1, madate2), 2, 2) & "T" & Year(DateAdd("m", 1, madate2))
End Sub

@+
 
Vote positif 0 Vote négatif
L

Law

XLDnaute Junior
Re : Help ! Prescription d'un trimestre !

Pierrot93 à dit:
Re

j'ai re modifié le code comme suit, il restait à tort des appels à la fonction "date" :

Code: 
Sub test()
Dim t As Byte, madate As Date, madate1 As Date, madate2 As Date
madate = Range("C1").Value
t = DatePart("q", madate, 2, 2)
Range("C2").Value = t & "T" & Year(madate)

Select Case t
    Case 1
        madate1 = DateSerial(Year(madate), 1, 1)
        madate2 = DateSerial(Year(madate), 3, 31)
    Case 2
        madate1 = DateSerial(Year(madate), 4, 1)
        madate2 = DateSerial(Year(madate), 6, 30)
    Case 3
        madate1 = DateSerial(Year(madate), 7, 1)
        madate2 = DateSerial(Year(madate), 9, 30)
    Case 4
        madate1 = DateSerial(Year(madate), 10, 1)
        madate2 = DateSerial(Year(madate), 12, 31)
End Select
Range("C3").Value = madate1
Range("C4").Value = madate2
Range("C5").Value = DatePart("q", DateAdd("yyyy", -3, DateAdd("m", -1, madate1)), 2, 2) & "T" & Year(DateAdd("yyyy", -3, DateAdd("m", -1, madate1)))
Range("C6").Value = DatePart("q", DateAdd("m", 1, madate2), 2, 2) & "T" & Year(DateAdd("m", 1, madate2))
End Sub

@+
Cliquez pour agrandir...


Merci beaucoup pour ta collaboration Pierrot93.

Mais bouh, les 2 bugs suivants demeurent :

1. Dans ta macro, si en date de référence (C1) tu saisis la date du jour (02/05/2008), alors en date de début du contrôle (C5), la macro donne le 1T2005. Ce trimestre était exigible le 30/04/2005. Donc aujourd'hui le 02/05/2008, il est prescrit. La macro ne doit donc pas renvoyer le 1T2005 mais le 2T2005.

2. Pour ce qui concerne la "fin étendue contrôle", je me suis sans doute mal exprimé. En fait, pour qu'un trimestre puisse faire partie de la période de trois ans susceptible de faire l'objet d'un contrôle, il faut qu'il soit prescrit. S'il ne l'est pas, il ne peut pas faire l'objet d'un contrôle. En conséquence, aujourd'hui le 02/05/2008, nous sommes au cours du 2T2008. Ce trimestre ne sera exigible que le 31/07/2008. C'est donc seulement le 01/08/2008 qu'un contrôle pourra retenir ce trimestre dans sa période contrôlée. Mais à aujourd'hui le 02/05/2008, le trimestre le plus récent susceptible de faire l'objet d'un contrôle est le 1T2008, ce dernier étant prescrit depuis le 30/04/2008.

J'espère que cette fois je suis parvenu à me faire comprendre ! :)

Merci pour la modif de C6 (passage d'une date jj/mm/aaaa en date de trimestre type 2T2008) ! :)

Bonne journée à toi Pierrot !
 
Vote positif 0 Vote négatif
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : Help ! Prescription d'un trimestre !

Re

reprends le fichier d'origine que tu avais posté, et remplis plusieurs colonnes avec des dates différentes et les résultats attendus en fonction des differents cas. ce sera plus facile pour t'aider... car là je suis un peu perdu avec toutes ces dates...
 
Vote positif 0 Vote négatif
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : Help ! Prescription d'un trimestre !

Re

un truc m'échappe tout de même, à la vue de ton fichier, avec des dates de départ différentes 26/12/2007 et 05/01/2008 nous arrivons avec un début de controle identique... idem pour les 28/03/2008 et 30/04/2008... en l'état vois pas trop la règle à appliquer...
 
Vote positif 0 Vote négatif
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : Help ! Prescription d'un trimestre !

Re

je viens juste de comprende pour le "début controle", enfin je crois, à priori donne le résultat escompté :

Code: 
Option Explicit
Sub test()
Dim t As Byte, madate As Date, madate1 As Date, madate2 As Date
madate = Range("C1").Value
t = DatePart("q", madate, 2, 2)
Range("C2").Value = t & "T" & Year(madate)

Select Case t
    Case 1
        madate1 = DateSerial(Year(madate), 1, 1)
        madate2 = DateSerial(Year(madate), 3, 31)
    Case 2
        madate1 = DateSerial(Year(madate), 4, 1)
        madate2 = DateSerial(Year(madate), 6, 30)
    Case 3
        madate1 = DateSerial(Year(madate), 7, 1)
        madate2 = DateSerial(Year(madate), 9, 30)
    Case 4
        madate1 = DateSerial(Year(madate), 10, 1)
        madate2 = DateSerial(Year(madate), 12, 31)
End Select
Range("C3").Value = madate1
Range("C4").Value = madate2
Range("C5").Value = DateSerial(Year(madate2), Month(madate2) + 2, 0)

Range("C6").Value = DatePart("q", DateAdd("yyyy", -3, DateAdd("m", -1, madate)), 2, 2) & "T" & Year(DateAdd("yyyy", -3, DateAdd("m", -1, madate)))

End Sub

manque plus que la fin de contrôle...;

@+
 
Vote positif 0 Vote négatif
L

Law

XLDnaute Junior
Re : Help ! Prescription d'un trimestre !

Pierrot93 à dit:
Re

je viens juste de comprende pour le "début controle", enfin je crois, à priori donne le résultat escompté :

Code: 
Option Explicit
Sub test()
Dim t As Byte, madate As Date, madate1 As Date, madate2 As Date
madate = Range("C1").Value
t = DatePart("q", madate, 2, 2)
Range("C2").Value = t & "T" & Year(madate)

Select Case t
    Case 1
        madate1 = DateSerial(Year(madate), 1, 1)
        madate2 = DateSerial(Year(madate), 3, 31)
    Case 2
        madate1 = DateSerial(Year(madate), 4, 1)
        madate2 = DateSerial(Year(madate), 6, 30)
    Case 3
        madate1 = DateSerial(Year(madate), 7, 1)
        madate2 = DateSerial(Year(madate), 9, 30)
    Case 4
        madate1 = DateSerial(Year(madate), 10, 1)
        madate2 = DateSerial(Year(madate), 12, 31)
End Select
Range("C3").Value = madate1
Range("C4").Value = madate2
Range("C5").Value = DateSerial(Year(madate2), Month(madate2) + 2, 0)

Range("C6").Value = DatePart("q", DateAdd("yyyy", -3, DateAdd("m", -1, madate)), 2, 2) & "T" & Year(DateAdd("yyyy", -3, DateAdd("m", -1, madate)))

End Sub

manque plus que la fin de contrôle...;

@+
Cliquez pour agrandir...

Magnifique Pierrot, là tu as tout bon !
Je ne vais pas t'embêter davantage avec la date de fin de contrôle, ce que tu as fait est déjà énorme. Je trouverai bien une solution !
Merci encore, et bonne journée à toi (chez moi il est 00h27, on est le 03/05/08, je vais dormir ! :) )
Ciao ciao !
 
Vote positif 0 Vote négatif
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : Help ! Prescription d'un trimestre !

Re,

j'arrive au résultat.... enfin j'espère.... à tester dans tous les cas de figure.

Code: 
Option Explicit
Sub test()
Dim t As Byte, madate As Date, madate1 As Date, madate2 As Date
madate = Range("C1").Value
t = DatePart("q", madate, 2, 2)
Range("C2").Value = t & "T" & Year(madate)
Select Case t
    Case 1
        madate1 = DateSerial(Year(madate), 1, 1)
        madate2 = DateSerial(Year(madate), 3, 31)
    Case 2
        madate1 = DateSerial(Year(madate), 4, 1)
        madate2 = DateSerial(Year(madate), 6, 30)
    Case 3
        madate1 = DateSerial(Year(madate), 7, 1)
        madate2 = DateSerial(Year(madate), 9, 30)
    Case 4
        madate1 = DateSerial(Year(madate), 10, 1)
        madate2 = DateSerial(Year(madate), 12, 31)
End Select
Range("C3").Value = madate1
Range("C4").Value = madate2
Range("C5").Value = DateSerial(Year(madate2), Month(madate2) + 2, 0)
Range("C6").Value = DatePart("q", DateAdd("yyyy", -3, DateAdd("m", -1, madate)), 2, 2) _
    & "T" & Year(DateAdd("yyyy", -3, DateAdd("m", -2, madate)))
Range("C7").Value = DatePart("q", DateAdd("q", 11, DateAdd("yyyy", -3, DateAdd("m", -1, madate))), 2, 2) _
    & "T" & Year(DateAdd("q", 11, DateAdd("yyyy", -3, DateAdd("m", -1, madate))))
End Sub

une bonne nuit à toi:rolleyes: et bonne fin d'après midi aux autres.
@+
 
Vote positif 0 Vote négatif

Discussions similaires

R
  • Question
Microsoft 365 Automatisation calcul
Réponses
2
Affichages
545
Roran
R
D
  • Question
XL 2016 Gestion complète d'un tournoi de sixte (de 30 à 60 équipes)
Réponses
3
Affichages
2 K
Laguye
L
C
  • Question
XL 2016 Création d'un graphique avec des colonnes vides
Réponses
4
Affichages
914
Cesar1275
C
C
  • Question
XL 2016 calcul sur horaire
2
Réponses
17
Affichages
3 K
jaypeeh
J
L
  • Question
XL 2010 vba pour masquer des colonnes lors d'un export
Réponses
1
Affichages
980
Staple1600
Staple1600
I
  • Question
XL 2019 transferer des formules d'un excel a un autre
2 3
Réponses
37
Affichages
2 K
ilien09
I
D
DXL10
Réponses
0
Affichages
267
DXL10
D
C
  • Question
Structurer des données, besoin de VBA ?
Réponses
6
Affichages
806
zebanx
zebanx
P
  • Question
XL 2013 calcul capacité charge équipe annuelle au jours le jours
Réponses
0
Affichages
9 K
PEX
P
R
  • Question
Calcul de Variation d'un mois a l'autre sur base de multicritères
Réponses
3
Affichages
3 K
Raynaud.JF
R

Membres actuellement en ligne

1 membres et 39 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 489
Messages
2 088 855
Membres
103 976
dernier inscrit
kaizertv2001@gmailcom