Excel calcul heure automatique après filtre entre 2 dates

Bonjour,

Je suis entrain de faire un modèle excel afin de pouvoir voir le temps d'ouverture d'une session entre 2 dates données.

Sur le fichier ci-joint

Je peux actuellement faire une recherche par nom d'utilisateur dans la partie Description.

Je possède une macro permettant d'enlever les événements 22 (inutile de mon point de vue).

Je possède 2 cellules en C2 et D2, la cellule C2 est la plus petite date souhaité, la D2 la plus lointaine.

Le premier bouton sert a filtré uniquement la première date et le second uniquement la seconde date.

Un Troisième bouton me permet de filtrer entre les 2 dates.

Depuis ce troisième bouton les 2 autres ne fonctionnent plus, je n'en trouve pas la cause ... Car lorsque je regarde sur le filtre "contient 03/09/18" c'est bien la date recherché ...

Je souhaite aussi faire dans une des cellules en haut disons F1, un calcul des heures total entre les deux dates. Cela comprend uniquement la fermeture et l'ouverture de session ( donc calculer uniquement l'intervalle entre les 2 événements ) Les événements ont l'air lié c'est a dire C8 et C9 , C11 et C12 par exemple étant donné qu'on supprime l’événement 22.

Le but étant de calculer après l'appuie sur le bouton Filtrer intervalle, les horaires d'allumage de la session.

Je ne vois pas par ou commencer et encore moins comment le faire, je pensais tout d'abord a fusionner les colonnes Date et Time, mais je ne vois même pas comment le faire ... ( le fichier importé actuellement possède quelques lignes, les vrai fichier comporterons environ 40-50 000 lignes ...

Je vous remercie par avance de votre aide

Bonjour,

Voici ton fichier pour les filtres.

Pour la question des heures, je vais laisser ce décompte à un/une meilleur/e dans le domaine.

ric

Bonjour,

Merci a toi pour cette premiere reponse, je sais pas si c’est pareil pour toi mais lorsque je mets la date et que je cherche en fonction de cette date ( date 1 et date 2) ba il faut que sa soit en format mm/jj/aa en revanche pour celui avec intervalle ba il faut que les dates soient en format jj/mm/aa ... c’est un coup a prendre en soit meme si sa serait plus pratique d’avoir le meme format pour les 2 recherches

Merci a toi il ne reste plus que les heures a trouver

Bonjour,

Je n'avais pas remarqué, je regarde....

ric

Bonjour,

Ici, comme dans le fichier que je t'ai proposé, tout est en année-mois-jour (2018-09-05)

C'est le format par défaut de mon Windows.

ric

Et on choisit ca ou ?:o

Je suis debutant sur du Excel je pensais m’en tirer en formatant les cellules mais au final pas du tout

Merci a toi de prendre de ton temps pour m’aider

Bonjour,

C'est légèrement différent selon des versions de Windows.

Mais en gros, Panneau de configuration, Région (Win10) ou Région et Langues (Win7).

Ensuite, dans les propriétés des cellules contenant des dates, tu peux choisir le format d'affichage désiré.

La partie Excel : https://support.office.com/fr-fr/article/mettre-en-forme-des-nombres-sous-forme-de-dates-ou-d-heures-418bd3fe-0577-47c8-8caa-b4d30c528309

ric

Bonjour,

Désolé de ne pas avoir trouvé les infos sur le site de Microsoft ...

Format de date Windows 10

https://www.windowscentral.com/how-change-date-and-time-formats-windows-10

Format de date Windows 7

https://superuser.com/questions/23875/how-to-change-windows-7-date-format

ric

Bonjour,

Merci a toi pour cette premiere reponse, je sais pas si c’est pareil pour toi mais lorsque je mets la date et que je cherche en fonction de cette date ( date 1 et date 2) ba il faut que sa soit en format mm/jj/aa en revanche pour celui avec intervalle ba il faut que les dates soient en format jj/mm/aa ... c’est un coup a prendre en soit meme si sa serait plus pratique d’avoir le meme format pour les 2 recherches

Merci a toi il ne reste plus que les heures a trouver

Bonsoir, Salut Ric !

Tes formats de dates par défaut sont définis par les paramètres régionaux système, mais VBA ne les utilise que quand on l'y force (en convertissant...) et pour la mise en oeuvre de fonctionnalités telles le filtre auto. Il prend en compte les dates au format américain : m/d/yyyy.

Ta notation est donc juste pour les filtrages de dates (mais effectivement, le format yyyy-mm-dd sera également reconnu).

