Office: Auto-Sortierung, mit Bezug zu Tagesdatum

Halloooooo und einen schönen, sonnigen Tag liebe Forengemeinde!

Qwertz84 mein "Name" und ich bin soeben neu zur Community hinzugestoßen, da ihr etwas Unschätzbares habt, was kein Buch der Welt hergibt: Erfahrung und Kreativität. Diese würde ich gern in Anspruch nehmen, sofern ihr mir das gestattet... denn nachdem ich nun schon zich Bücher (Galileo Computing, Herdt-Classics, "Excel-Zauberbücher" usw.) durchblättert habe, finde ich einfach keine Lösung zu meinem Projekt.

Ich habe eine ziemlich große Excel-Tabelle, die Rahmenverträge mit Lieferanten auflistet, der Thematik der Verträge, Vertragsbeginn, -Ende usw. Ziel für mich ist es das Datum des Vertragsendes mit dem aktuellen Tagesdatum vergleichen zu lassen und daraus die Restlaufzeit in Tagen ausgeben zu lassen. Je näher die Restlaufzeit in Tagen am Datum des Vertragsendes heran ist, umso roter sollen die Felder hinterlegt werden. Im optimalsten Falle soll noch eine aufsteigende Auto-Sortierung stattfinden, wenn man das Formular öffnet.

Ich möchte kurz ein Beispiel geben:

Ein Vertrag endet am 31.12.2014 und hat eine Kündigungsfrist von 1 Monat. Da ich aber eine gewisse Bearbeitungszeit brauche (Kündigung vorbereiten, evtl. Rücksprache mit Fachabteilung, Zeit für Verhandlungen zur Vertragsfortführung), möchte ich noch einen Puffer mit einbauen (dazu nachher noch eine Anmerkung):

Datum heut = 22.05.2014 --> zu 31.12.2014 = 223 Tage - 30 Tage Bearbeitungszeit = 193 Tage Restlaufzeit. Da diese Tagesanzahl noch recht groß ist, kann es eine andere Farbe haben als rot. Wenn ich aber die Liste öffne, wäre es toll wenn die Resttageslaufzeit immer neu berechnet wird und, da viele Verträge existieren, aufsteigend sortiert wird. Nach Möglichkeit automatisch mit öffnen des Formulars.

Ist so etwas möglich? Wie würdet Ihr schrittweise vorgehen?

Da ich mich zwar zu den Nichtkennern excel'scher Materie zähle, war ich dennoch nicht ganz untätig und habe per Hilfsspalte nachfolgende Formel errechnet:

=WENN(ISTLEER(N5);"";DATUM(JAHR(M5);MONAT(M5);TAG(M5))-HEUTE())

Heißt schlichtweg: Wenn die Bezugszelle leer ist, lass das Feld frei, ansonsten überprüfe das Datum in der Zelle M5 (da steht das Vertrags-Ende geschrieben) und ziehe das heutige Datum davon ab. Was hier noch rein muss ist der Bearbeitungspuffer von 30 Tagen aber da hänge ich einfach ein "-30" ran. Könntet ihr mir bei dem Rest helfen bitte?

LG
Qwertz84

 

Moin, deine Formel könnte auch so aussehen:

Code:

=WENN(ISTZAHL(M5);DATEDIF(HEUTE();M5;"D")-30;"")

Ansonsten beschäftige dich einfach einmal mit der bedingten Formatierung, die kann die gewiss dienlich sein ;).

 

OK, das sieht natürlich um einiges unkomplizierter aus als meine ö_Ö =D Vielen lieben Dank!
Die bedingte Formatierung... mhhh die wird wahrscheinlich dafür gut sein, um die Tage farblich zu markieren oder?
Und wie könnte man das mit der Auto-Sortierung machen, bei Dokument-Öffnung? Das wäre sogar fast das Wichtigere.

 

Zu deiner Ehrenrettung: Die Funktion DATEDIF() wird nicht im Funktionskatalog aufgeführt ... :) Mache bitte einfach einmal eine Muster-Tabelle und erkläre anhand dieses Beispiels noch einmal, was du erreichen willst. Das macht das Ganze etwas klarer ...

 

