Excel-Zeitsummen, Berücksichtigung Datumsgrenze

Computer: Software & Programmierung Visual Basic
#1

Hallo,

Ich bin VBA Neuling!!

Folgendes Problem - ich habe eine Tabelle die wie folgt aussieht:
Zelle A1 (Startzeit), Zelle B1 (Startdatum), Zelle C1 (Endzeit), Zelle D1 (Enddatum)

z.B. 15:00, 06.07.2010, 12:00, 07.07.2010

Davon habe ich dann mehrere Zeilen (ein Monat 01.07.2010 - 31.07.2010) mit unterschiedlichen Zeiten und Datum (ein Datum kann öfter als einmal vorkommen).

Ich benötige die Summe der Stunden pro Tag d.h. wenn Start und Ende an unterschiedlichem Datum sind (wie im Beispiel oben), sollen die jeweiligen Stunden dem richtigen Datum zugerechnet werden - Beispiel oben: 9 Stunden am 06.07.2010 und 12 Stunden am 07.07.2010.

Problem ist dann auch, wenn sich ein Ereignis über mehrere Tage zieht z.B. 12:00, 06.07.2010 bis 12:00, 10.07.2010. Das wären dann 12 Stunden am 06.07, jeweils 24 Stunden am 07., 08. und 09.07, und 12 Stunden am 10.07.2010

Zeiträume können sich auch überlappen d.h. pro Tag kann das Zeitsummen-Ergebnis auch über 24 Stunden liegen.

Wie kann ich das hinbekommen, dass ich am Ende eine Liste habe mit
01.07.2010 x Stunden
02.07.2010 y Stunden
…

Vielen Dank schon vorab für die Hilfe!!
LG
Phillip

#2

Hi Phillip,

die Frage, die sich mir aufdrängt ist, warum per VBA, wenn Excel das doch so kann…

  1. Addiere Datum und Uhrzeit in einer Spalte für Beginn und Ende
  2. Subtrahiere die größere Zeit von der kleineren
  3. Multipliziere diesen Wert mit 24 (weil excel in Tagen rechnet) oder Formatiere das Ergebnis benutzerdefiniert in [HH]:mm (gibt über 24 Stunden aus)

Dannach kannst du ja die Summe über die Spalte ziehen.

Geht natürlich auch in VBA, wenns sein muss, wäre da aber eher aufwändiger.

HTH

Gruß
Proteus

#3

Hallo Proteus,

Das würde mir die gesamte Zeit von Anfangszeit bis Endzeit geben, jedoch nicht die Mitternachtsgrenze berücksichtigen z.B. alles bis 24:00 soll dem gestrigen Datum und alles nach 00:00 dem heutigen Datum zugerechnet werden.

Beispiel:

Zeile 1: 1.1.2010 12:00 4.1.2010 12.00
Zeile 2: 2.1.2010 06:00 5.1.2010 06:00

Ergebnis sollte dann sein:
1.1.2010: 12h
2.1.2010: 42h
3.1.2010: 48h
4.1.2010: 36h
5.1.2010: 6h

LG
Phillip

#4

Hi Phillip,

jetzt hab ich es kapiert :smile:

Das sieht dann natürlich ein wenig anders aus…

Kannst du rudimentär damit was anfangen:

http://77.76.199.9/test1.xls

Das wäre mal ein Ansatz, den su dann auf deine Datei anpassen musst.

Gruß
Proteus

#5

Hallo Proteus,

Vielen Dank - ich habe mir dein Beispiel runtergeladen. Wenn ich den Button klicke passiert aber nichts? Wie gesagt, ich bin diesbezüglich wirklich Komplettanfänger - vielleicht mache ich was falsch oder muss noch eine Einstellung verändern?

Danke
Phillip

#6

Hi,

drücke Alt+F11 um in den VBA-Editor zu gelangen.
Dort steht die funktion. Im direktfenster (Strg+G) stehen deine werte dann, nachdem du auf die schaltfläche geklickt hast.

Ein wenig mit VBA musst such dich auf jeden Fall auseinandersetzen.

Gruß
Proteus

#7

Hallo Phil,

grundsätzlich gibt es auch eine Formellösung über Addition von mehreren SUMMENPRODUKT-Ausdrucken.

Alternativ eine etwas langsamere benutzerdefinierte VBA-Funktion, die in der Zelle etwas übersichtlicher ist. Die Function muss in ein allgemeines Modul der Datei eingefügt werden.

Gruß
Franz

Beispiel-Daten

Tabellenblattname: Zeiten

 A B C D 
1 Startdatum Startzeit Endedatum Endezeit 
2 01.01.2010 08:00 01.01.2010 19:00 
3 02.01.2010 09:00 03.01.2010 20:00 
4 05.01.2010 08:00 17.01.2010 08:00 
5 13.01.2010 08:30 14.01.2010 11:45 




Tabellenblattname: Summen

 A B C D E F G H 
 1 Zeit Zeit Zeit Zeit Function Function 
 2 Datum 1. Tag 24 h letzter Tag Summe Formel hh:mm Dezi 
 3 01.01.2010 11:00 0:00 0:00 11:00 11:00 11:00 11,00 
 4 02.01.2010 15:00 0:00 0:00 15:00 15:00 15:00 15,00 
 5 03.01.2010 0:00 0:00 20:00 20:00 20:00 20:00 20,00 
 6 04.01.2010 0:00 0:00 0:00 0:00 0:00 0:00 0,00 
 7 05.01.2010 16:00 0:00 0:00 16:00 16:00 16:00 16,00 
 8 06.01.2010 0:00 24:00 0:00 24:00 24:00 24:00 24,00 
 9 07.01.2010 0:00 24:00 0:00 24:00 24:00 24:00 24,00 
10 08.01.2010 0:00 24:00 0:00 24:00 24:00 24:00 24,00 
11 09.01.2010 0:00 24:00 0:00 24:00 24:00 24:00 24,00 
12 10.01.2010 0:00 24:00 0:00 24:00 24:00 24:00 24,00 
13 11.01.2010 0:00 24:00 0:00 24:00 24:00 24:00 24,00 
14 12.01.2010 0:00 24:00 0:00 24:00 24:00 24:00 24,00 
15 13.01.2010 15:30 24:00 0:00 39:30 39:30 39:30 39,50 
16 14.01.2010 0:00 24:00 11:45 35:45 35:45 35:45 35,75 
17 15.01.2010 0:00 24:00 0:00 24:00 24:00 24:00 24,00 
18 16.01.2010 0:00 24:00 0:00 24:00 24:00 24:00 24,00 
19 17.01.2010 0:00 0:00 8:00 8:00 8:00 8:00 8,00 
20 18.01.2010 0:00 0:00 0:00 0:00 0:00 0:00 0,00 

Benutzte Formeln:
B3: =SUMMENPRODUKT((A3=Start.Datum)\*(A3=Ende.Datum)\*(Ende.Zeit-Start.Zeit))
 +SUMMENPRODUKT((A3=Start.Datum)\*(A3Start.Datum)\*(A3Start.Datum)\*(A3=Ende.Datum)\*(Ende.Zeit))
D20: =SUMMENPRODUKT((A20\>Start.Datum)\*(A20=Ende.Datum)\*(Ende.Zeit))
E3: =SUMME(B3:smiley:3)
F3: =SUMMENPRODUKT((A3=Start.Datum)\*(A3=Ende.Datum)\*(Ende.Zeit-Start.Zeit))
 +SUMMENPRODUKT((A3=Start.Datum)\*(A3Start.Datum)\*(A3Start.Datum)\*(A3=Ende.Datum)\*(Ende.Zeit))
G3: =ZeitSumme(A3;Start.Datum;Start.Zeit;Ende.Datum;Ende.Zeit)
H3: =ZeitSumme(A3;Start.Datum;Start.Zeit;Ende.Datum;Ende.Zeit;WAHR)

