XL 2019 Formule permettant de calculer le nombre de jours sur la période de 12 mois précédents

[Thierry.dev]

Bonjour à tous,

Quelqu'un pourrait-il m'aider sur la formule Excel à mettre dans chaque ligne de la colonne E afin que chacune des lignes de la colonne E puisse calculer et afficher le nombre total de jours passé sur un site au cours des 12 derniers mois qui précèdent un jour (aléatoire) de contrôle ?

Une variante sur 12 mois glissants serait appréciée.

Un grand merci d'avance.

 

[Thierry.dev]

Bonjour,

J'avais préparé la solution power query ci-jointe que j'ai finalisée ce matin.
Mode de calcul des Jours :
Chaque date n'est comptée qu'une fois. par exemple les deux "01/01/2023" sur les deux dernières lignes ne seront comptés qu'une fois.
Pour chaque ligne, le calcul des 12 mois précédents inclut la date de sortie de la ligne actuel, ce qui peut se discuter

Cordialement

{Edition 13:06]

[/Edit]

Bonjour Hasco,

(je reformule différemment mon post d'hier que j'ai supprimé pour ne pas polluer le fil de discutions)

Un grand merci pour ton aide et ton tableau que je trouve très pratique.

Je te prie de noter que dans mon fichier initial (feuille 3 onglet bleu), la ligne du doublon de date était une erreur de ma part. De plus j'ai remarqué que j'ai fait deux autres erreur dans le décomposition en colonne F de deux dates de sortie que j'ai corrigé dans le fichier v2 ci-joint.

Au sujet de ton tableau en PQ:
* Effectivement, les jours des dates d'entrée et de sortie doivent être incluses dans la somme des jours sur les 12 mois précédents.
* Je constate une discordance dans les résultats en E5,E6,E7, E11,E12,E13 par rapport aux résultats (qui sauf erreur de ma part me semble corrects) du tableau de notre amis JHA (feuille 1 onglet vert) ainsi que de mon tableau "bricolé by myself" en troisième feuille.
*Penses tu qu'il serait possible de faire en sorte que les dates de périodes d'absences du site et donc le nombre de jours correspondants (p ex pour maladie) serait déduit de la somme des jours sur les 12 mois. Les dates du premier et du dernier jour d'absences pourraient être soit être incérer manuellement dans une ligne de la période sur site concernée ou faire l'objet d'un lien vers un tableau séparé avec les dates d'absence.
*Quelle est la procédure pour continuer à introduire des dates après ta ligne 13 ?

D'avance un grand merci à toi ainsi qu'à JHA.

Agréable journée à tous !

 

[Hasco]

Bonjour,

Vous voulez compter les nombres de jours d'absence dans un tableau destiner à compter les nombres de jour de présence !

De par sa nature une période entre deux périodes de présence est une période d'absence

Si les jours d'absence sont au milieu d'une période de présence c'est qu'il y a quelque chose qui ne va pas !
Il faut alors séparer la période présence en deux périodes.

Si vous voulez différencier les périodes il faut un tableau avec une colonne qui catégorise les périodes (Présence, maladie, congé, formation, RTT etc)

Quelle est la procédure pour continuer à introduire des dates après ta ligne 13 ?

se positionner sur la dernière cellule de la dernière ligne du tableau bleu, appuyer une fois sur la touche tabulation, une nouvelle ligne sous le tableau se crée.
Une fois la (les) nouvelle(s) ligne(s) renseignée(s), click-droit sur le tableau vert -> Actualiser, les nouvelles lignes calculées par la requête s'ajouteront automatiquement.

Peut-être serait-il temps pour vous d'apprendre à utiliser les tableaux structurés :

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

 

[Thierry.dev]

Bonjour,

Vous voulez compter les nombres de jours d'absence dans un tableau destiner à compter les nombres de jour de présence !

De par sa nature une période entre deux périodes de présence est une période d'absence

Si les jours d'absence sont au milieu d'une période de présence c'est qu'il y a quelque chose qui ne va pas !
Il faut alors séparer la période présence en deux périodes.

Si vous voulez différencier les périodes il faut un tableau avec une colonne qui catégorise les périodes (Présence, maladie, congé, formation, RTT etc)

se positionner sur la dernière cellule de la dernière ligne du tableau bleu, appuyer une fois sur la touche tabulation, une nouvelle ligne sous le tableau se crée.
Une fois la (les) nouvelle(s) ligne(s) renseignée(s), click-droit sur le tableau vert -> Actualiser, les nouvelles lignes calculées par la requête s'ajouteront automatiquement.

Peut-être serait-il temps pour vous d'apprendre à utiliser les tableaux structurés :

Bonjour Hasco,