Ce qui coinçait dans tes procédures, c'est que le critère d'égalité à une valeur date ne fonctionne pas en Criteria1, et qu'il faut en passer par un tableau en Criteria2, composé de couple d'éléments : le second étant la date et le premier indiquant si le filtrage à effectué porte sur le jour précisément ou sur le mois, voire sur l'année. Cet élément est 2 pour filtrer le jour. Avec Operator à xlFilterValues.

Comme cela n'est pas documenté dans l'aide, on est réduit à utiliser l'enregistreur pour déduire la syntaxe (mais une fois qu'on a vu le fonctionnement, on peut l'écrire en plus court !). Exemple :

Sub Date_fin()
    Dim d$
    With ActiveSheet
        d = Format(.Cells(2, 4), "m/d/yyyy")
        .ListObjects(1).Range.AutoFilter 2, , xlFilterValues, Array(2, d)
    End With
End Sub

Par contre ne crois pas que pour ta procédure intervalle tu utilisais des dates au format dd/mm/yyyy !

Regarde bien ta procédure : tu utilises des variables, Sup et Inf (qu'il aurait été bien de déclarer ! ) et tu initialises tes variables par la valeur de tes cellules en utilisant la propriété Value2.

Cette propriété renvoie la valeur de la cellule, mais s'agissant d'une date elle la renvoie dépourvue de ses attributs de date ! C'est à dire qu'en fait elle renvoie le numéro de série : 43346 ou 43348. Tu utilises donc des nombres et non des textes-dates formatés !

Mais ces nombres sont reconnus par le filtre. En définissant tes critères dans la commande de filtre tu concatènes les valeurs des variables aux opérateurs ">=" et "<=", tu envoies donc à la commande les critères textuels qu'elle attend (toute concaténation d'un nombre avec un élément de type String produit une valeur de type String).

Je n'ai par contre pas compris ta question complémentaire : tu parles de calculer les horaires d'allumage de la session...

C'est un calcul tout à fait indépendant du filtre ! Pour calculer une durée entre deux évènements, tu additionnes les dates et heures de chacun des évènements (dans les cellules ce sont des nombres, l'addition donnera un nombre décimal dont la partie entière représente la date et la partie décimale l'heure), puis tu fais la soustraction entre les deux.

