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

djidji59430

XLDnaute Barbatruc

• 10 Août 2022

• #2

Bonjour à tous,

Une proposition :
Et a quoi ça te sert ?

 

Pièces jointes

• MIN MAX.xlsx

  16.2 KB · Affichages: 11

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 10 Août 2022

• #3

Bonjour bb78, djidji59430,

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

VB:

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 :

Code:

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

Formule en I2 à tirer vers le bas :

Code:

=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+

 

Pièces jointes

• MIN MAX(1).xlsm

  17.6 KB · Affichages: 4

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 10 Août 2022

• #4

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

Il faut une formule matricielle en I2 :

Code:

=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.

 

Pièces jointes

• MIN MAX(2).xlsx

  11.9 KB · Affichages: 4

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 11 Août 2022

• #5

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

Voyez ce fichier (3) avec en H2 :

Code:

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

Et en I2 la formule matricielle :

Code:

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

Bonne nuit.

 

Pièces jointes

• MIN MAX(3).xlsx

  11.8 KB · Affichages: 4

Vote positif 0 Vote négatif

B

bb78

XLDnaute Nouveau

• 11 Août 2022

• #6

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

 

Pièces jointes

• MIN MAX V2.xlsx

  13.3 KB · Affichages: 10

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 11 Août 2022

• #7

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+

 

Vote positif 0 Vote négatif

djidji59430

XLDnaute Barbatruc

• 11 Août 2022

• #8

et ma solution ??

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 11 Août 2022

• #9

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

VB:

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.

 

Pièces jointes

• Absences(1).xlsm

  20.7 KB · Affichages: 4

Dernière édition: 11 Août 2022

Vote positif 0 Vote négatif

Solution

job75

XLDnaute Barbatruc

• 11 Août 2022

• #10

djidji59430 à dit:

et ma solution ??

Cliquez pour agrandir...

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.

 

Vote positif 0 Vote négatif

B

bb78

XLDnaute Nouveau

• 12 Août 2022

• #11

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à.

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 12 Août 2022

• #12

Bonjour bb78, le forum,

bb78 à dit:

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

Cliquez pour agrandir...

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

VB:

   '---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+

 

Pièces jointes

• Absences(2).xlsm

  20 KB · Affichages: 5

Vote positif 0 Vote négatif

chris

XLDnaute Barbatruc

• 12 Août 2022

• #13

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

 

Pièces jointes

• Absences_PQ.xlsx

  23.7 KB · Affichages: 9

Vote positif 0 Vote négatif

djidji59430

XLDnaute Barbatruc

• 12 Août 2022

• #14

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 !

 

Vote positif 0 Vote négatif

chris

XLDnaute Barbatruc

• 17 Août 2022

• #15

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

 

Vote positif 0 Vote négatif