Namen in der Datei:
Ende.Datum : =Zeiten!$C$2:blush:C$7
Ende.Zeit : =Zeiten!$D$2:blush:D$7
Start.Datum : =Zeiten!$A$2:blush:A$7
Start.Zeit : =Zeiten!$B$2:blush:B$7

Benutzerdefinierte Funktion:
Function ZeitSumme(Datum As Date, StartDatum As Range, StartZeit As Range, \_
 EndeDatum As Range, EndeZeit As Range, Optional Stunden As Boolean = False) As Double
 'folgende Formel umgesetzt in benutzerdefinierte VBA-Function
 'es werden die auf den jeweiligen Kalendertag entfallenden Stunden berechnet
 ' =SUMMENPRODUKT((A2=Start.Datum)\*(A2=Ende.Datum)\*(Ende.Zeit-Start.Zeit)) \_
 +SUMMENPRODUKT((A2=Start.Datum)\*(A2Start.Datum)\*(A2Start.Datum)\*(A2=Ende.Datum)\*(Ende.Zeit))
 Dim Zeile As Long
 For Zeile = 1 To StartDatum.Rows.Count
 'Anteil 1. Tag, Anfang und Ende am gleichen Tag
 ZeitSumme = ZeitSumme + IIf((Datum = StartDatum(Zeile, 1)) \_
 \* (Datum = EndeDatum(Zeile, 1)), EndeZeit(Zeile, 1) - StartZeit(Zeile, 1), 0)
 'Anteil 1. Tag, Anfang und Ende sind verschieden Tag
 ZeitSumme = ZeitSumme + IIf((Datum = StartDatum(Zeile, 1)) \_
 \* (Datum StartDatum(Zeile, 1)) \_
 \* (Datum StartDatum(Zeile, 1)) \_
 \* (Datum = EndeDatum(Zeile, 1)), EndeZeit(Zeile, 1), 0)
 Next
 If Stunden = True Then ZeitSumme = ZeitSumme \* 24
End Function
#8

Hallo Proteus,

Vielen Dank - das bringt mir das gewünschte Ergebnis.
Zwei Fragen noch:

1)ist es möglich die Ergebnisse wie im Direktfenster auch direkt in Excel (z.B. im nächsten Tabsheet auszugeben (2 Spalten [Datum, Stunden])) wenn man den Button drückt?

2)So wie der Code jetzt ist, müsste ich für jede Zeile einen separaten Button haben wenn ich das richtig sehe. Ich habe ca. 800 Zeilen jedes Monat - kann man mit einem Button gleich alle Zeilen auf einmal laut dem Code bearbeiten lassen?

Danke
LG
Phillip

#9

Hi,

das war ja nur ein BeispielCode…deswegen solltest du dich ja ein wenig mit VBA auseinandersetzen…:smile:)

1)ist es möglich die Ergebnisse wie im Direktfenster auch
direkt in Excel (z.B. im nächsten Tabsheet auszugeben (2
Spalten [Datum, Stunden])) wenn man den Button drückt?

Klar. Statt Degub.print … schreibst du z.B. Range(„Tabelle2!A1“) = …

2)So wie der Code jetzt ist, müsste ich für jede Zeile einen
separaten Button haben wenn ich das richtig sehe. Ich habe ca.
800 Zeilen jedes Monat - kann man mit einem Button gleich alle
Zeilen auf einmal laut dem Code bearbeiten lassen?

Da kommt jetzt um das Ganze noch eine For…next-Schleife drumrum, die das Genze zeilenweise durchläuft.
Ich weiss ja nicht von wo bis wo dein Bereich geht…
For…Next findest du in der VB-Hilfe. Und die Bereich, die in Range inAnführungszeichen stehen, kannst du als Text zusammensetzen.
For x = 1 to 100
Range(„A“ & x) = irgendwinberechneterwert
next

Also dann mal basteln

Gruß
Proteus

#10

Vielen Dank - das hat mir sehr geholfen!
Dann werd ich mich jetzt mal verkriechen und basteln :wink:

LG
Phillip