Excel - calcul différent pour les 30 prem.jrs
Résolu
Weiyin
Messages postés
21
Date d'inscription
Statut
Membre
Dernière intervention
-
Le Pingou Messages postés 12249 Date d'inscription Statut Contributeur Dernière intervention -
Le Pingou Messages postés 12249 Date d'inscription Statut Contributeur Dernière intervention -
Bonjour,
Je suis à réaliser un tableau qui me permettra de facturer mensuellement un séjour en hébergement avec des taux différent soit pour les 30 premiers jours exclusivement, puis pour les mois subséquents.
J'ai donc créé les colonnes suivantes :
ma cellule A1 étant la date de fin de mois en cours
Col. A = Date d’arrivée
Col. B = nombre de jours dans le mois courant dont la formule est: =SI($I8="";"";SOMMEPROD((MOIS(LIGNE(INDIRECT($I8&":"&SI($Q8="";DATE(ANNEE(S$6);MOIS(S$6)+1;0);$Q8))))=MOIS(S$6))*(ANNEE(LIGNE(INDIRECT($I8&":"&SI($Q8="";DATE(ANNEE(S$6);MOIS(S$6)+1;0);$Q8))))=ANNEE(S$6))))
(ne pas tenir compte du nom de cellule dans la formule - exemple seul)
Col. C= nombre de jour total depuis l’arrivée
Col. D= de 0 à 30 jours
Je veux que la valeur de la colonne K soit multiplié par 95 (les 30 premiers jours d'hébergement seulement à ce taux)
Col. E= de 31 jours et plus à facturer à 15$
je veux que la valeur de la colonne K excédant 30 jours et plus soit multiplié
par 15 (J'aurai donc 1 journée seulement des mois qui contiennent 31 jours lorsque cela sera pour le premier mois de séjour) à partir de la date d'arrivée (colonne I)
Col. F= de 31 jours et plus à facturer à 80$
Je veux que la valeur de la colonne L soit multiplié par 80$ à partir du 31 ième jour et ce pour tous les mois subséquents.
J'espère que quelqu'un saura m'aider - j'ai essayé avec la fonction SI - mais j'éprouve quelques problèmes.
Merci à l'avance
impossible de joindre le fichier excel - je peux vous donner mon adresse électronique si vous êtes prêts à m'aider et je pourrais vous faire parvenir le fichier.
2009-05-31(date de fin de mois)
A -Date d'arrivée
B - "Nbre jours pour le mois"
C -"Nbre jours depuis l'arrivée"
D - "Facturer usager
95 $ / jour
pour les 30 premiers
jours de séjour UNIQUEMENT"
[I]Le 30 jours de facturation à 95 $ peut s'étaler sur 2 mois dépendamment du mois d'arrivée/I
E - "Facturer usager 15 $ / jour
pour le 31e jour
et les mois
subséquents"
[I]lorsque date d'arrivée sera en date du 1er du mois précédent (sur un mois de 31 jours) - facturer ce qui restera du 30 jours initials dans colonne D puis l'excédent dans la colonne E et F/I
F - "Facturer l'org. 80 $ / jour
pour le 31e jour
et les mois
subséquents"
[I]lorsque date d'arrivée sera le 1er sur un mois de 31 jours - 30 jours facturés dans colonne D et 1 journée dans la colonne F/I
exemple:
si date de fin de mois = 2009-05-31
date d'arrivée = 2009-04-13
date de sortie = non applicable
Donc:
reste 12 jours à 95$
[I]"30 jours avril = 18 jours facturé à 95$
et 12 jours en mai pour le total de 30 jours"/I
19 jrs à 15 $ / jr
pour le mois de mai
19 jrs à 80 $ / jr
pour le mois de mai
Je vous remercie à l'avance !
(j'ai fait un fichier exemple pour ceux qui pensent pouvoir m'aider)
Je suis à réaliser un tableau qui me permettra de facturer mensuellement un séjour en hébergement avec des taux différent soit pour les 30 premiers jours exclusivement, puis pour les mois subséquents.
J'ai donc créé les colonnes suivantes :
ma cellule A1 étant la date de fin de mois en cours
Col. A = Date d’arrivée
Col. B = nombre de jours dans le mois courant dont la formule est: =SI($I8="";"";SOMMEPROD((MOIS(LIGNE(INDIRECT($I8&":"&SI($Q8="";DATE(ANNEE(S$6);MOIS(S$6)+1;0);$Q8))))=MOIS(S$6))*(ANNEE(LIGNE(INDIRECT($I8&":"&SI($Q8="";DATE(ANNEE(S$6);MOIS(S$6)+1;0);$Q8))))=ANNEE(S$6))))
(ne pas tenir compte du nom de cellule dans la formule - exemple seul)
Col. C= nombre de jour total depuis l’arrivée
Col. D= de 0 à 30 jours
Je veux que la valeur de la colonne K soit multiplié par 95 (les 30 premiers jours d'hébergement seulement à ce taux)
Col. E= de 31 jours et plus à facturer à 15$
je veux que la valeur de la colonne K excédant 30 jours et plus soit multiplié
par 15 (J'aurai donc 1 journée seulement des mois qui contiennent 31 jours lorsque cela sera pour le premier mois de séjour) à partir de la date d'arrivée (colonne I)
Col. F= de 31 jours et plus à facturer à 80$
Je veux que la valeur de la colonne L soit multiplié par 80$ à partir du 31 ième jour et ce pour tous les mois subséquents.
J'espère que quelqu'un saura m'aider - j'ai essayé avec la fonction SI - mais j'éprouve quelques problèmes.
Merci à l'avance
impossible de joindre le fichier excel - je peux vous donner mon adresse électronique si vous êtes prêts à m'aider et je pourrais vous faire parvenir le fichier.
2009-05-31(date de fin de mois)
A -Date d'arrivée
B - "Nbre jours pour le mois"
C -"Nbre jours depuis l'arrivée"
D - "Facturer usager
95 $ / jour
pour les 30 premiers
jours de séjour UNIQUEMENT"
[I]Le 30 jours de facturation à 95 $ peut s'étaler sur 2 mois dépendamment du mois d'arrivée/I
E - "Facturer usager 15 $ / jour
pour le 31e jour
et les mois
subséquents"
[I]lorsque date d'arrivée sera en date du 1er du mois précédent (sur un mois de 31 jours) - facturer ce qui restera du 30 jours initials dans colonne D puis l'excédent dans la colonne E et F/I
F - "Facturer l'org. 80 $ / jour
pour le 31e jour
et les mois
subséquents"
[I]lorsque date d'arrivée sera le 1er sur un mois de 31 jours - 30 jours facturés dans colonne D et 1 journée dans la colonne F/I
exemple:
si date de fin de mois = 2009-05-31
date d'arrivée = 2009-04-13
date de sortie = non applicable
Donc:
reste 12 jours à 95$
[I]"30 jours avril = 18 jours facturé à 95$
et 12 jours en mai pour le total de 30 jours"/I
19 jrs à 15 $ / jr
pour le mois de mai
19 jrs à 80 $ / jr
pour le mois de mai
Je vous remercie à l'avance !
(j'ai fait un fichier exemple pour ceux qui pensent pouvoir m'aider)
A voir également:
- Excel - calcul différent pour les 30 prem.jrs
- Calcul moyenne excel - Guide
- Liste déroulante excel - Guide
- Word et excel gratuit - Guide
- Déplacer colonne excel - Guide
- Si ou excel - Guide
15 réponses
Bonjour Weiyin,
Ma proposition sur la feuille 2:
https://www.cjoint.com/?gbxEJWU3DL
Note: j'ai mis des prix différents pour mieux contrôler les résultats.
Ma proposition sur la feuille 2:
https://www.cjoint.com/?gbxEJWU3DL
Note: j'ai mis des prix différents pour mieux contrôler les résultats.
Bonjour.
Voici une explication extrêmement compliquée pour conclure que le client paie 95 $ chaque jour !
Pourquoi dois-tu séparer les 15 $ et les 80 $ ? Pour appliquer une taxe différentielle ? Pour accorder une remise sur une des deux tranches de tarif ?
Il y a forcément des calculs beaucoup simples, si tu précises ce que tu veux.
Et que faut-il comprendre par "date de sortie = non applicable " ?
Pour m'adresser ton fichier, tu peux, maintenant que tu es inscrite comme membre, au choix :
- Consulter mon profil et y lire mon adresse électronique
- Coller dans un prochain message dans ce forum un lien, que tu crèes avec https://www.cjoint.com/
- M'envoyer un Message privé et utliser https://www.cjoint.com/
J'attends ta réponse.
Voici une explication extrêmement compliquée pour conclure que le client paie 95 $ chaque jour !
Pourquoi dois-tu séparer les 15 $ et les 80 $ ? Pour appliquer une taxe différentielle ? Pour accorder une remise sur une des deux tranches de tarif ?
Il y a forcément des calculs beaucoup simples, si tu précises ce que tu veux.
Et que faut-il comprendre par "date de sortie = non applicable " ?
Pour m'adresser ton fichier, tu peux, maintenant que tu es inscrite comme membre, au choix :
- Consulter mon profil et y lire mon adresse électronique
- Coller dans un prochain message dans ce forum un lien, que tu crèes avec https://www.cjoint.com/
- M'envoyer un Message privé et utliser https://www.cjoint.com/
J'attends ta réponse.
Bonjour M. Pentier,
C'est très gentil à vous d'avoir pris la peine de me répondre. En réalité, ce que je cherche à automatiser dans mon fichier excel est que:
L'usager se verra facturé 95$ pour les '30 premiers jours d'hébergement seulement' peut importe le temps total de journées depuis son arrivée.
Dans mon exemple, j'aurais pû y indiquer une date de départ au 31 mai..... ou tout autre date du mois mai.
Dans mon exemple, le client est arrivé le 13 avril et comme la facturation se fait le dernier jour de chaque mois, je devais dans un premier temps lui facturer 95$ par jour au 30 avril - ce qui à ce moment donnait 12 jours/30 - laissant un solde de 18 jours à facturer en mai au même taux, soit 95$, puis pour les 12 jours restants du mois de mai - la facturation doit se faire de la façon suivante: soit 12 jours à l'usager à 15$ et 12 jours payable par l'établissement 80$.
Je retourne mon fichier exemple pour vous éclairer un peu plus.
Merci encore une fois.
C'est très gentil à vous d'avoir pris la peine de me répondre. En réalité, ce que je cherche à automatiser dans mon fichier excel est que:
L'usager se verra facturé 95$ pour les '30 premiers jours d'hébergement seulement' peut importe le temps total de journées depuis son arrivée.
Dans mon exemple, j'aurais pû y indiquer une date de départ au 31 mai..... ou tout autre date du mois mai.
Dans mon exemple, le client est arrivé le 13 avril et comme la facturation se fait le dernier jour de chaque mois, je devais dans un premier temps lui facturer 95$ par jour au 30 avril - ce qui à ce moment donnait 12 jours/30 - laissant un solde de 18 jours à facturer en mai au même taux, soit 95$, puis pour les 12 jours restants du mois de mai - la facturation doit se faire de la façon suivante: soit 12 jours à l'usager à 15$ et 12 jours payable par l'établissement 80$.
Je retourne mon fichier exemple pour vous éclairer un peu plus.
Merci encore une fois.
Bonjour,
Juste au passage, ou se trouve votre fichier exemple ... ?
Il manque le lien fournit par CJOINT.com ou CIJOINT.fr ....?
Juste au passage, ou se trouve votre fichier exemple ... ?
Il manque le lien fournit par CJOINT.com ou CIJOINT.fr ....?
Je joint à nouveau le ficher
https://www.cjoint.com/?gboRwChUsn
Je ne comprends pas, pourtant je l'avais joint hier ??? désolée
https://www.cjoint.com/?gboRwChUsn
Je ne comprends pas, pourtant je l'avais joint hier ??? désolée
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Bonjour Weiyin,
Une petite astuce, déterminer le nombre de jours du mois courant avec cette formule :
JOUR(DATE(ANNEE(A1);MOIS(A1)+1;0))
ou "A1" est la date courante.
Une petite astuce, déterminer le nombre de jours du mois courant avec cette formule :
JOUR(DATE(ANNEE(A1);MOIS(A1)+1;0))
ou "A1" est la date courante.
Bonjour,
Juste un petit détail, est-ce que la date de départ peut-être vide (date non communiquée ...) ?
Dans la feuille [Doc travail révisé] votre colonne [Nb jours mois courant] la valeur de "K7" est fausse : se devrait être 31, soit le nombre de jours du mois de mai ...! Que vouliez vous faire exactemant ... ?
Merci de votre réponse.
Juste un petit détail, est-ce que la date de départ peut-être vide (date non communiquée ...) ?
Dans la feuille [Doc travail révisé] votre colonne [Nb jours mois courant] la valeur de "K7" est fausse : se devrait être 31, soit le nombre de jours du mois de mai ...! Que vouliez vous faire exactemant ... ?
Merci de votre réponse.
Bonjour,
Dans un premier temps, "oui" la case date de départ peut être vide.
J'ai probablement joint le fichier sur lequel je n'avais pas indiqué de commentaires.....
https://www.cjoint.com/?gmrE3eFOqe
En gros les critères à surveiller sont:
A) les 30 premiers jours d'occupations doivent être facturés à 95$ (pour l'usager uniquement)
B) les jours subséquents doivent l'être à 15$ (pourl'usager) et 85$ (pourl'org)
C) la facturation mensuelle (faite à la fin du mois) doit refléter le nombre de jours d'occupation (certains usagers se verront facturés la totalité du mois - sinon, si l'usager quitte en cours du mois, il ne se verra que facturé le nombre de jours d'occupation (tout en tenant compte des points A et B)
Je vous remercie encore une fois de votre aide !
Salutations!
Lucie
Dans un premier temps, "oui" la case date de départ peut être vide.
J'ai probablement joint le fichier sur lequel je n'avais pas indiqué de commentaires.....
https://www.cjoint.com/?gmrE3eFOqe
En gros les critères à surveiller sont:
A) les 30 premiers jours d'occupations doivent être facturés à 95$ (pour l'usager uniquement)
B) les jours subséquents doivent l'être à 15$ (pourl'usager) et 85$ (pourl'org)
C) la facturation mensuelle (faite à la fin du mois) doit refléter le nombre de jours d'occupation (certains usagers se verront facturés la totalité du mois - sinon, si l'usager quitte en cours du mois, il ne se verra que facturé le nombre de jours d'occupation (tout en tenant compte des points A et B)
Je vous remercie encore une fois de votre aide !
Salutations!
Lucie
Bonjour,
J'ai de l'avance, j'ai modifié la feuille [Méthode de calcul no 1] et aussi adapté votre feuille [Doc travail révisé] en conséquence.
Note: j'ai utilisé le nombre des jours à déduire selon la date de départ.
Je n'ai pas tout testé, si problème je suis là.
Votre dossier : https://www.cjoint.com/?gmrL3Htau5
Je constate que l'on c'est croisée, je vous laisse regarder le dossier ....
J'ai de l'avance, j'ai modifié la feuille [Méthode de calcul no 1] et aussi adapté votre feuille [Doc travail révisé] en conséquence.
Note: j'ai utilisé le nombre des jours à déduire selon la date de départ.
Je n'ai pas tout testé, si problème je suis là.
Votre dossier : https://www.cjoint.com/?gmrL3Htau5
Je constate que l'on c'est croisée, je vous laisse regarder le dossier ....
Bonjour,
J'ai trouvé une erreur de prise en compte des 30 premiers jours. Cette version est corrigé; sur les 2 feuilles bien sur. https://www.cjoint.com/?gmvbtmWVXb
J'ai trouvé une erreur de prise en compte des 30 premiers jours. Cette version est corrigé; sur les 2 feuilles bien sur. https://www.cjoint.com/?gmvbtmWVXb
re bonjour,
Avant que je ne reçoive le dernier fichier, j'avais élaboré sur le résultat que je désire obtenir
https://www.cjoint.com/?gmvE2l8f1L
Votre aide m'est très précieuse.... vous l'ai-je déjà dit ?
Lucie
Avant que je ne reçoive le dernier fichier, j'avais élaboré sur le résultat que je désire obtenir
https://www.cjoint.com/?gmvE2l8f1L
Votre aide m'est très précieuse.... vous l'ai-je déjà dit ?
Lucie
Bonjour,
Merci pour le message.
Avez-vous une demande supplémentaire ... ?
Est-ce que ma version [Weiyin_ccm_v02.xls] vous convient ... ?
Merci pour le message.
Avez-vous une demande supplémentaire ... ?
Est-ce que ma version [Weiyin_ccm_v02.xls] vous convient ... ?
Bonjour Lucie,
J'ai regardé les commentaires sur les résultats à obtenir et il se trouve que c'est exactemant se que vous trouvez sur ma version [Weiyin_ccm_v02.xls] .
Ou est-ce que je n'ai pas compris votre demande ?
J'ai regardé les commentaires sur les résultats à obtenir et il se trouve que c'est exactemant se que vous trouvez sur ma version [Weiyin_ccm_v02.xls] .
Ou est-ce que je n'ai pas compris votre demande ?
Bonjour Jean-Pierre,
Vous avez très bien compris ma demande; après avoir regardé à nouveau le fichier, il semble y avoir problème lorsque la facturation des 30 premiers jours d'occupation s'étale sur 2 mois, la base étant de 30 jours (c'est à dire le critère des 30 premiers jours à 95$), il manque 1 journée de facturation lorsque le mois référencié en comporte 31. Est-ce que cela fait un sens?
Salutations,
Lucie
(je pense que vous y êtes presque... merci encore)
Vous avez très bien compris ma demande; après avoir regardé à nouveau le fichier, il semble y avoir problème lorsque la facturation des 30 premiers jours d'occupation s'étale sur 2 mois, la base étant de 30 jours (c'est à dire le critère des 30 premiers jours à 95$), il manque 1 journée de facturation lorsque le mois référencié en comporte 31. Est-ce que cela fait un sens?
Salutations,
Lucie
(je pense que vous y êtes presque... merci encore)
Bonjour Jean-Pierre,
Je me disais bien qu'avec ce que vous m'aviez soumis, que vous aviez compris exactement ce que je recherchais. Par conséquent, veuillez ignorer mon dernier message..... la journée qui manquant venait du fait que la date de facturation que j'avais référencié était le 30 mai...... mai n'a-t-il pas 31 jours????? désolée pour cette erreur.....
Merci énormément pour votre aide si précieuse et surtout pour votre grande disponibilité.
Salutations,
Lucie
Je me disais bien qu'avec ce que vous m'aviez soumis, que vous aviez compris exactement ce que je recherchais. Par conséquent, veuillez ignorer mon dernier message..... la journée qui manquant venait du fait que la date de facturation que j'avais référencié était le 30 mai...... mai n'a-t-il pas 31 jours????? désolée pour cette erreur.....
Merci énormément pour votre aide si précieuse et surtout pour votre grande disponibilité.
Salutations,
Lucie
Bonjour Lucie,
Merci pour ses informations.
De mon côté je regarde une solution plus simple et aussi pour la date de facturation pour éviter une erreur.
Si j'ai une meilleure solution je vous l'enverrai !
Bon week-end.
Merci pour ses informations.
De mon côté je regarde une solution plus simple et aussi pour la date de facturation pour éviter une erreur.
Si j'ai une meilleure solution je vous l'enverrai !
Bon week-end.
Bonjour,
Suite à la lecture de votre avant dernier message, je vous avais réécris pour vous dire que ........ j'avais mal référencié la date de fin de mois...... depuis toujours à ce que je sache... le mois de mai compte 31 jours et j'avais inscris 2009-05-30 ..... désolée de vous avoir fait cherché inutilement, veuillez accepter toutes mes excuses. Mon message ne s'est pas rendu probablement à cause d'une perte de signal.
Merci de votre aide et surtout de votre grande patience.
Lucie
Suite à la lecture de votre avant dernier message, je vous avais réécris pour vous dire que ........ j'avais mal référencié la date de fin de mois...... depuis toujours à ce que je sache... le mois de mai compte 31 jours et j'avais inscris 2009-05-30 ..... désolée de vous avoir fait cherché inutilement, veuillez accepter toutes mes excuses. Mon message ne s'est pas rendu probablement à cause d'une perte de signal.
Merci de votre aide et surtout de votre grande patience.
Lucie
Bonjour Lucie,
Pour conclure se poste voici ma dernière version basé sur le nombre de jours à facturer pour le mois courant et la recherche du solde selon la base des 30 premiers jours.
https://www.cjoint.com/?goaea0ugCL
Faite votre choix ...
A une prochaine.
Pour conclure se poste voici ma dernière version basé sur le nombre de jours à facturer pour le mois courant et la recherche du solde selon la base des 30 premiers jours.
https://www.cjoint.com/?goaea0ugCL
Faite votre choix ...
A une prochaine.
Merci beaucoup Jean-Pierre,
Puisque mon tableau aura une vingtaine de lignes j'aurais besoin d'une dernière formule qui me permettrait d'obtenir la somme des journées facturées pour le mois courant (Col. K) .....si j'ai référencié une date d'arrivée (Col. I).
J'ai bien essayé, mais je ne réussis pas à trouver.
Merci
Lucie
Puisque mon tableau aura une vingtaine de lignes j'aurais besoin d'une dernière formule qui me permettrait d'obtenir la somme des journées facturées pour le mois courant (Col. K) .....si j'ai référencié une date d'arrivée (Col. I).
J'ai bien essayé, mais je ne réussis pas à trouver.
Merci
Lucie
Merci d'avoir pris le temps de résoudre mon problème - j'ai reporté les formules dans mon tableau final, sauf que lorsque je dois y inscrire une date de départ je ne réussi pas à modifier la formule afin que le nombre de jours d'occupation réel du mois courant soit pris en considération. Je joins donc, mon tableau final afin que vous puissiez, encore une fois, m'aider. Il ne me manque que ce petit détail et le tout sera parfait.
Encore une fois, milles merci !
https://www.cjoint.com/?gmlzgCaHjv
Weiyin