Produktionszeit

[AnneWaterkant]

Revision 1:

Ich nehme mal an, dass 3 berührte Wochen (somit maximal 15 Werktage) nicht überschritten werden. Dann bekommen wir das auch ohne Zeilensplit hin:

B24: 23.11.2017 11:10:00  
C24: 28.11.2017 14:45:00

Die Wochenarbeitszeiten (Vorsicht, genau lesen!):

D1: 54:00
D2[:D21]: =D1+1
E1[:E21]: =VERWEIS(REST(ZEILE()+1;7);{0.2.6};{0.10.6,5})/24+D1

ZEILE(2:19) ausblenden (sonst nervt's!)

Und jetzt kommt die eigentliche Formel, nur noch in einer Zelle, ohne Aufteilung:

D24: 
{=SUMME(
WENNFEHLER(EXP(LN(E$1:E$21-
WENNFEHLER(EXP(LN(E$1:E$21-REST($C24-KÜRZEN(KÜRZEN($B24)/7)*7;21)));)-D$1:D$21-
WENNFEHLER(EXP(LN(REST($B24-KÜRZEN(KÜRZEN($B24)/7)*7;21)-D$1:D$21));)));))}

Das mit dem {} kennst Du ja schon von vorhin.

Anmerkung: Für jede benötigte Woche mehr (momentan sind es 3 BERÜHRTE Wochen von einem Arbeitsbeginn bis -ende) erhöhst Du 21 um jeweils 7: 

• Vor Zeile 21 entweder 7 oder 14 oder ... Zeilen einfügen
  
• Dann von D20 bis zum neuen Ende E28 oder E35 oder ... runterkopieren
  
• Dann in der Formel beide (es sind nur 2) ;21))) auf ;28))) oder ;35))) oder ... ändern.
  

Der Abzug von Feiertagen erfolgt ja immer ganz. Das geht mit einer FT-Liste, wo alle Datümer mit ihrem Gewicht 10: oder 6:30 oder 0 summiert und abgezogen werden. Habe ich mir hier aber mal vorläufig geschenkt.

Hoffentlich hast Du meine erste Lösung noch nicht ausprobiert. Falls doch, spar Dir die manuelle Arbeit oben:

Sub WerktagsArbeitsdauerVonBis() 'Konstruktionsmakro
Workbooks.Add xlWorksheet
[D:E].NumberFormat = "[h]:mm"
[D1] = "54:00"
[D2:D21] = "=R[-1]C+1"
[E1:E21] = "=LOOKUP(MOD(ROW()+1,7),{0,2,6},{0,10,6.5})/24+RC[-1]"
[2:19].EntireRow.Hidden = True
[A23:C23] = Split("lfdNr von bis")
[B24] = "11/23/2017 11:10"
[C24] = "11/28/2017 14:45"
[D24].FormulaArray = "=SUM(" & _
"IFERROR(EXP(LN(" & Chr(10) & "R1C[1]:R21C[1]-" & _
"IFERROR(EXP(LN(R1C[1]:R21C[1]-MOD(RC3-TRUNC(TRUNC(RC2)/7)*7,21))),)-" & Chr(10) & "R1C:R21C-" & _
"IFERROR(EXP(LN(MOD(RC2-TRUNC(TRUNC(RC2)/7)*7,21)-R1C:R21C)),))),))"
[B:E].Columns.AutoFit
End Sub

[AnneWaterkant]

Revision 2: mit Feiertagen und wochenübergreifend

1. als Datei (durch Konstruktionsmakro erstellt; für den, den die Hintergründe nicht so interessieren)

  171127 Produktionszeit mit Feiertagen.xlsx (Größe: 9,8 KB / Downloads: 2)

2. als Konstruktionsmakro (zum Datei-Selbsterstellen)

• Feiertage in F1:F14 (manuell einzutragen; erledigt hier aber das Konstruktionsmakro mit Testdaten vorab)
  
• ab 2 "berührbare" Wochen auszuwählen (das muss VBA zwecks "harten Tabellenaufbaus" vorher wissen!)
  
