Nachtstunden berechnen

Computer: Software & Programmierung Office Software
#1

Liebe Excelianer,

ich habe eine Datei, in der mehrere hundert Einträge stehen, in Spalte A das Beginndatum, in Spalte B das Enddatum, in Spalte C die Summe der Stunden. A und B im Format TT.MM.JJJJ hh:mm. Die Summe der Stunden bilde ich durch B minus A mal 24.

16.11.2008 10:00 16.11.2008 12:00 2
16.11.2008 10:00 17.11.2008 12:00 26
16.11.2008 10:00 22.11.2008 12:00 146
16.11.2008 10:00 16.11.2008 22:00 12
17.11.2008 23:00 18.11.2008 23:00 24
18.11.2008 23:00 19.11.2008 04:00 5
16.11.2008 04:00 16.11.2008 09:00 5
17.11.2008 04:00 22.11.2008 22:45 138,75

Jetzt sollte ich zusätzlich zu den Gesamtstunden wissen, wieviele Stunden zwischen 21:00 und 6:00 Uhr angefallen sind und ich scheitere da kläglich. Es geht ja nicht nur in diesen Bereich hinein oder hinaus oder zwischendrin, sondern kann ja auch über mehrere Tage gehen, die diesen Bereich betreffen, also inkl. Datumswechsel.

Habt ihr eine Idee?

Vielen Dank schon mal für allfällige Antworten.

Hostarea: http://www.hostarea.de/server-01/Januar-c375a5de96.xls

Liebe Grüße

Jorge

#2

Hallo Jorge!

Da wirst du dir wahrscheinlich eine Funktion programmieren müssen. Ich habe leider jetzt gerade kein Excel zur Verfügung um das in VBA zu schreiben. Ich versuche es so allgemein wie möglich zu beschreiben.

Function Nacht(A, B)
 Dim Dauer
 'Wenn Anfang und Ende nicht am gleichen Tag sind, liegt eine Nacht dazwischen.
 If Tag(B) - Tag(A) \> 0 Then
 'Über Nacht
 If B \> (Tag(A) + 1) + Zeit("6:00") Then
 'Die Endzeit liegt am nächsten Tag nach 6:00 oder später.
 'Dazu wird die Funktion dann rekursiv aufgerufen. Zur Gesamtnachtzeit
 'werden also neun Stunden plus die Nachtzeiten des darauffolgenden
 'Tages gerechnet. Wenn sich das über mehrere Tage zieht, wird
 'die Funktion öfters rekursiv aufgerufen.
 Dauer = 9 + Nacht(Tag(A) + 1, B)
 Else
 'Die Endzeit liegt am nächsten Tag vor 6 Uhr. Die Nacht
 'berechtnet sich daher aus 24:00 - 21:00 plus der Zeit am
 'nächsten Tag.
 Dauer = 3 + Zeit(B)
 Else
 'Am selben Tag.
 If Zeit(B) \> Zeit("21:00") Then
 'Die Endzeit liegt zwischen 21 und 24 Uhr. Die Zeit ergibt
 'sich durch Subtraktion.
 Dauer = Zeit(B) - Zeit("21:00")
 Else
 'Die Endzeit liegt vor 21:00, daher keine Nachtzeit.
 Dauer = 0
 End If
 End If
 Return Dauer
End Function

Die Funktionen Tag und Zeit geben jeweils nur den Tag bzw. nur die Zeit des als Parameter übergebenen DateTime-Wertes zurück.

Ich hoffe dieser Ansatz hilft dir weiter.

mfg
christoph

#3

Hallo Jorge,

ich habe eine Datei, in der mehrere hundert Einträge stehen,
in Spalte A das Beginndatum, in Spalte B das Enddatum, in
Spalte C die Summe der Stunden. A und B im Format TT.MM.JJJJ
hh:mm. Die Summe der Stunden bilde ich durch B minus A mal 24.

Wenn ich das versuche, kommt da aber was ganz anderes raus … seltsam.
Mir fällt leider auch nicht wirklich was dazu ein; nur so kurz zur Info: Es gibt auch eine Funktion namens =STUNDE(), vielleicht kannst Du damit - eventuell an anderer Stelle - etwas anfangen.

Liebe Grüße

Jorge

#4

Hallo Christoph,

ich hab noch nie eine Funktion programmiert. Hättest du Lust, mir eine kurze Anleitung zu schreiben?
Googeln hat nich so viel gebracht und auch die Excel-Hilfe war nicht wirklich eine, ebensowenig wie der 7 Jahre alte Artikel in wer-weiß-was.

Hat es was mit der Benutzerdefinierten Funktion zu tun? Bei „Funktion auswählen“ bekomme ich die Anzeige „ZugriffVBA“. Aber dann verließen sie ihn bei mir.

Würde mich freuen, wenn ich was dazu lernen kann.

Liebe Grüße

Jorge

#5

Hallo Jorge,

ich hab noch nie eine Funktion programmiert. Hättest du Lust,
mir eine kurze Anleitung zu schreiben?

das ist einfach, du schreibst in ein Standardmodul, also z.B Modul1

Function Doppelt(Zelle as range)
Doppelt=zelle*2
End Function

dann schreibst du in A1 einen beliebigen Zahlenwert, in B1 dann
=Doppelt(A1)

Und die Funktion „Doppelt“ kannst du auch über Einfügen–Funktion–Benutzerdefiniert… einfügen.

Googeln hat nich so viel gebracht und auch die Excel-Hilfe war
nicht wirklich eine, ebensowenig wie der 7 Jahre alte Artikel
in wer-weiß-was.

Welcher Artikel?

Hat es was mit der Benutzerdefinierten Funktion zu tun? Bei
„Funktion auswählen“ bekomme ich die Anzeige „ZugriffVBA“.

Verstehe ich nicht, hast du eine Funktion selbsgebastelt die so heißt?
Oder, als Standard ist bei manchen Excelversionen dektiviert daß man dem Zugriff auf VBa vertrauen, ist es das?
Was machst du damit wie wodurch wo „Funktion auswählen“ erscheint?

Ich habe den Code von Christoph nur überflogen, da ich erstmal an einer Excel-Formellösung bin.

genau wie er kam ich darauf, VOLLE Tage zwischen den Terminen schlagen mit 9 Stunden zu Buche, siehe Spalte D.

dazu muß man nur noch, falls der Anfangstag unterschiedlich ist zum Endtag, die zeit am Anfangstag, die zwischen 21:00 und 23:59 (00:00?) liegt addieren, dazu dann das Gleiche für den Endtag.

Ist der Anfangstag auch der Endtag dann halt sinngemäß.

Wäre auch sehr wahrscheinlich schon gelöst, dazu gibt es ja eine entsprechende Formel auf Excelformeln.de.
Leider ist die falsch wie ich feststellen mußte, eine Ausnahme dort :frowning:

Da ich ne faule Socke bin warte ich ab bis sie da die Formel korrigiern :smile:

Ich schreib schneller eine Vba-Lösung für dein Problem als mich da in fremde lange Formeln einzuarbeiten.

