Supportnet / Forum / Tabellenkalkulation
Formel UND mit ODER verküpft
Frage
Hallo,
ich habe in meiner bedingten Formatierung folgende Formel:
=ODER(DATEDIF(E1;HEUTE();"d")>60;DATEDIF(F1;HEUTE();"d")>0)
Diese Formel soll aber nur angewendet werden, wenn G1="offen".
Das wäre dann ein UND mit ODER verküpft.
Geht das überhaut in einer Formel?
Wenn ja, kann mir bitte jemand sagen wie das geht.
MfG
BigFlash
Antwort 1 von ChatAlligator
Mir wird zwar nicht ersichtlich, was deine Formel macht und ob sie so wie sie da steht funktionsfähig ist, doch wenn ja dann schließe sie einfach in die Klammer der UND-Verknüfung ein:
=UND(G1="offen";ODER(DATEDIF(E1;HEUTE();"d")>60;DATEDIF(F1;HEUTE();"d")>0) )
wobei zu bemerken ist, dass
G1="offen" liefert den Wert WAHR, wenn der Text "offen" in G1 steht
G1="" liefert den wert WAHR, wenn ein Text ohne Inhalt in G1 steht
ISTLEER(G1) liefert den Wert WAHR, wenn in G1 kein Inhalt steht (auch keine Formel)
=UND(G1="offen";ODER(DATEDIF(E1;HEUTE();"d")>60;DATEDIF(F1;HEUTE();"d")>0) )
wobei zu bemerken ist, dass
G1="offen" liefert den Wert WAHR, wenn der Text "offen" in G1 steht
G1="" liefert den wert WAHR, wenn ein Text ohne Inhalt in G1 steht
ISTLEER(G1) liefert den Wert WAHR, wenn in G1 kein Inhalt steht (auch keine Formel)
Antwort 2 von BigFlash
Hallo,
danke für die schnelle Antwort, ich hatte schon einiges versucht, deine Lösung aber nocht nicht und sie funktioniert, Danke.
Meine Formel soll eine Ampel für einen Auftragsstatus regeln:
E1=Auftragseingang (Alarm wenn älter als 2 Monate und nicht erledigt (G1))
F1=geplanter Realisierungstermin (Alarm wenn Datum überschritten und nicht erledigt (G1))
G1=Fertigstellung (Standartwert "offen", wenn fertig, dann das Datum und keinen Alarm mehr)
Die Formel ansich funktioniert, nur wenn ein Ergebnis aus DATEDIF negativ ist, wird die ganze Formel ingnoriert wegen Fehler.
z.B. in E1 ist das Datum um 2 Monate überschritten und in F1 hat einer kein Datum sondern "KW 44" eingetragen.
Dafür habe ich auch noch keine Lösung.
MfG
BigFlash
danke für die schnelle Antwort, ich hatte schon einiges versucht, deine Lösung aber nocht nicht und sie funktioniert, Danke.
Meine Formel soll eine Ampel für einen Auftragsstatus regeln:
E1=Auftragseingang (Alarm wenn älter als 2 Monate und nicht erledigt (G1))
F1=geplanter Realisierungstermin (Alarm wenn Datum überschritten und nicht erledigt (G1))
G1=Fertigstellung (Standartwert "offen", wenn fertig, dann das Datum und keinen Alarm mehr)
Die Formel ansich funktioniert, nur wenn ein Ergebnis aus DATEDIF negativ ist, wird die ganze Formel ingnoriert wegen Fehler.
z.B. in E1 ist das Datum um 2 Monate überschritten und in F1 hat einer kein Datum sondern "KW 44" eingetragen.
Dafür habe ich auch noch keine Lösung.
MfG
BigFlash
Antwort 3 von ChatAlligator
Reicht es dir, dass die Formatierung dann für überfällig steht, wenn "KW 44" (ein Text) statt Datum eingetragen wird?
Dann reicht die Formel wie folgt erweitert:
=UND(G1="offen";ODER(DATEDIF(E1;HEUTE();"d")>60;DATEDIF(F1;HEUTE();"d")>0 ;ISTTEXT(F1) ) )
Sicher kann man die Kalenderwoche auch als Datum auflösen - das erfordert allerdings etwas mehr Programmierung (entweder in separater Spalte oder als Funktion)
Dann reicht die Formel wie folgt erweitert:
=UND(G1="offen";ODER(DATEDIF(E1;HEUTE();"d")>60;DATEDIF(F1;HEUTE();"d")>0 ;ISTTEXT(F1) ) )
Sicher kann man die Kalenderwoche auch als Datum auflösen - das erfordert allerdings etwas mehr Programmierung (entweder in separater Spalte oder als Funktion)
Antwort 4 von Saarbauer
Hallo,
die Lösung von @ ChatAlligator ist ok.
Mein Vorschlag ist die Zeile oder das Datum mit einer "Bedingten Formatierung" farbig zu hinterlegen
dann erkennt man sofort wo der Hase im Pfeffer liegt
Gruß
Helmut
die Lösung von @ ChatAlligator ist ok.
Mein Vorschlag ist die Zeile oder das Datum mit einer "Bedingten Formatierung" farbig zu hinterlegen
dann erkennt man sofort wo der Hase im Pfeffer liegt
Gruß
Helmut
Antwort 5 von BigFlash
Hallo,
die Formel mit dem Anhang ";ISTTEXT(F1)" funktioniert nicht, weil "=DATEDIF(F1;HEUTE();"d")" kein Ergebnis liefert (Fehler "#WERT!").
Wenn ein Teil der Formel einen fehlerhafen Wert erzeugt, dann scheint Excel, auch bei einer Oderverknüpfung, die komplette Formel zu ignorieren.
d.h. die Formel "=DATEDIF(F1;HEUTE();"d")" muss irgendwie so umgestellt werden, dass sie immer ein Ergebnis liefert (auch negativ oder wenn negativ dann immer 0).
Habt ihr dafür eine Idee?
MfG
BigFlash
die Formel mit dem Anhang ";ISTTEXT(F1)" funktioniert nicht, weil "=DATEDIF(F1;HEUTE();"d")" kein Ergebnis liefert (Fehler "#WERT!").
Wenn ein Teil der Formel einen fehlerhafen Wert erzeugt, dann scheint Excel, auch bei einer Oderverknüpfung, die komplette Formel zu ignorieren.
d.h. die Formel "=DATEDIF(F1;HEUTE();"d")" muss irgendwie so umgestellt werden, dass sie immer ein Ergebnis liefert (auch negativ oder wenn negativ dann immer 0).
Habt ihr dafür eine Idee?
MfG
BigFlash
Antwort 6 von ChatAlligator
Eine Alternative bringt folgende Formel - jetzt wird sogar die Kalenderwoche berücksichtigt und die meisten Fehler abgefangen
=UND(G1="offen";Fällig(F1;E1;60;5))
eine 5 als Zahlenwert bedeutet 5.Wochentag als Stichtag - sollte eine Fälligkeit an einem Samstag oder Sonntag geschehen, so wird sie schon am Freitag angezeigt. Die 60 steht immer noch als Frist nach geplanten Termin.
Vorausgesetzt folgende Funktionen werden ins Makro eingetragen.
Mit ihnen lassen sich auch das Datum eines bestimmten Wochentags innerhalb einer Kalenderwoche bestimmen oder die Kalenderwoche zu einem Datum
Sollte die Fälligkeitsfunktion eine Kalenderwoche lesen, die kleiner ist, als die der Auftragserteilung, so wird davon ausgegangen, dass sie ins nächste Jahr gehört (z.B. Auftragserteilung im November -> Kalenderwoche im Februar des Folgejahres)
Nun sage nicht, ich hätte mir keine Mühe gemacht ... ;-)
Gruß CA
´------------------------------------------------------------
Option Explicit
Function DatumVonKalenderwoche(ByVal Woche As Byte, ByVal Jahr As Integer, Optional ByVal WochentagNr As Byte = 7) As Date
´WochentagNr gibt an, ob das Ausgabedatum auf einen Montag=1, Dienstag=2,...,Sonntag=7 fallen soll
DatumVonKalenderwoche = Datum(1, 1, Jahr) - Weekday(Datum(1, 1, Jahr), 2) + (Woche - 1) * 7 + WochentagNr
End Function
Function KalenderwocheVonDatum(ByVal Termin As Date) As Byte
KalenderwocheVonDatum = Int((Termin - (Datum(1, 1, Year(Termin)) - Weekday(Datum(1, 1, Year(Termin)))) - 1) / 7) + 1
End Function
Function Datum(ByVal Tag As Byte, ByVal Monat As Byte, ByVal Jahr As Integer) As Date
Datum = CDate(Trim(CStr(Tag)) & "." & Trim(CStr(Monat)) & "." & Trim(CStr(Jahr)))
End Function
Function Fällig(ByVal DatumOderKalenderwoche As Variant, ByVal Referenzdatum As Range, ByVal FälligNachTagen As Integer, ByVal Fälligkeitswochentag As Byte) As Boolean
Dim temp As Boolean
Dim Kalenderwoche As Integer
Dim Vorgabe As Variant
Dim Referenz As Date
Vorgabe = DatumOderKalenderwoche.Value
Referenz = Referenzdatum.Value
temp = True
Select Case True
Case IsDate(Vorgabe)
If Date < Vorgabe + FälligNachTagen - 7 + Fälligkeitswochentag Then temp = False
Case IsEmpty(Vorgabe)
If Date < Referenzdatum + FälligNachTagen - 7 + Fälligkeitswochentag Then temp = False
Case IsNumeric(Vorgabe)
If Vorgabe > 54 Or Vorgabe > Int(Vorgabe) Then
Fällig = True
Exit Function
End If
If Vorgabe < KalenderwocheVonDatum(Referenzdatum) Then
Vorgabe = DatumVonKalenderwoche(Vorgabe, Year(Referenz) + 1, Fälligkeitswochentag)
Else
Vorgabe = DatumVonKalenderwoche(Vorgabe, Year(Referenz), Fälligkeitswochentag)
End If
If Date < Vorgabe Then temp = False
Case Else
On Error Resume Next
If InStr(UCase(Vorgabe), "KW") > 0 Then Mid(Vorgabe, InStr(UCase(Vorgabe), "KW"), 2) = Space(2)
If InStr(UCase(Vorgabe), "KALENDERWOCHE") > 0 Then Mid(Vorgabe, InStr(UCase(Vorgabe), "KALENDERWOCHE"), 13) = Space(13)
Vorgabe = Trim(Vorgabe)
Kalenderwoche = CInt(Vorgabe)
If Kalenderwoche < KalenderwocheVonDatum(Referenzdatum) Then
Vorgabe = DatumVonKalenderwoche(Vorgabe, Year(Referenz) + 1, Fälligkeitswochentag)
Else
Vorgabe = DatumVonKalenderwoche(Vorgabe, Year(Referenz), Fälligkeitswochentag)
End If
If Date < Vorgabe Then temp = False
On Error GoTo 0
End Select
Fällig = temp
End Function
´------------------------------------------------------------
=UND(G1="offen";Fällig(F1;E1;60;5))
eine 5 als Zahlenwert bedeutet 5.Wochentag als Stichtag - sollte eine Fälligkeit an einem Samstag oder Sonntag geschehen, so wird sie schon am Freitag angezeigt. Die 60 steht immer noch als Frist nach geplanten Termin.
Vorausgesetzt folgende Funktionen werden ins Makro eingetragen.
Mit ihnen lassen sich auch das Datum eines bestimmten Wochentags innerhalb einer Kalenderwoche bestimmen oder die Kalenderwoche zu einem Datum
Sollte die Fälligkeitsfunktion eine Kalenderwoche lesen, die kleiner ist, als die der Auftragserteilung, so wird davon ausgegangen, dass sie ins nächste Jahr gehört (z.B. Auftragserteilung im November -> Kalenderwoche im Februar des Folgejahres)
Nun sage nicht, ich hätte mir keine Mühe gemacht ... ;-)
Gruß CA
´------------------------------------------------------------
Option Explicit
Function DatumVonKalenderwoche(ByVal Woche As Byte, ByVal Jahr As Integer, Optional ByVal WochentagNr As Byte = 7) As Date
´WochentagNr gibt an, ob das Ausgabedatum auf einen Montag=1, Dienstag=2,...,Sonntag=7 fallen soll
DatumVonKalenderwoche = Datum(1, 1, Jahr) - Weekday(Datum(1, 1, Jahr), 2) + (Woche - 1) * 7 + WochentagNr
End Function
Function KalenderwocheVonDatum(ByVal Termin As Date) As Byte
KalenderwocheVonDatum = Int((Termin - (Datum(1, 1, Year(Termin)) - Weekday(Datum(1, 1, Year(Termin)))) - 1) / 7) + 1
End Function
Function Datum(ByVal Tag As Byte, ByVal Monat As Byte, ByVal Jahr As Integer) As Date
Datum = CDate(Trim(CStr(Tag)) & "." & Trim(CStr(Monat)) & "." & Trim(CStr(Jahr)))
End Function
Function Fällig(ByVal DatumOderKalenderwoche As Variant, ByVal Referenzdatum As Range, ByVal FälligNachTagen As Integer, ByVal Fälligkeitswochentag As Byte) As Boolean
Dim temp As Boolean
Dim Kalenderwoche As Integer
Dim Vorgabe As Variant
Dim Referenz As Date
Vorgabe = DatumOderKalenderwoche.Value
Referenz = Referenzdatum.Value
temp = True
Select Case True
Case IsDate(Vorgabe)
If Date < Vorgabe + FälligNachTagen - 7 + Fälligkeitswochentag Then temp = False
Case IsEmpty(Vorgabe)
If Date < Referenzdatum + FälligNachTagen - 7 + Fälligkeitswochentag Then temp = False
Case IsNumeric(Vorgabe)
If Vorgabe > 54 Or Vorgabe > Int(Vorgabe) Then
Fällig = True
Exit Function
End If
If Vorgabe < KalenderwocheVonDatum(Referenzdatum) Then
Vorgabe = DatumVonKalenderwoche(Vorgabe, Year(Referenz) + 1, Fälligkeitswochentag)
Else
Vorgabe = DatumVonKalenderwoche(Vorgabe, Year(Referenz), Fälligkeitswochentag)
End If
If Date < Vorgabe Then temp = False
Case Else
On Error Resume Next
If InStr(UCase(Vorgabe), "KW") > 0 Then Mid(Vorgabe, InStr(UCase(Vorgabe), "KW"), 2) = Space(2)
If InStr(UCase(Vorgabe), "KALENDERWOCHE") > 0 Then Mid(Vorgabe, InStr(UCase(Vorgabe), "KALENDERWOCHE"), 13) = Space(13)
Vorgabe = Trim(Vorgabe)
Kalenderwoche = CInt(Vorgabe)
If Kalenderwoche < KalenderwocheVonDatum(Referenzdatum) Then
Vorgabe = DatumVonKalenderwoche(Vorgabe, Year(Referenz) + 1, Fälligkeitswochentag)
Else
Vorgabe = DatumVonKalenderwoche(Vorgabe, Year(Referenz), Fälligkeitswochentag)
End If
If Date < Vorgabe Then temp = False
On Error GoTo 0
End Select
Fällig = temp
End Function
´------------------------------------------------------------
Antwort 7 von BigFlash
Hi,
uff, das sieht ja nach viel arbeit aus.
Da ich bis jetzt nur mit Formeln in Excel gearbeitet habe und noch nie mit Makros, muss ich erstmal schauen wie ich das Makro richtig einbinde.
Ich werds aber auf jedenfall ausprobieren, vielen Dank.
MfG
BigFlash
uff, das sieht ja nach viel arbeit aus.
Da ich bis jetzt nur mit Formeln in Excel gearbeitet habe und noch nie mit Makros, muss ich erstmal schauen wie ich das Makro richtig einbinde.
Ich werds aber auf jedenfall ausprobieren, vielen Dank.
MfG
BigFlash
Antwort 8 von ChatAlligator
Eintippen brauchste nicht - nur kopieren (also wenig Arbeit für dich)
Und das Einbinden ist auch nicht so schlimm:
In Excel:
Extras/Makro/Visual Basic-Editor öffnen
Rechtsklick auf Microsoft Excel Objekte
Einfügen/Modul
Es öffnet sich ein ein weißes Blatt rechts daneben, in dem die Makros hineinkopiert werden wönnen
Und schon ist die Arbeitsmappe um 3 Funktionen reicher:
DatumVonKalenderwoche
KalenderwocheVonDatum
Fällig
Gruß CA
Und das Einbinden ist auch nicht so schlimm:
In Excel:
Extras/Makro/Visual Basic-Editor öffnen
Rechtsklick auf Microsoft Excel Objekte
Einfügen/Modul
Es öffnet sich ein ein weißes Blatt rechts daneben, in dem die Makros hineinkopiert werden wönnen
Und schon ist die Arbeitsmappe um 3 Funktionen reicher:
DatumVonKalenderwoche
KalenderwocheVonDatum
Fällig
Gruß CA