Microsoft 365 Trouver date début et fin de période sans interruption

[bb78]

Bonjour

J'ai vraiment besoin de votre aide concernant la date de début de période par rapport à une date.

J'ai par exemple 3 périodes d'arrêts de travail pour le même salarié
P1 : du 01/01 au 15/01

P2: du 01/03 au 31/03
du 01/04 au 30/04
du 01/05 au 31/05
du 01/06 au 30/06
du 01/07 au 31/07

P3 : du 15/08 au 31/08
du 01/09 au 15/09

lorsque je vais rechercher la date MIN, il va me trouver le 01/01 pour les 3 périodes et la date MAX le 15/09, ce qui me fait 258 jours d'arrêt sur l'année au lieu de 200 jours
Il faut que je trouve pour chaque période le MIN et le MAX soit pour P1 : le 01/01-15/01, pour P2 : le 01/03-31/05 et pour P3 : le 15/08-15/09

J'ai essayé avec MIN, MAX, GRANDE.VALEUR, PETITE.VALEUR mais je ne trouve pas de solution donc je bidouille avec la ligne au dessus ou en dessous mais le fichier doit être alimenté tous les mois donc si j'oublie de faire le tri, ca fausse les données.

Merci par avance pour votre aide précieuse

 

[djidji59430]

Bonjour à tous,

Une proposition :
Et a quoi ça te sert ?

 

[job75]

Bonjour bb78, djidji59430,

Voyez le fichier .xlsm joint et cette fonction VBA qui renvoie la 1ère cellule vide sous une cellule :

Function CelluleVide(c As Range) As Range
Set CelluleVide = c.EntireColumn.Find("", c, xlValues)
End Function

Le code doit être placé impérativement dans un module standard.

Formule En H2 à tirer vers le bas :

=SI(K2="début";D2;SI(K2="prolongation";DECALER(H2;-1;);""))

Formule en I2 à tirer vers le bas :

=SI(K2="début";MAX(E2:CelluleVide(E2));SI(K2="prolongation";DECALER(I2;-1;);""))

Bien entendu il faut s'être assuré que les dates en colonnes D et E sont de vraies dates (nombres).

Ce n'était pas le cas, j'ai dû revalider certaines cellules pour les convertir.

A+

 

[job75]

Cela dit on peut se passer de la fonction VBA, voyez ce fichier (2) .xlsx.

Il faut une formule matricielle en I2 :

=SI(K2="début";MAX(DECALER(E2;;;EQUIV(VRAI;ESTVIDE(DECALER(E2;;;100));0)));SI(K2="prolongation";DECALER(I2;-1;);""))

à valider par Ctrl+Maj+Entrée.

Nota : pour la recherche la plage à une dimension de 100 en hauteur, c'est plus que suffisant.

 

[job75]

En fait la colonne K avec "début" et "prolongation" était inutile.

Voyez ce fichier (3) avec en H2 :

=SI(D2="";"";SI(N(DECALER(D2;-1;));DECALER(H2;-1;);D2))

Et en I2 la formule matricielle :

=SI(E2="";"";SI(N(DECALER(E2;-1;));DECALER(I2;-1;);MAX(DECALER(E2;;;EQUIV(VRAI;ESTVIDE(DECALER(E2;;;100));0)))))

Bonne nuit.

 

[bb78]

Bonjour job75 et djidji59430,

merci pour vos réponses ! par contre cela ne marche pas lorsque j'enlève les lignes vides que je n'avais mis que pour rendre mon exemple plus clair.

Mon fichier fait environ 20 000 lignes car je dois extraire les absences de tous les salariés sur l'année afin de faire mes stats et notamment savoir le nombre de période d'arrêt par salarié.
Dans ce fichier, les dates ne sont pas triées par ordre, ni les matricules.
Je vous joins un fichier avec un exemple plus concret.

Merci pour votre aide

 

[job75]

Bonjour bb78,

Un fichier joint doit être représentatif du fichier réel.

