XL 2019 Datedif avec critère

[rocknroll2]

Bonjour à tous,
je souhaite connaître le nombre de jours entre des dates en fonction d'un critère.

Colonne a : des dates non consécutives.
Colonne b : des critères.
Les critères peuvent se retrouver plusieurs fois.
Chaque critère est au regard de 2 dates, l'un de début l'autre de fin.
Exemple :
Critère 3 en face de 01/01/19 et de 15/01//19 ==> 15 jours
mais aussi :
Critère 3 en face de 23/01/19 et de 30/01//19 ==> 8 jours
Soit total critère 3 = 23 jours.

Impossible de m'en sortir !

Merci à tous

 

[zebanx]

Bonjour RockNroll2

Un UDF à disposition mais qui ne fonctionne que si les colonnes début / fin sont différentes.
Si votre fichier reste en l'état, il ne vous servira à rien

A vous lire

 

[rocknroll2]

Merci pour cette réponse, mais il ne m'est pas possible de modifier le fichier. En effet, en face de chaque date, j'ai un commentaire associé. Il n'est donc pas possible de créer une colonne supplémentaire et d'avoir une colonne "début" et une colonne "fin".

D'autre idées peut-être ????

 

[Jocelyn]

Bonjour le Forum,
Bonjour rocknroll2, l'ami zebanx ,

Un essai en fichier joint merci pour une prochaine demande de poster un fichier exemple plutot qu'une image (pas facile de tester sur une image)

Cordialement

 

[rocknroll2]

Jocelyn bonjour,
A première vue, après quelques tests, cela me semble parfait
Seul bémol éventuel, cela ne fonctionne pas bien si il y a des lignes vides. Mais pour moi, aucun problème car il ne peux il avoir de lignes vide !

Donc un grand merci à toi.

PS : Je vais étudier ta formule car à première vue elle me semble obscure

Rocknroll2

 

[rocknroll2]

Oups, j'ai parlé trop vite...
Ci-joint fichier exemple avec en onglet "Indices" les données réelles...

 

[Hasco]

Bonjour,

Dans le fichier joint deux tentatives par powerquery puisqu'il est intégré dans xl 2019.
En feuille 'feuil1': ça fonctionne parce qu'il y a des nombres paires de dates à comparer par indice
En feuille 'indices 2019': ça ne fonctionne plus, chaque indice ayant un nombre impaire de dates.
indice 4 : 29 lignes
indice 3: 5 + 2 lignes
indice 1: 3 lignes
indice 0: 3 lignes.
Le principe (comme pour les solutions précédentes) étant de faire les opérations par paire de dates pour un indice (un début et une fin).
Sur cette dernière feuille, il y a également des heures -> les jours et heures sont totalisées pour donner un nombre décimal.
Si on les convertit en entier par power query, ils sont arrondit au jour supérieur.
J'ai donc ajouté une colonne 'Entier' avec formule =Ent(-----) au tableau de résultat.

Votre problème va donc consisté à définir une règle à appliquer pour déterminer des périodes à calculer.

 

[rocknroll2]

Roblochon bonjour,
Je comprends le(s) souci(s)...
Effectivement, dans l'exemple, le nombre de lignes est pair ou impair.
Mais il n'y a aucun problème cependant pour qu'il n'y ait qu'un seul indice de début et un seul indice de fin avec des lignes vides au milieu.
Pour les heures dans les formats… aucun souci pour traiter avec un ent() à la sortie. Il me suffit de savoir qu'on arrondi au supérieur (ou inférieur!)

Pour PowerQuery, ça m'ennuie un petit peu, le fichier pouvant être manié par d'autres personnes ne connaissant pas trop excel… C'est un peu pour ça que je pensais passer par une formule. Sinon, j'aurais essayé de passer aussi par une fonction...
Donc là, toujours coincé.

 

[Hasco]

Bonjour,

Je ne comprends pas : "Mais il n'y a aucun problème cependant pour qu'il n'y ait qu'un seul indice de début et un seul indice de fin avec des lignes vides au milieu."

Ce que vous cherchez est un nombre de jours en fonction de dates. Il faut nécessairement une date de début et une date de fin.

Nous avons trois dates pour indice 0

dim 02/06/2019 0:00
lun 03/06/2019 0:00
ven 14/06/2019 8:30

Faut-il compter les jours et surtout comment le dire à excel:
1 - du dim 2 au lun 3 + ven 14
2 - du dim 2 au lun 3 + du lun 3 au ven 14
3 - du dim 2 au vend 14
4 - du lun 3 au ven 14 + dim 2
etc..

Sinon l'avantage avec power query est que si les données sont bien structurées et formées, le reste reste transparent à l'utilisateur qui n'a plus qu'à cliquer sur le bouton actualiser.

 

[rocknroll2]

Oups, je précise...
Normalement, il y a AU MINIMUM une date de début et une date de fin pour chaque indice.
Entre ces 2 dates, l'indice est obligatoirement le même, même si une date existe. Donc effectivement, on peux avoir un nombre de dates paires ou impaires, mais l'indice est inchangé.
Les exemples que j'ai pris sont peut-être donc pas trop justes.
Exemple réel:
01/01/2019 indice 4
05/01/2019 indice 4
12/01/2019 indice 4
Ce qui est important, c'est le décompte jour entre le 01/01/19 et le 12/01/19. La date du 05/01/19 n'est présente que parce qu'il existe un commentaire spécifique lié à cette date (et ça, on s'en tape LOL)
Nous aurons donc ici : 12 jours pour l'indice 4...
et si par hasard, au mois de février, nous avons :
01/02/2019 indice 4
05/02/2019 indice 4
08/02/2019 indice 4
12/02/2019 indice 4

Au total, nous aurons 24 jours d'indice 4 et on se fiche du 05/01 ET du 05/02/ ET du 08/02...… !!!!

Hahaha, j'ai bien fait de demander de l'aide, je pensais m'en sortir seul, mais visiblement, ce n'est pas si simple LOL

Rocknroll

 

[Jocelyn]

re,

un nouvel essai en feuille 2

Cordialement

 

[Hasco]

Bonjour,
Hi @Jocelyn


Par formule matricielle à tirer vers le bas (date maxi - date mini +1 de chaque indice).

Bonne après-midi

 

[rocknroll2]

Merci Roblochon, mais la formule matricielle ne fonctionne pas trop bien (vérifie par exemple avec l'indice 3)

 

[rocknroll2]

Jocelyn,
a priori, ta solution fonctionnerait...
ci-joint fichier avec, sous ta solution, un contrôle manuel des nombres de jours de chaque indice.

Per contre, quelque chose me chiffonne dans ta formule...
$B$1:$B$42=E2 et $B$2:$B$43=E2 dans la même formule (décalage d'une ligne : 1 à 2 et 42 à 43)

Est-ce normal ou faut-il corriger et mettre partout 1 (ou 2) et 42 (ou 43) ?

 

[Hasco]

Re,

Merci Roblochon, mais la formule matricielle ne fonctionne pas trop bien (vérifie par exemple avec l'indice 3)

J'ai mal du interprété vos dernières explications. Mais si je relis bien, je ne vois pas autre chose qu'un comptage de jours entre une date maxi et une date mini et de ce point de vu là le calcul pour indice 3 est juste. du 14/06 (date max 3) au 25/12 (date min 3) il y a 171 jours.

Cordialement