Discussion :

[Phanloga]

Bonjour à tous.

Mes recherches ici ou ailleurs n'ayant rien donné de concluant, je vous pose ma question.

Soit une période donnée à cheval sur plusieurs mois.
Je souhaite savoir combien de jours calendaires contient chaque mois et ventiler le résultat dans une colonne par mois.

Merci d'avance.

Cordialement.

[Philippe Tulliez]

Bonjour,
Ne pourrais-tu pas être plus explicite.

Soit une période donnée à cheval sur plusieurs mois.

Une cellule contient une date de début, une autre date de fin ?

Je souhaite savoir combien de jours calendaires contient chaque mois et ventiler le résultat dans une colonne par mois.

Une date dans une cellule et tu veux connaître le nombre de jours qu'il y a dans le mois qui correspond à cette date ?

[Phanloga]

Bonjour.

Oui. J'ai une colonne de dates de début et une de dates de fin.
Je souhaite connaitre en jours le poids de chaque mois inclus dans la période.

Par exemple, pour la période du 15 avril au 20 juin, j'aimerais récupérer automatiquement le nombre de jour d'avril dans une colonne avril, le nombre de jours de mai dans une colonne mai et même chose pour juin.

Certaines des périodes de ma listes s'étendent jusqu'à 6 mois.

Merci !

[Philippe Tulliez]

Bonjour,
Tu distilles tes informations au compte gouttes ?
Si tu expliquais comment est organisé ton tableau.
Dans quelles colonnes sont les dates de début et fin.
Tu parles d'une répartition sur 6 mois mais j'imagine que sur 2 lignes tu peux avoir
Début............Fin
10/02/2012 - 15/04/2012
05/04/2012 - 25/10/2012
Et là nous sommes déjà confronté à plus de 8 colonnes de répartition et il ne s'agit que de 2 lignes.
Y a t'il 6, 12, 24 colonnes de répartitions ?
Les colonnes sont elles dynamiques par rapport à la date du jour etc...?

[Daranc]

le plus simple ( d'après ce que j'en ai compris )

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Function cpt_jou_mois(Deb As Date, fin As Date, mois As Byte)
For t = Deb To fin
If Month(t) = mois Then
jour = jour + 1
End If
Next
cpt_jou_mois = jour
End Function

et dans la feuille

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=cpt_jou_mois($C7;$E7;H$6)

les colonne C et E correspondant respectivement au debut et à la fin des periodes
la ligne H6 à h 18 donnant les numéros des mois
la formule se colle en H7 et se tire à droite sur les douze mois et vers le bas sur la longueur des colonnes à traitées

[Philippe Tulliez]

Bonjour Daranc,

le plus simple ( d'après ce que j'en ai compris )

C'est ce que j'avais cru décoder également mais comme il me semble que la question porte sur l'utilisation d'une ou plusieurs fonctions natives d'Excel et pas d'une fonction personnalisée. Il me semblait intéressant de connaître le nombre de colonnes 6, 12 ou 24 pour la répartition mensuelle.
Parce-que si la date de début est le 15/01/2012 et la date de fin le 28/02/2013, le nombre de jours en janvier sera de 48 jours et février 57 jours.
D'où ma demande d'explication.

[Daranc]

45 jours en janvier ? les périodes ne sont que jusqu'à 6 mois, ce qui devrait limiter les risques . d'un autre coté se servir des fonction native d'excel
sans même voir le début d'une idée , j'imagine l'usine à gaz et le nombre de colonnes intermédiaires ( à moins qu'une telle fonction existe en natif dans 2007 ou 2010 là ce serait cool)

[Phanloga]

Bonjour et merci pour votre intérêt à mon problème.
C'est vrai que ce qui me semble évident ne l'est pas pour vous qui ne voyez pas le tableau.
Surtout que j'ai parlé de période sur 6 mois au maximum, mais la période globale concernée est sur un peu plus de 2 ans.
Désolé pour l'imprécision.
Je veux donc, pour chaque mois de la période, connaitre le nombre de jours d'absence pour chaque ligne.
Voici donc une copie de la feuille concernée. Les colonnes C et D sont vides pour préserver la confidentialité de ma feuille, mais elles existent.

[Benoit Schwob]

Bonjour,

Ce que vous cherchez à faire, ce serait pas dans le classeur ci-dessous ?
(Sans VBA, juste avec des tests logiques et des calculs sur des dates.)

Phanloga, dites-moi si c'est ça. Je détaillerai le raisonnement.

Benoît

[Phanloga]

Bonjour Benoit.

C'est exactement le résultat que je souhaitais.
L'utilisation me semble évidente.
Si j'ai bien compris, il suffit que j'indique le 1er de chaque mois dans la ligne 2 et que je recopie les formules vers le bas devant les bornes de mes périodes.

J'essaie ça immédiatement.

Un grand merci à vous, à Duranc et Corona, ainsi qu'à tous ceux qui permettent aux béotiens comme moi d'utiliser au mieux ces outils.

Bravo à ce forum.

[Benoit Schwob]

Disséquons cette formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(OU($B3<C$2;$A3>D$2-1);0;MIN($B3;D$2-1)-MAX($A3;C$2)+1)

- J'ai les dates de début dans la colonne A, les dates de fin dans la colonne B.
- Dans la colonne C et les suivantes, la ligne 2 porte des dates de début de mois (01/01/12, 01/02/12, 01/03/12, etc).
Avec un format qui ne fait apparaître que le mois et l'année.
- L'idée est de se servir de ces dates-étiquettes comme bornes de chaque période de 1 mois sur laquelle on veut travailler, l'une après l'autre.