Si tu affectes le résultat à une cellule, il suffira de mettre le format de cellule : [h]:mm:ss pour avoir la durée exprimée en heures. (Pour l'afficher hors cellule, il faudra ruser car la fonction Format ne permet pas l'attribution d'un format horaire de durées supérieures à 24 h...)

La seule question à régler demeure la détection des valeurs (lignes) permettant le calcul d'une durée.

Or, si je suis tes indications et rapproche les lignes 8 (ouverture) et 9 (fermeture), je constate que la fermeture intervient avant l'ouverture. Là il y a un os, et ça va pas le faire !

En constatant aussi que la chronologie de ton tableau va du bas vers le haut, il est logique de penser que la session fermée en ligne 9 a été ouverte en ligne 11.

Il faut donc que tu précises exactement ce qu'il en est, et si la durée doit être calculée pour chaque session, elle devrait disposer d'une colonne et être calculée sur la ligne fermeture...

A partir de là on peut le faire par formule ou par VBA...

(Dans ton tableau, la même station est concernée mais est-ce toujours le cas ?)

Cordialement.

Bonjour,

Désolé du retard de ma réponse

Je prends en note tout ça, je n'ai pas accès à mon poste pour refaire des test. J'y serais à partir de lundi .

Enfaite pour l'horaire de fermeture de session j'aimerais si possible que en fonction de mon filtrage de date en intervalle, que dans la cellule choisis s'affiche le nombre d'heure d'ouverture des sessions ( en cumul ) par exemple prend la dernière ouverture de la session prendre la première fermeture trouvé ( donc en partant du bas si on suit la logique ) Calculer ceci. Faire de même ensuite avec la prochaine ouverture et fermeture de session et ainsi de suite. Au final affiché dans la cellule choisis la somme de tout ceci.

Faire un filtrage du 3-4 septembre et calculé uniquement ses heures là pour l'exemple.

Droken

Bonjour,

Il n'y a aucun besoin de filtrer, comme je l'ai dit, pour faire ce calcul. On se contente de tester à partir de la fourchette de date que tu indiques : les ouvertures doivent être supérieures ou égales à la première date, et les fermetures inférieures ou égales à la deuxième date. Les mêmes critères que tu utilises pour filtrer, mais qui peuvent s'utiliser indépendamment.

Re,

Tu peux le faire par formule, mais il te faut éliminer une éventuelle fermeture non précédée d'une ouverture dans la fourchette, et une éventuelle ouverture non suivie d'une fermeture dans la fourchette, donc adjoindre à la date en C2 l'heure de la première ouverture du jour, et à la date en D2 l'heure de la dernière fermeture du jour.

[Et aussi, cela quel que soit le mode de calcul choisi, convertir préalablement tes heures de la colonne C en valeurs horaires car tu as du texte ! ]

Si on calcule la date-heure minimale en G1 :

=MIN(SI((B7:B18=C2)*(D7:D18=21);C7:C18;""))+C2

Formule matricielle à valider par Ctrl+Maj+Entrée.

Va nous donner en résultat : 03/09/2018 12:17

La date-heure maximale en G2 :

=MAX(SI((B7:B18=D2)*(D7:D18=23);C7:C18;""))+D2

Formule matricielle à valider par Ctrl+Maj+Entrée.

Va nous donner en résultat : 05/09/2018 09:34

On peut placer en F1 la formule de calcul :

=SOMMEPROD((B7:B18+C7:C18<=G2)*(B7:B18+C7:C18>=G1)*(D7:D18=23)*(B7:B18+C7:C18))-SOMMEPROD((B7:B18+C7:C18>=G1)*(B7:B18+C7:C18<=G2)*(D7:D18=21)*(B7:B18+C7:C18))

SOMMEPROD calcule sur des matrices, mais a la faculté en général de se passer de validation matricielle.

Elle va nous fournir le résultat (en mettant la cellule au format : [h]:mm:ss) : 45:12:05.

Résultat que l'on peut vérifier en calculant par ailleurs les durées des 3 sessions comprises dans l'intervalle et en additionnant ces durées.

Cordialement.

edit : Je n'ai pas utilisé les références structurées permises par ton tableau Excel et qui feraient s'adapter les formules aux variations dudit tableau sans avoir à les ajuster, car la longueur du nom de ton tableau rend un peu trop compliqué de suivre le développement de la formule qu'on est en train d'écrire !

Bonjour M.Ferrand

Merci de tes réponses qui m'aident énormément, en faisant ce que tu m'as dit je me retrouve avec un problème, du moins 2.

( Je joindrais mon fichier dès que le PC qui le possède aura accès à internet ...)

1) J'ai du bidouiller quelque chose sans faire exprès, j'ai ajouter les heures aux dates en C2 et D2, j'ai passé la colonne Time en Heure, le filtrage de date1 et date2 fonctionne correctement, en revanche le filtrage pour l'intervalle ne fonctionne plus, "Supérieur ou égal à "43346512442196" et Inférieur ou égal à "433483988078704", le filtre ne marche plus

Sub Date_interval()

Sup = Range("c2").Value2
Inf = Range("d2").Value2
If Worksheets("Feuil1").AutoFilterMode Then
    Worksheets("Feuil1").AutoFilterMode = False
End If
ActiveSheet.ListObjects( _
"Tableau_Microsoft_Windos_TermnalServices_LocalSessionManager_4Operational_on_WORKSTATION2_2018_9_5_9_36_30_15" _
).Range.AutoFilter Field:=2, Criteria1:=">=" & Sup; Operator:=xlAnd, Criteria2:="<=" & Inf
End Sub

Je suppose qu'il faudrait forcer le formatage de Sup et de Inf ?

Après avoir relu l'un de tes postes, il faut effectivement formater ces variables pour qu'elle ne renvoies pas un nombre mais une date.

Je suppose qu'il existe une formule pour permettre a une variable d'avoir un format prédéfini comme dans le filtre Date_début et Date_fin.

2) Second problème après avoir fait les formules en G1 G2 et F1, le problème est que en F1 cela me renvoie la valeur 21:12:05 ce qui n'est pas du tout la bonne valeur

Bonjour,

Je regarde de plus près, mais :

1) tes heures sont toujours du texte en C, sauf les 2 premières sans doute converties lors des manipulations...

[Il y en a peu, tu peux donc éditer et re-valider chaque cellule, cela les convertira. Pour un grand nombre, taper 1 dans une cellule extérieure, copier cette cellule 1, sélectionnner la colonne heures et collage special Multiplication.]

2) ne rien changer en C2 et D2, mettre des dates seulement (pas des dates et heures...

3) en F1, mettre format de cellule [h]:mm:ss pour afficher des heures superieures à 24h (le h doit être entre crochets)

