700 Aufrufe
Gefragt in Tabellenkalkulation von mamamia Mitglied (348 Punkte)

Hallo, ich muss die Abweichung der Durchlaufzeit berechnen. Aber in meiner Formel stimmt etwas nicht. Es ist in Spalte "Ausgang Ist zu Soll (Tage)" folgende Formel eingefügt: =WENN(CJ45=1;"";NETTOARBEITSTAGE(CJ45;CN45;Freier_Tag)+WENN(CJ45>CN45;1;-1)-(CK45-CO45))

     CJ               CK         CN          CO

Ausgang Datum Ist Ausgang
Uhrzeit Ist
Ausgang Datum
Soll
Ausgang
Uhrzeit
Soll
Ausgang Ist zu Soll (Tage)
01.05.2021 6:48 30.04.2021 10:30 0,2
01.05.2021 3:45 30.04.2021 0:30 -0,1
29.04.2021 21:56 27.04.2021 11:17 -2,4
30.04.2021 23:00 30.04.2021 4:30 -0,8

In der 1. Zeile ist eine positive Zahl?? und Zeile 2 ist auch nicht richtig.

In der Datei habe ich ein weiteres Datenblatt

Arbeitstag Wochenende Freier Tag
29.Apr 21
30.Apr 21
01.Mai 21 01.Mai 21
02.Mai 21

Ich denke, das hängt mit dem 01. Mai zusammen, aber ich hab keine Ahnung wie ich das richtig stellen kann.

Vielen Dank und

viele Grüße

MamaMia

8 Antworten

0 Punkte
Beantwortet von steffen2 Experte (6.4k Punkte)
was ist "Freier_Tag"

Ist das eine Liste mit allen Feiertagen für dein Bundesland?
0 Punkte
Beantwortet von mamamia Mitglied (348 Punkte)
Ja, das ist eine Liste mit den Feiertagen.

Viele Grüße

MamaMia
0 Punkte
Beantwortet von flupo Profi (17.7k Punkte)

Probleme mit Zeitberechnungen kann man vermeiden wenn man weiß, wie Excel Datums- und Zeitwerte umsetzt.

Das Datum ist die Zahl der Tage ab dem 01.01.1900 gezählt. Die Uhrzeit sind Nachkommastellen, wobei der ganze Tag 1 ergibt. 12 Uhr mittags ist also 0,5

Der Zeitpunkt 1.5.21 06:48 Uhr wird so für Excel zu 44317,28333 (Datum,Uhrzeit).

Mit diesem Wissen kannst du dir zwei Hilfsspalten bauen (die man später ausblenden kann) die die Spalten CJ und CK sowie CN und CO addieren. Mit den Werten in diesen Spalten kannst du dann rechnen, ohne dass Tageswechsel ein Problem sind (solange das Ergebnis positiv ist).

Wenn du mit den Hilfsspalten Ausgang ist minus Ausgang soll rechnest, brauchst du die Ergebnisspalte nur als Zahl mit einer Nachkommastelle zu formatieren, um das korrekte Ergebnis zu erhalten (zumindest erstmal ohne Berücksichtigung der Wochenenden und Feiertage):

0,8
1,1
2,4
0,8

Ein bisschen schwierig ist dann aber, dass der 1.5.21 zwar als Feiertag berücksichtigt werden soll, es aber dennoch Werte mit diesem Datum gibt. Um das aufzulösen, muss es Regeln geben, wie damit umgegangen werden soll (wie sieht hier ein korrektes Ergebnis aus?).

Warum bei dir negative Werte rauskommen sollen wenn der Durchlauf länger dauert, als geplant, ist mir aber rätselhaft.

Gruß Flupo

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo MamaMia,

was willst du eigentlich berechnen? Nur den Unterschied zwischen Soll-Zeit und Ist-Zeit?

Hast du deine Formel mal in ihre Einzelteile zerlegt? Ich habe mal deine Beispieltabelle nachgebaut (ohne natürlich die genaue Formatierung zu kennen).

Hier mal die Ergebnisse der 1. Zeile mit Daten:

Deine Nettoarbeitstage-Formel ergibt -1, der Wenn-Teil ergibt 1 und die Subtraktion der Zeiten ergibt -0,1542 (als Zahl formatiert, negative Zeiten können in Excel nicht dargestellt werden). Somit ergibt deine Formel -1+1-(-0,1542), insgesamt eben die 0,2 (gerundet).

Dies entspricht als Uhrzeit 3:42, also 3 Stunden 42 Minuten, was dem Unterschied zwischen der Soll-Zeit (10:30 Uhr) und der Ist-Zeit (6:48 Uhr) entspricht (ohne Berücksichtigung des Vorzeichens und unterschiedlicher Tage).

Ziehst du z.B. die Ist-Zeit in dem Format 01.05.2021  06:48:00 von der Soll-Zeit 30.04.2021  10:30:00 ab, so bekommst du als Ergebnis 20:18, also 20 Stunden und 18 Minuten (hier mit Berücksichtung der unterschiedlichen Tage).

In der Formel NETTOARBEITSTAGE hast du als Ausgangsdatum auf die Zelle CJ verwiesen (deine Spalte mit der Bezeichnung Ausgangsdatum). Das ist aber falsch! Hier ist das Datum anzugeben, an dem der zu berechnende Intervall beginnt, bei dir also die Spalte CN (die Soll-Zeit). Und als Enddatum musst du das Ende des zu berechnenden Zeitraums eingeben, also die Spalte CJ (die tatsächliche Ist-Zeit). Da du die Zeiten vertauscht hast, bekommst du negative Tage, wenn der Ist-Tag nach dem dem Soll-Tag liegt und umgekehrt.

Gruß

M.O.
0 Punkte
Beantwortet von mamamia Mitglied (348 Punkte)

Hallo M.O., ich muss die Durchlaufzeit der Produkte berechnen. D.h. es ist eine Durchlaufvorgabe (Ausgang Soll) und wie das Produkt tatsächlich durchgelaufen ist (Ausgang Ist). Nun muss ich die Abweichung berechnen, ob es optimal oder verspätet abgeliefert wird. Wenn es verspätet abgeliefert wird, ist die Berechnung um wieviel Tage (Abweichung negativ) wenn es optimal abgeliefert (Vorgabeplan ist eingehalten) ist die Berechnung positiv. Wochenende und Feiertage müssen natürlich auch berücksichtigt werden.

Wenn ich, nach deinem Vorschlag, die Spalten vertausche, erhalte ich -0,2. Das Ergebnis ist wird zwar negativ, aber ist nicht ganz richtig. Zu spät raus sind 20 Stunden sollte -0,8 ergeben. Wenn wegen des Feiertages nur bis 30.04.21 24:00 Uhr gerechnet würde, wären 13,5 Std. zu spät - sollte 0,6 ergeben.

=WENN(CJ349=1;"";NETTOARBEITSTAGE(CN349;CJ349;Freier_Tag)+WENN(CN349>CJ349;1;-1)-(CO349-CK349))

Aussgang Datum Ist Ausgang
Uhrzeit Ist
Ausgang Datum
Soll
Ausgang
Uhrzeit
Soll
Ausgang Ist zu Soll (Tage)
01.05.2021 6:48 30.04.2021 10:30 -0,2
Keine Ahnung wo hier der Fehler liegt.
Viele Grüße
MamaMia

0 Punkte
Beantwortet von flupo Profi (17.7k Punkte)
Nochmal: Die Idee, hier mit NETTOARBEITSTAGE() zu rechnen, ist nicht zielführend wenn dennoch Zeiten an Feiertagen berücksichtigt werden sollen.
Wie kann das Produkt am 1.5. 6:48 Uhr fertig werden, wenn nicht gearbeitet wird?

Wenn ich das richtig verstehe, sollen die Feiertage und Wochenenden ja nur nicht mit eingerechnet werden, wenn die Überziehung den ganzen Tag abdeckt. Es dürfte dann keinen Unterschied machen, ob der Vorgang am 1.5. oder am 3.5. abgeschlossen wird.

Genau das meinte ich damit, dass es Regeln geben muss, wie mit Feiertagen (und WE) umgegangen werden muss.

Gruß Flupo
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo MamaMia,

der Fehler liegt darin, dass die Formel nicht für das geeignet ist, was du berechnen willst.

1. Das Ergebnis -0,2 (gerundet) ist nach deiner Formel richtig

  Ich habe dir ja in meinem ersten Post dargelegt, was die Ergebnisse der Einzelteile deiner Formel sind. im  Endeffekt wird bei deiner Formel nur die Ist-Uhrzeit 6:48 Uhr von der Soll-Uhrzeit 10:30 Uhr abgezogen, d.h. nach deiner Formel war der Ausgang 3 Stunden und 48 Minuten früher als geplant. Daher die -0,2.

2. Auf die Verspätung von 20 Stunden und 18 Minuten kann deine Formel nicht kommen, da unter Berücksichtigung des freien Tags 0 Tage Verspätung vorhanden sind. Hier müsstest du z.B. (um bei den Zeilenagaben deiner ursprünglichen Frage zu bleiben) wie folgt rechnen: =CJ45+CK45-CN45-CO45
Es werden hier aber alle Tage berechnet, auch freie Tage.

3. Ebenso wenig kann deine Formel die Zeit zwischen Soll-Ausgang und Mitternacht berechnen, wenn der nächste Tag z.B. ein freier Tag ist. Das hast du in deiner Formel nirgends stehen.

Du kannst in der Funktion NETTOARBEITSTAGE ja leider auch nicht mir Uhrzeiten rechnen, da hier nur volle Tage ausgegeben werden.

Ich weiß nicht, ob du mit einer Formellösung zu dem von dir gewünschten Ergebnis kommst.

Gruß

M.O.
0 Punkte
Beantwortet von mamamia Mitglied (348 Punkte)
Vielen Dank für eure Informationen. Ich habe es mal nach Flupo's Vorschlag, mit weiteren Spalten, versucht, bin aber noch zu keiner Lösung gekommen.

Viele Grüße

MamaMia
...