Formule calcul de date sans weekend

Le 23/12/2019 à 12:53

Bonjour,

J'ai besoin d'un coup de main concernant la formule de mon tableau ci-joint (cases : D2 et D3).

J'aimerai que cette dernière ne prennes pas en compte les weekend (samedi et dimanche) dans les délais indiqués selon "l'urgence".

Pourriez-vous m'aider svp ?

Merci !

30test1.xlsx (8.73 Ko)
Le 23/12/2019 à 13:11

Bonjour Tonyto59,

=SI(C2="OUI";SI(B2="";"";SI(HEURE(B2)<10;TEXTE(B2;"jj/mm/aaaa")&" 15:30";SI(HEURE(B2)>=10;TEXTE((B2+1);"jj/mm/aaaa")&" 12:00")));SI(B2="";"";SI(HEURE(B2)<10;TEXTE(B2+2;"jj/mm/aaaa")&" 15:30";SI(HEURE(B2)>=10;TEXTE((B2+3);"jj/mm/aaaa")&" 12:00"))))

En décryptant votre formule, si la commande urgent est faite avant 10h00, livraison

le jour même à 15h30, sinon

le lendemain 12h00

si la commande non urgent est faite avant 10h00, livraison

à deux jour à 15h30, sinon

à trois jour à 12h00

J'aimerai que cette dernière ne prennes pas en compte les weekend (samedi et dimanche) dans les délais indiqués selon "l'urgence".

Veut dire pas de commande urgente le weekend?

Le 23/12/2019 à 13:24

Bonjour njhub,

C'est exactement ça :

Demande urgente avant 10h -> livraison avant 15h30

Demande urgente après 10h -> livraison avant le lendemain 12h00

Demande non-urgente avant 10h -> livraison J+2 avant 15h30

Demande non-urgente après 10h -> livraison à J+3 avant 12h00

Les modifications que j'aimerais si possible, sur les demandes du Vendredi (et jeudi pour les non-urgentes) :

Demande urgente Vendredi avant 10h -> livraison Vendredi avant 15h30 (RAS)

Demande urgente Vendredi après 10h -> livraison Lundi avant 12h00

Demande non-urgente Vendredi avant 10h -> livraison Mardi avant 15h30

Demande non-urgente Vendredi après 10h -> livraison Mercredi avant 12h00

Merci beaucoup !

Le 23/12/2019 à 20:06

Bonjour Tonyto59,

Je pense que cette formule pourrait être simplifiée, factorisée, voyez dans le fichier joint si elle donne les résultats attendus

=SI(SOMME(JOURSEM(B2;11)<6;B2-ENT(B2)<10/24;C2<>"oui")=3;ENT(B2)+2,646+SI(ET(JOURSEM(B2;11)>3;JOURSEM(B2;11)<6);2;0);SI(SOMME(JOURSEM(B2;11)>=6;B2-ENT(B2)<10/24;C2<>"oui")=3;ENT(B2)+2,646+SI(ET(JOURSEM(B2;11)>3;JOURSEM(B2;11)<6);2;0);SI(SOMME(JOURSEM(B2;11)<6;B2-ENT(B2)>10/24;C2<>"oui")=3;ENT(B2)+2,5+SI(ET(JOURSEM(B2;11)>3;JOURSEM(B2;11)<6);2;0);SI(SOMME(JOURSEM(B2;11)>=6;B2-ENT(B2)>10/24;C2<>"oui")=3;ENT(B2)+4,646+SI(ET(JOURSEM(B2;11)>3;JOURSEM(B2;11)<6);2;0);SI(SOMME(JOURSEM(B2;11)<6;B2-ENT(B2)<10/24;C2="oui")=3;ENT(B2)+0,646;SI(SOMME(JOURSEM(B2;11)>=6;B2-ENT(B2)<10/24;C2="oui")=3;ENT(B2)+2,646+SI(ET(JOURSEM(B2;11)>3;JOURSEM(B2;11)<6);2;0);SI(SOMME(JOURSEM(B2;11)<6;B2-ENT(B2)>10/24;C2="oui")=3;ENT(B2)+1,5+SI(ET(JOURSEM(B2;11)>=5;JOURSEM(B2;11)<=7);2;0);SI(SOMME(JOURSEM(B2;11)>=5;B2-ENT(B2)>10/24;C2="oui")=3;ENT(B2)+4,646+SI(ET(JOURSEM(B2;11)>3;JOURSEM(B2;11)<6);2;0);""))))))))
11tonyto59-test1.xlsx (10.28 Ko)
Le 24/12/2019 à 12:57