Les solutions que j'ai données nécessitent des cellules vides pour séparer et distinguer les plages.

A+

 

[djidji59430]

et ma solution ??

 

[job75]

Avec le nouveau tableau la solution la plus simple passe par une macro :

Sub Calcul()
Dim c As Range, tablo, deb&, i&, j&
Application.ScreenUpdating = False
With [A1].CurrentRegion
    '---convertit les textes en dates- en colonnes D et E---
    On Error Resume Next
    For Each c In .Columns(4).Resize(, 2).SpecialCells(xlCellTypeConstants, 2) '
        c = CDate(c)
    Next c
    On Error GoTo 0
    '---tri sur les matricules et les dates---
    .Sort .Columns(1), xlAscending, .Columns(4), , xlAscending, .Columns(5), xlAscending, Header:=xlYes
    '---analyse du tableau---
    tablo = .Resize(.Rows.Count + 1, 10) 'matrice, plus rapide, 1 ligne de plus
    deb = 2
    For i = 3 To UBound(tablo)
        If tablo(i, 1) <> tablo(i - 1, 1) Or tablo(i, 4) <> tablo(i - 1, 5) + 1 Then
            For j = deb To i - 1
                tablo(j, 8) = tablo(deb, 4) 'pour la colonne H
                tablo(j, 9) = tablo(i - 1, 5) 'pour la colonne I
            Next j
            deb = i
        End If
    Next i
    '---restitution---
    .Resize(, 10) = tablo
End With
End Sub

La macro trie le tableau sur les matricules et les dates.

Si vous voulez rétablir l'ordre initial c'est facile, dites-le.

Edit : la dernière ligne n'était pas traitée, j'ai corrigé la macro.

 

[job75]

et ma solution ??

Ton fichier du post #2 ne va pas.

Sur toute la plage I4:I8 il faut obtenir la date 31/07/2022.

Sur toute la plage I10:I11 il faut obtenir la date 15/09/2022.

 

[bb78]

Bonjour job75
Un grand merci pour ton aide et désolée pour le fichier de départ, est ce que j'ai le droit à une dernière question ?
J'ai bien réussi à adapter la macro à mon fichier c'est nickel mais lors de la restitution, il supprime les formules de toutes les colonnes adjacentes. J'ai bien pensé à déplacer les colonnes mais je travaille sur les dates recalculées donc j'ai besoin de garder au moins ces colonnes là.

 

[job75]

Bonjour bb78, le forum,

lors de la restitution, il supprime les formules de toutes les colonnes adjacentes.

Alors il suffit de restituer uniquement sur les colonnes H et I, fichier (2) :

   '---analyse du tableau---
    tablo = .Resize(.Rows.Count + 1, 10).Value2 'matrice, plus rapide, 1 ligne de plus
    '-----
    '---restitution---
    .Columns(8) = Application.Index(tablo, , 8)
    .Columns(9) = Application.Index(tablo, , 9)

A+

 

[chris]

Bonjour

Une solution PowerQuery (intégré à Excel)

Il y a une erreur dans le fichier du #1 en I11:J12 sur l'enchaînement des 2 périodes
Il suffit d'actualiser par clic droit dans la résultat ou Données, Actualiser tout

A noter que PowerQuery convertit simplement les pseudos dates en dates lors du typage

 

[djidji59430]

Job 75
ah .
je ne vois pas pourquoi, parce que les prolongations se font au jour le jour, les unes a la suite des autres, Donc on a une date de fin de prolongation a chaque fois. et ce qu'il veut (d'apres moi ..), c'est la date finale de l'arret.
D'un autre cote, je voudrais bien savoir a quoi ça peut servir, surtout qu'au debut, il dit
", ce qui me fait 258 jours d'arrêt sur l'année au lieu de 200 jours"
je n'ai rien vu par rapport a ça dans le fichier


j'ai oubliér hier d'appuyer sur le bouton !

 

[chris]

Bonjour à tous
A noter qu'il s'agit d'un crosspost et pas de nouvelles de la demandeuse ici ou ailleurs