Wie erstelle ich eine WENN DANN Excel Funktion mit Bezug auf Wochentage?

2 Antworten

Zunächst mal kannst du deine bisherige Formel so vereinfachen:

=MAX(0;H6-G6/60-8)

Jetzt brauchst du die -8 datumsabhängig abzuziehen. Leider schreibst du nicht, wo das Datum steht, ich verwende als Beispiel einfach mal A6. Dann lautet die Formel

=MAX(0;H6-G6/60-(REST(A6;7)<=1)*8)

Kleiner Fehler meinerseits (so werden die 8 Stunden nur an den Wochenenden abgezogen, du möchtest es ja umgekehrt). Die korrigierte Formel:

=MAX(0;H6-G6/60-(REST(A6;7)>1)*8)

@DeeDee07

Wow, das ging ja fix, sehr geil! Hat perfekt funktioniert, A6 war sogar richtig geraten :D

Kannst du mir noch kurz die Formel erläutern, damit ich genau verstehe, was da passiert?

@Arsonil

Die Formel
=MAX(0;H6-G6/60-8)
bedeutet: Nimm von den beiden Werten 0 und H6-G6/60-8 den größten. Falls also die Zeitberechnung kleiner als 0 ist, wird 0 verwendet, ansonsten die Rechnung.

Jetzt zu
-(REST(A6;7)>1)*8)

Da in Excel jeder Tag seit 1.1.1900 einer fortlaufenden Zahl entspricht, kann man ihn durch 7 teilen und den Rest bilden. Somit hat jeder Wochentag den gleichen Rest (Samstag=0; Sonntag=1;...;Freitag=6).
Dieser Rest wird mit >1 verglichen. Bei Samstag und Sonntag liefert er den Wert FALSCH, bei den anderen Tagen WAHR. Diese Werte entsprechen den Zahlen 0 und 1, und mit denen kann man rechnen (z.B. mit 8 multiplizieren). FALSCH*8=0; WAHR*8=8

Klingt zwar am Anfang alles recht kompliziert, jedoch kann man in Excel aus der Mathematik eine Menge rausholen. Auf jeden Fall finde ich solche Lösungen eleganter als die verschachtelten WENN-Bandwurmformeln.

@DeeDee07

Und Rest(Datum;7) ist als Beschreibung des Wochentags Gebietsschema-unabhängig, da der Wochentag in Europa, Amerika und in islamischen Ländern unterschiedlich definiert ist!.

Bei uns in Europa ist ja seit knapp 50 Jahren der Mo der 1. Wochentag, in Amerika und Israel der So und in islamischen Ländern der Sa(?)

@Iamiam

Jedoch ist

Rest(12.12.2016;7)=2

, heute ist aber Montag. Wenn du also Montag mit 0 haben möchtest, und Sonntag mit 7, dann musst du

"REST(Datum-2;7)"

berechnen. Genau wegen der Gebietsabhängigkeit hat die Funktion Wochentag einen zweiten optionalen Parameter [1].

=WOCHENTAG(Datum;3)

liefert in dem Fall IMMER Montag = 0 und Sonntag = 6 als Ergebnis - jedoch nur vor dem 29.02.1900. Siehe [2]

[1] https://support.office.com/de-de/article/WOCHENTAG-Funktion-60e44483-2ed1-439f-8bd0-e404c190949a

[2] http://www.at-exceltraining.de/index.php/fragen-a-anworten-zu-excel/1-datum-und-uhrzeit/33-datumsprobleme-in-excel.html

pro Forma nicht als Kommentar, sondern als extra AW (Damit Du DeeDee eine Hilfreichste geben kannst):

Datum ist ja eine fortlaufende Zahl seit 1.1.1900. Teilst du diese Zahl durch 7, so entsteht ein Wochenrhythmus:

Zufällig ist Rest("10.12.16";7) als Sa =0, So=1 usw. bis Fr=6. Und zufällig deshalb

ist am Wochenende =Rest(Datum;7)<2 oder <=1 (Wenn Zeiten auch eine Rolle spielen, bevorzuge ich <2, weil dann die Zählung bis So 23:59:59,9 geht)

In der ersten Formel wird also *8 genommen, wenn Sa, So WAHR sind

bzw in der zweiten , wenn

=Rest(Datum;7)>=2 (auch hier genauer, wenn Zeiten integriert sind, als das >1, das für glatte Datümer wie hier aber gültig ist).

Du kannst mit Rest sogar über Mitternacht rechnen (was Du sicher auch noch brauchst!):

=Rest(Ende-Anfang;1) summiert die Zeiten vor und nach Mitternacht (bis zu knapp1Tag Differenz), also

=Rest("5:00"-"20:00";1) ergibt "9:00" (sofern Du Dich an die xl-Zeitdarstellung hältst, also DEN TAG ALS EINHEIT 1 setzt,

und nicht die Industriestunde, da müsstest du dann zB

=Rest(Ende/14-Anfang/24;1)*24 rechnen. oder

=Rest(Ende-Anfang;24)

Zu den Gänsefüßchen: xl erkennt Darstellungen in integrierten Datum- und/oder Zeitformaten diese als Datümer/Zeiten, in manchen Zusammenhängen muss man aber "Datum und/oderZeit"*1 nehmen, damit es nicht als Text abgetan wird. Sicherer ist es, das IMMER *1 zu nehmen!

Noch zum Verständnis von Rest(): xl zählt dabei immer entlang des Zahlenstrahls AUFSTEIGEND, der Rest von -0,25 ist also +0,75, nämlich von -1 aus gerechnet. (Die Funktion ergänzt sich so widerspruchsfrei mit Ganzzahl, das in der gleichen Richtung zählt).

Was möchtest Du wissen?