Gruß
Reinhard

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │ D │
──┼────────────────┼────────────────┼────────┼────┤
2 │ 16.11.08 10:00 │ 16.11.08 12:00 │ 2,00 │ 0 │
──┼────────────────┼────────────────┼────────┼────┤
3 │ 16.11.08 10:00 │ 17.11.08 12:00 │ 26,00 │ 0 │
──┼────────────────┼────────────────┼────────┼────┤
4 │ 16.11.08 10:00 │ 22.11.08 12:00 │ 146,00 │ 45 │
──┼────────────────┼────────────────┼────────┼────┤
5 │ 16.11.08 10:00 │ 16.11.08 22:00 │ 12,00 │ 0 │
──┼────────────────┼────────────────┼────────┼────┤
6 │ 17.11.08 23:00 │ 18.11.08 23:00 │ 24,00 │ 0 │
──┼────────────────┼────────────────┼────────┼────┤
7 │ 18.11.08 23:00 │ 19.11.08 4:00 │ 5,00 │ 0 │
──┼────────────────┼────────────────┼────────┼────┤
8 │ 16.11.08 21:35 │ 16.11.08 22:12 │ 0,62 │ 0 │
──┼────────────────┼────────────────┼────────┼────┤
9 │ 16.11.08 4:00 │ 22.11.08 22:45 │ 162,75 │ 45 │
──┴────────────────┴────────────────┴────────┴────┘
Benutzte Formeln:
C2: =(B2-A2)\*24
C3: =(B3-A3)\*24
usw.

D2: =MAX(0;DATEDIF(A2;B2;"d")-1)\*9
D3: =MAX(0;DATEDIF(A3;B3;"d")-1)\*9
D4: =MAX(0;DATEDIF(A4;B4;"d")-1)\*9
usw.

Zahlenformate der Zellen im gewählten Bereich:
A2:A9,B2:B9
haben das Zahlenformat: T.M.JJ h:mm
C2:C9
haben das Zahlenformat: 0,00
D2:smiley:9
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

#6

Hallo Jorge!

Bin gestern erst später fertig geworden und hatte dann keine Zeit mehr zum Hereinstellen. Es waren in meinem Code noch einige logische Fehler, die musste ich noch ausbessern (dabei habe ich mein eigenes Konzept über den Haufen geworfen).

Um eine benutzerdefinierte Funktion zu verweden, öffnest du zuerst über das Menü Extras->Makro den Visual-Basic-Editor. Dort legst du dann mit Einfügen->Modul ein neues Modul an. Hier könntest du dann eigenen VBA-Code eingeben.

Die Funktion schaut so aus:

Public Function Nachtstunden(A As Date, B As Date) As Integer
 If DateDiff("d", A, B) = 0 Then
 'Anfang und Ende am selben Tag
 If Hour(A) 21 Then
 Dim Anfang As Integer
 If Hour(A) \> 21 Then
 Anfang = Hour(A)
 Else
 Anfang = 21
 End If
 Nachtstunden = Nachtstunden + (Hour(B) - Anfang)
 If Hour(B) = 23 And Minute(B) = 59 Then
 Nachtstunden = Nachtstunden + 1
 End If
 End If
 Else
 'Differenz zwischen den Tagen
 Dim Trenner As Date
 'Rekursiver Aufruf mit um Mitternacht getrennten Werten
 Trenner = DateAdd("n", 1439, DateSerial(Year(A), Month(A), Day(A)))
 Nachtstunden = Nachtstunden(A, Trenner)
 Trenner = DateAdd("n", 1, Trenner)
 Nachtstunden = Nachtstunden + Nachtstunden(Trenner, B)
 End If
End Function

Folgende Hinweise noch:
Es werden nur die Stunden beachtet. Das heißt keine halben oder viertel Stunden. Möchte man bis zur Minute genau sein, müsste man aber nicht viel ändern.
Und wenn die Endzeit genau „23:59“ ist, dann wird das als ganzer Tag, also bis 00:00 gerechnet. Das ist deshalb nötig, da ich so die längeren Abschnitte auf einzelne Tage aufteile.

Wenn du den Code eingefügt hast, brauchst du den Visual-Basic-Editor einfach nur zu schließen. Im Excel-Arbeitsblatt setzt du die Formel, dann einfach so ein

=Nachtstunden(A2;A3)

Ich hoffe das funktioniert auch bei dir und ich habe keine Fehler übersehen.

mfg
christoph

1 Like
#7

Hallo Reinhard!

dazu muß man nur noch, falls der Anfangstag unterschiedlich
ist zum Endtag, die zeit am Anfangstag, die zwischen 21:00 und
23:59 (00:00?) liegt addieren, dazu dann das Gleiche für den
Endtag.

Du musst aber auch beachten, wenn der Anfangszeitpunkt vor sechs Uhr liegt. Dann fallen am ersten Tag auch in der Früh noch Stunden an. Genauso wenn der Endzeitpunkt nach 21 Uhr liegt.
Ich nehme zwar an, dass man das alles noch mit Excel-Formeln lösen kann, aber kurz wird die Formel auf jeden Fall nicht.

mfg
christoph

#8

Hallo Christoph,

habs probiert und schriftlich !! nachgerechnet: funktioniert einwandfrei!! Danke.

Folgende Hinweise noch:
Es werden nur die Stunden beachtet. Das heißt keine halben
oder viertel Stunden. Möchte man bis zur Minute genau sein,
müsste man aber nicht viel ändern.

Da bin ich natürlich neugierig. Ich weiß nicht, wie oft das vorkommt, aber genauer wäre es allemal.
In diesem Zusammenhang die Bitte, wenn du Zeit hast, mir doch noch mehr Erläuterungen zu deiner Funktion zu schreiben. Verstanden habe ich sie auch im Groben nicht…:smile:

Und wenn die Endzeit genau „23:59“ ist, dann wird das als
ganzer Tag, also bis 00:00 gerechnet. Das ist deshalb nötig,
da ich so die längeren Abschnitte auf einzelne Tage aufteile.

Das kommt nicht vor. Maximal viertelstunden-Beginn und -Ende.

Wenn du den Code eingefügt hast, brauchst du den
Visual-Basic-Editor einfach nur zu schließen. Im
Excel-Arbeitsblatt setzt du die Formel, dann einfach so
ein

=Nachtstunden(A2;A3)

Mit A2:A3 ( ich habs kopiert) hast du mich natürlich erst mal irritiert…:smile:

Ich hoffe das funktioniert auch bei dir

wie gesagt: einwandfrei

und ich habe keine Fehler übersehen.

Das weiß ich natürlich nicht…

Mfg

Jorge

#9

Hallo Christoph,

Ich nehme zwar an, dass man das alles noch mit Excel-Formeln
lösen kann, aber kurz wird die Formel auf jeden Fall nicht.

genau dadran hänge ich ja noch.
Wie ich an deinem Code sehen sind wir uns ja einig was da alles abzuprüfen ist.

Apropos dein Code, anscheinend weißt du das nicht.
Excel-Vba ist hauptsächlich ein Interpreter. Beim Start einer Prozedur wird nur wenig kompiliert.
Wenn du mal im I-Net auf eine Seite stösst wo steht was genau da kompiliert wird, so bin ich daran sehr interessiert.

Was ich aber weiß ist, alle DIM-Anweisunngen werden beim Prozeduraufruf kompiliert und das unabhängig davon wo im Prozedurcode sie stehen.
Ergo, alle DIMs zuanfangs der Prozedur schreiben aus Übersichtsgründen. Sie in einer If-Schleife zu stellen bringt keinerlei Vorteile, nur Übersichtsnachteil.

Zurück zu einer Excel-Formel-Lösung.
Ja, meine bisherigen Versuche sind schon ellenlange Formelwürmer und decken noch gar nicht alles ab.
Aber macht nix, ist gut für den Kopf da zu probieren die Formel zu minimieren obwohl ich als Problemlösung logo eine Vba-Lösung wie deine besser finde.

Gruß
Reinhard

#10

Hallo Reinhard,

dir und allen anderen auch ein gutes neues Jahr.

Nur zum sagen: die Funktion von Christoph funktioniert, wie oben schon erwähnt, wenn auch nicht auf die viertelstunde genau…:smile:

Liebe Grüße

Jorge