Microsoft 365 AUTOMATISER 2 CELLULES D'UN FICHIER EXCEL EXISTANT

[Kalinda]

Bonjour à tous,

Je sollicite votre aide pour automatiser 2 colonnes dans la feuille DT (en rouge dans le tableau joint), la première s'agit du nombre de mois de présence du salarié dans l'année à calculer en fonction de sa date d'entrée et de sa date de sortie sachant que 80% des salariés ont été présent toute l'année (2019). Je souhaiterai que cette colonne calcul le nombre de mois et de jours réel de chaque mois (calendaire).

La seconde colonne concerne le calcul du plafond théorique dont la colonne cité ci-dessus fera partie du calcul de ce plafond.

Le plafond théorique s'élève à 3377€ pour un mois complet c'est à dire qu'un salarié touchant 4000€ son plafond sera de 3377€ en rechanche, un salarié touchant une salaire de 2500 € pour un mois complet son plafond sera de 2500€.

C'est compliqué je sais mais je reste disponible si vous avez besoin de précision.... peut-être commencer par la 1ère colonne et avancer petit à petit?

Je vous remercie pour votre aide

 

[laurent3372]

Bonjour,

Les dates en colonnes D et E sont sous forme de texte, ce qui interdit tout calcul faisant intervenir ces colonnes.
Est-ce la même chose dans le fichier d'origine ?
Sinon, je peux écrire une macro qui remet ces date dans le bon format.

Dans l'attente d'une réponse,
--
LR

 

[jmfmarques]

Bonjour
Et pour ce qui est de la seconde question, telle qu'exprimée :
Ce sera donc TOUJOURS la plus petite de deux valeurs : celle su salaire perçu et celle du plafond théorique -->> urilisatiobn simple de la fonction MIN de Excel

 

[Kalinda]

Bonjour

Merci beaucoup pour votre réponse, oui c'est la même chose que dans le fichier d'origine, à vrai dire je ne suis pas experte sur Excel je prendrai en compte toutes vos solutions

Merci



Bonjour,

Les dates en colonnes D et E sont sous forme de texte, ce qui interdit tout calcul faisant intervenir ces colonnes.
Est-ce la même chose dans le fichier d'origine ?
Sinon, je peux écrire une macro qui remet ces date dans le bon format.

Dans l'attente d'une réponse,
--
LR
[/QUOTE]

 

[Kalinda]

Bonjour,

Merci pour votre réponse, concernant la deuxième colonne c'est un peu plus compliqué que cela je pense car un deuxième paramètre s'ajoute à cela, l'objectif de la 2ème colonne c'est de calculer le plafond théorique de chaque salarié.

Le calcul est simple si le salarié est présent toute l'année dans la société et votre formule fonctionnera parfaitement.

Exemple :
Salarié présent toute l'année à 4000€ = 3377(Urssaf)*12 mois de présence= 40524
Salarié à 2500€ = 2500*12= 30 000

En revanche, pour les salariés entrées/sorties en cours d'année, le plafond de la sécurité social devra être proratiser (d'où l'utilité du calcul de la 1ère colonne je pense).

Exemple :
Salarié du 10/03/19 au 04/06/19 à 4000 €
Mars = 3377/31(jr calendaire)*22(jr de présence calendaire) = 2396,58
Avril = 3377
Mai = 3377
Juin = 3377/30(Jr calendaire)*4(Jr de présence calendaire)= 450,27

Ce qui nous donne un plafond annuel pour ce salarié de 9600,85

Même exemple pour un salarié à 2500€ :
Mars = 2500/31(jr calendaire)*22(jr de précence calendaire) = 1774,19
Avril = 2500
Mai = 2500
Juin = 2500/30(Jr calendaire)*4(Jr de précence calendaire)= 333,33

Ce qui nous donne un plafond annuel pour ce salarié de 7107,52


Si vous voulez, je souhaiterai automatiser cette colonne pour éviter de calculer chaque plafond de chaque salarié à la mains (vue le nombre de salarié)

En terme de présentations des données dans le tableau n'hesitez pas à me dire si mon raisonnement n'est pas bon et si je dois améliorer quelque chose

 

[laurent3372]

Voici ma solution:

 

[laurent3372]

Je n'avais pas lu votre second message...
Il va falloir que je retravaille la notion de jours calendaires pour tenir compte de vos remarques.

 

[Kalinda]

Merci beaucoup!

 

[laurent3372]

Je ne m'en sortirai pas avec des formules. Il va falloir du code VBA.

 

[Kalinda]

D'accord et comment je peux vous aider?

 

[Kalinda]

Déjà est-ce que ma demande est faisable?

 

[laurent3372]

Une petite fonction personnalisée, et le tour est joué.

Public Function PlafondSS(brut As Double, deb As Date, fin As Date) As Double
    Dim plafMensuel As Double
    Dim Année As Long
    Dim mois As Long, nbjoursMois As Long
    Dim debMois As Date, finMois As Date
    Dim debPer As Date, finPer As Date
    Dim nbjoursPer As Long
    Dim brutPlafonné As Double
    Dim brutPer As Double
    
    plafMensuel = [pm]
    Année = Year(deb)
    brutPlafonné = Application.Min(brut, plafMensuel)
    PlafondSS = 0
    For mois = 1 To 12
        If mois >= Month(deb) And mois <= Month(fin) Then
            debMois = DateSerial(Année, mois, 1)
            finMois = DateSerial(Année, mois + 1, 0)
            nbjoursMois = finMois - debMois + 1 'Nombre de jours total dans le mois
            debPer = Application.Max(debMois, deb)
            finPer = Application.Min(finMois, fin)
            nbjoursPer = finPer - debPer + 1
            brutPer = brutPlafonné / nbjoursMois * nbjoursPer
            PlafondSS = PlafondSS + brutPer
        End If
    Next mois
    
End Function

 

[jmfmarques]

Bonjour Laurent
Je me demande si ceci ne serait pas en mesure de te faciliter la tâche (d'aide à kalinda) :

debut	fin	nb mois	nb jours		formule C2 tirée vers le bas : =DATEDIF(A2;B2;"ym")
12/01/2020​	15/05/2020​	4​	3​
03/02/2020​	29/02/2020​	0​	26​		formule D2 tirée vers le bas : =DATEDIF(A2;B2;"md")
28/02/2020​	29/02/2020​	0​	1​
24/12/2020​	03/01/2021​	0​	10​
19/12/2020​	08/02/2021​	1​	20​

 

[laurent3372]

Je ne connaissais pas DATEDIF ! Ca aide, mais ça ne suffit pas pour calculer le plafond selon les instructions de @Kalinda qui exigent qu'on calcule mois par mois.

 

[Kalinda]

D'accord super merci, et comment dois-je faire pour l'appliquer ?