Excel: Arbeitszeiterfassung - Minusstunden anzeigen

B

Bruniela

Mitglied
Guten Tag Excel-Spezialisten

Ich will eine Arbeitszeiterfassung mit Excel erstellen und bin auch schon ganz schön weit gekommen. Hier das Beispiel der Zeile 3, das heisst, eines Arbeitstages:

Spaltenformatierungen:
Spalte A (Datum)
Format: Datum, tt.mm.jjjj
z.B. 02.01.2008

Arbeitszeit am Morgen:
Spalte B (Morgen einstempeln)
Format: Text
Hier trage ich die 1. Uhrzeit ein
z.B. 7:23

Spalte C (Morgen ausstempeln)
Format: Text
Hier trage ich die 2. Uhrzeit ein
z.B. 12:49

Spalte D (1. Zwischentotal)
Format: Standard
Hier berechnet folgende Formel die Zeit, die zwischen Spalte B (einstempeln) und Spalte C (ausstempeln) liegt.
Formel: =TEXT(C3-B3;"h:mm")
Resultat: 5:26

Arbeitszeit am Nachmittag:
Spalte E (Nachmittag einstempeln)
Format: Text
Hier trage ich die 3. Uhrzeit ein.
z.B. 13:29

Spalte F (Nachmittag ausstempeln)
Format: Text
Hier trage ich die 4. Uhrzeit ein.
z.B. 17:32

Spalte G (2. Zwischentotal)
Format: Standard
Hier berechnet folgende Formel die Zeit, die zwischen Spalte E (einstempeln) und Spalte F (ausstempeln) liegt.
Formel: =TEXT(F3-E3;"h:mm")
Resultat: 4:03

Tagesarbeitszeit:
Spalte H (Total der Arbeitszeit des Tages)
Format: Zahl, 2 Dezimalstellen, Format: -1234.10
Hier addiert folgende Formel die Arbeitszeit des Morgens und die Arbeitszeit des Nachmittags
Formel: =TEXT(D3+G3;"h:mm")
Resultat: 9:29

Soll-Arbeitszeit:
Spalte I (Sollzeit)
Format: Text
Hier trage ich die Tages-Sollzeit im Format hh:mm ein.
z.B. 4:12

Gleitzeit:
Spalte J (Gleitzeit)
Format: Zahl, 2 Dezimalstellen, Format: -1234.10
In dieser Zelle wird berechnet, wieviel Zeit im Verhältnis zum Tagessoll übrig bleibt, indem ich die Tages-Sollzeit von der Tages-Arbeitszeit abziehe.
Formel: =TEXT(H3-I3;"h:mm")
Resultat: 5:17

Gleitzeitsaldo:
Spalte K (Gleitzeitsaldo)
Format: Zahl, 2 Dezimalstellen, Format: -1234.10
Hier werden die Tagesergebnisse der Gleitzeit addiert.
Formel: =TEXT(J2+J3;"[h]:mm")
Resultat: 25:00 (weil in Zelle J2 der Übertrag des Vormonats von 19:43 steht)

Jetzt komme ich zu meinem Problem:
Solange ich mehr arbeite, als ich muss (mehr als die Sollzeit), wird alles schön berechnet und angezeigt. Wenn ich aber mal nur 3 statt 4:12 Stunden arbeite, wird ab da in den Berechnungszellen nur noch das angezeigt:

#WERT!

Wie kriege ich Excel dazu, auch Minusstunden zu berechnen und schön anzuzeigen?
 
host07

host07

Stammgast
Ich glaube du musst unter Optionen, im Register "Berechnung" ein Häckchen bei "1904-Datumswerte" setzen.
 
A

Arc-en-ciel

Aktives Mitglied
Negative Stunden

Bruniela schrieb:
Guten Tag Excel-Spezialisten

Ich will eine Arbeitszeiterfassung mit Excel erstellen und bin auch schon ganz schön weit gekommen. Hier das Beispiel der Zeile 3, das heisst, eines Arbeitstages:

