|
Vielleicht haben Sie sich auch schon mal über die Datumseingabe bei Excel geärgert. Auf dem Nummernblock lassen sich die Zahlen zwar flink eintippen, aber es fehlt der Trennpunkt. Mit dem Komma kann Excel nichts anfangen. Und auf der normalen Tastatur ist zwar der Punkt vorhanden, aber die Zahlen lassen sich nur mühsam angeben.
Tipp: Die Datumseingabe lässt sich komplett mit dem Nummernblock erledigen. Verwenden Sie statt des Trennpunktes einfach das Minuszeichen in der rechten oberen Ecke. Diese Eingabe versteht Excel als Datumsangabe und kann sie entsprechend umwandeln.
Aus der Eingabe 25-9-20 entsteht nach dem Drücken der Eingabetaste das richtige Datum 25.09.2020. Bei Daten, die mit Null beginnen, muss aber die erste Null eingegeben werden: 08-1-19.
Wenn Sie in Excel Zeiten wie beispielsweise "5 Stunden und 12 Minuten" eingeben wollen, schreiben Sie normalerweise in eine Zelle 5:12. Excel erkennt anhand des Doppelpunktes selbständig, dass es sich hier um eine Zeitangabe handelt, und formatiert die Zelle entsprechend.
In manchen Fällen ist es jedoch erforderlich, eine mehrstündige Zeitspanne nicht in Stunden und Minuten, sondern komplett als Minutenwert auszugeben. Möchten Sie einen Zeitraum in Minuten anzeigen, benötigen Sie eine entsprechende Formel. Mit folgender Formel erhalten Sie die Anzahl der Minuten des Zeitraums in der Zelle A1:
=STUNDE(A1)* 60+MINUTE(A1)
Nicht vergessen: Die Zelle, in der sich diese Formel befindet, muss als "Zahl" formatiert werden (ohne Nachkommastellen).
Beim Transfer von Daten aus anderen Systemen kann es passieren, dass das Datum im Format JJMMTT in der Zelle steht | und dies zu allem Überfluss vielleicht auch noch im Textformat. Mit einer Formel können Sie jedoch dieses Format in ein normales Datum umwandeln. Wenn das Datum in der Zelle A1 steht, lautet diese Formel:
=DATUM(LINKS(A1;2);RECHTS(LINKS(A1;4);2);RECHTS(A1;2))
Mit Hilfe dieser Formel wird z. B. aus dem Wert 981215 das Datum 15.12.98 oder aus 980801 der 01.08.98. Damit Sie dieses Datum auch tatsächlich als Wert und nicht als Formel in Excel weiterverwenden können, müssen Sie es umwandeln.
Dafür markieren Sie alle Zellen, die von einer Formel in ein Datum verwandelt werden sollen, und kopieren sie mit [Strg] [C] in die Zwischenablage. Dann setzen Sie den Cursor in eine freie Zelle und wählen Bearbeiten | Inhalte einfügen. In der folgenden Dialogbox markieren Sie die Option Werte und klicken danach auf die Schaltfläche OK. Schon erscheinen die zuvor in die Zwischenablage kopierten Formeln als Datumswerte in der Tabelle.
Beispiele für Industrieminuten sind:
– 1 Arbeitsstunde = 1,00 h = 100 Industrieminuten
– 8 Arbeitsstunden 45 Minuten = 8,75 h = 875 Industrieminuten (oder 87500 Industriesekunden)
In Feld A werden die Industrieminuten eingegeben.
Dann in Feld B diese Formel verwenden: =A1/5*3
Problem: Sie haben eine Spalte mit Monatsnamen und möchten mit den Monaten rechnen. Excel unterstützt aber nur eine siebenfache Verschachtelung der Funktion WENN. Eine vollständige Zuordnung der Monatsnamen zu Zahlen ist deshalb nicht möglich. Gibt es dennoch einen Weg, basierend auf den Namen zu rechnen?
Die Begrenzung auf die siebenfache Verschachtelung hängt mit der Konstruktion der Excel-Tabellenfunktionen zusammen und stellt deshalb eine unüberwindliche Schwelle dar. Das muss Sie aber nicht daran hindern, mit den Monatsnamen zu rechnen. Mit Hilfe der Funktion MONAT(Argument) und einem kleinen Trick erreichen Sie das gewünschte Ziel sogar schneller als mit der Verschachtelung.
Die Funktion MONAT errechnet aus einer Anzahl von Tagen den entsprechenden Monat und gibt eine fortlaufende Zahl zwischen 1 und 12 zurück. So erzeugt beispielsweise das Argument 360 das Ergebnis 12. Die Funktion akzeptiert neben reinen Zahlen aber auch Zeichenketten, die wie ein Datum aussehen. So erzeugt das Argument 15. Mai das Ergebnis 5. Diesen Umstand können Sie sich zunutze machen.
Interpretieren Sie den Monatsnamen als Zeichenkette und stellen Sie noch eine Zahl zwischen 1 und 28, gefolgt von einem Punkt und einem Leerzeichen voran. Dann kann die Funktion die entsprechende Monatszahl ermitteln. Wenn Sie beispielsweise in der Zelle A1 den Monatsnamen Januar stehen haben, schreiben Sie folgenden Funktionsaufruf in Zelle B1, um das Ergebnis 1 zu erhalten:
=MONAT("1. "&A1)
Damit rufen Sie die Funktion MONAT auf und übergeben ihr die Zeichenkette "1. Januar". Als Rückgabewert erhalten Sie 1. Auf diese Weise gibt die Funktion für alle Monatsnamen die fortlaufende Nummer von 1 bis 12 an. Ohne die vorangestellte Tagesangabe in Form einer Zahl und dem Leerzeichen erhalten Sie eine Fehlermeldung.
Möchte man in Excel Arbeitszeiten von Mitarbeitern erfassen, steht man häufig vor dem Problem, dass die Tabellenkalkulation Schwierigkeiten hat, negative Zeiten, die etwa beim Vergleich von Soll- und Istzeiten leicht anfallen können, richtig darzustellen. Versucht man einen größeren von einem kleineren Zeitwert abzuziehen, füllt Excel die Zelle mit Gittern, die sich nur durch eine Standardformatierung als Zahl, nicht aber als Zeit beseitigen lassen. Dezimalwerte sind jedoch in Zeiterfassungstabellen meist nicht erwünscht. Befriedigende Ergebnisse erhält man erst dann, wenn man diese Zahlen in eine Hilfszeile setzt.
Möchten Sie in Zeile 10 die Mehrstunden und in Zeile 11 die Minderstunden darstellen, um mit diesen Werten weiterzurechnen, verwenden Sie die Zeile 9 als Hilfszeile, die in der fertigen Tabelle ausgeblendet wird. In dieser Zeile erfolgt die Berechnung Ist- minus Soll-Stunden.
Solange dieser Wert positiv ist, hat Excel kein Problem, doch ist er negativ, kommt es zum oben beschriebenen Effekt. Mit dem Format | Zellen | Zahlen | Uhrzeit lässt sich dieser Wert nicht darstellen. Wohl aber kann er zur weiteren Berechnung herangezogen werden, dann, wenn die Funktion ABS verwendet wird. Sie entbindet eine Zahl ihres Vorzeichens, schafft also den hinderlichen negativen Wert aus der Welt.
Damit in Zeile 10 die täglichen Überstunden und in Zeile 11 die zu wenig geleisteten Stunden erscheinen, tragen Sie dort folgende Formeln ein: in Zelle B10 die Formel =WENN(B9>0;(B9);0 und in Zelle B11 =WENN(B9<0;ABS(B9);0). Kopieren Sie diese Formeln über sämtliche Spalten der Woche und formatieren Sie anschließend die Zeilen 10 und 11 mit Format | Zellen | Zahl | Uhrzeit im gewünschten Format.
Eine Besonderheit werden Sie noch in der Summenspalte feststellen: Excel ist so "intelligent", Zeiten nur bis zu 24 Stunden zu addieren und danach wieder bei 0 zu beginnen. Versuchen Sie also, eine Summe aus den 5 Arbeitstagen á 8 Stunden zu bilden, wird Excel beharrlich die Stundenzahl 16:00 ausgeben. Sie können diese, in diesem Fall unerwünschte, Eigenmächtigkeit umgehen, indem Sie diejenigen Zellen, die Zeitensummen über 24 Stunden anzeigen sollen, mit einem benutzerdefinierten Format ausgeben.
Wählen Sie dazu Format | Zellen | Zahlen | Benutzerdefiniert und geben Sie dann unter Formate die Zeichenfolge [h]:mm ein. Durch die eckigen Klammern im Zellenformat wird Excel angewiesen, Stundenwerte auch über 24 Stunden hinaus auszugeben.
Möchte man mit Excel Nachtschichten berechnen, bietet es sich an, spaltenweise die Sollstunden, den Arbeitsbeginn, das Ende, die Summe und die Über- oder Unterstunden zu erfassen. Allerdings gerät man in eine Falle, wenn man in der Summe der Stunden die einfache Formel "Arbeitsende minus Arbeitsanfang minus Pausen" verwendet. Sobald man versucht, Nachtschichten zu berechnen, die an einem Tag beginnen und am nächsten Tag enden, erhält man im Summenfeld Minuswerte.
Um das zu verhindern, kann man im Summenfeld statt der einfachen Subtraktion eine Wenn-Abfrage verwenden. Sie überprüft, ob der Wert in der Spalte Arbeitsende kleiner ist als der in der Spalte Arbeitsbeginn, und addiert in diesem Fall einen Tag dazu.
Möchten Sie also im Feld F2 die Summe der Arbeitsstunden ermitteln, verwenden Sie die Formel =WENN(C2>D2;D2+1-C2-E2;D2-C2-E2). Dann addiert Excel einen Tag, also 24 Stunden, falls das Arbeitsende auf den nächsten Tag fällt. Fallen Arbeitsbeginn und Arbeitsende auf denselben Tag, werden die Werte in den Spalten C und E von denen in Spalte D abgezogen.
Beachten Sie jedoch, dass Sie bei Arbeitszeitberechnungen die Option "1904-Datumswerte" in Extras | Optionen | Berechnen aktivieren müssen, damit Excel mit eventuellen Minuszeiten in der Spalte G klarkommt. Um die Summen korrekt darzustellen, verwenden Sie in den Summenfeldern außerdem mit Format | Zellen | Zahlen die Kategorie Benutzerdefiniert und das Format [h]:mm. Andernfalls zeigt Excel keine Stundenwerte über 24 an, sondern beginnt wieder mit einer Eins, wenn die Summe mehr als 24 ergibt.
Die Excel-Funktion KALENDERWOCHE entspricht nicht ganz den in Deutschland üblichen Regeln. Vor allem zwei Abweichungen stören: der erste Tag der Kalenderwoche ist nach DIN der Montag, und die erste bzw. letzte Woche eines Jahres zählt zu dem Kalenderjahr, in dem mindestens vier Tage dieser Woche liegen.
Eine Anpassung an die Kalendernotation der Deutschen Industrienorm ist mit Hilfe eines kurzen VBA-Moduls möglich. Rufen Sie in Excel mit der Tastenkombination "Alt + F11" den VBA-Editor auf. In dessen Menüzeile wählen Sie den Befehl "Einfügen Modul" aus. Das folgende Listing tippen Sie dann in das Modulfenster:
Function KWoche (d as Date) Dim t as Long t = DateSerial (Year (d + (8 - Weekday (d)) Mod 7 - 3), 1, 1) KWoche = ((d - t - 3 + (Weekday (t) + 1) Mod 7)) \ 7 + 1 End Function
Erhalten Sie beim Speichern des Moduls die Fehlermeldung "Listentrennzeichen erwartet", dann tauschen Sie die beiden Kommata in der Zeile "t =" gegen das Listentrennzeichen aus, das in der Systemsteuerung unter "Ländereinstellung | Zahlen" als solches angegeben ist.
Hinweis: Die Funktion KALENDERWOCHE steht (in einigen Excel-Versionen) nur dann zur Verfügung, wenn man das Add-In "Analyse-Funktionen" in Excel installiert hat.
Von der tatsächlich geleisteten Arbeitszeit wird die theoretische abgezogen. Dadurch ergeben sich manchmal negative Zeiten. Diese werden in den Zellen mit ##### dargestellt.
Wie in den Spalten E bis G zu sehen ist, lässt sich damit aber rechnen:
Zunächst verschiedene Beispiele (Zeile für Zeile), wie Excel Arbeitszeiten berechnet, die an einem Tag abgeleistet werden – sowie einige Arbeitszeiten, die tagübergreifend stattfinden:
Merke: Erst durch "Benutzerdefinierte Formatierung" kann tagesübergreifend korrekt abgerechnet werden!
Das Format der Zellen muss beachtet werden! Öffnen Sie das Excel-Menü "Start | Format | Zellen formatieren".
In Spalte A und B wird dann das "Benutzerdefinierte Format" TT.MM.JJJJ hh:mm ausgewählt (siehe Screenshot etwas weiter unten).
Im zweiten Teil erfahren Sie mehr über die "Benutzerdefinierte Formatierung" sowie die "Bedingte Formatierung" in Excel!
Spalte C: Stunden und Minuten in Minuten umwandeln (bis maximal 24 Stunden)
=WENN(B6=A6;SUMME(24*60);WENN(B6>A6;SUMME(STUNDE(B6-A6)*60)+(MINUTE(B6-A6));"Startzeit ist größer!"))
Spalte C: Tage, Stunden und Minuten in Minuten umwandeln (ohne Limit)
=WENN(B6=A6;SUMME(24*60);WENN(B6>A6;SUMME(TAG(B6-A6)*24*60)+(STUNDE(B6-A6)*60)+(MINUTE(B6-A6));"Startzeit ist größer!"))
Theoretisch könnte man das noch weiter ausbauen auf MONAT und JAHR.
Spalte D: Minuszeiten darstellen (24-Stunden-Maximum wurde hier nicht berücksichtigt)
Grundformel: =TEXT(ABS(B6-A6);" - hh:mm:ss")
Erweiterung: =WENN(B6>=A6;TEXT(ABS(B6-A6);"h:mm:ss");TEXT(ABS(B6-A6);" - hh:mm:ss"))
Wenn der rechte Zellwert größer als der linke Zellwert ist, dann rechne Plus-Zeiten; ansonsten rechne Minuszeiten.
© Zeitwerte mit Excel berechnen (Sekunden, Minuten, Stunden, Datumsformate) wurde dokumentiert von Winfried Brumma (Pressenet), 2020. Bildnachweis oben: Arbeiten mit Laptop und iPad, CC0 (Public Domain Lizenz).
Archive:
Jahrgänge:
2022 |
2021 |
2020 |
2019 |
2018 |
2017 |
2016 |
2015 |
2014 |
2013 |
2012 |
2011 |
2010 |
2009
Themen:
Autor werden |
Buch-Rezensionen |
Ratgeber |
Sagen & Legenden |
Fantasy Mythologie |
IT & Technik |
Krimi Thriller |
Fachartikel & Essays |
Jugend- & Kinderbücher |
Bedeutung der Tarotkarten |
Bedeutung der Krafttiere
Noch mehr Bücher lesen (Werbung):
Fantasy & Science Fiction
| Krimis & Thriller
| Ratgeber
| Reise & Abenteuer
Sie schreiben anspruchsvolle Romane und Erzählungen? Wir suchen neue Autorinnen und Autoren. Melden Sie sich!
Wenn Sie die Informationen auf diesen Seiten interessant fanden, freuen wir uns über einen Förderbeitrag. Empfehlen Sie uns auch gerne in Ihren Netzwerken. Herzlichen Dank!
Sitemap Impressum Datenschutz RSS Feed