Auftragsplanung mit variablem Datum

[bonnie]

Hallo Excel-Freunde,
ich brauch mal wieder eure Hilfe.

Wie ihr an meinem angehängten Beispiel seht, habe ich eine Auftragsplanung erstellt, die diverse Anforderungen erfüllen soll, jetzt möchte ich das komfortabler machen und bin mit meinem Latein am Ende. Hoffentlich könnt Ihr mir helfen !

Von mir gelöst, ich habe Auftrag mit einer festen Laufzeit ( hier 12 Monate ) wenn bis zum einem bestimmten Termin nicht gekündigt wird ( hier 4 Wochen vor Ende Vertragslaufzeit ) verlängert sich der Auftrag wiederum um ( hier 12 Monate)

1. Anfang und Ende der Laufzeit soll kalendarisch dargestellt werden.
2. Das Datum der Kündigungsfrist soll automatisch errechnet werden.
3. Der Angebotspreis soll während der festen Laufzeit mit einem leichten Grün dargestellt werden, wenn die feste Laufzeit abgelaufen ist, soll die automatische Verlängerung in der g l e i c h e n Zeile evtl. in einem leichten Rot dargestellt werden.

Für eine schöne Lösung wäre ich dankbar.

LG Bonnie

[WillWissen]

Hi Wolfgang,

schön, wieder von dir zu hören.

1. Anfang und Ende der Laufzeit soll kalendarisch dargestellt werden.

bezieht sich das darauf, dass du momentan mit 30 Tagen/Monat rechnest? Falls ich dich richtig verstehe, soll bei einem Start (z.B.) 1.3.16 das Ende des Vertrags auf den 28.2.17 datiert sein. Dann kannst du mit der Fkt. EDATUM rechnen:

=EDATUM(Startdatum;Monate)-1

Sobald ich weiß, ob ich mit meiner Vermutung richtig liege, schau ich mir auch deine anderen Fragen genauer an. Vllt. finde ich eine Lösung.

[bonnie]

Hallo Günther,

war lange im Krankenhaus und danach sofort wieder im Ausland  - - ich glaub ich muss kürzer treten.

. . .   nun zum Problem passt  - -  wie immer  - -  danke !!!

Ich hoffe, du oder ein anderer hat die Lösung für meine nächsten Probleme.

Vielen herzlichen Dank !!!

Wolfgang

[WillWissen]

Hi Wolfgang,

ich hoffe, es war nichts Ernstes. Schon dich - nichts ist so wichtig wie die Gesundheit.


Deine nächsten beiden Fragen müssten - wieder unter der Prämisse, dass ich dich richtig verstanden habe - so gelöst werden:

Kundenstamm

	F	G	H	I	J	K	L	M	N	O	P	Q
17	01.06.2015	31.05.2016	539,10	90%				x		12	4	03.05.16
18	01.06.2016	31.05.2017	539,10	100%					x	12	4	03.05.17

Formeln der Tabelle

Zelle Formel G17 =EDATUM(F17;12)-1 I17 =WENN(HEUTE()>Q17;90%;100%) J17 =WENN(($I17=$E$2);"x";" ") K17 =WENN(($I17=$E$3);"x";" ") L17 =WENN(($I17=$E$4);"x";" ") M17 =WENN(($I17=$E$5);"x";" ") N17 =WENN(($I17=$E$6);"x";" ") Q17 =G17-28

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format I17 1. / Formel ist =$I17=1 Abc I17 2. / Formel ist =$I17=0,9 Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Die beiden Zellen in Sp.I sind in % formatiert; die Werte entsprechend 0,9 bzw. 1

[bonnie]

Hallo Günther,

leider richtig böse, Ärzte sagen, sie können mir nicht mehr helfen   -   aber das Leben geht weiter !!!!

Du, so ganz hast du das Problem offensichtlich nicht verstanden, das mit den Datums haben wir ja jetzt im Griff.

. . .   aber der 100 % Betrag soll grün unterlegt in einer Zeile sein;  in der selben Zeile soll nach Ablauf der Laufzeit mit 100 % der Betrag mit 90 % rot unterlegt sein.

