Clever-Excel-Forum › Office-Anwendungen › Excel v
Zeiterfassung mit "wenn;und;dann;sonst">als;<als falsch durchdacht

Thema geschlossen 
Baumstrukturmodus
Zeiterfassung mit "wenn;und;dann;sonst">als;<als falsch durchdacht
thoju Offline
pausiert
Registriert seit: 02.10.2016
Version(en): 2010
#1
Question  02.10.2016, 01:19
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
Suchen
Top
IchBinIch Offline
pausiert
Registriert seit: 21.07.2016
Version(en): 2007
#2
02.10.2016, 07:19
Hallo Jule,

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

Gruß 
Ich
Suchen
Top
WillWissen Offline
CEF-Moderator
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
#3
02.10.2016, 08:06
Hi Jule,

meinst du so?

Tabelle1

ABCD
2BeginnEndePauseArbeitszeit
321:0023:0000:3001:30
421:0023:0002:00
521:0004:0000:4506:15
621:0004:0007:00
Formeln der Tabelle
ZelleFormel
D3=REST(B3-A3-C3;1)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8




Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
[-] Folgende(r) 1 Nutzer sagt Danke an WillWissen für diesen Beitrag:
  • thoju
Suchen
Top
thoju Offline
pausiert
ThreadStarter
Registriert seit: 02.10.2016
Version(en): 2010
#4
03.10.2016, 20:30
[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]
Suchen
Top
thoju Offline
pausiert
ThreadStarter
Registriert seit: 02.10.2016
Version(en): 2010
#5
03.10.2016, 20:51

.xlsx   Stunden neu überdacht.xlsx (Größe: 20,26 KB / Downloads: 12)
Suchen
Top
Jockel Offline
VUP & {Formelsympathisant}
Registriert seit: 10.04.2014
Version(en): Office 2019
#6
03.10.2016, 21:08
Hallo, viele deiner Ergebnisse sind keine (Uhr)Zeiten {Zahlen}, weil sie in Hochkommata stehen...
Gruß Jörg
stolzes Mitglied im ----Excel-Verein
Freund einer excellenten Power Query-Abfrage
Suchen
Top
thoju Offline
pausiert
ThreadStarter
Registriert seit: 02.10.2016
Version(en): 2010
#7
03.10.2016, 21:40
(03.10.2016, 21:08)Jockel schrieb: 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
Suchen
Top
Rabe Offline
:-)
Registriert seit: 10.04.2014
Version(en): 2016 + 365
#8
19.10.2016, 14:04 (Dieser Beitrag wurde zuletzt bearbeitet: 19.10.2016, 14:04 von Rabe.)
Hi Jule,

(03.10.2016, 21:40)thoju schrieb: 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))
Suchen
Top
BoskoBiati Offline
नौसिखिया [vidyaarthi]

Registriert seit: 13.04.2014
Version(en): 365
#9
19.10.2016, 15:24
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'
 ABCDEFGHIKLMNOPQRSTUV
9Dienstag02.08.201606:2520:15      13,831:001,0012,83 0,001,930,000,00 4,83

ZelleFormatWert
B9TT.MM.JJJJ02.08.2016
C9[hh]:mm0,267361111111111
D9[hh]:mm0,84375
K9hh:mm 
L90,0013,8333333333333
M9h:mm;@4,16666666666667E-02
N9#.##0,001
O9#.##0,0012,8333333333333
P90,00 
Q9#.##0,000
R9#.##0,001,93333333333333
S9#.##0,000
T9#.##0,000
U9#.##0,00 
V90,004,83333333333333

ZelleFormel
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]
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Suchen
Top
Thema geschlossen 


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste