Zeiterfassung mit "wenn;und;dann;sonst">als;<als falsch durchdacht

[thoju]

Hallo zusammen,
ich bin ein absoluter Excel-Laie und versuche für die Arbeit eine Zeiterfassungstabelle zu erstellen.
Leider gibt es dabei unglaublich viele Eventualitäten, an denen es bei mir mit der Umsetzung scheitert.

C7= Arbeitszeitbeginn (Montag ab 00:00 Uhr)  D7=Arbeitszeitende (Montag bis 24:00 Uhr)
C8= Arbeitszeitbeginn (Dienstag ab 00:00 Uhr) D8= Arbeitszeitende (Dienstag bis 24:00 Uhr)

In T7+T8 sollen die Stunden, die es als Nachtzuschlag mit 25% gib

Nachtzuschläge erhält der Monteuer in den Zeiten zwischen 20:00 Uhr bis 06:00 Uhr morgens.

Eventualitäten: Da die Monteuer auch mal durchackern, wenn es um Projekte geht, dann arbeiten sie auch mal weitaus länger als 8 Stunden.
Somit würde ich gerne alles (so unwahrscheinlich es auch sein mag) mit einberechnet haben. Der Beginn und das Ende sollen flexibel sein.
Ich habe mal in anderen Zellen Musterformeln zusammengebastelt, um zu schauen ob das überhaupt funktioniert. Diese sehen wie folgt aus:

1. =WENN(UND((C7*24)<6>=0;(D7*24)<=6);(D7-C7)*24)                 Beginn 00:00 - 05:59       Ende 00:01 - 06:00
   
2. =WENN(UND((C7*24)<6>=0;(D7*24)>6<20);(6-(C7*24)))             Beginn 00:00 - 05:59       Ende 06:01 - 19:59
   
3. =WENN((UND((C7*24)<20>=6;(D7*24)<=20>=6));0)                    Beginn 06:00 - 19:59       Ende 06:00 - 20:00
   
4. =WENN(UND((C7*24)<20>=6;(D7*24)>20<=24);(D7*24-20))       Beginn 06:00 - 19:59       Ende 20:01 - 24:00
   
5. =WENN(UND((C7*24)<24>=20;((D7*24)<24>20));(D7-C7)*24)     Beginn 20:00 - 23:59       Ende 20:01 - 23:59
   

Einzeln funktionieren die Formeln soweit ich gesehen habe, aber irgendwie überschneiden sie sich. Gebe ich zum Beispiel bei Beginn 21:00 Uhr und bei Ende 23:00 Uhr ein, dann verändert sich nicht nur Ergebnis 5 sonder auch die Ergebnisse aus Formel Nr. 3 und 4.
So lauten die Ergebnisse
3. 0,00
4. 3
5. 2 (dieser Wert wäre ja als Einziger für mich richtig).

Nun die Frage, wo liegt mein Denkfehler? Wie bekomme ich alle Formeln zusammen als eine Formel - und dann so, dass alle Eventualitäten abgedeckt, aber nicht mehrfach (wie es scheinbar momentan der Fall ist) vorhanden sind?

Mein Gedanke war Tagesübergreifende Stunden (z. B. von 22:00 Uhr bis 04:00 Uhr) durch die zwei verschiedenen Zeilen zu lösen
(also 22:00 - 24:00 Uhr und von 00:00 - 04:00 Uhr) und diese dann zum Schluss zu addieren.

Ich hoffe mir kann jemand helfen.


Liebe Grüße, Jule

[IchBinIch]

Hallo Jule,

stell doch bitte einmal eine Beispieldatei ein, dann lassen sich sich die Zusammenhänge viel
einfacher nachvollziehen. 

Gruß 
Ich

[WillWissen]

Hi Jule,

meinst du so?

Tabelle1

	A	B	C	D
2	Beginn	Ende	Pause	Arbeitszeit
3	21:00	23:00	00:30	01:30
4	21:00	23:00		02:00
5	21:00	04:00	00:45	06:15
6	21:00	04:00		07:00

Formeln der Tabelle

Zelle Formel D3 =REST(B3-A3-C3;1)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[thoju]

[quote pid='54041' dateline='1475388384']

Hallo WillWissen,
lieben Dank, aber diese Daten habe ich bereits. Ich versuche gleich mal einen Auszug meiner Tabelle zu schicken (wenn ich das hinbekomme).

LG, Jule

[/quote]

[thoju]

  Stunden neu überdacht.xlsx (Größe: 20,26 KB / Downloads: 12)

[Jockel]

Hallo, viele deiner Ergebnisse sind keine (Uhr)Zeiten {Zahlen}, weil sie in Hochkommata stehen...

[thoju]

Hallo, viele deiner Ergebnisse sind keine (Uhr)Zeiten {Zahlen}, weil sie in Hochkommata stehen...

Hallo Jockel,
wie gesagt, ich bin ein absoluter Neuling, was Excel angeht und habe nie etwas in der Richtung gelernt.
Deshalb bitte ich dabei um eine Lösung (gern auch mit Erklärung).

Liebe Grüße, Jule

[Rabe]

Hi Jule,

Deshalb bitte ich dabei um eine Lösung (gern auch mit Erklärung).

zu dieser Formel in Zelle M9:
=WENN(L9>=12;"00:60";WENN(L9<=6;"0";WENN(L9<=12>=6;"00:45")))

Was ist 00:60 für eine Uhrzeit?

Wenn Du eine Zahl in "" in eine Zelle schreibst, ist es keine Zahl sondern ein Text und damit ist weiterrechnen oft schwer! Das ist auch erkennbar an der Ausrichtung in der Zelle: linksbündig Text, rechtsbündig Zahl.

Ich würde es so formulieren:
=WENN(L9>=12;1/24;WENN(L9<=6;0;WENN(UND(L9<=12;L9>=6);0,75/24)))

Dann zu den Formeln in Spalte X:
=WENN(UND((C7*24)<6>=0;(D7*24)>6<20);(6-(C7*24)))
dies ist auch keine Formel, die funktionieren kann:
Du kannst keine Verknüpfung von Bedingungen in einer Bedingung abfragen (das fett markierte geht also nicht)! Außerdem fehlt der SONST-Wert!
Allgemein geht die Formel so:

Code:

=WENN(UND(Wert1<6;Wert1>=0;Wert2>x;Wert2<y); DANN-Wert; SONST-Wert)

Innerhalb der UND-Klammer sind so sehr viele Bedingungen verknüpfbar!

Ähnliches gilt für die Formeln in T, was willst DU damit erreichen?
=WENN(ODER(C7<20>6;D7<20>6);((C7<20>6)+(D7<20>6))*2,5;0)

Was soll diese Formel in B55 machen:
=+WENN(Stunden="Service";G7-F7)+WENN(Stunden="Projekt";"H7-G7(WENN(H7-G7>00:30))")

oder diese in C62:
=WENN((D7*24)<20,01;("0,00");WENN(UND((D7*24)>20,001;(D7*24)<20,499);((D7-20)*50%);WENN(UND((D7*24)>20,509;(D7*24)<21,001);(((D7-20)*150%));(J6-20)*70%)))

diese in H60:
=WENN(Stunden="Wartung";G60-F60)*24+WENN(Stunden="Service";G60-F60)*24+WENN(UND(Stunden="Projekt";(G60-F60)*24>0,5);(G60-F60)*24;0)

diese in F62:
=WENN(UND(Stunden="Wartung";(G60F60)*24);(G60-F60)*48;0))

[BoskoBiati]

Hallo,

diese Tabelle ist ein Graus! Es kann nicht sein, dass für einen Tag 2Zeilen existieren, für den Rest nur Eine! Wenn es mehrere Zeilen pro Tag geben kann, dann wäre das besser durch zusätzliche Spalten abzufangen. Die Formeln in Zeile 8 sind ja zum Teil sowas von daneben! Hier mal eine Lösung für Zeile 9:

[html]

Arbeitsblatt mit dem Namen 'Tabelle1'

A

B

C

D

E

F

G

H

I

K

L

M

N

O

P

Q

R

S

T

U

V

9

Dienstag

02.08.2016

06:25

20:15

13,83

1:00

1,00

12,83

0,00

1,93

0,00

0,00

4,83

Zelle	Format	Wert
B9	TT.MM.JJJJ	02.08.2016
C9	[hh]:mm	0,267361111111111
D9	[hh]:mm	0,84375
K9	hh:mm
L9	0,00	13,8333333333333
M9	h:mm;@	4,16666666666667E-02
N9	#.##0,00	1
O9	#.##0,00	12,8333333333333
P9	0,00
Q9	#.##0,00	0
R9	#.##0,00	1,93333333333333
S9	#.##0,00	0
T9	#.##0,00	0
U9	#.##0,00
V9	0,00	4,83333333333333

Zelle	Formel
L9	=WENN(D9-C9<0;(D9-C9)*24+24;(D9-C9)*24)
M9	=VERWEIS(L9*24;{0;6;12};{0;0,75;1})/24
N9	=M9*24
O9	=L9-N9
Q9	=(V9<=4)*V9*25%
R9	=(V9<=9)*(V9>4)*V9*40%
S9	=(V9>9)*V9*50%
T9	=MIN(D9;0,25)-WENN(C9>D9;0;MIN(C9;0,25))
V9	=WENN(O9<=8;"0";O9-8)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[/html]