Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1304to1308
1304to1308
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Anzahl Tag/Nacht-Stunden aus Zeitraum auslesen

Forumthread: Anzahl Tag/Nacht-Stunden aus Zeitraum auslesen

Anzahl Tag/Nacht-Stunden aus Zeitraum auslesen
07.04.2013 13:02:06
Jens
Hallo, liebe Excel-Community...
Ich erstelle gerade einen Arbeitszeit-Nachweis und stehe vor folgendem Problem :
Spalte
A = Datum
B = Wochentag
C = Markierung Feiertag/Urlaub (x/u)
D = Arbeitsbeginn
E = Arbeitsende
einer separaten 1.Spalte soll nun folgendes ausgegeben werden :
Wenn Spalte C =x und Spalte D=18:30, dann Anzahl der Stunden zwischen 06:00 und 22:00 sonst 0.
einer separaten 2.Spalte soll nun folgendes ausgegeben werden :
Wenn Spalte C =x und Spalte D=22:00, dann Anzahl der Stunden zwischen 22:00 und 06:00 sonst 0.
Die Zellen sind ordnungsgemäss formatiert.
Würde mich freuen,wenn mir jemand eine Hilfestellung geben könnte.

Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Anzahl Tag/Nacht-Stunden aus Zeitraum auslesen
07.04.2013 13:15:28
ransi
Hallo
Mach mal so:
Tabelle1

 CDEF
2x18:3016 
3x22:00 8

Formeln der Tabelle
ZelleFormel
E2=(C2="x")*(D2=0,770833333333333)*16
F3=(C3="x")*(D3=0,916666666666667)*8


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
ransi

Anzeige
ungenaue Dezimalzahlen
07.04.2013 14:20:39
Erich
Hi ransi,
beim Vergleich von Dezimalzahlen wäre vielleicht mehr Vorsicht vor kleinen Ungenauigkeiten sinnvoll.
Die Uhrzeiten in Sp. D können Ergebnisse von Berechnungen sein und sind dann nicht unbedingt
identisch mit den Konstanten in deinen Formeln.
Hier zwei Beispiele:
 CDEFGHI
2x18:300 16 0,770833333333332
3x22:00:00,000 0 80,916666666666671

Formeln der Tabelle
ZelleFormel
D2="18:30:00"-0,000000000000001
E2=(C2="x")*(D2=0,770833333333333)*16
G2=16*(C2="x")*(TEXT(D2;"h:mm:ss")="18:30:00")
I2=D2
D3=22,0000000000001/24
F3=(C3="x")*(D3=0,916666666666667)*8
H3=8*(C3="x")*(TEXT(D3;"h:mm:ss")="22:00:00")
I3=D3

Bei den Formeln in G2 und H3 wird auf volle Sekunden gerundet. Das scheint mir sicherer.
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
noch zwei Formeln
07.04.2013 14:27:15
Erich
Hi,
hier noch eine Variante:
 CDEF
2x18:3016 
3x22:00:00,000 8

Formeln der Tabelle
ZelleFormel
D2="18:30:00"-0,000000000000001
E2=16*(C2="x")*(RUNDEN(D2*86400;0)=3600*18,5)
D3=22,0000000000001/24
F3=8*(C2="x")*(RUNDEN(D3*86400;0)=3600*22)

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Anzahl Tag/Nacht-Stunden aus Zeitraum auslesen
07.04.2013 15:09:16
Jens
Danke,für eure Lösungsansätze.
Leider stehe ich im Moment ein wenig auf der Leitung,oder aber, ich habe mich falsch erklärt.
Habe mal eine Datei mit Kommentaren erstellt, villeicht ist das verständlicher....
https://www.herber.de/bbs/user/84765.xlsx

Anzeige
in K7: =WENN(C7="x";G7;"") owT.
08.04.2013 14:49:29
Rainer

AW: Anzahl Tag/Nacht-Stunden aus Zeitraum auslesen
08.04.2013 20:36:08
Erich
Hi Jens,
kannst du damit etwas anfangen?
https://www.herber.de/bbs/user/84787.xls
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

AW: Anzahl Tag/Nacht-Stunden aus Zeitraum auslesen
08.04.2013 22:07:22
Jens
Vielen Dank, Erich G.
Super-Hilfe,danke.
Ich werde mich jetzt einmal anhand deines Beispiels mit der Formel (welcher=9), wo du mir den Link geschickt hast,aueinandersetzen,da ich ja nun ein praktisches Beispiel habe. Das mit den Hilfszeilen ist auch eine prima Lösung, mit der ich nunmehr öfters arbeiten werde.
Also,noch einmal vielen Dank.
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