Bonjour njhub,

De mon côté, tout à l'air de fonctionner sauf pour un cas :

Urgence avant 10h -> Ok

Urgence après 10h -> Ok

Non-urgence avant 10h -> Ok

Non-urgence après 10h -> "réglée" à J+2 avant 12h (samedi à J+3 avant 15h30 / dimanche J+4 avant 15h30), j'aurais souhaité à "J+3 avant 12h", si possible bien évidement

Je ne voudrais pas foirer toute la formule en essayant de faire la modification moi-même .

En tout cas, un grand merci pour votre aide et ce super travail !

capture
Le 24/12/2019 à 14:24

Bonjour Tonyto59,

Confirmer vos souhaits en remplaçant les ? par les jours et heures souhaitées ou par OK

Commandé le lun. 10:45 non urgent livraison le mer. 12:00 délai 2 jours souhaité ?

Commandé le mar. 10:45 non urgent livraison le jeu. 12:00 délai 2 jours souhaité ?

Commandé le mer. 10:45 non urgent livraison le ven. 12:00 délai 2 jours souhaité ?

Commandé le jeu. 10:45 non urgent livraison le lun. 12:00 délai 4 jours souhaité ?

Commandé le ven. 10:45 non urgent livraison le mar. 12:00 délai 4 jours souhaité ?

Commandé le sam. 10:45 non urgent livraison le mer. 15:30 délai 4 jours souhaité mar. 12:00?

Commandé le dim. 10:45 non urgent livraison le jeu. 15:30 délai 4 jours souhaité mer. 12:00?

Merci

Le 26/12/2019 à 10:40

Bonjour njhub,

Commandé le lun. 10:45 non urgent livraison le mer. 12:00 délai 2 jours souhaité -> livraison le Jeu. 12h00

Commandé le mar. 10:45 non urgent livraison le jeu. 12:00 délai 2 jours souhaité -> livraison le Ven. 12h00

Commandé le mer. 10:45 non urgent livraison le ven. 12:00 délai 2 jours souhaité -> livraison le Lun. 12h00

Commandé le jeu. 10:45 non urgent livraison le lun. 12:00 délai 4 jours souhaité -> livraison le Mar. 12h00

Commandé le ven. 10:45 non urgent livraison le mar. 12:00 délai 4 jours souhaité -> livraison le Mer. 12h00

Commandé le sam. 10:45 non urgent livraison le mer. 15:30 délai 4 jours souhaité mar. 12:00 -> livraison le Mer. 15h30

Commandé le dim. 10:45 non urgent livraison le jeu. 15:30 délai 4 jours souhaité mer. 12:00 -> livraison le Mer. 15h30

Pour les 2 dernières lignes (demandes passées le week-end), je les considères comme si elles étaient passées le lundi avant 10h00.

Merci à vous !

Le 26/12/2019 à 14:33

Bonjour Tonyto59,

Voyez dans le fichier joint si la nouvelle formule vous donne les résultats attendus

=CHOISIR(SI(SOMME((JOURSEM(B2;11)<6);(B2-ENT(B2)<10/24);(C2="oui"))=3;1;SI(SOMME((JOURSEM(B2;11)<5);(B2-ENT(B2)>10/24);(C2="oui"))=3;2;SI(SOMME((JOURSEM(B2;11)={1;2;3;6;7});(B2-ENT(B2)<10/24))=2;3;SI(SOMME((JOURSEM(B2;11)={3;4;5});(B2-ENT(B2)>10/24);(C2="non"))=3;8;SI(SOMME((JOURSEM(B2;11)={1;2;5});(B2-ENT(B2)>10/24))=2;4;SI(SOMME((JOURSEM(B2;11)=7);(B2-ENT(B2)>10/24);(C2="non"))=3;5;SI(SOMME((JOURSEM(B2;11)={6;7});(B2-ENT(B2)>10/24))=2;6;SI(SOMME((JOURSEM(B2;11)={4;5});(B2-ENT(B2)<10/24))=2;7;""))))))));"05:45:00";"25:15:00";"53:45:00";"73:15:00";"76:45:00";"100:45:00";"101:45:00";"121:15:00")+B2
15tonyto59-test2.xlsx (9.00 Ko)
Le 27/12/2019 à 10:41

Bonjour njhub,

Quand j'ouvres le fichier en mode protégé c'est parfait !

Mais dès que "j'actives les modifications", certains résultat affichent une erreur de valeur...

Même chose quand je copie la formule dans mon fichier.

J'ai essayé de changer mon format de cellule mais pas de changement.

Encore merci !!

EDIT :

Quand je modifie "l'urgence" les résultats s'inversent (erreur de valeurs sur ceux qui étaient corrects, et résultats corrects sur ceux qui étaient en erreur)

3 protege nonprotege
Le 27/12/2019 à 13:32

Bonjour Tonyto59,

J'ai l'impression que vous faites des manipulations sans avoir enregistré le fichier sur votre ordinateur...

Le 31/12/2019 à 08:38

Bonjour njhub,

J'enregistre bien votre fichier sur mon bureau avant de l'ouvrir, mais j'ai tout de même le mode protégé d'activé et lorsque je clique sur "activer la modification", là certaines formules passent en résultat "#VALEUR".

étapes par étapes :

J'enregistre sous, le fichier "Tonyto59_test" sur mon bureau.

J'ouvres le fichier (toujours à partir du bureau).

Il s'ouvre en "mode protégé", les résultats ont l'air tous corrects

protege

A partir de là, si "j'active la modification", le mode protégé est retiré mais certaines des valeurs passent en erreur...

nonprotege

Et même si je n'enregistre pas le fichier en le fermant, lorsque je le rouvre les erreurs restent...

Merci beaucoup !

Meilleurs vœux, et bonne année 2020 !!

Le 31/12/2019 à 11:06

Bonjour, salut njhub !

J'ai également tenté l'exercice... Je propose cette formule (exemple en D2) :

=SI(JOURSEM($B2;2)>=6;ENT($B2)+207,5/24-JOURSEM($B2;2)+2*($C2="non");ENT($B2)+SI($B2-ENT($B2)<10/24;15,5/24;36/24)+2*($C2="non")+2*(JOURSEM(ENT($B2)+SI($B2-ENT($B2)<10/24;15,5/24;36/24)+2*($C2="non");2)>=6))

Si j'ai bien saisi :

  • Toute commande passée au cours du WE est considérée comme passé le lundi matin avant 10h.
  • Toute commande dont la date prévisionnelle de livraison tombe un WE est décalée de 2 jours

Le fichier :

6test1.xlsx (11.10 Ko)

PS : pour fonctionner, la formule travaille sur des dates valides (= convertibles en valeur numérique) et donnent comme résultat une date également valide.

Le 31/12/2019 à 11:11

Quelques compléments sur le fonctionnement :

JOURSEM(Date;2) : donne le jour de la semaine correspondant à une date (ici 1 pour un lundi, et 7 pour un dimanche)

Date-ENT(Date) : isole l'heure d'une date (en lui retirant la partie entière = les jours)

2*(Urgence="non") : si la partie entre parenthèses est VRAIE, elle renvoie 1, sinon 0. Multiplié par 2, cela permet d'ajouter un délai de 2 jours si la commande n'est pas urgente

La dernière partie de la formule reprend le même principe pour ajouter 2 jours si la livraison tombe un WE...

Le 31/12/2019 à 12:05

Bonjour Pedro22,

Merci pour vos contributions, en espérant que votre fichier soit issu d'excel, il fonctionnera peut-être mieux que le mien sur la machine de Tonyto59

Le 31/12/2019 à 12:12
njhub a écrit :

Bonjour Pedro22,

Merci pour vos contributions, en espérant que votre fichier soit issu d'excel, il fonctionnera peut-être mieux que le mien sur la machine de Tonyto59

Je n'ai pas testé votre proposition, mais vous pouvez tenté de modifier les heures issues de la fonction CHOISIR par leur valeur numérique réelle (et pas l'heure au format texte). Exemple : "05:45:00" devient 5,75.

L'erreur #VALEUR peut provenir d'un calcul sur une valeur non numérique (exemple : =5*"patate")

Rechercher des sujets similaires à "formule calcul date weekend"