Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


Projektzeiterfassung
#1
Hallo allerseits,

und im Voraus vielen Dank, falls Ihr Euch meiner Frage annehmt.

Problemstellung:
Für eine Projektzeiterfassung sollen die Zeiten, die an ein und demselben Tag erbracht wurden, automatisch saldiert werden. Dabei steht nicht fest, ob am Tag 1x, 2x oder gar 5x an einem Projekt gearbeitet wird.

Beispiel einer Zeiterfassung:
Code:
    A                      B             C                D
1    Datum           Beginn    Ende            Projektzeit
2    14.04.2014    21:58    23:59    02:01
3    15.04.2014    00:00    00:09    00:09
4    15.04.2014    18:11    20:20    02:09
5    15.04.2014    22:39    23:59    01:20
6    16.04.2014    18:44    19:18    00:34
7    16.04.2014    19:41    20:21    00:40
8    17.04.2014    10:23    11:45    01:22
9    17.04.2014    15:03    15:47    00:44
10    17.04.2014    19:47    20:56    01:09
11    17.04.2014    21:25    22:54    01:29
12    17.04.2014    23:41    23:59    00:18

Gewünschtes Ergebnis:
Code:
14.04.2014    02:01
15.04.2014    03:38
16.04.2014    01:14
17.04.2014    05:02
Eventuell noch wochenweise zusammengefasst.

Wenn nur maximal drei Projektzeiten am Tag erbracht werden, könnte man das für die Projektzeit der ersten Zeile mit nachfolgender Formel machen:
=WENN(A2=A3;WENN(A3=A4;WENN(A4=A5;E5+E4+E3+E2;E4+E3+E2);E3+E2);E2)

Ab vier Projektzeiten werden es zu viele Argumente. Also muss irgend eine VBA-Schleife her.
Sinngemäß:
Die Zelle mit dem Datum der ersten Projektzeit muss aktiviert sein (hier A6).
Summe P=D6; Zähler I=1;
Wenn A7=A6 dann P=P+D7 sonst trage P in Zelle E6 ein, formatiere sie fett und mit Texthervorhebungsfarbe GRAU und aktiviere Zelle A(6+I)
Sobald in der Spalte A eine leere Zelle erreicht ist, soll der VBA-Code beendet werden.

Ich kann es leider nicht selbst in VBA umsetzen.

Herzliche Grüße

Michael


Auch hier im neuen Forum gilt:
Tabelle strukturiert dargestellt durch 3. Button von rechts im Beitragsformular: #
Moderator
?mage
to top
#2
Hallo,

ich würde hier gar nicht mit VBA arbeiten sondern werfe einfach mal eine Pivottabelle in den Ring:

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGH
1DatumBeginnEndeProjektzeit    
214.04.201421:5823:5902:01    
315.04.201400:0000:0900:09    
415.04.201418:1120:2002:09    
515.04.201422:3923:5901:20  ZeilenbeschriftungenSumme von Projektzeit
616.04.201418:4419:1800:34  14.04.201402:01
716.04.201419:4120:2100:40  15.04.201403:38
817.04.201410:2311:4501:22  16.04.201401:14
917.04.201415:0315:4700:44  17.04.201405:02
1017.04.201419:4720:5601:09  Gesamtergebnis11:55
1117.04.201421:2522:5401:29    
1217.04.201423:4123:5900:18    
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Eine Zelle in der Datentabelle markieren - Einfügen - Pivottable und dem Assistenten folgen. Ziehe das Feld Datum in die Zeilenbeschriftung und das Feld Projektzeit in das Wertefeld. Das sollte es eigentlich schon gewesen sein.

Wenn du zuätzlich noch Kalenderwochen willst dann erstelle in der Spalte E eine Hilfsspalte in der du Kalenderwoche, bezogen auf das Datum der Spalte A errechnest, und verwende dieses Feld dann zusätzlich in der PT

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHI
1DatumBeginnEndeProjektzeitKW    
214.04.201421:5823:5902:0116    
315.04.201400:0000:0900:0916    
415.04.201418:1120:2002:0916    
515.04.201422:3923:5901:2016    
616.04.201418:4419:1800:3416    
716.04.201419:4120:2100:4016  ZeilenbeschriftungenSumme von Projektzeit
817.04.201410:2311:4501:2216  1611:55
917.04.201415:0315:4700:4416  14.04.201402:01
1017.04.201419:4720:5601:0916  15.04.201403:38
1117.04.201421:2522:5401:2916  16.04.201401:14
1217.04.201423:4123:5900:1816  17.04.201405:02
13       Gesamtergebnis11:55
14         

