Excel Arbeitszeiterfassung

Computer: Software & Programmierung Office Software
#1

Hallo zusammen,

ich bräuchte eine Formel um meine tägliche Arbeitszeit festzuhalten.
Ich habe die Excel-Tabelle wie folgt aufgebaut:

E5 = Beginn
F5 = Ende
G5 = Pause
H5 = Stunden

Ich hätte alles gerne in [hh:mm], da unsere Arbeitszeit minütlich genau abgerechnet wird.
Beispiel:
E5 = 7:00 Uhr
F5 = 15:30
G5 = ja
H5 = 8,00

Wenn in G5 ein „ja“ steht, soll Excel mir ganz normal die 30 Minuten Pause abziehen in der Spalte H5. Sollte dort ein „nein“ stehen, sollen mir die 30 Minuten auf meine Arbeitszeit angerechnet werden. Im obigen Beispiel hätte ich dann in H5 = 8,30 Stunden gearbeitet.

Ich habe es so probiert:
=WENN(G5=„nein“;((F5-E5));F5-E5-0,0202)

Das ganze wirkt aber ein bisschen umständlich, zumal die korrekte Zeit auch angegeben wird, wenn ich am Ende 0,0203 abziehe oder 0,0204.

Gibt es hier eine bessere Formel?
Desweiteren wäre es gut, wenn die Spalte H5 leer bleibt, wenn in E5 und F5 nichts steht.

Vielen,vielen Dank schon einmal!

LG Andre

#2

Hallo
diese Formel sollte das erledigen:
=REST(F5-E5;1)-(G5=„ja“)*ZEIT(0;30;0)

Gruß Holger

#3

Hallo Holger,

danke für deine Antwort - leider rechnet die Formel nicht korrekt die Werte die ich benötige.

H5 soll leer bleiben wenn in E5 & F5 nichts steht.
Steht in E5 „7:00 Uhr“ für den Arbeitsbeginn und in F5 „15:30 Uhr“ für das Arbeitsende, dann sollte in H5 ja „8:30“ stehen.

Damit die Formel die Pause abzieht müsse man ja hinzufügen, dass bei einem „ja“ in G5 in H5 30 Minuten abgezogen werden.
Solange dort ein nein steht, ists ja korrekt, dass ich 8:30 Stunden gearbeitet habe.

Dies ist bei der oben genannten Formel von dir leider noch nicht der Fall. Zudem steht bei Stunden „0:00“ wenn E5 und F5 leer sind :wink:

Danke schon einmal vorab!

#4

Halo,

Du musst daran denken, dass 8 Stunden und 30 Minuten ja 8 Stunden und eine halbe Stunde sind, also musst Du mit 8,5 Stunden als Wert rechnen, Somit ist eine Viertelstunde zwar 15 Minuten, aber rechnerich eben 0,25

Für die Pause würde ich dann auch 0,5 bzw. 0,25 rechnen bzw. wenn keine Pause dann 0

Alternativ kannst Du einfach die Zellen als „Zeit“ formatieren und dann direkt rechnen:
H5 =F5 - E5 - G5 und dann bekommst Du als Wert tatsächlich 7 Stunden und 30 Minuten 7:30

Übrigens: denk daran dass Du gesetzlich verpflichtet bist, nach sechs Stunden Arbeit eine Pause von 30 Minuten einzulegen. Kommt drauf an wer Deine Liste mal zu Gesicht bekommt … Ärger ist vorprogrammiert !

#5

Desweiteren wäre es gut, wenn die Spalte H5 leer bleibt, wenn in E5 und F5 nichts steht.

OK, das habe ich überlesen, die Formel funktioniert aber:
=WENN(ISTFEHLER(E8/F8);"";REST(F8-E8;1)-((G8=„ja“)*ZEIT(0;30;0)))

ISTFEHLER(E8/F8)
es kommt immer ein Fehler raus wenn nicht zwei Zahlen dividiert werden; dann „“
REST(F5-E5;1)
berechnet die reguläre Arbeitszeit; auch nach 24:00Uhr
-(G5=„ja“)*ZEIT(0;30;0)
zieht 30min ab wenn in G5 „ja“ steht

