Bitte um Formelerstellung für Profis kein Problem

Computer: Software & Programmierung Office Software
1

Guten Tag liebe Leser,

ich würde unbedingt eine Formelberechnung benötigen um meine Über-/stunden minutengenau erfassen zu können. Eigentlich ist dies für mich kein Problem (Spalten in Zeit formatieren, abziehen, fertig), aber da ich am Abend arbeite und die Arbeitszeit 0:00 Uhr überschreitet, klappt meine Formel nicht (Beispiel weiter unten).
Zudem werden Uhrzeiten in meiner Lohnabrechnung nach 0:00 Uhr wie folgt angegeben: z.B. 1 Uhr ist dann 25 Uhr. Überstunden, davon die Minuten werden in Dezimalzahlen dargestellt, also z.B. 2,5 Stunden sind 2 Stunden und 30 Minuten.

Meine Formel sollte dann in Etwa so aussehen:

Anfangszeit bis Arbeitsende = Ergebnis in Dezimalzahl.

Ein Beispiel aus meiner Lohnabrechnung mit einer Berechnung der Sollarbeitszeit von 8,1 Stunden:

Anfangszeit 17:00 Uhr

Arbeitsende 25:43 Uhr

Erfasst: 8,72 Stunden

Die Zeiten ergeben in meiner Abrechnung unter Berücksichtigung einer Sollarbeitszeit von 8,1 Stunden eine Überstundenleistung von 0,62.

Mein Gedanke ist eigentlich ganz einfach. Die Anfangszeit und Endzeit sollten in Excel als Zeit eingetragen und dargestellt werden können, die Berechnung müßte dann ganz einfach nur als Dezimalzahl erfolgen, dann wäre auch für mich die Formel kein Problem.

Könnte mir vieleicht jemand weiter helfen?

Danke schon mal!

Gruß

Andreas

2

Hi,

Anfangszeit 17:00 Uhr
Arbeitsende 25:43 Uhr
Erfasst: 8,72 Stunden

Dein Problem ist, dass du neue Uhrzeiten erfinden möchtest. Ein Tag hat nun mal 24 Stunden, auch wenn es dir nicht gefällt.

Wenn du in die Zellen Anfangs- und Endzeit nicht nur die Uhrzeit sondern auch das Datum mit erfasst, kannst du die beiden Werte einfach subtrahieren und mit 24 multipizieren und bist schon fertig.

z. B
Zelle A1: 25.02.2011 17:00
Zelle A2: 26.02.2011 1:43
Zelle A3: =(A2-A1)*24

Die Differenz (A2-A1) ist in Tagen ausgedrückt, darum musst du mit 24 multiplizieren.

3

Zudem werden Uhrzeiten in meiner Lohnabrechnung nach 0:00 Uhr
wie folgt angegeben: z.B. 1 Uhr ist dann 25 Uhr. Überstunden,
davon die Minuten werden in Dezimalzahlen dargestellt, also
Berücksichtigung einer Sollarbeitszeit von 8,1 Stunden eine
Überstundenleistung von 0,62.

Hallo Andreas,

achte auf die Zellformate.

Tabellenblatt: [Mappe3]!Tabelle1
 │ A │ B │ C │ D │
──┼───────┼───────┼──────┼───────┤
1 │ 17:00 │ 25:43 │ 8,10 │ 0,62 │
──┼───────┼───────┼──────┼───────┤
2 │ 17:00 │ 18:13 │ 8,10 │ -6,88 │
──┴───────┴───────┴──────┴───────┘
Benutzte Formeln:
D1: =(B1-A1)\*24-C1
D2: =(B2-A2)\*24-C2

Zahlenformate der Zellen im gewählten Bereich:
A1:A2,B1:B2
haben das Zahlenformat: [hh]:mm
C1:C2,D1:smiley:2
haben das Zahlenformat: 0,00

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

4

Super! Echt ihr seit prima und ich bin stundenlang am verzweifeln gewesen. Vielen vielen Dank.

Jetzt muß ich nur noch rausfinden wie ich meine Formel erweitere um mein Gesamtergebnis nicht mehr zu verfälschen.