ZelleFormel
E2=KALENDERWOCHE(A2;21)
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß
Peter
to top
#3
Hallo Michael,

das geht ohne VBA.

Tabelle5
ABCDEFG
1DatumBeginnEndeProjektzeitDatumZeit
214.04.201421:5823:5902:0114.04.20142:01
315.04.201400:0000:0900:0915.04.20143:38
415.04.201418:1120:2002:0916.04.20141:14
515.04.201422:3923:5901:2017.04.20145:02
616.04.201418:4419:1800:34
716.04.201419:4120:2100:40
817.04.201410:2311:4501:22
917.04.201415:0315:4700:44
1017.04.201419:4720:5601:09
1117.04.201421:2522:5401:29
1217.04.201423:4123:5900:18

verwendete Formeln
Zelle Formel Bereich N/A
F2=MIN(A:A)
F3:F12{=WENN(MIN(WENN(A:A>F2;A:A); )<F2;"";MIN(WENN(A:A>F2;A:A); ))}$F$3
G2:G12=WENN(F2<>"";SUMMENPRODUKT(($A$2:$A$12=F2)*($D$2:$D$12); );"")
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!


Zahlenformate
Zelle Format Wert
A2, F2 'TT.MM.JJJJ 41743
A3:A5, F3 'TT.MM.JJJJ 41744
A6:A7, F4 'TT.MM.JJJJ 41745
A8:A12, F5 'TT.MM.JJJJ 41746
B2 'hh:mm 0,915277777777778
B3 'hh:mm 0
B4 'hh:mm 0,757638888888889
B5 'hh:mm 0,94375
B6 'hh:mm 0,780555555555556
B7 'hh:mm 0,820138888888889
B8 'hh:mm 0,432638888888889
B9 'hh:mm 0,627083333333333
B10 'hh:mm 0,824305555555556
B11 'hh:mm 0,892361111111111
B12 'hh:mm 0,986805555555556
C2, C5, C12 'hh:mm 0,999305555555556
C3: D3 'hh:mm 0,00625
C4 'hh:mm 0,847222222222222
C6 'hh:mm 0,804166666666667
C7 'hh:mm 0,847916666666667
C8 'hh:mm 0,489583333333333
C9 'hh:mm 0,657638888888889
C10 'hh:mm 0,872222222222222
C11 'hh:mm 0,954166666666667
D2 'hh:mm 8,40277777777778E-02
D4 'hh:mm 8,95833333333333E-02
D5 'hh:mm 5,55555555555556E-02
D6 'hh:mm 2,36111111111111E-02
D7 'hh:mm 2,77777777777778E-02
D8 'hh:mm 5,69444444444444E-02
D9 'hh:mm 3,05555555555556E-02
D10 'hh:mm 4,79166666666667E-02
D11 'hh:mm 6,18055555555556E-02
D12 'hh:mm 0,0125
F6:F12 'TT.MM.JJJJ
G2 '[h]:mm 8,40277777777778E-02
G3 '[h]:mm 0,151388888888889
G4 '[h]:mm 5,13888888888889E-02
G5 '[h]:mm 0,209722222222222
G6:G12 '[h]:mm
Zellen mit Format Standard werden nicht dargestellt
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 17.11 einschl. 64 Bit



GrußformelHomepage
to top
#4
Hallo Hajo,

Danke für Deine Antwort. Und zuerst: Ich verwende Windows7 und MS Office2007.

Ich habe gerade Deine Lösung in einer neuen Tabelle eingegeben. Die Zelle F2 funktioniert wie beschrieben. Die Zelle G2 bringt die Fehlermeldung #WERT!. Die Zellen F3 bis F12 und G3 bis G12 bleiben leer.

Also irgend etwas habe ich falsch umgesetzt, obwohl ich es aus Deinem Beitrag kopiert und die Hinweise zur Matrixformel beachtet habe. In der Spalte D habe ich natürlich jeweils eine Formel (Differenz der Zeiten).

An Deiner Antwort mit der Pivot-Tabelle bastele ich noch.

Gruß Michael und Danke.
to top
#5
Hallo Michael,

Du hast alle Texte bei der Tabelle beachtet. Ich vermute Nein. Den Hinweis zu Array hast Du nicht umgesetzt.
Ich sehe Deine Datei nicht, darum kann ich nur vermuten.

Gruß Hajo
to top
#6
Hallo Hajo,

er schreibt, dass die Fehlermeldung in G2 kommt und dort steht keine Matrixformel.

Du hast in G2 folgende Formel stehen

=WENN(F2<>"";SUMMENPRODUKT(($A$2:$A$12=F2)*($D$2:$D$12); );"")

Kann es sein, dass das Addin die Formel nicht richtig übersetzt, denn dieses ; nach $D$12) passt m.E. nicht. Die Formel müsste doch eigentlich so aussehen:

=WENN(F2<>"";SUMMENPRODUKT(($A$2:$A$12=F2)*($D$2:$D$12));"")
Gruß
Peter
to top
#7
Hallo Peter,

ich habe die Baumstruktur damit ich immer nur einen sehe. Damit ich nicht aus versehen einen Beitrag von jemanden lese der auf meiner roten Liste steht.
Du hast Recht in G2 steht keine Array Formel Dann trifft mein Zusatz zu. ich sehe die Datei nicht.

Gruß Hajo
to top
#8
Hallo Hajo,

Zitat:ich sehe die Datei nicht.

du siehst aber die Formel die du gepostet hast und die scheint mir etwas seltsam zu sein wie ich bereits in meinem vorherigen Beitrag geschrieben habe.

Auch die Matrixformel in F2 müsste eigentlich so aussehen

=WENN(MIN(WENN(A:A>F2;A:A))F2;A:A)))

und nicht wie in deinem Beitrag dargestellt so:

=WENN(MIN(WENN(A:A>F2;A:A); )F2;A:A); ))
Gruß
Peter
to top
#9
Hallo Peter,

in meinem Beispiel wird das richtige Ergebnis angezeigt, das reicht mir.

Tabelle5
ABCDEFGH
1DatumBeginnEndeProjektzeitDatumZeit
214.04.201421:5823:5902:0114.04.20142:012:01
315.04.201400:0000:0900:0915.04.20143:383:38
415.04.201418:1120:2002:0916.04.20141:141:14
515.04.201422:3923:5901:2017.04.20145:025:02
616.04.201418:4419:1800:34
716.04.201419:4120:2100:40
817.04.201410:2311:4501:22
917.04.201415:0315:4700:44
1017.04.201419:4720:5601:09
1117.04.201421:2522:5401:29
1217.04.201423:4123:5900:18

verwendete Formeln
Zelle Formel Bereich N/A
F2=MIN(A:A)
H2:H5=SUMMEWENN($A$2:$A$12;F2;$D$2:$D$12)
F3:F12{=WENN(MIN(WENN(A:A>F2;A:A); )<F2;"";MIN(WENN(A:A>F2;A:A); ))}$F$3
G2:G12=WENN(F2<>"";SUMMENPRODUKT(($A$2:$A$12=F2)*($D$2:$D$12); );"")
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 17.11 einschl. 64 Bit



Gruß Hajo
to top
#10
Hallo nochmal Hajo,

jetzt läuft auch die Pivot-Tabelle (meine allererste).

Nur das mit der Kalenderwoche habe ich noch nicht begriffen. Spalte E angelegt und KW berechnet - ok. Aber wie ich die PT dazu bekomme mir die Wochensummen anzuzeigen, habe ich noch nicht herausbekommen. Wie gesagt, es ist meine allererste PT Dodgy

Aber arbeiten kann ich so erst mal. Danke.

Gruß Michael
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste