Excel-Formel gesucht (Formel für Kalendertage und Werktage, bzw. die Ermittlung der Differenz)?
Hallo zusammen,
ich bin bereits seit 4Tagen dabei eine Lösung für mein kleines Problem im Bereich Lagerlogistik zu finden, komme aber leider nicht auf die Antwort. Eventuell gibt es ja hier einen Excel-Spezi der meine Frage versteht und bereit ist mir zu helfen.
Hier die ein Beispiel für die Ausgangssituation:
Anliefertag: 01.01.2017
Abholtag: 31.01.2017
Gebühren:
Die ersten 12ARBEITSTage = Gebührenfrei.
Ab dem 1.ten Gebührenpflichtigen KALENDERTag bis zum 14. Gebührenpflichtigen KALENDERTAG = EUR 10,-/KALENDERTag
Ab dem 15.Gebührenpflichtigen KALENDERTag = EUR 20,-/KALENDERTag.
Ich habe bereits die Feiertage separat hinterlegt und nominiert, sodass ich durchaus auf die Anzahl der Arbeits- und Kalendertage zwischen dem 01.01.2017 und 31.01.2017 komme.
Allerdings schaffe ich es nicht, mittels Formel auszurechnen, welches der erste gebührenpflichtige Tag ist. Darüber hinaus muss ich dann ja noch eingrenzen, wie lange man 10EUR abrechnet und ab wann 20EUR.
Am Ende des Tages würde ich gerne das Eingangs- und das Ausgangsdatum in die Tabelle eintragen und Excel soll mir dann sagen, wann der erste gebührenpflichtige Tag ist und wie hoch die Gebühr ist.
Entschuldigt bitte, falls diese Frage sehr kompliziert formuliert ist.
So oder so... Vielen Dank im voraus!
LG Christopherus
5 Antworten
So ich versuche mal zu helfen. Ich bin mir nicht sicher ob ich die Frage richtig verstanden habe aber ich gebe mein bestes.
Also du möchtest im Prinzip die Differenz aus Anlieferungs- und Abholtermin haben sehe ich das richtig? Wenn die Differenz wie in dem Fall dann 30 Tage Betrüge, dann wären 1-14. Kalendertag 15€ und 14-30. 20€. soweit auch richtig?
Das wäre dann ganz einfach. Die beiden Zellen in denen das Datum steht müssen auch als Datum formatiert werden (Sagen wir mal Zelle C2 für Anlieferungsdatum und D2 Abholtermin). Dann musst du eine 3. Zelle einbauen (Bspw. E2) und dort die Formel: =D2-C2 eingeben (sofern D2>C2).
Dann brauchst du noch eine 4. Zelle (sagen wir F2). Dort muss dann hin: =WENN(E2<=12;0;WENN(E2<=14;(E2-12)*10;(E2-14)*20+(14-12)*10)) --> damit erhältst du die Summe der anfallenden Gebühren (bis inklusive Tag 12 keine Gebühren; ab Tag 13 10€ gebühren bis inklusive Tag 14; ab tag 15 20€ Gebühren)
Dann brauchst du noch eine 5. Zelle (sagen wir G2): dort gibst du dann: =C2+12 (ab diesem Tag beginnt die erste Gebühr i.H.v 10€)
In Zelle 6 kannst du dann noch angeben: =C2+14 (ab diesem Tag 20€ Gebühr)
Ich hoffe das war hilfreich :)
Hallo Bibiwi,
Unter Anderem möchte ich die Differenz aus Anlieferungs- und Abholtermin haben. Ja. Dieses Ergebnis möchte ich zum einen in "Kalendertagen" und auch in "Arbeitstagen" ausgewiesen haben.
Man beachte bitte, dass der Anliefertag und Ausliefertag noch zählen. Sprich, wird eine Ware an einem Montag angeliefert und Freitag ausgelagert, müssen alle 5Tage berechnet werden. (Abzüglich der freien Zeit).
Es sind eigentlich mehrere Annahmen. Aber ich dachte die wären
unwesentlich für meine Frage. Ich glaube diese sind aber doch
mitentscheident.
Wir haben nämlich mehrere "Wenns". Es muss zwischen der Herkunft der
Ware (Südafrika und Chile), zwischen Lagergeld und Containermiete und
zwischen der Dauer (freizeit, 1-14Tage und 15.Tag aufwärts)
unterschieden werden.
Darf ich nochmal alle "Annahmen" bzw. "Voraussetzungen" erläutern:
**************************
Wenn die Ware aus Südafrika (ZA) kommt, dann gelten folgende Regeln:
Containermiete = 12Arbeitstage frei
Ab 1.Kalendertag nach der freien Zeit, bis zum 14.Kalendertag -> 10EUR/Kalendertag.
Ab 15.Kalendertag -> 20EUR/Kalendertag
Beispiel:
Eingang aus Südafrika (ZA) = 01.01.2017
Ausgang aus Lager= 28.02.2017
Bedeutet also:
Mietfrei bis: 17.01.2017 (12 Kalendertage ab 01.01.2017)
Ab 18.01.2017 bis 31.01.2017 fallen EUR 10,-/Kalendertag an. Also EUR 140,-
Ab 01.02.2017 bis 28.02.2017 fallen EUR 20,-/Kalendertag an. Also EUR 560,-.
--------------------
Wenn die Ware aus Chile (CL) kmmt, dann gelten folgende Regeln:
Containermiete = 10Arbeitstage frei
Ab 1.Kalendertag nach der freien Zeit, bis zum 30.Kalendertag -> 30EUR/Tag.
Dannach, also ab Tag 31 nach der freien Zeit -> EUR 50/Kalendertag
---------------
Für beide Destinationen (ZA und CL) gelten allerdings die gleichen Lagergeldregelungen:
Lagergeld= 10Kalendertage -> Kostenlos
Ab Kalendertag 11 -> EUR 1,80/Tag
Wir wollen also die Herkunft (ZA oder CL), das Eingangsdatum und das
Ausgangsdatum eingeben und die Kosten sollen jeweils passend errechnet
werden.
Sind das eventuell zu viele Annahmen für Excel? (Geht das überhaupt?!)
Entschuldigt bitte diese etwas undurchsichtige Fragestellung.
Und vielen Dank für die Unterstützung!!!
Hast Du in Deinen Feiertagen auch die Nichtarbeitstage drin, 2017 wäre das zB gleich der 1.1, der 24.12. (wird der als Feiertag gerechnet?) sowie (ebenso?) der 31.12..
Solche Doppelungen schiebst Du am besten aus der Feiertagsliste raus, sonst könnte was doppelt gezählt werden.
Ich hab die Antworten im Einzelnen noch nicht nachvollzogen und hab auch im Moment dringenderes zu tun, wenn was nicht funktioniert dort, könnte ich mich der Sache nochmal annehmen, dazu musst du aber hier in dieser AW einen Kommentar abgeben, sonst sehe ich das nicht.
Also... Ich bin gerade dabei alles mal durchzugehen... Ist schwieriger als ich dachte.
Die Formeln von Britzcontrol sehen gut aus. Wenn ich diese aber in meine Tabelle einsetze (natürlich passend auf meine Tabelle was die Zellen betrifft), passt irgendetwas nicht.
Es sind eigentlich mehrere Annahmen. Aber ich dachte die wären unwesentlich für meine Frage. Ich glaube diese sind aber doch mitentscheident.
Wir haben nämlich mehrere "Wenns". Es muss zwischen der Herkunft der Ware (Südafrika und Chile), zwischen Lagergeld und Containermiete und zwischen der Dauer (freizeit, 1-14Tage und 15.Tag aufwärts) unterschieden werden.
Darf ich nochmal alle "Annahmen" bzw. "Voraussetzungen" erläutern:
**************************
Wenn die Ware aus Südafrika (ZA) kommt, dann gelten folgende Regeln:
Containermiete = 12Arbeitstage frei
Ab 1.Kalendertag nach der freien Zeit, bis zum 14.Kalendertag -> 10EUR/Kalendertag.
Ab 15.Kalendertag -> 20EUR/Kalendertag
Beispiel:
Eingang aus Südafrika (ZA) = 01.01.2017
Ausgang aus Lager= 28.02.2017
Bedeutet also:
Mietfrei bis: 17.01.2017 (12 Kalendertage ab 01.01.2017)
Ab 18.01.2017 bis 31.01.2017 fallen EUR 10,-/Kalendertag an. Also EUR 140,-
Ab 01.02.2017 bis 28.02.2017 fallen EUR 20,-/Kalendertag an. Also EUR 560,-.
--------------------
Wenn die Ware aus Chile (CL) kmmt, dann gelten folgende Regeln:
Containermiete = 10Arbeitstage frei
Ab 1.Kalendertag nach der freien Zeit, bis zum 30.Kalendertag -> 30EUR/Tag.
Dannach, also ab Tag 31 nach der freien Zeit -> EUR 50/Kalendertag
---------------
Für beide Destinationen (ZA und CL) gelten allerdings die gleichen Lagergeldregelungen:
Lagergeld= 10Kalendertage -> Kostenlos
Ab Kalendertag 11 -> EUR 1,80/Tag
Wir wollen also die Herkunft (ZA oder CL), das Eingangsdatum und das Ausgangsdatum eingeben und die Kosten sollen jeweils passend errechnet werden.
Sind das eventuell zu viele Annahmen für Excel? (Geht das überhaupt?!)
Entschuldigt bitte diese etwas undurchsichtige Fragestellung.
Und vielen Dank für die Unterstützung!!!
A1: Startdatum
B1: Enddatum
Spalte C: Hinterlegte Feiertage
Formel für letzten Tag der 12 Arbeitstage ab Startdatum:
=ARBEITSTAG(A1;12;C:C)
(Der erste Gebührenpflichtige Tag wäre obige Formel +1)
Für die Anzahl der gebührenpflichtigen Tage ziehst du das oben ermittelte Zwischendatum vom Enddatum ab, also:
=B1-ARBEITSTAG(A1;12;C:C)
Achso, wegen der Gebühr:
Die Formel =B1-ARBEITSTAG(A1;12;C:C) steht im Beispiel in B2
Die folgende Formel berechnet die Gebühr:
=MAX(0;B2-14)*10+B2*10
Hier mal nach MEINEM Verständnis.
Arbeitstage = Mo-Fr, Wochenende nicht in der gebührenfrei-Berechnung,
Achtung: Frei und Feiertage werden hier nicht berücksichtigt.
Mal so als Anregung, mach was draus.
Gruß aus Berlin
Hallo Britzcontrol,
Also... Ich bin gerade dabei alles mal durchzugehen... Ist schwieriger als ich dachte.
Die Formeln von Britzcontrol sehen gut aus. Wenn ich diese aber in
meine Tabelle einsetze (natürlich passend auf meine Tabelle was die
Zellen betrifft), passt irgendetwas nicht.
Es sind eigentlich mehrere Annahmen. Aber ich dachte die wären
unwesentlich für meine Frage. Ich glaube diese sind aber doch
mitentscheident.
Wir haben nämlich mehrere "Wenns". Es muss zwischen der Herkunft der
Ware (Südafrika und Chile), zwischen Lagergeld und Containermiete und
zwischen der Dauer (freizeit, 1-14Tage und 15.Tag aufwärts)
unterschieden werden.
Darf ich nochmal alle "Annahmen" bzw. "Voraussetzungen" erläutern:
**************************
Wenn die Ware aus Südafrika (ZA) kommt, dann gelten folgende Regeln:
Containermiete = 12Arbeitstage frei
Ab 1.Kalendertag nach der freien Zeit, bis zum 14.Kalendertag -> 10EUR/Kalendertag.
Ab 15.Kalendertag -> 20EUR/Kalendertag
Beispiel:
Eingang aus Südafrika (ZA) = 01.01.2017
Ausgang aus Lager= 28.02.2017
Bedeutet also:
Mietfrei bis: 17.01.2017 (12 Kalendertage ab 01.01.2017)
Ab 18.01.2017 bis 31.01.2017 fallen EUR 10,-/Kalendertag an. Also EUR 140,-
Ab 01.02.2017 bis 28.02.2017 fallen EUR 20,-/Kalendertag an. Also EUR 560,-.
--------------------
Wenn die Ware aus Chile (CL) kmmt, dann gelten folgende Regeln:
Containermiete = 10Arbeitstage frei
Ab 1.Kalendertag nach der freien Zeit, bis zum 30.Kalendertag -> 30EUR/Tag.
Dannach, also ab Tag 31 nach der freien Zeit -> EUR 50/Kalendertag
---------------
Für beide Destinationen (ZA und CL) gelten allerdings die gleichen Lagergeldregelungen:
Lagergeld= 10Kalendertage -> Kostenlos
Ab Kalendertag 11 -> EUR 1,80/Tag
Wir wollen also die Herkunft (ZA oder CL), das Eingangsdatum und das
Ausgangsdatum eingeben und die Kosten sollen jeweils passend errechnet
werden.
Sind das eventuell zu viele Annahmen für Excel? (Geht das überhaupt?!)
Entschuldigt bitte diese etwas undurchsichtige Fragestellung.
Und vielen Dank für die Unterstützung!!!
@ DeeDee
Danke für das "Danke".
B7 ist das Ergebnis des Nachdenkens über den Unterschied zwischen Arbeits- und Werktag (Mo-Fr; Mo-So).
B11 bzw. B12 wird dann benötigt, wenn die Lagerzeit mehr als 26 Tage beträgt, nämlich zur Berechnung der "20.-€"-Zeit (siehe B15).
Gruß aus Berlin
@ Fragesteller Chr.
da kommen ja noch so einige Informationen.
Das liest sich, als würde die xl-Datei zu kommerziellen Zwecken verwandt.
Wenn dem so ist, sehe ich es nicht als meine Aufgabe an, der Wirtschaft (berufliche Programmierer) Aufgaben wegzunehmen. Vielmehr sollte man bei einem solchen Bedürfnis einen Auftrag vergeben, schließlich will der Auftragnehmer sowohl Arbeit haben als auch Steuern an den Staat bezahlen.
PS.: Für eine Lösung wäre die eingestellte Bildschirmkopie unbrauchbar, fehlen doch alle in dieser Antwort gegebenen Kriterien als auch die Spalten- und Zeilenköpfe.
Gruß aus Berlin
DH! Anschaulich und ausführlich.
Dennoch eine kleine Korrektur. Das Datum in B11 (und folglich auch B12) darf nicht hinter dem Abholtag (B2) liegen.
Die Angabe in B7 verwirrt mich ein wenig. Ich bin auch nicht sicher, ob die WE-Regelung SO gedacht war (also ob ein WE nach den 12 Tagen nicht mitgezählt werden soll).