Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
22.01.2015, 06:31
(Dieser Beitrag wurde zuletzt bearbeitet: 22.01.2015, 06:48 von WillWissen.)
Hi schadre,
Zitat:Kurios für mich ist weiterhin, dass wenn ich "01.12.2014 - 30.01.2015" eingeben, er den Wert 01.12.2020 in der Hilfszelle ausgibt und dann nicht einfärbt.
da hast du wohl nicht das Format TT.MM.JJ gewählt. Siehe Beitrag #2 v. 13.12.2014, 22:33
Zitat:Wichtig ist aber, dass deine Daten immer im selben Format, z.B. TT.MM.JJ, geschrieben werden.
und Beitrag #15 v. 14.12.2014, 15:33
Zitat:Sollte das Format TT.MM.JJJJ gewünscht sein, muss lediglich die Formel
... LÄNGE(I9));8)*1;"") auf 10
geändert werden. Aber das wäre dann eine einmalige Anpassung.
An deinem anderen Problem arbeite ich.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Registriert seit: 14.04.2014
Version(en): 2003, 2007
Hallo zusammen,
Wenn VBA eingesetzt werden kann, dann wäre eine VBA Lösung einfacher umzusetzen.
Unten ein VBA Code, der in Spalte I ab Zeile 9 die Zellen, die ein Datum enthalten, rot färbt.
Folgenden Code in das Code Fenster der betreffenden Tabelle einfügen:
Code: Private Sub Datum_kleiner_groesser()
Dim i As Long, j As Long, lngZ As Long
Dim arr
lngZ = Cells(Rows.Count, 9).End(xlUp).Row
Range("I9:I" & lngZ).Interior.ColorIndex = xlNone
For j = 2 To lngZ
If Cells(j, 9) <> "" Then
arr = Split(Cells(j, 9))
If UBound(arr) >= 0 Then
For i = UBound(arr) To LBound(arr) Step -1
If IsDate(arr(i)) Then
If CDate(arr(i)) < Date Then
Cells(j, 9).Interior.ColorIndex = 3
End If
i = LBound(arr)
End If
Next i
End If
End If
Next j
End Sub
Die Bedingte Formatierung für die Spalte I löschen.
Den Cursor in eine Codezeile setzen und die Funktionstaste F5 drücken.
Jetzt müssten die entsprechenden Zellen rot gefärbt sein.
Damit das zukünftig automatisch passiert, folgenden Code zusätzlich in das Code Modul der betreffenden Tabelle einfügen:
Code: Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And Target.Row > 8 Then
On Error GoTo fehler
Application.EnableEvents = False
Application.ScreenUpdating = False
Datum_kleiner_groesser
End If
fehler:
Application.EnableEvents = True
Application.ScreenUpdating = True
If Err Then MsgBox "FEehler: " & Err.Number & vbLf & vbLf & Err.Description
End Sub
Dieser Code reagiert auf Eingaben in der Spalte I ab Zeile 9
Gruß Atilla
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
22.01.2015, 09:06
(Dieser Beitrag wurde zuletzt bearbeitet: 22.01.2015, 09:09 von WillWissen.)
Hi,
nachdem dies deine Vorgabe ist
Zitat:Bei deiner ersten Formel vom 13.12.2014, 22:33, hat er die Zelle nicht eingefärbt, da der zweite Wert ja noch in der Zukunft liegt.
So hätte ich das wenn möglich auch weiterhin. Bitte, bitte.....
meine Formellösung:
Tabelle3 | I | J | K | L | 9 | möchte ab 01.03.15 in Filiale A arbeiten | 01.03.2015 | 01.03.2015 | zukünftig | 10 | Urlaub von 15.1.15 - 10.02.15 | 15.01.2015 | 10.02.2015 | zweites Datum zukünftig | 11 | Seminar 01.01.15 - 20.01.15 | 01.01.2015 | 20.01.2015 | beide Daten Vergangenheit | 12 | ab 15.01.15 in 3. Abtlg. | 15.01.2015 | 15.01.2015 | Datum Vergangenheit + 3. Abtlg. hinten | 13 | Urlaub 01.01.15 - 22.1.15 | 01.01.2015 | 22.01.2015 | erstes Datum Vergangenheit, zweites heute | 14 | 3. Abtl. bis zum Urlaub am 13.12.14 | | 13.12.2014 | Vergangenheit + 3. Abtlg. vorne | 15 | 3. Abtl. bis zum Urlaub am 1.02.15 | | 01.02.2015 | Datum zukünftig + 3. Abtlg. vorne | 16 | Meier 2. Abmahnung | 02.01.1900 | | kein Datum | Formeln der Tabelle | Zelle | Formel | J9 | {=WENNFEHLER(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));8)*1;"")} | K9 | {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));8))*1;WENNFEHLER(TEXT(TEIL(I9;FINDEN("-";I9)+2;8);"TT.MM.JJJJ")*1;J9))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));8))*1;WENNFEHLER(TEXT(TEIL(I9;FINDEN("-";I9)+2;8);"TT.MM.JJJJ")*1;J9)))} | J10 | {=WENNFEHLER(LINKS(TEIL(LINKS(I10;MAX(ISTZAHL(TEIL(I10;SPALTE(10:10);1)*1)*SPALTE(10:10)));VERGLEICH(1;ISTZAHL(TEIL(I10&0;SPALTE(10:10);1)*1)*1;0);LÄNGE(I10));8)*1;"")} | K10 | {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I10;MAX(ISTZAHL(TEIL(I10;SPALTE(10:10);1)*1)*SPALTE(10:10)));VERGLEICH(1;ISTZAHL(TEIL(I10&0;SPALTE(10:10);1)*1)*1;0);LÄNGE(I10));8))*1;WENNFEHLER(TEXT(TEIL(I10;FINDEN("-";I10)+2;8);"TT.MM.JJJJ")*1;J10))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I10;MAX(ISTZAHL(TEIL(I10;SPALTE(10:10);1)*1)*SPALTE(10:10)));VERGLEICH(1;ISTZAHL(TEIL(I10&0;SPALTE(10:10);1)*1)*1;0);LÄNGE(I10));8))*1;WENNFEHLER(TEXT(TEIL(I10;FINDEN("-";I10)+2;8);"TT.MM.JJJJ")*1;J10)))} | J11 | {=WENNFEHLER(LINKS(TEIL(LINKS(I11;MAX(ISTZAHL(TEIL(I11;SPALTE(11:11);1)*1)*SPALTE(11:11)));VERGLEICH(1;ISTZAHL(TEIL(I11&0;SPALTE(11:11);1)*1)*1;0);LÄNGE(I11));8)*1;"")} | K11 | {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I11;MAX(ISTZAHL(TEIL(I11;SPALTE(11:11);1)*1)*SPALTE(11:11)));VERGLEICH(1;ISTZAHL(TEIL(I11&0;SPALTE(11:11);1)*1)*1;0);LÄNGE(I11));8))*1;WENNFEHLER(TEXT(TEIL(I11;FINDEN("-";I11)+2;8);"TT.MM.JJJJ")*1;J11))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I11;MAX(ISTZAHL(TEIL(I11;SPALTE(11:11);1)*1)*SPALTE(11:11)));VERGLEICH(1;ISTZAHL(TEIL(I11&0;SPALTE(11:11);1)*1)*1;0);LÄNGE(I11));8))*1;WENNFEHLER(TEXT(TEIL(I11;FINDEN("-";I11)+2;8);"TT.MM.JJJJ")*1;J11)))} | J12 | {=WENNFEHLER(LINKS(TEIL(LINKS(I12;MAX(ISTZAHL(TEIL(I12;SPALTE(12:12);1)*1)*SPALTE(12:12)));VERGLEICH(1;ISTZAHL(TEIL(I12&0;SPALTE(12:12);1)*1)*1;0);LÄNGE(I12));8)*1;"")} | K12 | {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I12;MAX(ISTZAHL(TEIL(I12;SPALTE(12:12);1)*1)*SPALTE(12:12)));VERGLEICH(1;ISTZAHL(TEIL(I12&0;SPALTE(12:12);1)*1)*1;0);LÄNGE(I12));8))*1;WENNFEHLER(TEXT(TEIL(I12;FINDEN("-";I12)+2;8);"TT.MM.JJJJ")*1;J12))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I12;MAX(ISTZAHL(TEIL(I12;SPALTE(12:12);1)*1)*SPALTE(12:12)));VERGLEICH(1;ISTZAHL(TEIL(I12&0;SPALTE(12:12);1)*1)*1;0);LÄNGE(I12));8))*1;WENNFEHLER(TEXT(TEIL(I12;FINDEN("-";I12)+2;8);"TT.MM.JJJJ")*1;J12)))} | J13 | {=WENNFEHLER(LINKS(TEIL(LINKS(I13;MAX(ISTZAHL(TEIL(I13;SPALTE(13:13);1)*1)*SPALTE(13:13)));VERGLEICH(1;ISTZAHL(TEIL(I13&0;SPALTE(13:13);1)*1)*1;0);LÄNGE(I13));8)*1;"")} | K13 | {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I13;MAX(ISTZAHL(TEIL(I13;SPALTE(13:13);1)*1)*SPALTE(13:13)));VERGLEICH(1;ISTZAHL(TEIL(I13&0;SPALTE(13:13);1)*1)*1;0);LÄNGE(I13));8))*1;WENNFEHLER(TEXT(TEIL(I13;FINDEN("-";I13)+2;8);"TT.MM.JJJJ")*1;J13))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I13;MAX(ISTZAHL(TEIL(I13;SPALTE(13:13);1)*1)*SPALTE(13:13)));VERGLEICH(1;ISTZAHL(TEIL(I13&0;SPALTE(13:13);1)*1)*1;0);LÄNGE(I13));8))*1;WENNFEHLER(TEXT(TEIL(I13;FINDEN("-";I13)+2;8);"TT.MM.JJJJ")*1;J13)))} | J14 | {=WENNFEHLER(LINKS(TEIL(LINKS(I14;MAX(ISTZAHL(TEIL(I14;SPALTE(14:14);1)*1)*SPALTE(14:14)));VERGLEICH(1;ISTZAHL(TEIL(I14&0;SPALTE(14:14);1)*1)*1;0);LÄNGE(I14));8)*1;"")} | K14 | {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I14;MAX(ISTZAHL(TEIL(I14;SPALTE(14:14);1)*1)*SPALTE(14:14)));VERGLEICH(1;ISTZAHL(TEIL(I14&0;SPALTE(14:14);1)*1)*1;0);LÄNGE(I14));8))*1;WENNFEHLER(TEXT(TEIL(I14;FINDEN("-";I14)+2;8);"TT.MM.JJJJ")*1;J14))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I14;MAX(ISTZAHL(TEIL(I14;SPALTE(14:14);1)*1)*SPALTE(14:14)));VERGLEICH(1;ISTZAHL(TEIL(I14&0;SPALTE(14:14);1)*1)*1;0);LÄNGE(I14));8))*1;WENNFEHLER(TEXT(TEIL(I14;FINDEN("-";I14)+2;8);"TT.MM.JJJJ")*1;J14)))} | J15 | {=WENNFEHLER(LINKS(TEIL(LINKS(I15;MAX(ISTZAHL(TEIL(I15;SPALTE(15:15);1)*1)*SPALTE(15:15)));VERGLEICH(1;ISTZAHL(TEIL(I15&0;SPALTE(15:15);1)*1)*1;0);LÄNGE(I15));8)*1;"")} | K15 | {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I15;MAX(ISTZAHL(TEIL(I15;SPALTE(15:15);1)*1)*SPALTE(15:15)));VERGLEICH(1;ISTZAHL(TEIL(I15&0;SPALTE(15:15);1)*1)*1;0);LÄNGE(I15));8))*1;WENNFEHLER(TEXT(TEIL(I15;FINDEN("-";I15)+2;8);"TT.MM.JJJJ")*1;J15))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I15;MAX(ISTZAHL(TEIL(I15;SPALTE(15:15);1)*1)*SPALTE(15:15)));VERGLEICH(1;ISTZAHL(TEIL(I15&0;SPALTE(15:15);1)*1)*1;0);LÄNGE(I15));8))*1;WENNFEHLER(TEXT(TEIL(I15;FINDEN("-";I15)+2;8);"TT.MM.JJJJ")*1;J15)))} | J16 | {=WENNFEHLER(LINKS(TEIL(LINKS(I16;MAX(ISTZAHL(TEIL(I16;SPALTE(16:16);1)*1)*SPALTE(16:16)));VERGLEICH(1;ISTZAHL(TEIL(I16&0;SPALTE(16:16);1)*1)*1;0);LÄNGE(I16));8)*1;"")} | K16 | {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I16;MAX(ISTZAHL(TEIL(I16;SPALTE(16:16);1)*1)*SPALTE(16:16)));VERGLEICH(1;ISTZAHL(TEIL(I16&0;SPALTE(16:16);1)*1)*1;0);LÄNGE(I16));8))*1;WENNFEHLER(TEXT(TEIL(I16;FINDEN("-";I16)+2;8);"TT.MM.JJJJ")*1;J16))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I16;MAX(ISTZAHL(TEIL(I16;SPALTE(16:16);1)*1)*SPALTE(16:16)));VERGLEICH(1;ISTZAHL(TEIL(I16&0;SPALTE(16:16);1)*1)*1;0);LÄNGE(I16));8))*1;WENNFEHLER(TEXT(TEIL(I16;FINDEN("-";I16)+2;8);"TT.MM.JJJJ")*1;J16)))} |
| Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen! | Matrix verstehen |
Bedingte Formatierungen der Tabelle | Zelle | Nr.: / Bedingung | Format | I9 | 1. / Formel ist =$K9<HEUTE() | Abc | I10 | 1. / Formel ist =$K9<HEUTE() | Abc | I11 | 1. / Formel ist =$K9<HEUTE() | Abc | I12 | 1. / Formel ist =$K9<HEUTE() | Abc | I13 | 1. / Formel ist =$K9<HEUTE() | Abc | I14 | 1. / Formel ist =$K9<HEUTE() | Abc | I15 | 1. / Formel ist =$K15<HEUTE() | Abc | I16 | 1. / Formel ist =$K9<HEUTE() | Abc |
| Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
... und wie üblich mein Rat, die Hilfsspalten einfach ausblenden.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
Hallo Atilla,
schöne VBA-Lösung. Und macht nebenbei noch keinen Unterschied zwischen 2-stelliger oder 4-stelliger Jahreszahl.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Registriert seit: 14.04.2014
Version(en): 2003, 2007
22.01.2015, 10:22
(Dieser Beitrag wurde zuletzt bearbeitet: 22.01.2015, 11:01 von atilla.)
Hallo zusammen,
Danke Günter für Dein Feedback. In VBA gibt es die Split Funktion, die es in der Tabelle nicht gibt. Und in diesem Fall kann man sie sehr gut einsetzen, was die Sache sehr vereinfacht.
Mir ist aber gerade aufgefallen, dass das Ereignis Makro nicht immer alle Zellen neu bearbeiten muss.
Es muss nur die gerade geänderte Zelle prüfen, deshalb ist folgender Code besser geeignet:
Code: Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim arr
If Target.Count = 1 Then
If Target.Column = 9 And Target.Row > 8 Then
On Error GoTo fehler
Application.EnableEvents = False
arr = Split(Target)
If UBound(arr) >= 0 Then
For i = UBound(arr) To LBound(arr) Step -1
If IsDate(arr(i)) Then
If CDate(arr(i)) < Date Then
Target.Interior.ColorIndex = 3
Else
Target.Interior.ColorIndex = xlNone
End If
Exit For
End If
Next i
End If
End If
Else
If Target.Column = 9 And Target.Row > 8 Then
On Error GoTo fehler
Application.EnableEvents = False
Datum_kleiner_groesser
End If
End If
fehler:
Application.EnableEvents = True
If Err Then MsgBox "FEehler: " & Err.Number & vbLf & vbLf & Err.Description
End Sub
Gruß Atilla
Folgende(r) 1 Nutzer sagt Danke an atilla für diesen Beitrag:1 Nutzer sagt Danke an atilla für diesen Beitrag 28
• schadre
Registriert seit: 13.04.2014
Version(en): 365, 2019
Hallo,
ich setze da immer noch auf Formeln:
Arbeitsblatt mit dem Namen 'Tabelle1' | | I | J | K | L | 9 | möchte ab 01.01.15 in Filiale A arbeiten | 01.01.15 | | WAHR | 10 | | | | #WERT! | 11 | | | | #WERT! | 12 | Urlaub von 01.12.2014 - 10.12.2014 danach 3.Abteilung | 01.12.14 | 10.12.14 | WAHR | 13 | | | | #WERT! | 14 | | | | #WERT! | 15 | Arbeitet von 10.01.15 - 31.01.2015 in Fil. A | 10.01.15 | 31.01.15 | FALSCH | 16 | Urlaub 01.07.14 -14.07.14 | 01.07.14 | 14.07.14 | WAHR | 17 | | | | #WERT! | 18 | | | | #WERT! | 19 | Urlaub am 10.02.15 | 10.02.15 | | FALSCH |
Zelle | Formel | J9 | =WENNFEHLER(TEIL(I9;SUCHEN("??.??.??";I9);8+ISTZAHL(TEIL(I9;SUCHEN("??.??.????";I9);10)*1)*2)*1;"") | K9 | =WENN(ISTZAHL(SUCHEN("-";I9));GLÄTTEN(TEIL(I9;SUCHEN("-";I9)+1;12))*1;"") | L9 | =(TEIL(I9;SUCHEN("??.??.??";I9);8+ISTZAHL(TEIL(I9;SUCHEN("??.??.????";I9);10)*1)*2)*1<HEUTE())+WENNFEHLER(((ISTZAHL(SUCHEN("-";I9))*(GLÄTTEN(TEIL(I9;SUCHEN("-";I9)+1;12))*1))<HEUTE())*1;1)>1 |
Zelle | bedingte Formatierung... | Format | I9 | 1: =(TEIL(I9;SUCHEN("??.??.??";I9);8+ISTZAHL(TEIL(I9;SUCHEN("??.??.????";I9);10)*1)*2)*1<HEUTE())+WENNFEHLER(((ISTZAHL(SUCHEN("-";I9))*(GLÄTTEN(TEIL(I9;SUCHEN("-";I9)+1;12))*1))<HEUTE())*1;1)>1 | abc |
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
Die Formeln in der Tabelle sind für die bedingte Formatierung nicht notwendig, sie dienen nur der Veranschaulichung!
Leider werden die Farben in I9;I12 u. I16 nicht dargestellt (da, wo die Formel in L ein WAHR ergibt).
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Registriert seit: 14.04.2014
Version(en): 2003, 2007
(22.01.2015, 11:18)BoskoBiati schrieb: Hallo,
ich setze da immer noch auf Formeln:
Hallo Edgar,
da hast Du jetzt aber was primitives rausgehauen
Mit dieser Formel würde ich dann auch überlegen, ob ich VBA nutze.
Denn die bisherigen drückten doch stark auf die Performance Bremse.
Meine VBA Lösung kann aber auch ein allein stehendes Daum verarbeiten, d.h. in der Zelle kann auch nur Datum stehen. :49:
Gruß Atilla
Registriert seit: 13.04.2014
Version(en): 365, 2019
Hallo,
das können die Formeln auch:
Code: =ISTZAHL(I9)+(WENNFEHLER(TEIL(I9;SUCHEN("??.??.??";I9);8+ISTZAHL(TEIL(I9;SUCHEN("??.??.????";I9);10)*1)*2)*1;99999)<HEUTE())+WENNFEHLER(((ISTZAHL(SUCHEN("-";I9))*(GLÄTTEN(TEIL(I9;SUCHEN("-";I9)+1;12))*1))<HEUTE())*1;1)>1
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Registriert seit: 13.12.2014
Version(en): 2010
Hallo an alle fleißigen Helfer.
Konnte eure Hilfe leider erst heute ausprobieren und habe die Formeln von WillWissen ausprobiert und es klappt. Vielen vielen Dank. Ihr seid die Besten.
|