*Merci pour le lien sur les T.S. dans lequel j'ai commencé à m'y plonger et faire quelques exercices. C'est très bien fait.

* Avez-vous pu jeter un œil sur le fait que je constate une discordance dans les résultats en E5,E6,E7, E11,E12,E13 par rapport aux résultats (qui sauf erreur de ma part me semble corrects) du tableau de notre amis JHA (feuille 1 onglet vert) ainsi que de mon tableau "bricolé by myself" en troisième feuille.

*Pour les jours d'absences, toutes absences confondues, effectivement il faudrait ajouter deux ou trois colonne pour signifier | date début absence | date fin absence | nbre jours absence | ... étant comme vous le voyez "novice" je ne sais pas de quelle manière efficace ces données d'absences pourraient être incérées dans le tableau que vous avez fait.

Merci d'avance et agréable week-end.

 

[JHA]

Bonjour à tous,

Les dates ne sont pas identiques.

JHA

 

[Hasco]

Bonjour,

Hello @JHA

@Thierry.dev : plus ça va et plus on s'éloigne du motif initial de ce fil et de sa question.
Faites des recherches sur le forum sur la gestion de planning.

@JHA et moi-même avons largement répondu la la question telle que posée au post#1. Choisissez celle avec laquelle vous êtes le plus à l'aise et terminez cette conversation ou restez sur le sujet initial.

S'il y a un blocage sur la requête, j'y répondrai.

P.s. Je réponds de mon téléphone et n'ai pu vérifier les dates. Mais je pense que @JHA l'a fait.

 

[Hasco]

Re,

Pour vous permettre de comprendre et de vérifier, dans la V3 ci-jointe vous trouverez une nouvelle requête nommée "Jours sur site" qui liste la totalité des jours sur site (532) pour les dates données.
En colonne H une formule de calcul renvoyant la date de sortie -12 mois
En colonne I une formule comptant le nombre de dates sur site entre la date de sortie -12 mois et la date de sortie.

vous verrez qu'on trouve le même nombre qu'avec les requêtes des versions précédentes.

Cordialement

 

[Thierry.dev]

Bonjour XLD JHA et Hasco,

Je me permets de revenir sur le sujet dont nous m'avez proposé des formules pour résoudre mon problème de connaitre la somme des nombres de jours passés sur un site au cours des 365 jours précédent la dernière sortie du site.
J'aimerais avoir votre avis sur les formules A et formules B qui m'ont été proposées par chatGPT.
Je joint ici la feuille de calcul sur laquelle nous avons travaillé précédemnt et vous pouvez prendre connaissance du tableau dans l'onglet rouge.

Mon soucis est que chaque formule, les votres HASCO et JHA, ChatGPT et ma solution donnent des résultats différents.

Merci d'avance pour vos avis et agréable dimanche

 

[Cousinhub]

Bonjour,
Tout d'abord, les requêtes ne peuvent se mettre à jour, vu que tu as des lignes vides en bas de tes données...
Ensuite, sans vraiment analyser les formules de ton copain, les résultats sont visiblement faux... (de simples calculs mentaux suffisent, notamment pour l'année 2023...)
A toi de choisir, une IA comprenant uniquement ce qu'on lui dit (et donc l'exécutant fidèlement), et des calculs bien mieux élaborés par un gars en chair et en os...

 

[Thierry.dev]

Bonjour,
Tout d'abord, les requêtes ne peuvent se mettre à jour, vu que tu as des lignes vides en bas de tes données...
Ensuite, sans vraiment analyser les formules de ton copain, les résultats sont visiblement faux... (de simples calculs mentaux suffisent, notamment pour l'année 2023...)
A toi de choisir, une IA comprenant uniquement ce qu'on lui dit (et donc l'exécutant fidèlement), et des calculs bien mieux élaborés par un gars en chair et en os...

Bonsoir Cousinhub,

J'ai testé l'IA mais effectivement un cerveau humain est bien mieux
Toi, as-tu une idée pour faire la somme à chaque ligne du nombre total de jours passés sur le site au cours de 365 derniers jours précédent la date de sortie de la ligne concernée.

Bonne soirée

 

[Cousinhub]

Bonjour,
Faire ce total en 1 formule est peut-être possible, mais j'en suis bien incapable.
Par contre, la solution donnée par @Hasco fonctionne très bien...
Bonne journée

 

[Thierry.dev]

Bonjour,
Faire ce total en 1 formule est peut-être possible, mais j'en suis bien incapable.
Par contre, la solution donnée par @Hasco fonctionne très bien...
Bonne journée

 

[Thierry.dev]

Bonjour Cousinhub,
Effectivement la solution en P.Q d'Hasco fonctionne mais je voulais tenter une pure formule Excel afin d'éviter P.Q.
Agréable journée à vous.