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

bb78

XLDnaute Nouveau
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
 

Pièces jointes

  • MIN MAX.xlsx
    15.5 KB · Affichages: 12
Solution
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...

job75

XLDnaute Barbatruc
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

job75

XLDnaute Barbatruc
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

job75

XLDnaute Barbatruc
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

bb78

XLDnaute Nouveau
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

job75

XLDnaute Barbatruc
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:

bb78

XLDnaute Nouveau
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

XLDnaute Barbatruc
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) :
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

chris

XLDnaute Barbatruc
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

djidji59430

XLDnaute Barbatruc
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 !
 

Discussions similaires

Statistiques des forums

Discussions
313 204
Messages
2 096 206
Membres
106 529
dernier inscrit
ironmachine