• Überstunden-Weiterberechnungs-Schichten statt uniformer Arbeitszeiten je nach Wunsch einfach vor E:E einfügbar - die Formeln sind dafür schon vorbereitet
  
• keine DÜ/bedF hinsichtlich Eingabe B:C, dass etwa FT, WE und "Bis vor Von" falsch, aber möglich sind
  

Sub AuftragsdauerVonBis() 'Konstruktionsmakro; erfordert xl2007+ wegen WENNFEHLER
Workbooks.Add xlWorksheet: ActiveSheet.Name = "T": [A2:C2] = Split("lfdNr von bis")
w = InputBox("Geben Sie eine ganze Zahl ab 2 ein! (für die max. Anzahl berührter Wochen)") * 7
[D:G].NumberFormat = "[h]:mm": [F:F].NumberFormat = "MM/DD/YYYY"
[B3] = "11/23/2017 11:10": [C3] = "11/28/2017 14:45" '2 Feiertage enth.
[B4] = "11/30/2017 11:10": [C4] = "12/05/2017 14:45"
[B5] = "11/27/2017 05:59": [C5] = "12/01/2017 12:31" '1 Feiertag enth.
'Datumsformat muss andersrum (dt. statt engl.) definiert sein bei vorangestelltem Wochentag:
[B:C].NumberFormat = "DDD DD/MM/YYYY hh:mm": Rows("2:" & w + 1).EntireRow.Insert
'Umweg über defNamen aufgrund des alten Arrayformel-Erstellungslimits "255 Zeichen in VBA":
ActiveWorkbook.Names.Add Name:="WT", RefersToR1C1:= _
Replace("=SUM(IFERROR(EXP(LN(R1C[1]:R#C[1]-" & _
"IFERROR(EXP(LN(R1C[1]:R#C[1]-MOD(RC3-TRUNC(TRUNC(RC2)/7)*7,#))),)-R1C:R#C-" & _
"IFERROR(EXP(LN(MOD(RC2-TRUNC(TRUNC(RC2)/7)*7,#)-R1C:R#C)),))),))", "#", w)
ActiveWorkbook.Names.Add Name:="FT", RefersToR1C1:= _
"=SUM((ROW(INDIRECT(TRUNC(RC2)&"":""&TRUNC(RC3)))" & _
"=TRANSPOSE(R1C6:R14C6))*TRANSPOSE(R1C7:R14C7))"
[D1] = "54:": Range("D2:D" & w) = "=R[-1]C+1"
'Ohne explizites ".FormulaR1C1" muss im xl12-Modell "+R[0]C4" statt "+RC4" referenziert werden:
Range("E1:E" & w) = "=LOOKUP(MOD(ROW()+1,7),{0,2,6},{0,10,6.5})/24+R[0]C4"
'Fr 24.11. und Mo 27.11. sind demonstrationshalber FT, so dass aus 30:05 nur 13:35 Std werden:
[F1] = "1/1/17": [F2] = "5/1/17": [F3] = "10/3/17": [F4] = "11/24/17": [F5] = "11/27/17"
[G1:G10] = "=INDEX(R1C[-2]:R7C[-2],MOD(RC[-1]-2,7)+1)-INDEX(R1C[-3]:R7C[-3],MOD(RC[-1]-2,7)+1)"
Range("2:" & w - 2).EntireRow.Hidden = True: [B:G].Columns.AutoFit 
'Select wegen FreezePanes. Außerdem: Keine Arrayformel nötig, da defNamen automatisch so rechnen:
Range("D" & w + 3).Select: ActiveWindow.FreezePanes = True: Selection.Resize(3, 1) = "=WT-FT"
End Sub

[cartinho]

Das ist wirklich eine "Wucht"
Vielen lieben Dank für diese Ausarbeitung ;)
Die Umsetzung funktioniert super.

Ganz kurze Frage, welche Parameter müsste ich ändern, um die Arbeitszeit anzupassen? Also Arbeitsbeginn und Feierabend?

Viele Grüße
cartinho

PS. Habe es selbst geändert

[RPP63]

Klicke zum Antworten bitte auf den gleichnamigen Button, NICHT auf zitieren.
Danke, Ralf

[AnneWaterkant]

Blende alle Zeilen ein.

D1 enthält mit 54: die 6: vom Montag (nämlich Tag 2 = 48: plus 6: Stunden = 54:). Das wiederholt sich in D2 mit =D1+1.
E1 enthält die Formel, die den Wochentag 10, 6:30 oder 0 Stunden lang sein lässt:
E1: =VERWEIS(REST(ZEILE()+1;7);{0.2.6};{0.10.6,5})/24+$D1

Zeile 1 ist Montag, Zeile 2 Dienstag, ... Zeile 8 dann entsprechend erneut Montag.
Im VERWEIS ist 0 Samstag und 6 Freitag (das ist so in Excel geregelt). Somit wird aus einer Woche mit Arbeitsbeginn 8:00 (D1: 56:) und den unveränderten Endzeiten 16: oder 12:30 die Formel
E1: =VERWEIS(REST(ZEILE()+1;7);{0.2.6};{0.8.4,5})/24+$D1

Vergiss die Feiertage in F nicht. Die sind falsch, weil experimentell.

In einer nächsten Revision muss ich den Beginn auf Sa statt Montag setzen. Das spielt eine Rolle beim Wochenüberlappen.

Anmerkung: Anscheinend hast Du tatsächlich das Konstruktionsmakro statt der (gleichen) Datei verwendet, wie es übrigens auch nicht angemeldete User können (großer Vorteil für solche User; geht schneller, als das Abtippen der html-Tabellen, und keine etwaige Gefahr durch Fremde oder Gäste für die Forumsdaten)! Danke schön!

[Rabe]

Hi,

zu Deiner Anmerkung die Frage eines Unwissenden:
Wie erzeuge ich so ein Konstruktionsmakro?
Mir erscheint das komplizierter, als die Tabelle manuell zu erstellen!

[AnneWaterkant]

Das ist zugegeben etwas speziell, trainiert aber den Umgang mit VBA, indem man eine anfänglich simple Aufzeichnung einfach entschlackt und sich die jeweiligen Objektzuweisungen anschaut. Dann noch etwas Sortieren im Code, weil es auch dafür eine Erstellungsreihenfolge gibt, und fertig.

Mehr als sportlicher Ehrgeiz ist es also nicht. Tabelle dauert 2 Minuten, Konstruktionsmakro 30 oder mehr, da man dabei meist auf total seltsame Ideen kommt. Da die meisten soviel Verrücktheit nicht haben, werde ich wohl damit einsam bleiben. Allerdings reichen 5 Minuten auch aus, wenn man nur die Selects rausnimmt. Das Ding soll eine Tabelle erstellen. Mehr nicht.

Bei meinem Code oben allerdings wechsle ich noch innerhalb einer Excelformel den Platzhalter # gegen die ermittelte letzte Zeile der Vergleichszeiten D:E. Das ist dann schon ein wenig mehr.

Charmant allerdings ist die Erkenntnis, dass ich mit einem durchschnittlichen "Konstruktionscode" von etwa 2 KB ca. 500 Vollanwendungen in einer einzigen 1-MB-Datei mitgeben kann - soviel, wie ich niemals in meinem Leben entwerfen kann (als Nichtinformatikerin). Das kann man, falls .ZIP nicht effizient genug sein sollte, wovon ich aber ausgehe, noch durch DurchWECHSELN häufiger und langer Befehle steigern, z.B. WorksheetFunction.Transpose(Array()) als WTA oder so, falls man für die Verwendung des Codes einen Codeerzeugungscode benutzt.

Bleibt noch anzumerken, dass eine .xls 14 KB und eine .xlsx 7 KB groß ist. Also nur eine knappe Größenordnung entfernt zum Konstruktionscode.
___________________
Am Rande: Google Docs kennt keine benannten Formeln. Das ist bekannt. Aber eine interessante Meldung:  

Die hier verwendete WENNFEHLER-Formel funktioniert in Docs als ArrayFormula nicht. Das ist die erste Inkompatibilität zu Excel, die ich beobachte, außer dem fehlenden TEXTKETTE (TEXTVERKETTEN kennt es als TEXTJOIN) und AGGREGAT.