4) la formule en F1, utilisant SOMMEPROD n'a pas besoin de validation matricielle.

Ton fichier. J'en ai profité pour nettoyer le code. Le calcul est indépendant du filtre.

Cordialement.

Merci à toi, joli ménage

J'en ai fais un modèle car le fichier sera utilisé assez souvent.

Je rencontre quelque soucis lorsque je le lance sans Tableau.

1) Je dois convertir les colonne Date et Time ( bon sa prend quelque clics c'est pas trop dérangeant )

2) Il faut absolument que la première fois que je veux utiliser le système de calcul d'heure que je mette une date ET une heure dans les cellules C2 et D2 après cela fonctionne niquel.

3) Obligation de supprimer le premier Fermeture de session et le dernier Ouverture de session.

4) Je me demande si pour faire le calcul des heures sur des milliers de cellules il suffit juste de remplacé B18 et C18 par B65000 et C65000 ?

Merci énormément à toi j’espère ne pas trop abuser de ta gentillesse ...

Droken

Edit: Après vérification pour le calcul de la date il faut obligatoirement mettre l'heure en cellule D2

Edit 2:

5) Ce n'est pas un problème mais plutôt une demande, en cellule A2 je fais une recherche dans la colonne Description du tableau une recherche par mot ( ici un utilisateur )

Je viens de me souvenir que le calcul des heures doit être fait en fonction de l'utilisateur choisis ainsi que l'intervalle des dates, c'est pour cela que je demandais en fonction d'un filtrage, j'avais oublié totalement que le filtrage doit etre fait avec l'utilisateur aussi, ainsi que le calcul des heures....

Je ne te suis pas sur tes considérations de date ET heure.

En C2 et D2 date seulement, elle sert pour les filtres. En G1 et G2 calcul de la date-heure en fonction des évènements à retenir pour ajuster les bornes du calcul, elle sert à la formule de calcul, qui fonctionne en dehors du filtre.

Si la formule est écrite en références structurées elle pourra s'appliquer toujours au tableau, mais avec un nom de tableau qui fait déjà 111 caractères, qui sera répété 14 fois dans la formule... je sois dire que j'ai renoncé à l'écrire sous cette forme.

Cordialement.

Le nom du Tableau peut être changé si je ne dis pas de bêtise je l'ai renommé en Tableau_logs sur le fichier qui sera joint en fin de message.

Je viens d'éditer mon message au-dessus pour éviter des doubles Post pensant que tu n'avais pas encore vu...

Bon double post

J'ai trouvé une partie de la solution que je souhaitais en creusant dans ma tête, Je fais un copier coller de toute les lignes qui contiennent dans la colonne I le mot contenu en A2 dans une autre feuille ( la feuil2) et je mettrais en place les formules Comme dans la feuille 1 ( pour éviter d'avoir a réfléchir )

Le soucis étant que dans ma macro je fais un clear, à cause du clear ba les formules disparaissent aussi

Il y a t'il une solution pour seulement clear de la cellule A3 à I(le plus loin possible)

Sub test()
Dim Nblg As Long

  Application.ScreenUpdating = False
  Nblg = Range("I" & Rows.Count).End(xlUp).Row
  With ActiveSheet
  .ListObjects(1).Range.AutoFilter 9, "=*" & Cells(2, 1) & "*"
  If Application.Subtotal(103, Range("I6:I" & Nblg)) > 0 Then
    With Sheets("Feuil2")
    .Cells.Clear
      Range("A7:I" & Nblg).SpecialCells(xlCellTypeVisible).Copy .Range("A4")

    End With
    ActiveSheet.AutoFilterMode = False
  End If
End With
End Sub

Cordialement

Droken

Bonjour,

Essaie ceci :

Sub AfficherTout()
    With ActiveSheet.ListObjects(1)
        If .ShowAutoFilter Then .AutoFilter.ShowAllData
    End With
End Sub

Sub test()
Dim rng As Range

    Application.ScreenUpdating = False

    With ActiveSheet.ListObjects(1)
        .Range.AutoFilter 9, "=*" & ActiveSheet.Cells(2, 1) & "*"
        With .AutoFilter.Range
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 9) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        End With
    End With

    If Not rng Is Nothing Then
        With Worksheets("Feuil2")
            .Cells(4, 1).CurrentRegion.ClearContents
            rng.Copy .Cells(4, 1)
        End With
    End If

    ActiveSheet.ListObjects(1).Range.AutoFilter 9

End Sub
Rechercher des sujets similaires à "calcul heure automatique filtre entre dates"