Habe eine Formel angelegt für jeden Tag Arbeitszeit minus 0,5 (Pause) ist Gesamt, da ich aber noch keine Werte an den Tagen habe wo noch nicht sind ist es natürlich 0 und trotzdem 0,5 Minus.

Sieht dann so aus:

=(D6-C6)*24 ist dann Gesamtarbeiszeit, nächste Zeile, nächster Tag
=(D7-C7)*24 usw.

Dann in einem seperaten Kästchen hab ich Gesamtarbeitszeit mit der Formel:
=F6-0,5+F7-0,5+F8-0,5 usw.

Mit der Formel hab ich nun mein Ergebnis, stimmt aber nicht, da ja die 0,5 Stunden schon von jedem Tag mit einberechnet sind. Heißt praktisch ich fange an mit März, trage meine Stunden bis Heute ein, aber die Pausen für das ganze Monat wurden schon berechnet.

Müßte doch eigentlich nur die Formel für die Gesamtarbeitszeit erweitern, oder? Z.B. =(D6-C6)*24 und dann wenn=0 nicht einbeziehen.

Gruß

Andreas

5

Hallo Andreas,

lade deine Tabelle doch mal hier hoch:

http://www.file-upload.net/

Danach kannst du den Link zu der Tabelle hier ins Forum stellen. So können wir uns die Tabelle herunter laden und direkt sehen was da gemacht werden kann.

Wenn deine Tabelle persönliche Daten enthält die du nicht veröffentlichen möchtest, kannst du diese Daten ja vorher ändern.

Gruß Horst

6

Jetzt muß ich nur noch rausfinden wie ich meine Formel
erweitere um mein Gesamtergebnis nicht mehr zu verfälschen.

Hallo Andreas,

mir scheint du gehst das Gesamtproblem von der falschen Seite aus an und machst es dir selbst znnötig schwer.

Mach die Kiste aus, nimm einen großen Zettel und male/skizziere mit Bleistift, also schnell wieder änderbar, wie eigentliche da deine Zeiterfassungs aussehen soll, also welche Spalten brauchst du.

Und wie soll das Ganze ablaufen damit Excel das auch zeigen kann, was es kann, dir unnötige Schritte, Dopelschritte abnehmen kann.

D,h., pro Mitarbeiter ein Blatt pro Monat?, pro Arbeitsgruppe ein Blatt pro KW?

Muß man Extraspalten haben um deutlich Schichtzulagen in unterschiedlicher Höhe für Nacht, Spät, Feiertage usw. zu haben.
Sind die Pausen gleichlang oder werden die länger ab 14 h Arbeit.
Was ist wenn einer keine pause macht.

Für den Überblick ist es auch wichtig zu wissen an welchen tagen da was gemacht wird. Ergo brauchst du da Excelformeln, wo du pro Blatt nur oben in eine Zelle den Monat eingibst und in eine andere das Jahr.
Den Rest des Blattes füllt dann Excel selbst aus, alle Tage untereinander, WEs farblich gekennzeichent, Feiertage euingetragen usw.

Das ist ungemain wichtig daß du dafür viel Zeit aufwendest, denn nur du kannst das tun wir nicht.
Wenn das steht, ist der Rest nur noch paar Formelchen und fertig.

Achja, wie ist das wenn jmd krank ist, kriegt er dann auch Pause abgezogen? :smile:

Die Excelfunktion Kalenderwoche benutze nicht, die lügt gelegentlich in D.

Und, bei Tabellen ist es ein guter Trick, die Gessamt stunden usw. OBEN in den Kopf zu schreiben, da kann man die Zeilen fixieren, also immer sichtbar wenn du nach unten scrollst.

Und der Zweck des ganzen soll ja sein, daß du oder andere nur noch an einer oder wenigen Stellen alle daten nur einmalig eingeben, alles andere macht dann Excel.

Wenn du dann genau weißt was du willst, dann google mal los nach
Zeiterfassung.xls
Arbeitszeiterfassung
Stundenabrechnung.xls
usw. usw.
da gibt es tausende Varianten…

Gruß
Reinhard

7

Hallo ihr Zwei,

