Hier ein Konstruktionsmakro
13.12.2017 22:45:30
lupo1
Sub AuftragsdauerVonBis() 'Konstruktionsmakro; erfordert xl2007+ wegen WENNFEHLER
Workbooks.Add xlWorksheet: ActiveSheet.Name = "T": [A2:C2] = Split("lfdNr von bis")
w = 10 * 7
[D:G].NumberFormat = "[h]:mm": [F:F].NumberFormat = "MM/DD/YYYY"
[B3] = "12/01/2017 04:00": [C3] = "12/24/2017 07:00"
[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 defN aufgrund des alten Arrayformel-Erstellungslimits 256 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] = "56:": 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},{0,8})/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 defN diese automatisch _
erstellen:
Range("D" & w + 3).Select: ActiveWindow.FreezePanes = True: Selection.Resize(3, 1) = "=WT-FT"
End Sub
E1[:E70]: =VERWEIS(REST(ZEILE()+1;7);{0.2};{0.8})/24+$D1 wird für Sa und So mit ebenfalls 8 statt 0 Stunden zu
E1[:E70]: =VERWEIS(REST(ZEILE()+1;7);{0.2};{8.8})/24+$D1
Ab F1 stehen noch manuell Feiertage, falls welche in den Bereich fallen und zu berücksichtigen sind.