Eine Formel in Excel Arbeitszeiten-Rechner geht nicht ins Minus, weiss jemand wieso?
Hallo zusammen
Ich habe letzte Woche einen neuen Arbeitszeiten-Rechner erstellt, weil dieser von meinem Mitarbeiter fehlerhaft war. Bei meinem zeigt es allerdings die Überstunden nur an wenn sie im plus sind, sonst kommt nur "#####". Die Formel lautet: =WENN(B1="";"";B5-$A$8/5).
B1, ist wann ich zur Arbeit erschienen bin, B5 ist die Arbeitszeit am Tag und A8 ist die insgesamt Sollzeit pro Woche.
Falls mir jemand helfen kann, wäre ich sehr dankbar :)
4 Antworten
Das klappt so
Excel - Datei - Optionen - erweitert 1904 Haken setzen
Es ist lediglich das Problem der Anzeige.
Rechnerisch rechnet Excel auch bei Dir, wenn Du z.B. diese Minus-Zeit (#######) mit einer Pluszeit verrechnest erkennst Du das, sofern das Ergebnis positiv ist.
Dies kannst du über das Excel Format 1904 erreichen, ich würde jedoch von diesem Format dringlich abraten, denn damit versaust du dir ggf. die gesamte Tabelle, da alle Datumswerte um +4 Jahre nach vorne springen.
ich würde Daher die Zeit die Excelintern von 0-1 für 0 bis 24 Uhr geht einfach in Stunden umrechnen also *24
z.B A1: 08:00 B1: 14:00
=(A1-B1)*24 >> -6 als Zahlenformat muss logischerweise Zahl und nicht Zeit angegeben sein.
Falls es dir hingegen nur um eine Anzeige geht kannst du auch ein Minus manuell hinzufügen z.B. durch
willst du mit dem wert hingegen weiter rechnen, müsstest du es wieder ein eine Zahl umrechnen z.b. durch Multiplizieren mit *1
Weil es keine negative Uhrzeit gibt. Rechne deine Uhrzeiten in z.B. Stunden um, dann kommst du zu einem Ergebnis.
Dies ist bereits der fall und trotzdem funktioniert es nicht. Daher auch meine Verwirrung.
Hab nen Weg gefunden aus einer Uhrzeit eine Zahl zu machen:
Diese Formel auf eine Zelle mit Uhrzeit anwenden und die Zelle selber als Zahl formatieren --> =STUNDE(A1)+(MINUTE(A1)/60)
Dann erhältst du einen Zahlen bzw. Stundenwert aus deiner Uhrzeit. Und mit diesen Werten kannst du dann in den negativen Bereich kommen.
Du hast irgendwie nicht verstanden, was eine Uhrzeit in Excel ist und daher kommt bei Deiner Rechnerei Unsinn raus, wenn Du Uhrzeiten mit 42/5 verrechnest (übrigens auch dort, wo Du meinst, das positive Ergebnis "Überstunden" sei noch korrekt).
Eine Uhrzeit in Excel ist ein Bruchteil von 1 - also eine Zahl zwischen 0 und 1, wobei 0 der Uhrzeit 00:00 Uhr entspricht, 0,25 wäre dann 06:00 Uhr und 0,5 ist 12:00 Uhr mittags. Die Zahl 0,75 ist dann 18:00 Uhr am Abend und 1 entspricht 24:00 Uhr
Wenn Du nun von der Zahl 07:39 Uhr (= 0,31875) die Zahl 42/5 = 8,4 abziehst, bekommst Du die Zahl -8,08125 raus. Wenn dann die Zelle als Uhrzeit bzw. Datum formatiert ist, ergibt das wenig Sinn, weil die Zahl vor der Excel-Zeitrechnung liegt, die mit dem 1.1.1900 beginnt (Datum liegt dann irgendwann am 23.12.1899)
Anders: Um aus einer Uhrzeiten-Differenz oder Uhrzeiten-Summe eine echte Stundenzahl zu machen, musst Du die Uhrzeiten-Differenz/Summe mit 24 multiplizieren. In Deiner Formel also:
=WENN(B1="";"";B5*24-$A$8/5)
Dann käme hier -0,75 Stunden als Fehlzeit heraus (42/4 Stunden = 8,4 Stunden sind 8 Stunden und 24 Minuten tägliche Arbeitszeit und dann fehlen bei 7 Stunden 39 Minuten gearbeiteter Zeit noch 45 Minuten = 0,75 Stunden auf die Sollarbeitszeit)
Am Ende ist eine Arbeitszeiterfassung mit Excel & Co formelmäßig extrem aufwändig, wenn man mit Uhrzeiten oder Differenzen von Uhrzeiten arbeiten will/muss - insbesondere dann, wenn auch noch Tageswechsel (z.B bei Schichtbetrieb) in Spiel kommen.
Du hast finde ich nicht ganz Unrecht, allerdings rechnet der Fragesteller ja nicht mit 42 sondern mit 42:00 also die 42 bereits in das Urzeiten Format umgerechnet.
Ich bevorzuge dennoch die Berechnung mit Dezimalzahlen da es weniger Fehleranfällig ist. Insbesondere auch deswegen, da man damit nicht ständig auf negative Ergebnisse achten muss und diese abfangen muss. Sondern lediglich einmal am Ende, falls man die Stundendarstellung schöner findet.
Ich würde also in dezimal rechnen und am Ende in Excel Zeit umrechnen ggf ein ‐ vorne mit anfügen siehe der Formel in meinem Beitrag.
Vielen Dank für deine Antwort, leider stimmt das nicht so ganz. Ich habe eine Sollarbeitszeit von 8:24 excl. Pause. Wie du bei dem einen Bild sehen kannst, habe ich 4 Minuten Gleitzeit gut gemacht und das stimmt. Ich habe es extra noch mit den SAP daten verglichen um sicherzugehen. :)
Das werde ich haben. Ich wünsche dir noch einen Wundervollen Arbeitstag.
Vielen dank für deine Antwort, jetzt klappt es :)