Jetzt komme ich zu meinem Problem:
Solange ich mehr arbeite, als ich muss (mehr als die Sollzeit), wird alles schön berechnet und angezeigt. Wenn ich aber mal nur 3 statt 4:12 Stunden arbeite, wird ab da in den Berechnungszellen nur noch das angezeigt:

#WERT!

Wie kriege ich Excel dazu, auch Minusstunden zu berechnen und schön anzuzeigen?
Zum Vergrößern anklicken....

Microsoft schreibt dazu (ich hoffe, das erklärt/löst das Problem):

Problembeschreibung
Ich möchte in Microsoft(R) EXCEL 5.0 für WINDOWS(TM) mit Zeiten rechnen, wobei sich negative Uhrzeiten ergeben können, z.B. "02:00-05:00". Statt der erwarteten "-03:00" als Ergebnis stehen in der Ergebniszelle nur Nummernzeichen (###). Was mache ich falsch bzw. wie kann ich trotzdem negative Zeiten errechnen?

Lösung
EXCEL speichert alle Datumswerte als serielle Zahl und alle Zeitwerte als Dezimalbrüche. Da es in der Realität keine "negativen Zeiten" gibt, kann EXCEL solche negativen Zeiten normalerweise auch nicht darstellen. Eine Ergebniszelle, die mit einem Zeitformat belegt ist und eine negative Zeit enthält, liefert deshalb "###" zurück.

Diese Einschränkung trifft allerdings nur zu, wenn das Datumsformat mit Datumswerten auf der Basis von 1900 rechnet. Stellt man das Datumsformat von EXCEL unter EXTRAS - OPTIONEN "Berechnen" auf "1904-Datumswerte" um, kann EXCEL negative Zeiten darstellen.

Mit der Einstellung der 1904-Datumswerte ist es trotzdem nicht möglich, negative Zeiten in einer Zelle einzugeben. Das können Sie umgehen, indem Sie die gewünschte negative Zeit durch eine einfache Formel berechnen lassen, z.B. "-05:00" ergibt sich aus "01:00-06:00".

Über BEARBEITEN - KOPIEREN und BEARBEITEN - INHALTE EINFÜGEN "Werte" kopiert man die negative Zeit auf sich selbst, wobei die Formel, welche diese Zeit berechnet, verloren geht. Mit der so gewonnenen negativen Zeit kann nun problemlos gerechnet werden.
 
B

Bruniela

Mitglied
Excel - Minusstunden addieren

GL-Fridolin schrieb:
Mit der Einstellung der 1904-Datumswerte ist es trotzdem nicht möglich, negative Zeiten in einer Zelle einzugeben. Das können Sie umgehen, indem Sie die gewünschte negative Zeit durch eine einfache Formel berechnen lassen, z.B. "-05:00" ergibt sich aus "01:00-06:00".

Über BEARBEITEN - KOPIEREN und BEARBEITEN - INHALTE EINFÜGEN "Werte" kopiert man die negative Zeit auf sich selbst, wobei die Formel, welche diese Zeit berechnet, verloren geht. Mit der so gewonnenen negativen Zeit kann nun problemlos gerechnet werden.
Zum Vergrößern anklicken....

Danke für die hilfreichen Tipps. Es klappt.
Jetzt schreibe ich von Hand z.B. in die Zelle N3 (mit dem benutzerdefinierten Format hh:mm) "01:00", in die Zelle O3 (mit dem benutzerdefinierten Format hh:mm) "06:00", und in die Zelle P3 (mit dem benutzerdefinierten Format hh:mm) die Formel "=N3-O3". Dann kopiere ich mit copy and paste (Ctrl+C und Ctrl+V) das negative Resultat in meine Zelle J3. So addiert es in der Zelle K3 die negative Gleitzeit sauber.

Muss ich ein Makro schreiben, um diesen Vorgang zu automatisieren, oder geht das auch mit einer Formel? Wenn es da eine Formel geben sollte, bin ich für ganz genaue Anweisungen seeeehr dankbar. Bei solchen Sachen bin ich geistig schwer herausgefordert. Man kann ja nicht überall ein Hirsch sein, aber zum Glück gibt es euch.
Übrigens: Falls das ein Fall für ein Makro ist, gilt in Sachen Anweisungen das selbe ;-).

