PDA

Vollständige Version anzeigen : Zeitraum/Stunden Monaten zuordnen


bra
08.07.2014, 09:02
Hallo zusammen,

ich habe folgende Herausforderung, saß bereits einige Stunden an dem Thema, und hoffe, dass hier jemand die Lösung weiß. Ich möchte die Stunden (also stundengenau) den jeweiligen Monten zuordnen. Die Tabelle sieht so aus:

A B C D ...
Startzeit Endezeit Jan 2008 Feb 2008 ... 01.01.2008 00:50:00 20.02.2008 13:00:00
05.01.2008 03:54:00 04.05.2008 15:22:22

In den Spalten soll die auf den Monat entfallende Summe von Stunden zugeordnet werden.

Danke für Eure Hilfe.

Viele Grüße

Bra

Mc Santa
08.07.2014, 09:51
Hallo,

ich habe folgende Formellösung:
<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:111px;" /><col style="width:111px;" /><col style="width:53px;" /><col style="width:57px;" /><col style="width:57px;" /><col style="width:56px;" /><col style="width:57px;" /><col style="width:54px;" /><col style="width:50px;" /><col style="width:58px;" /><col style="width:57px;" /><col style="width:56px;" /><col style="width:59px;" /><col style="width:57px;" /><col style="width:53px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td><td>N</td><td>O</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC99 ;; text-align:left; ">DATF</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#CCCCFF ;; text-align:left; ">DATT</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Jan 08</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Feb 08</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Mrz 08</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Apr 08</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Mai 08</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Jun 08</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Jul 08</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Aug 08</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Sep 08</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Okt 08</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Nov 08</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Dez 08</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Jan 09</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC99 ;; text-align:right; ">1.1.08 0:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#CCCCFF ;; text-align:right; ">31.12.08 0:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">744</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">696</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">744</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">720</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">744</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">720</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">744</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">744</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">720</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">744</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">720</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">720</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">7.12.08 15:30</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">9.12.08 10:26</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">42,9333333332324</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">17.12.08 14:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">17.12.08 22:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">8,0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">14.12.08 2:21</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">14.12.08 6:10</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">3,82</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>C2:N5</td><td>=(MIN($B2;D$1)-MAX($A2;C$1))*N($A2&lt;=D$1)*N($B2&gt;=C$1)*24</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt;width:800px; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCCCC; text-align:center; font-weight:bold; "><td colspan="3" > Zahlenformate </td></tr><tr valign="top" style="background-color:#FFCCCC; text-align:center; font-weight:bold; "><td> Zelle </td><td> Format </td><td> Wert </td></tr><tr><td>A2:B5 </td> <td style="text-align:center; ">'T.M.JJ h:mm;@</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>C1:O1 </td> <td style="text-align:center; ">'MMM JJ</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>C2:N5 </td> <td style="text-align:center; ">'[=0]0;#.##0,0#</td> <td style="text-align:right; ">&nbsp;</td></tr></table>

Hilft dir das weiter?
VG

@Chris: Sehr schöne Pivotlösung, ich lerne da immer noch dazu!

chris-kaiser
08.07.2014, 09:59
Hi,

so etwas?

Mc Santa
08.07.2014, 10:44
Hi,

so etwas?

Hallo Chris,

in deiner Tabelle ist leider ein Fehler: die erste Zeile wird komplett dem Januar zugerechnet, statt die Stunden auf alle Monate im Jahr zu verteilen. Das ist immer der Fall, wenn das Ende-Datum nicht im gleichen Monat ist, wie das Start-Datum.

Vielleicht kann man das trotzdem in der Pivot korrigieren?

VG

chris-kaiser
08.07.2014, 10:53
Hi,

das werden aber viele Spalten^^

Nein, rein über Pivot geht das nicht aber ich würde einfach die Termine in Monaten darstellen. Mal schauen vielleicht bastele ich da was, ich glaube sogar das ich das mal brauchen könnte.

chris-kaiser
08.07.2014, 12:46
Hi,

so
ich habe mal aufgeteilt,