Anzahl Tag/Nacht-Stunden aus Zeitraum auslesen

Schritt-für-Schritt-Anleitung

Um die Anzahl der Tag- und Nachtstunden aus einem Zeitraum in Excel zu ermitteln, kannst du folgende Schritte befolgen:

  1. Daten eingeben: Erstelle eine Tabelle mit den Spalten A bis E, in denen du das Datum, den Wochentag, die Markierung für Feiertage/Urlaub, den Arbeitsbeginn und das Arbeitsende einträgst.

  2. Formeln hinzufügen:

    • In einer neuen Spalte (z.B. F) kannst du die Anzahl der Stunden zwischen 06:00 und 22:00 ermitteln. Verwende diese Formel in Zelle F2: 
      =WENN(C2="x";WENN(D2=TIME(18;30;0);(TIME(22;0;0)-TIME(6;0;0))*24;0);0)
    • In einer weiteren neuen Spalte (z.B. G) kannst du die Anzahl der Stunden zwischen 22:00 und 06:00 ermitteln. Verwende diese Formel in Zelle G2: 
      =WENN(C2="x";WENN(D2=TIME(22;0;0);(TIME(6;0;0)-TIME(22;0;0))*24;0);0)

  3. Formeln nach unten ziehen: Ziehe die Formeln in den Zellen F2 und G2 nach unten, um sie auf die restlichen Zeilen anzuwenden.

  4. Ergebnisse überprüfen: Stelle sicher, dass die Ergebnisse in den neuen Spalten korrekt angezeigt werden.

Häufige Fehler und Lösungen

  • Problem: Ungenaue Ergebnisse durch Dezimalzahlen.

    • Lösung: Achte darauf, dass die Uhrzeiten in Spalte D genau sind. Verwende die Funktion RUNDEN, um sicherzustellen, dass die Werte korrekt verglichen werden. Beispiel: 
      =WENN(C2="x";(RUNDEN(D2*86400;0)=3600*18,5)*16;0)

  • Problem: Excel zeigt unerwartete Fehler an.

    • Lösung: Überprüfe, ob alle Zellen korrekt formatiert sind (Uhrzeit-Format). Manchmal können auch kleine Ungenauigkeiten in den Formeln zu Problemen führen.

Alternative Methoden

  1. Hilfszeilen verwenden: Nutze zusätzliche Spalten für Zwischenergebnisse, wie z.B. die Umwandlung von Uhrzeiten in Dezimalzahlen. Dies kann die Übersichtlichkeit erhöhen.

  2. Pivot-Tabellen: Wenn du eine größere Datenmenge hast, kannst du eine Pivot-Tabelle erstellen, um die Anzahl der Tag- und Nachtstunden schnell zu aggregieren.

Praktische Beispiele

  • Beispiel-Daten: Datum Wochentag Feiertag Arbeitsbeginn Arbeitsende
    01.01.2022 Montag x 18:30 22:00
    02.01.2022 Dienstag x 22:00 06:00

  • Beispiel-Formeln:

    • Für F2 (Tagstunden): 
      =WENN(C2="x";WENN(D2=TIME(18;30;0);(TIME(22;0;0)-TIME(6;0;0))*24;0);0)
    • Für G2 (Nachtstunden): 
      =WENN(C2="x";WENN(D2=TIME(22;0;0);(TIME(6;0;0)-TIME(22;0;0))*24;0);0)

Tipps für Profis

  • Verwendung von benannten Bereichen: Erstelle benannte Bereiche für die Uhrzeiten, um die Formeln übersichtlicher zu gestalten.
  • Datenvalidierung: Setze Datenvalidierungsregeln für die Spalten, um sicherzustellen, dass nur gültige Uhrzeiten eingegeben werden.
  • Makros: Automatisiere den Prozess mit einem Makro, wenn du regelmäßig ähnliche Berechnungen durchführen musst.

FAQ: Häufige Fragen

1. Wie kann ich die Anzahl der Nächte berechnen?
Um die Anzahl der Nächte zu berechnen, kannst du eine ähnliche Formel wie für die Tagstunden verwenden, jedoch auf den Nachtzeitraum (22:00 bis 06:00) fokussieren.

2. Was tun, wenn die Berechnungen nicht stimmen?
Überprüfe die Formatierung der Zellen und stelle sicher, dass die Uhrzeiten in einem konsistenten Format vorliegen. Nutze die RUNDEN-Funktion, um Genauigkeitsprobleme zu vermeiden.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige