Hallo MaryJo,
ohne Hilfsspalten ist das leider nicht möglich, die dafür nötige Formel ist so lang, dass sie Excel nicht mehr verarbeiten kann. Falls du dich für Hilfsspalten entscheidest, wo du die bereinigten Datumsangaben berechnest, könnte deine Formel in [i]C1[/i] so
[b]=WENN(WOCHENTAG(A1;2)>5;ABRUNDEN(A1;0)+1/24*7+(8-WOCHENTAG(A1;2));ABRUNDEN(A1;0)+WENN(STUNDE(A1)>=17;1/24*17;WENN(STUNDE(A1)<7;1/24*7;A1-ABRUNDEN(A1;0))))[/b] und in [i]D1[/i] so
[b]=WENN(WOCHENTAG(B1;2)>5;ABRUNDEN(B1;0)+1/24*17-(WOCHENTAG(B1;2)-5);ABRUNDEN(B1;0)+WENN(STUNDE(B1)>=17;1/24*17;WENN(STUNDE(B1)<7;1/24*7;B1-ABRUNDEN(B1;0))))[/b] aussehen. Beides als Datum formatiert. Dann ließen sich in [i]E1[/i] die Std. berechnen: [b]=WENN(D1<C1;0;WENN(ABRUNDEN(D1;0)=ABRUNDEN(C1;0);D1-C1;D1-(ABRUNDEN(D1;0)+1/24*7)+(ABRUNDEN(C1;0)+1/24*17-C1)))[/b]
Das mag für deinen Fall ausreichen. So richtig zufriedenstellend ist das aber nicht, zumal die Formeln für Dritte immer noch recht unübersichtlich sind. Einfacher und korrekter wäre hier eine Benutzerformel über VBA, die viel mehr Möglichkeiten bietet. Gib dazu in einem Standardmodul den folgenden Code ein:
[code]Function Arbeitsstunden(FirstDay As Date, LastDay As Date, BeginOfWork As String, EndOfWork As String) As Date
Dim std As Date
If FirstDay < Int(FirstDay) + TimeValue(BeginOfWork) Then FirstDay = Int(FirstDay) + TimeValue(BeginOfWork)
If LastDay > Int(LastDay) + TimeValue(EndOfWork) Then LastDay = Int(LastDay) + TimeValue(EndOfWork)
If FirstDay > Int(FirstDay) + TimeValue(EndOfWork) Then FirstDay = Int(FirstDay) + 1 + TimeValue(BeginOfWork)
If LastDay < Int(LastDay) + TimeValue(BeginOfWork) Then LastDay = Int(LastDay) - 1 + TimeValue(EndOfWork)
If Weekday(FirstDay, vbMonday) > 5 Then FirstDay = Int(FirstDay) + 8 - Weekday(FirstDay, vbMonday) + TimeValue(BeginOfWork)
If Weekday(LastDay, vbMonday) > 5 Then LastDay = Int(LastDay) - (Weekday(LastDay, vbMonday) - 5) + TimeValue(EndOfWork)
If Int(FirstDay) = Int(LastDay) Then
Arbeitsstunden = LastDay - FirstDay
Else
std = Int(FirstDay) + TimeValue(EndOfWork) - FirstDay + LastDay - Int(LastDay) - TimeValue(BeginOfWork)
If LastDay > FirstDay + 1 Then
std = std + (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * (Int(LastDay) - Int(FirstDay) - 1)
std = std - Int((LastDay - FirstDay) / 7) * (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * 2
If Weekday(LastDay, vbMonday) < Weekday(FirstDay, vbMonday) Then std = std - (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * 2
End If
Arbeitsstunden = std
End If
End Function[/code]Damit bist du nun nicht nur die leidigen Hilfsspalten los sondern sogar in der Lage die Stunden auch für mehrere Tage anzuzeigen oder Arbeitsbeginn und -ende schneller neu zu definieren. Die Formel in Excel lautet dann schlicht:
[b]=arbeitsstunden(A1;B1;"7:00";"17:00")[/b]
Sollter der Fehler [i]#WERT![/i] ausgegeben werden, bedeutet das, dass der Zieltag (B1) selbst im bereinigten Zustand noch vor dem Starttag (A1) liegt. Eine Berechnung ist hier dann unsinnig. Den Fehler kannst du vermeiden indem du noch ein WENNFEHLER(...;0) drumrum baust. Probiers aber lieber erstmal ohne, damit du ein Gefühl für das jeweils richtige Ergebnis bekommst.
Gruß Mr. K.