Le raisonnement est le suivant :
SI date de début < étiquette de la colonne OU date de fin > étiquette de la colonne suivante,
ALORS le nombre de jour est donné par
minimum parmi date de fin et étiquette de la colonne suivante
- maximum parmi date de début et étiquette de la colonne
+1

Voilà !

(Je trouve que la question se visualise bien. Au besoin, prenez un papier et un crayon, tracez une ligne de temps, isolez une période de 1 mois, et déclinez tous les cas de figure...)

Benoît

[Philippe Tulliez]

Bonsoir,
Et bien voilà quand je disais que l'on pouvait utiliser les fonctions natives.
Juste pour le plaisir intellectuel, une façon d'éviter le SI et le OU

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=($B3>=F$2)*($A3<=G$2-1)*(MIN($B3;G$2-1)-MAX($A3;F$2)+1)

[Philippe Tulliez]

Bonjour,
J'avais pensé plutôt à utiliser la fonction NB.JOURS.OUVRES parce-qu'il me semblait que les jours d'absences ne sont comptabilisés qu'en fonction de jours ouvrés.
Si c'est le cas, voici une autre proposition. jFerie représente la plage nommée des jours fériés

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=($B3>=C$2)*($A3<=D$2-1)*NB.JOURS.OUVRES(MAX($A3;C$2);MIN($B3;DATE(ANNEE(C$2);MOIS(C$2)+1;0));jFerie)

Petite explication sur la valeur 0 du 3ème argument de la fonction DATE : =DATE(2012;5;0) donne la date du dernier jour d'avril 2012

La fonction FIN.MOIS aurait pu faire l'affaire aussi

[Phanloga]

Bonjour.
Super d'avoir encore approfondi le raisonnement.

Pour le cas qui m'intéresse, les jours calendaires sont la base du calcul.
Mais l'évo2 est intéressante.

Encore merci.

[Phanloga]

Bonjour.

Je ré-ouvre ce fil, car il me vient une contrainte inattendue.

Je dois déduire les dimanches. Seulement les dimanches !
Ce qui m'interdit l'usage de NB.JOURS.OUVRES().

Y a-t-il une solution à ce problème...?

Merci d'avance.

[Daranc]

il y a ce truc donné par microsoft

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
=date_fin-date_départ-ENT((date_fin-date_départ
-SI(JOURSEM(date_départ)=1;0;8-JOURSEM(date_départ)))/7)
-SOMMEPROD((jours_fériés>=date_départ)*(jours_fériés<=date_fin)*
(JOURSEM(jours_fériés)<>1))

pas tester mais ça donne ( en théorie) le nombre de jours ouvrables lesquels sont les tous les jours sauf le dimanche et non fériés ( ce qui sers au décompte des jours de congé)

[papouclo]

Bonjour,
Si je ne me suis pas trompé, la formule ci-dessous donne le nombre de dimanches compris entre deux dates (incluses) contenues dans des cellules notées datedébut et datefin.
Code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ENT((datefin-SI(JOURSEM(datedébut)=1;B1;7*(ENT(datedébut /7)+(MOD(datedébut;7)<>0))+1))/7)+1

La partie

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SI(JOURSEM(datedébut)=1;B1;7*(ENT(datedébut/7)+(MOD (datedébut;7)<>0))+1))/7)

détermine le premier dimanche après datedébut.
En notant dim ce premier dimanche, la différence datefin - dim donne le nombre de jours compris entre ce premier dimanche et la date de fin, ces jours commençant un lundi.
Calculer le nombre de dimanches contenus dans ce nombre de jours revient à chercher combien il y a de semaines : d'où la formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ENT((datefin - dim)/7)

nombre auquel il faut rajouter 1 pour tenir compte du dimanche dim non pris en compte dans le nombre de jours de la différence.
Cordialement

[papouclo]

Bonsoir,
Toutes mes excuses !
En relisant mon message précédent, je m'aperçois que j'ai fait une erreur non dans la formule complète (je pense qu'elle est bonne) mais dans l'analyse.
Le 1er dimanche après datedébut est donné non pas par ce que j'avais écrit mais simplement par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SI(JOURSEM(datedébut)=1;B1;7*(ENT(datedébut/7)+(MOD (datedébut;7)<>0))+1)

Le reste de l'analyse me semble correct.
Cordialement

[Phanloga]

Bonjour.
Désolé de ne pas avoir répondu plus vite à vos propositions, par manque de disponibilité.
Je vais essayer d'intégrer ça et je vous donnerai un retour ASAP.
Cordialement.

[Phanloga]

Au final, j'ai fait un mix.

J'ai repris la formule initiale à laquelle j'ajoute la moitié de la différence entre la durée totale et la durée jour ouvrés. Comme ça, je récupère mes samedis.
C'est certainement lourdingue, mais comme disent les cousins québécois : "Ca fait la job !".

=($B3>=C$2)*($A3<=D$2-1)*NB.JOURS.OUVRES(MAX($A3;C$2);MIN($B3;DATE(ANNEE(C$2);MOIS(C$2)+1;0)); )+((SI(OU($B3<C$2;$A3>D$2-1);0;MIN($B3;D$2-1)-MAX($A3;C$2)+1)-($B3>=C$2)*($A3<=D$2-1)*NB.JOURS.OUVRES(MAX($A3;C$2);MIN($B3;DATE(ANNEE(C$2);MOIS(C$2)+1;0)); ))/2)

Il me reste à lister mes jours fériés et ajouter ce paramètre à ma formule.

Encore un grand merci à tous.

Bien amicalement.