XL 2013 Nombre de jours dans une série d'intervalles de dates (se chevauchant)

[massepi]

Bonjour,
Je n'arrive pas à trouver un titre assez clair pour expliquer mon problème:
- J'ai un premier tableau qui affiche les situations de paie de salariés (un salarié peut avoir plusieurs situations dans l'année et donc 1 ligne par situation). Dans cette ligne on retrouve la date de début et de fin de la situation sous format JJ/MM/AAAA.

- J'ai un autre tableau qui affiche les absences du salarié pendant l'année : 1 ligne par absence avec date début, date fin et nombre de jours.

- Le but est de déterminer pour chaque situation (tableau 1), le nombre de jours d'absences du salarié, sachant bien sûr qu'une absence peut se trouver à cheval sur 2 situations...

j'ai trouvé une solution avec SOMMEPROD qui fonctionne dans le cas où les absences ne sont pas à cheval sur 2 (voire plusieurs) situations de paie.

Je vous joins un exemple, j'ai laissé sur fond jaune l'emplacement des valeurs à calculer et juste à côté les valeurs que l'on est sensé trouver.
Mon problème réside dans le fait que lorsqu'une période d'absence est à cheval sur 2 situations de paie, mes critères du SOMMEPROD sont alors faux (ma date de fin de première situation n'est pas supérieure à la date de fin de l'absence...)

Voilà, je sais c'est assez complexe à expliquer mais avec l'exemple c'est plus clair!

Merci pour votre aide,
Bonne journée

 

[mapomme]

Bonsoir @massepi, bienvenue sur XLD ,

Un essai avec une fonction personnalisée en VBA dont le code se trouve dans Module1.

Function NbrABS(xMat, DebSit, FinSit, tabloAbs) As Long
' xMat     -> c'est le numéro matricule
' DebSit   -> c'est le début de situation
' FinSit   -> c'est la fin de situation
' tabloAbs -> c'est le tableau des absences
Dim t, i&, k&
  t = tabloAbs
  For i = 1 To UBound(t)
    If t(i, 1) = xMat Then
      For k = DebSit To FinSit
        If k >= t(i, 2) And k <= t(i, 3) Then NbrABS = NbrABS + 1
      Next k
    End If
  Next i
End Function

Ou une autre écriture sans doute plus rapide :

Function NbrABS(xMat, DebSit, FinSit, tabloAbs) As Long
Dim t, i&, d1&, d2&
  t = tabloAbs
  For i = 1 To UBound(t)
    If t(i, 1) = xMat Then
      If DebSit >= t(i, 2) Then d1 = DebSit Else d1 = t(i, 2)
      If FinSit <= t(i, 3) Then d2 = FinSit Else d2 = t(i, 3)
      If d1 <= d2 Then NbrABS = NbrABS + d2 - d1 + 1
    End If
  Next i
End Function

 

[massepi]

Un grand merci mapomme !
J'essaie d'expliquer le principe pour ceux que ça intéresse :
- sur la 1ère macro : pour chaque jour d'une situation, on regarde si ce jour est compris dans l'intervalle d'abscence et on incrémente un compteur.
- sur la 2eme macro : on recrée des intervalles de date (d'absence) pour n'avoir que des intervalles de dates qui ne chevauchent pas les situations et ainsi additionner les valeurs de chaque intervalle.

Encore merci,
Bonne journée

 

[mapomme]

Re @massepi ,

Merci d'avoir fait la partie du boulot que d'emblée je ne fais plus.

Par principe, je ne commente pas. Mais si le questionneur (ou tout autre le demande) alors je commente. Je fais du commentaire sur demande.