ich möchte keinem Arbeit aufbrummen, die übernehme ich natürlich selbst, nur ist es schade zu wissen was Excel eigentlich kann und dies nicht nutzen zu können, leider.

Im Grunde hab ich eigentlich schon das erreicht was ich wollte. Es sind eben die Kleinigkeiten, die mich verrückt machen.

Nach Standardzeiterfassungen hab ich schon gegoogelt, da aber unsere Zeitberechnung so sehr komplex ist, hat mir keine wirklich weiter geholfen.

Habe nun mal meine wie gewünscht upgeloadet:

http://www.file-upload.net/download-3273102/Arbeitsz…

Meine Ziele sind wie folgt:

Arbeitsbeginn (Eingabe), Arbeitsende (Eingabe) ist dann Gesamt mit seperate Anzeige der Überstunden. Pausen werden zwar gemacht, sollen aber anhand der Werte (pauschal einbezogen) nicht berücksichtigt werden, da ich ja sonst jeden Tag Arbeitszeit bis zur Pause, nach der Pause bis Ende eingeben müßte.

Die reine geleistete Arbeitszeit (incl. Überstunden) so zusammen zählen, damit ich jeden Tag weis wieviel Stunden ich bereits gearbeitet habe, denn mehr als 180 Arbeitsstunden zahlt unsere Firma ab diesem Monat nicht mehr.

Meine noch bestehenden Probleme:

Stunden werden durch die Formel zusammen gezählt, aber trage ich Frei ein, ist ein Samstag (Arbeitsfrei)oder die Tage an denen ich noch nicht gearbeitet habe werden durch die Formel mit -0,5 berechnet und somit die Gesamtarbeitszeit verfälscht.
Das Ergebnis in der Spalte F brauche ich aber, da es in meiner Stundenabrechnung auch so angegeben ist.

Falls nun das alles zu viel ist, kann ich es durchaus verstehen. Ich möchte hier niemanden unnötig Arbeit aufbrummen. Ist ja eh schon sehr sehr nett für eure bisherigen Bemühungen.

Gruß

Andy

8

Hallo Andreas,

ich habe mir deine Tabelle herunter geladen und so angepasst wie ich dich verstanden habe. Ich hoffe dass ich dich richtig verstanden habe.

Die neue Tabelle hat (nur zu deinem Vergleich) drei Blätter.

Tabelle1 = Deine Original Tabelle
Neu = Die von mir angepasste Tabelle (Spalte H ausgeblendet)
Neu2= Die von mir angepasste Tabelle (Spalte H eingeblendet)

Bei der Sollanfangsz. und Endzeit steht zwar immer „Uhr“ hinter der Eingabe, aber du brauchst nur z.B. 15:30 einzugeben. Daraus wird automatisch „15:30 Uhr“

Achtung: In den Spalten F / G / H befinden sich Formeln, also nicht löschen.

Die Tabelle kannst du dir hier herunter laden:

http://www.file-upload.net/download-3273216/Arbeitsz…

Achte aber beim Herunterladen darauf dass du den richtigen Downloadbutton benutzt. Die Buttons zum Download auf der Seite sind etwas Irreführend. Hier kannst du sehen welchen Button du benuten musst.

http://www.abload.de/img/dateidownload1pxb.jpg

Gruß Horst

9

Nachtrag
Hallo Andreas,

hier noch mal die gleiche Tabelle, nur leicht verändert.

http://www.file-upload.net/download-3273227/Arbeitsz…

1 ) Es gibt nur noch Blatt1

2 ) Das Seitenformat ist nun auf Quer gesetzt, dadurch kannst du die Seite auf 1 Blatt ausdrucken.

3 ) Die Zellen sind mit Rahmen versehen.

4 ) Das Blatt hat einen Blattschutz, so dass du nicht aus Versehen die Formeln löschen kannst. Wenn die Tabelle aber bewusst verändern möchtest, gehst du vorher auf „Extras“ dort auf „Schutz“ und wählst dann „Blattschutz aufheben“

Gruß Horst

10

Hallo Horst,

vielen vielen Dank für deine Hilfe! Ganz genau so sollte die Tabelle arbeiten. Testweise habe ich eine alte Stundenliste genommen und Zeiten eingetragen und es kommt genau das raus, wie ich von unserer Firma bekomme.