Puuuuh Gott sei Dank :D

Also eine Test-Tabelle habe ich angelegt, leider kann ich an dem Rechner keine Ad-Ins installieren, um eine Tabelle direkt darstellen zu lassen. Ich hoffe ein Screenshot tuts auch erstmal ._.



Spalte H soll hier ausschlaggebend sein (die Werte sind frei erfunden). Sie berechnet den Kündigungstermin abzüglich der Kündigungsfrist und des heutigen Datums. Wenn ich das Dokument öffne, soll Excel diese Tage immer anpassen und optimalerweise automatisch sortieren, sodass (im Beispiel) die Lieferanten C und D ganz oben stehen, da dort nur noch 47 Tage Zeit sind, bis der Vertrag endet und ich weiß, dass ich dort handeln muss. Als nächstes kämen E, B und zuletzt A. Also aine aufsteigende Sortierung. Ich glaube das mit der täglichen Aktualisierung ist so ein open:if-Makro oder wie das heißt oder?

LG
Qwertz84

 

So, dank der eigenwilligen (individuellen) Fristen ist die Formel etwas größer geworden. Sollten es einmal Jahre sein, müssen die in Monate umgerechnet werden (18 Monate statt 1,5 Jahre). Die Spalte mit den Rest-Tagen kannst du dann ganz normal sortieren.

 

Hallooooo =)
Welche etwas größer gewordene Formel meinst du denn?
Ja ich weiß, die Fristen sind etwas eigenwillig aber in seltensten Fällen dürften sich diese > 1 Jahr bewegen. Bei 14 Tagen dürften es dann 0,5 Monate sein, richtig?

Beste Grüße
Qwertz84

 

Gute Frage ...
irgendwie ist mir die angehängte Datei dann doch verloren gegangen. Ich versuche es jetzt noch einmal mit der Datei!

(Jetzt sehe ich sie auch ;))

 

Vielen, vielen lieben Dank für deine Mühen!
Ich werd am Montag versuchen das in meine Ursprungstabelle reinzubasteln und würde mich im Bedarfsfalle nochmals melden. Natürlich würden erstmal Eigenexperimente gestartet :D

Ein (hoffentlich) schönes verlängertes Wochenende =)

 

Hallooo und guten Morgen =)
Habe mir das nun alles etwas Genauer angesehen und möchte mich nochmals dafür bedanken. Ist wirklich super!
Der erste Schritt ist somit gemacht und nun bräuchte ich jedoch noch die Möglichkeit, dass sich die Liste von selbst sortiert (nach Spalte C, wenn man vom Beispiel von cpt. Nemo ausgeht), wenn man sie öffnet. Geht das?

Ist das hier der richtige Anhaltspunkt?

Private Sub Workbook_Open()
Dim wks As Worksheet
Set wks = Worksheets("Tabelle4")
With wks
.Unprotect Password:="123"
.Range("B9:F23").Sort Key1:=.Range("F9"), order1:=xlDescending, header:=xlNo
.Protect Password:="123"
End With
End Sub

Liebe Grüße und einen tollen Start in die Woche wünscht euch

Qwertz84

 

Hallooooo =)
Bei meiner Auto-Sortierung kann mir wohl niemand weiterhelfen was? Habs allein probiert via Makros und im Grunde müsste ich die immer erst aktivieren, bevor Excel sie sortiert... da kann man sich das auch sparen und gleich manuell sortieren oder?

Nun aber ein anderes Anliegen:
Ich konnte die Formel der oben mit-zitierten Datei sehr gut in meine Excel-Liste integrieren. Die Berechnung der Rest-Tage bis zur Kündigungsfrist funktioniert sehr gut. Nun aber eine Frage:

Wenn ich 3 Monate Kündigungsfrist habe und der Kündigungszeitpunkt der 31.08.2014 ist, sollte ich bis 31.05.2014 gekündigt haben. Ist es möglich dass Excel, wenn ich die Frist versäumt habe, automatisch ein weiteres Jahr zu meiner "Verbleibende Tage zum Handeln"-Spalte hinzufügt?

Anbei ein Screenshot der das verdeutlichen soll (natürlich professionell mit Paint! :D )