1 Like
#6

Du musst daran denken, dass 8 Stunden und 30 Minuten ja 8 Stunden und eine halbe : Stunde sind, also musst Du mit 8,5 Stunden als Wert rechnen, Somit ist eine Viertelstunde zwar 15 Minuten, aber rechnerich eben 0,25

Hallo Jürgen,
sorry, aber dort steht einiges was nicht stimmt

Schreibe dein Wert 0,25 mal in eine Zelle und stell anschließend das Zellenformat auf Uhrzeit
0,25 das ist keine 15min sondern 6 Stunden oder 1/4Tag

Excel rechnet alle Zeitwerte in Tage um
Ein Tag hat den Wert 1

Schreibe mal den wert 1 in eine Zelle und stell das Zellenformat auf [hh]:mm dann siehst du das.

8,5 sind also nicht 8,5 Stunden sondern 8,5 Tage.
(Auch hier mal das Zellenfornat auf [hh]:mm stellen)

Gruß Holger

#7

Beispiel:
E5 = 7:00 Uhr
F5 = 15:30
G5 = ja
H5 = 8,00

Hallo Andre,

entferne in den Optionen das Häkchen bei
[] Nullwerte.

Markiere E5 und F5, dann
Daten—klick auf das rechte obere Symbol in Datentools. Bei Zulassen wählst du"zeit" aus.

Tabellenblatt: [Mappe1]!Tabelle1
 │ E │ F │ G │ H │
───┼───────┼───────┼──────┼───────┤
 4 │ 07:00 │ 15:30 │ ja │ 08:00 │
───┼───────┼───────┼──────┼───────┤
 5 │ 07:00 │ 15:30 │ ja │ 08:00 │
───┼───────┼───────┼──────┼───────┤
 6 │ 07:00 │ 15:30 │ ja │ 8,00 │
───┼───────┼───────┼──────┼───────┤
 7 │ │ │ │ │
───┼───────┼───────┼──────┼───────┤
 8 │ 07:00 │ 15:30 │ nein │ 08:30 │
───┼───────┼───────┼──────┼───────┤
 9 │ 07:00 │ 15:30 │ nein │ 08:30 │
───┼───────┼───────┼──────┼───────┤
10 │ 07:00 │ 15:30 │ nein │ 8,50 │
───┼───────┼───────┼──────┼───────┤
11 │ │ │ │ │
───┼───────┼───────┼──────┼───────┤
12 │ │ │ nein │ │
───┼───────┼───────┼──────┼───────┤
13 │ │ │ nein │ │
───┼───────┼───────┼──────┼───────┤
14 │ │ │ nein │ │
───┴───────┴───────┴──────┴───────┘
Benutzte Formeln:
H4 : =(E4"")\*(F4"")\*(F4-E4-0,5/24\*(G4="ja"))
H5 : =WENN(ODER(E5="";F5="");"";F5-E5-0,5/24\*(G5="ja"))
H6 : =WENN(ODER(E6="";F6="");"";(F6-E6-0,5/24\*(G6="ja"))\*24)
H8 : =(E8"")\*(F8"")\*(F8-E8-0,5/24\*(G8="ja"))
H9 : =WENN(ODER(E9="";F9="");"";F9-E9-0,5/24\*(G9="ja"))
H10: =WENN(ODER(E10="";F10="");"";(F10-E10-0,5/24\*(G10="ja"))\*24)
H12: =(E12"")\*(F12"")\*(F12-E12-0,5/24\*(G12="ja"))
H13: =WENN(ODER(E13="";F13="");"";F13-E13-0,5/24\*(G13="ja"))
H14: =WENN(ODER(E14="";F14="");"";(F14-E14-0,5/24\*(G14="ja"))\*24)