Ich sollte echt mal einen Excel Kurs besuchen, bevor ich wieder so Etwas in Angriff nehme, denn es ist sehr interessant und beneide die Leute, wie dich, die so etwas drauf haben!

Nochmals vielen Dank für deine Hilfe!

Gruß

Andy

11

Grüezi Andreas

Wenns mal Veränderungen der Soll-Arbeitszeit gibt oder auch Feiertage mit berücksictigt werden sollen, dann schau dir doch mal die Rapporte auf meinem WebSpace näher an:

http://users.quick-line.ch/ramel/Files/ARBEITS-RAPPO…

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
12

Hallo Zusammen,

jetzt hab ich noch etwas vergessen. Wenn ich frei habe, Urlaub und Krankheit müßte ich ja auch eingeben können.

Müßte nun die Formel in Spalte F erweitern. Meine Formel dafür hab ich schon erstellt.
Wenn in der B Zelle ein Wort steht, sollte in F der Wert 7,6 erscheinen, da ich ja keine Arbeitszeiten eingebe.

Somit müßte ich die Formel in F =WENN(C6>0;REST(D6-C6;1)*24;"") mit meiner kombinieren.
Das ist =WENN(ISTNV(SVERWEIS(B6;{„Frei“.7,6;„Urlaub“.7,6;„Krank“.7,6;„Flexi“.7,6};2;0));0;SVERWEIS(B6;{„Frei“.7,6;„Urlaub“.7,6;„Krank“.7,6;„Flexi“.7,6};2;0))

Ist das überhaupt möglich?

Gruß

Andy

13

Grüezi Andreas

…schau dir meinen Beitrag weiter oben an - der Rapport 8a dürfte da recht gut passen.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
14

Hallo Thomas,

danke dir, das dürfte für mich mit ein wenig Geschick abänderbar sein. Dachte aber es ging einfacher, irgendwie meine 2 Formeln zu kombinieren.

Aber so dürfte ich auch zu meinem Ergebnis kommen. Werde mich Morgen mal hinsetzen :smile:

Gruß

Andy

15

Hallo Andreas,

ich weiß zwar nicht, wie du auf den Wert 7,6 kommst, aber du wirst das schon wissen.

Erstens

Wenn in Spalte B eines der Worte „Urlaub / Frei / Krank / Flexi“ vorkommt, dann soll in der entsprechenden Zeile bei Spalte F 7,6 Stunden eingetragen werden?

Zweitens

Wenn nicht exakt eines dieser Worte in Spalte B Eingetragen ist, dann soll die normale Berechnung über die Spalte D-C erfolgen?

Drittens

Wenn keines dieser Worte in Spalte B eingetragen ist, und in Spalte C keine Sollanfangszeit eingetragen ist, dann soll die entsprechende Zeile in Spalte F leer bleiben?

Ich habe deine Tabelle jetzt mal nach diesen Vorgaben geändert. Ich hoffe dass ich dich da richtig verstanden habe.

Damit nicht aus Versehen Formeln gelöscht werden, habe ich den „Blattschutz“ aktiviert. Aber wenn du möchtest, kannst du den natürlich wieder deaktivieren.

Das Datum kannst du bei den monatlichen Eingaben nur noch am 1. Januar eingeben. Alle anderen Tage werden automatisch eingetragen.

Die Tabelle kannst du dir hier herunter laden.

http://www.file-upload.net/download-3275743/Arbeitsz…

P.S. Ich habe gerade festgestellt das es manchmal Probleme geben könnte über den Link an die Datei zu kommen. Bei mir hat dann geholfen den Popupblocker kurzfristig auszuschalten.

Gruß Horst

16

Guten Morgen liebe Leser,

da bei uns, wie Heute Nacht erfahren, nur die rein geleisteten Anwesenheitsstunden berücksichtigt werden, müssen Tage wie Urlaub oder Überstundenfrei nicht berücksichtigt werden und deshalb ist meine Mappe dank eurer Hilfe fertig!

Möchte mich nochmals herzlichst bedanken!

Grüße und ein schönes warmes Wochenende wünsche ich allen!

Andy