Zum Verständnis hab ich dir nochmal eine Tabelle angefügt, in der die Problematik besser dargestellt ist.

Hoffe, du kannst damit etwas anfangen !

Ganz liebe Grüße
Wolfgang

[WillWissen]

Hi Wolfgang,

. . .   aber der 100 % Betrag soll grün unterlegt in einer Zeile sein;  in der selben Zeile soll nach Ablauf der Laufzeit mit 100 % der Betrag mit 90 % rot unterlegt sein.

dann hoffe ich mal, dass ich das Problem jetzt verinnerlicht habe. Diesen Teil hatten wir schon:

Kundenstamm

	F	G	H	I	J	K	L	M	N	O	P	Q
8	Monat von (TT.MM.JJ)	Monat bis (TT.MM.JJ)	Angebotspreis	Status	1	2	3	4	5	Laufzeit	Kündigungsfrist	Kündigungstermin
17	01.06.2016	31.05.2017	539,10	100%					x	12	4	03.05.17

Formeln der Tabelle

Zelle Formel G17 =EDATUM(F17;12)-1 I17 =WENN(HEUTE()>Q17;90%;100%) J17 =WENN(($I17=$E$2);"x";" ") K17 =WENN(($I17=$E$3);"x";" ") L17 =WENN(($I17=$E$4);"x";" ") M17 =WENN(($I17=$E$5);"x";" ") N17 =WENN(($I17=$E$6);"x";" ") Q17 =G17-28

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format I17 1. / Zellwert ist gleich =0,9 Abc I17 2. / Zellwert ist gleich =1 Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Den Teil habe ich nach meinem jetzigen Verständnis so aufgebaut (müsste eigentlich passen):

Kundenstamm

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

8

Jan. 16

Feb. 16

Mrz. 16

Apr. 16

Mai. 16

Jun. 16

Jul. 16

Aug. 16

Sep. 16

Okt. 16

Nov. 16

Dez. 16

Jan. 17

Feb. 17

Mrz. 17

17

539,10

539,10

539,10

539,10

539,10

485,19

485,19

485,19

485,19

485,19

485,19

485,19

485,19

485,19

485,19

Formeln der Tabelle

Zelle Formel R8 =B2 S8 =MONATSENDE(R$8;0)+1 T8 =MONATSENDE(S$8;0)+1 U8 =MONATSENDE(T$8;0)+1 V8 =MONATSENDE(U$8;0)+1 W8 =MONATSENDE(V$8;0)+1 X8 =MONATSENDE(W$8;0)+1 Y8 =MONATSENDE(X$8;0)+1 Z8 =MONATSENDE(Y$8;0)+1 AA8 =MONATSENDE(Z$8;0)+1 AB8 =MONATSENDE(AA$8;0)+1 AC8 =MONATSENDE(AB$8;0)+1 AD8 =MONATSENDE(AC$8;0)+1 AE8 =MONATSENDE(AD$8;0)+1 AF8 =MONATSENDE(AE$8;0)+1 R17 =WENN((R$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) S17 =WENN((S$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) T17 =WENN((T$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) U17 =WENN((U$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) V17 =WENN((V$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) W17 =WENN((W$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) X17 =WENN((X$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) Y17 =WENN((Y$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) Z17 =WENN((Z$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) AA17 =WENN((AA$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) AB17 =WENN((AB$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) AC17 =WENN((AC$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) AD17 =WENN((AD$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) AE17 =WENN((AE$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9) AF17 =WENN((AF$8<=DATUM(JAHR($F$17);MONAT($F$17);));$H$17;$H$17*0,9)

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format R17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc R17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc S17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc S17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc T17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc T17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc U17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc U17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc V17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc V17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc W17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc W17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc X17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc X17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc Y17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc Y17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc Z17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc Z17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc AA17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc AA17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc AB17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc AB17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc AC17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc AC17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc AD17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc AD17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc AE17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc AE17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc AF17 1. / Formel ist =R$8>=DATUM(JAHR($F$17);MONAT($F$17);) Abc AF17 2. / Formel ist =R$8<=DATUM(JAHR($F$17);MONAT($F$17);) Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8


Ich habe auch die Formeln für die Datumsberechnungen geändert. In B2 gibst du den 1.1.16 ein (Format MMM JJ), den Rest siehst du im Tabellenausschnitt.

Falls du die "Datei zum Spielen" haben willst, lade ich sie dir gerne hoch.

[WillWissen]

Hi Wolfgang,

noch 'ne Formelverkürzung:

Kundenstamm

	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
17	01.03.2015	29.02.2016	539,10	90%				x		12	4	01.02.16	539,10	539,10	485,19	485,19	485,19	485,19	485,19

Formeln der Tabelle

Zelle Formel G17 =EDATUM(F17;12)-1 I17 =WENN(HEUTE()>Q17;90%;100%) J17 =WENN(($I17=$E$2);"x";" ") K17 =WENN(($I17=$E$3);"x";" ") L17 =WENN(($I17=$E$4);"x";" ") M17 =WENN(($I17=$E$5);"x";" ") N17 =WENN(($I17=$E$6);"x";" ") Q17 =G17-28 R17 =WENN((R$8<=$G$17);$H$17;$H$17*0,9) S17 =WENN((S$8<=$G$17);$H$17;$H$17*0,9) T17 =WENN((T$8<=$G$17);$H$17;$H$17*0,9) U17 =WENN((U$8<=$G$17);$H$17;$H$17*0,9) V17 =WENN((V$8<=$G$17);$H$17;$H$17*0,9) W17 =WENN((W$8<=$G$17);$H$17;$H$17*0,9) X17 =WENN((X$8<=$G$17);$H$17;$H$17*0,9)

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format I17 1. / Zellwert ist gleich =0,9 Abc I17 2. / Zellwert ist gleich =1 Abc R17 1. / Formel ist =R$8<=$G$17 Abc R17 2. / Formel ist =R$8>=$G$17 Abc S17 1. / Formel ist =R$8<=$G$17 Abc S17 2. / Formel ist =R$8>=$G$17 Abc T17 1. / Formel ist =R$8<=$G$17 Abc T17 2. / Formel ist =R$8>=$G$17 Abc U17 1. / Formel ist =R$8<=$G$17 Abc U17 2. / Formel ist =R$8>=$G$17 Abc V17 1. / Formel ist =R$8<=$G$17 Abc V17 2. / Formel ist =R$8>=$G$17 Abc W17 1. / Formel ist =R$8<=$G$17 Abc W17 2. / Formel ist =R$8>=$G$17 Abc X17 1. / Formel ist =R$8<=$G$17 Abc X17 2. / Formel ist =R$8>=$G$17 Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[bonnie]

Hallo Günter,

exakt !!!!  Echte Hilfe wie immer !!!!

Viele liebe Grüße
Wolfgang

. . . . . .  wenn ihr mal Hilfe zu betriebswirtschaftlichen Fragen braucht  . .. . . .   auch monetär  . . . .   gerne

[WillWissen]

Hi Wolfgang,

schön, dass ich dir helfen konnte.

Jetzt solltest du vllt. tatsächlich mal die Arbeit ruhen lassen und ausspannen. Der Termin Forumstreffen in Kornwestheim steht immer noch und ein Platzerl im Hotel wird bestimmt auch noch frei sein. Wir tun dann auch noch ein gutes Werk und unterstützen ganz kräftig die (Hotel)Betriebswirtschaft.

Ein bis zwei oder mehrere gehen bestimmt.  :15:  :32:

[bonnie]

Hallo Günter,

auf den ersten Blick haben ja deine Formeln super funktioniert.

Jetzt beim durchtesten hab ich doch noch einen kleinen Fehler gefunden.
Ich hab jetzt mal deine Formeln auf die Zeile 22 angewendet.
Wie du siehst habe ich den Auftrag mit einer Laufzeit ab 01.08.2016 eingegeben. Da müsste doch von Jan2016 bis Juli2016 eine 0 drin stehen.
Kannst du das noch abfangen ?

LG Wolfgang