Sub TeilMichauf()
Dim var(), arrNew() As Date, i As Long, lng_C As Long, TmpDate1 As Date, TmpDate2 As Date
Dim sh As Worksheet
Set sh = Tabelle1
With sh
var = .Range(.Cells(2, 1), .Cells(.Rows.Count, 2).End(xlUp))
For i = LBound(var) To UBound(var)
TmpDate1 = CDate(var(i, 1))
TmpDate2 = CDate(var(i, 2))
Do
ReDim Preserve arrNew(2, lng_C)
If check(TmpDate1, CDate(var(i, 2))) = True Then
Else
arrNew(0, lng_C) = CDate(TmpDate1)
TmpDate2 = IIf(TmpDate2 <= var(i, 2), DateSerial(Year(TmpDate1), Month(TmpDate1) + 1, 0) + TimeValue("23:59:59"), var(i, 2))
arrNew(1, lng_C) = CDate(TmpDate2)
arrNew(2, lng_C) = TmpDate2 - TmpDate1
TmpDate1 = TmpDate2 + TimeValue("00:00:01")
TmpDate2 = IIf(TmpDate2 <= var(i, 2), DateSerial(Year(TmpDate1), Month(TmpDate1) + 1, 0) + TimeValue("23:59:59"), var(i, 2))
lng_C = lng_C + 1
End If
Loop Until check(TmpDate1, CDate(var(i, 2)))
ReDim Preserve arrNew(2, lng_C)
arrNew(0, lng_C) = CDate(TmpDate1)
arrNew(1, lng_C) = CDate(var(i, 2))
arrNew(2, lng_C) = CDate(var(i, 2) - TmpDate1)
lng_C = lng_C + 1
Next
End With
'oder gleicher Stelle
With Tabelle2
sh.Range("A1:C1").Copy .Cells(1, 1)
With .Range(.Cells(2, 1), .Cells(2, 3)).Resize(UBound(arrNew, 2))
.Value = WorksheetFunction.Transpose(arrNew)
'.NumberFormat = "general" ^^
End With
End With
End Sub

Function check(ByVal t1 As Date, ByVal t2 As Date) As Boolean
If Month(t1) <> Month(t2) Or Year(t1) <> Year(t2) And t1 < t2 Then
check = False
Else
check = True
End If
End Function


da hätte ich selber eine Frage :), ich schaffe es noch nicht das Datenarray wirklich im Datumsformat auszugeben?
Ist zwar nur eine Kleinigkeit das (im nachhinein) umzuwandeln, aber nervig. :grins:

Sind die Daten richtig geteilt
dann ist es leicht eine Pivot daraus zu erzeugen.

bra
08.07.2014, 16:04
Hey! Danke für die Hilfe. Hab zwar schon in VBA programmiert, bin jetzt aber etwas überfordert...

1) wo muss ich den Code denn reinkopieren (Hab bisher immer User Form --> Option button benutzt)?
2) Auf welchen Teil bezieht sich "'oder gleicher Stelle".
3) Was muss ich denn noch ändern, um das auf eine andere Datei/andere Spalten zu beziehen (das war nur eine Beispieldatei für mehrere "Problemdateien", bei denen ich die Verteilung anwenden möchte?

Dann probier ichs morgen mal und schreibe da irgendwelche Fake-Werte rein, um zu sehen, ob es richtig aufteilt.

Sieht auf jeden Fall supi aus ;-)

LG Bra

bra
08.07.2014, 16:06
@ Santa deine Lösung gefällt mir auch sehr gut. Vielleicht besser fürn "Anfänger" wie mich!? Mal schaun, obs mit VBA klappt, sonst werd ich deine Lösung mal ausprobieren.

LG Bra

chris-kaiser
08.07.2014, 16:08
Hallo Bra,

der Code ist jetzt wirklich nur für diese Mappe ausgelegt....
um die Daten "aufzubereiten" damit diese über Pivot abgefragt werden können.

"'oder gleicher Stelle".

damit habe ich gemeint, ich könnte auch gleich über die alten Daten mit den neuen Daten überschreiben, mehr nicht.