Zahlenformate der Zellen im gewählten Bereich:
E7,E11,F7,F11,G4:G14,H7,H11
haben das Zahlenformat: Standard
E4:E6,E8:E10,E12:E14,F4:F6,F8:F10,F12:F14,H4:H5,H8:H9,H12:H13
haben das Zahlenformat: hh:mm
H6,H10,H14
haben das Zahlenformat: 0,00

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

#8

Hallo Holger,

prima die Formel klappt einwandfrei.

Vielen,vielen Dank.

@Reinhard
„Markiere E5 und F5, dann
Daten—klick auf das rechte obere Symbol in Datentools. Bei Zulassen wählst du"zeit“ aus"

Dies habe ich nicht gefunden - ansonsten ist deine Hilfestellung auch sehr anschaulich und verständlich. Vielen Dank

@Jürgen
Mir ist bewusst, dass ich eine Pause einlegen muss - allerdings ist dies nicht immer machbar. Da ich häufig umherfahre nutze ich die Fahrzeit zum Essen etc.
Rechtlich vielleicht nicht ganz sauber aber bisher komme ich damit gut klar und solange die 30 Minuten bezahlt werden kann es meinetwegen auch so weitergehen - fühle mich nicht um meine Fitness betrogen :smile:

Nochmals Danke Euch dreien!

#9

Hallo Holger,

vielen Dank für deine Hinweise, aber ich formatiere die zellen immer VORHER und mache dann die eigentliche Dateneingabe, dann funktioniert es auch wie beschrieben.

#10

Eine Frage habe ich noch:

In I5 habe ich die Überstunden stehen.
Meine täglichen Arbeitsstunden stehen ja in H5 und werden im Uhrzeitformat angegeben.

Diese Formel klappt, rechnet mir das Ganze aber in Dezimal aus - ich brauche jedoch die minutengenauen Überstunden, nicht 0,25/0,5/0,75/1,0

=H5*24-8

Alle anderen Versuche bringen mir Ergebnisse von 0,02 o.ä. :smile:

Kann ich im Anschluss, dann die geleisteten Überstunden einfach addieren, oder muss ich dort wieder etwas beachten wegen des Uhrzeitformats?

Danke und Gruß,

Andre

#11

Hallo Andre,

=H5*24-8

anhand deiner Formel gehe ich jetzt bei meiner Formel von 8 Soll-Stunden aus.

Wenn du in I5 die Überstunden ausrechnen möchtest, dann wäre folgende Formel eine Möglichkeit.

=H5-(8/24)

Allerdings würde das in I5 zu Fehlern führen wenn die Stunden in H5 weniger als 8 wären. Und bei genau 8 Stunden in H5 hättest du in I5 den unschönen Eintrag 0:00

Um das zu vermeiden könntest du mit folgender Formel arbeiten.

=WENN(H51) Die entsprechenden Zellen in der Spalte I müssen genauso wie in den Spalten E + F + H im Uhrzeitformat formatiert sein.

2) Die geleisteten Überstunden können dann ganz normal mit SUMME (nicht mit + addieren) zusammengerechnet werden. Als Beispiel würde dann in der Zelle I16 folgendes stehen.

=SUMME(I5:I15)

3) Eine Besonderheit ist bei der Zelle mit dem Ergebnis (Summe) aller Überstunden zu beachten. In meinem Beispiel wäre das die Zelle I16. Damit Excel beim zusammenzählen der Stunden nicht nach 24 Stunden neu anfängt zu zählen, muss die Ergebniszelle besonders formatiert werden.

Dazu wählst du in dem Menü für die Formatierung der Zellen bei den Kategorien den Eintrag „Benutzerdefiniert“. Und als „Typ“ trägst du dann folgende Formatierung ein.

Hierbei besonders auf die Klammern bei den Stunden achten

Siehe hier einen Screenshot http://abload.de/img/5ncrxl.jpg

Gruß
N.N

#12

Hallo N.N,

deine Formel klappt perfekt, dankeschön!

Mir sind noch 2 Probleme aufgefallen.
Wenn ich an einem Wochenende Arbeite, sind dies ja generell Überstunden.

Wenn ich Werte in ein WE eingetragen habe, komme ich mit dieser Formel weiter:

=WENN(H11

#13

Hallo Andre,

Wenn ich an einem Wochenende Arbeite, sind dies ja generell Überstunden
=H11 ist hier wohl die sinnvollere Variante, oder?

Wenn ich dich richtig verstanden habe, würde das ja bedeuten dass du für die Überstunden in der Spalte I zwei unterschiedliche Formeln benutzen müsstest. Für die normalen Arbeitstage die normale Formel, und für die Wochenenden dann deine =H11. Das halte ich für etwas umständlich.

Außerdem könnte es ja vielleicht auch mal vorkommen dass du an einem Feiertag (oder im Urlaub?) zusätzlich arbeiten müsstest. Dann wären das ja auch komplette Überstunden.

Deswegen würde ich dir vorschlagen für solche Sonderfälle eine zusätzliche Spalte (Zusatzdienst) einzurichten.

Das könnte dann ungefähr so aussehen wie bei diesem Screenshot.
http://abload.de/img/1zkk5u.jpg

Wenn du dir diese Beispieltabelle herunter laden möchtest, kannst du das hier machen.
http://www.file-upload.net/download-8312303/Zeitrech…

Wäre es möglich dies so umzuformulieren, dass im Falle von 7 Arbeitsstunden (-01:00) dort steht? Oder wenn ich 5 Minuten früher gehe eben (-00:05)

Die Möglichkeit bei der Zeitrechnung mit Minuszahlen zu rechnen ist normalerweise bei Excel nicht vorgesehen. Allerdings kann man das trotzdem mit einer besonderen Einstellung bei den Optionen erreichen.

Das solltest du aber nur machen wenn in deiner Tabelle nicht irgendwelche Berechnungen vorkommen in dem du ein bestimmtes Datum errechnen lässt.

Gehe dazu bei Excel auf „Extras“ und wähle dort „Optionen“. Bei den Optionen wählst du dann die Registerkarte „Berechnung“. Dort markierst du dann die Option „1904-Datumswerte“

Siehe hier: http://abload.de/img/1hrpzi.jpg

Wenn du diesen Weg gehen möchtest (ein anderer Weg ist mir nicht bekannt), dann könntest du für die Berechnung der Überstunden (inklusive Zusatzdienste) folgende Formel verwenden.

=WENN((ISTFEHLER(B2/C2));"";WENN(G2"";E2;WENN(E2=8/24;"";E2-(8/24))))

WENN((ISTFEHLER(B2/C2));"" überprüft ob bei „Beginn“ und „Ende etwas eingetragen ist. Sonst bleibt die Zelle mit den Überstunden leer.

WENN(G2"";E2 überprüft ob bei „Zusatzdienst“ etwas eingetragen ist, wenn ja dann sind die kompletten Stunden = Überstunden.

WENN(E2=8/24;"" überprüft ob bei den Überstunden als Ergebnis 0:00 heraus kommt. Wenn ja bleibt die Zelle leer

E2-(8/24)))) trägt die Überstunden ein wenn kein Zusatzdienst eingetragen wurde und wenn die Berechnung mehr oder weniger als 0:00 Überstunden ergibt.

Diese Formel ist natürlich für meine Beispieltabelle gedacht. Bei deiner Tabelle müsstest du dann die noch die Zellen anpassen. Deswegen empfehle ich dir zur besseren Übersicht meine Beispieltabelle herunter zu laden.

Gruß
N.N

#14

Hallo N.N,

vielen,vielen Dank für deine ausführliche Antwort !!!

Hat alles super funktioniert und ist nun genau so wie ich es haben wollte!

Prima!

#15

Hallo Andre,

vielen Dank für deine Rückmeldung. Das wird leider viel zu oft vergessen. Es freut mich dass ich dir helfen konnte und dass nun alles so funktioniert wie du es wolltest.

Gruß
N.N