Herzlichen Dank im Voraus.

Bruniela
 
dhbb

dhbb

Stammgast
Es scheint auch ohne Makro zu gehen, allerdings sehe ich deine Tabelle noch n icht ganz vor mir. Allerdings wenn im Voraus schon klar ist wo die Summierung hinkommt lässt sich statt dem Copy&Paste an der/n entsprechende/n Stelle/n auch ein Verweis resp. Formel einbauen. Gegebenenfalls unter Extras/Einstellungen die Anzeige von Nullwerten unterdrücken, falls dich das stören sollte.
 
T

Thomas Ramel

Stammgast
Grüezi Bruniela

Einen negativen Zeitwert kannst Du direkt in eine Zelle wie folt eintragen (exakt so übernehmen bitte):

=-"5:00"



Aber dennoch möchte ich anregen, dass Du auf die 1904-Datumsoption verzichtest - der einzige Vorteil ist die Anzeige von negativen Zeitwerten; rechnen kann und tut Excel auch korrekt mit den angezeigten ####.

Der Nachteil der 1904-Option ist, dass bereits bestehende Datumsangaben um 4 Jahre und 1 Tag verschoben werden und dass diese Verschiebung auch bestehen bleibt wenn Du Daten mit anderen Mappen vergleichst, die diese Option nicht gesetzt haben. Bei Copy/Paste von Datumswerten ist dies ebenfalls der Fall.

Eine Bekannte hat sich auf diese Weise mal eine Mitarbeiter-Übersicht dermasen zerschossen, dass sie anschliessend sämtliche Datumsangeben von Hand neu eintragen musste, da die Verschiebung beim einkopieren kaum auffällt.


Ich rate dir daher, die Plus- und die Minus-Stunden in separaten Spalten als positive Werte zu berechnen und gegeneinander aufzurechnen. Schau dir die Arbeitsrapporte auf meinem WebSpace doch mal näher an, da habe ich dies genau so umgesetzt:
 
O

Officer

Stammgast
Als ergänzender Vorschlag:

Mach in dem Fall alles mit positiven Werten und arbeite mit der bedingten Formatierung, um die eingentlich Negativen Arbeitsstunden herauszuheben.

=-"5:00" * -1 ergibt 5:00 positiv
 
D

dschoni

Neues Mitglied
Probleme mit 1904-Option

Die Verwendung der 1904-Datumsoption bringt Probleme mit sich.

Wer Formeln zur Berechnung von Feiertagen verwendet wie beispielsweise Ostern =RUNDEN((TAG(MINUTE(C$1/38)/2+55)&".4."&C$1)/7; )*7-6
... In Zelle C$1 befindet sich die Jahreszahl ...
Der erhält mit der 1904-Option falsche Daten. Die Formel stimmt dann überhaupt nicht und lässt sich auch durch das Hinzufügen der fehlenden 1461 Tage nicht korrigieren.

Und es kommt noch besser mit =WOCHENTAG(HEUTE()) erhalte ich am 13.01.2012 die Anzeige "Do" (Format TTT). Heute ist aber Freitag !!!

Wenn Microsoft behauptet, dass es keine negativen Zeitwerte gäbe, dann arbeiten wohl alle Mitarbeiter dort auf Überzeit. (WOW, was für ein motiviertes Team.) Vor der Tatsache, dass es sehr wohl negative Zeitdifferenzen gibt, verschliessen sie die Augen.

Ganz nebenbei: Mit OpenOffice Calc ohne jegliche Option wird Ostern richtig berechnet, die Eingabe von -12345:45:12 ist möglich und wird auch richtig dargestellt, und =WOCHENTAG(HEUTE()) ergibt "Fr".

Einziges Manko: Die Syntax der Befehle ist nicht exakt gleich, am Beispiel Ostern =RUNDEN(DATWERT(TAG(MINUTE(C$1/38)/2+55)&".4."&C$1)/7)*7-6
Aber entscheidet selbst ...

Beste Grüsse
dschoni
 
Zuletzt bearbeitet:
Du musst dich einloggen oder registrieren, um hier zu antworten.
Oben