Werte-Anzahl anhand von Datum + Uhrzeit

Fabian0787 · 2. Februar 2017

Hallo Leute,

ich brauche eure Hilfe bei einer Auswertung und komme irgendwie nicht drauf wie ich das Problem lösen kann:

Ich habe folgende Tabelle mit zwei Spalten:

Datum Uhrzeit
11.05.2016 22:41:38
11.05.2016 22:41:38
11.05.2016 22:41:40
11.05.2016 22:41:40
11.05.2016 22:41:42
11.05.2016 22:41:42
11.05.2016 22:41:48
11.05.2016 22:41:52
11.05.2016 22:46:14
11.05.2016 22:46:14
11.05.2016 22:46:14
11.05.2016 22:46:18
11.05.2016 22:46:20
11.05.2016 22:46:20
11.05.2016 22:46:20
11.05.2016 22:46:20
11.05.2016 22:46:24
11.05.2016 22:46:24
12.05.2016 00:04:18
12.05.2016 00:04:20
12.05.2016 00:04:24
12.05.2016 00:04:24
12.05.2016 22:09:20
12.05.2016 22:09:22
12.05.2016 22:12:08
12.05.2016 22:12:08
12.05.2016 22:12:10
13.05.2016 00:49:18
13.05.2016 00:49:18
13.05.2016 00:49:18
13.05.2016 00:49:20

Ich möchte nun, dass ich eine Anzahl der Werte mit dem Datum bekomme, aber nicht nur in Abhängigkeit von dem Tag sondern sozusagen die Anzahl über die Nächte erfasse wenn Tage aufeinander folgen. Die Anzahl der hier eingefärbten Tage soll zusammenaddiert werden. Der Schnittpunkt bei den Uhrzeiten liegt bei 12:00 Uhr mittags.

Ist so etwas mit einer Formel die ich in Spalte C einfüge und für jede Zelle kopiere möglich?

Grüße
Fabian

wahli · 2. Februar 2017

Welche DB?
Welche Datentypen haben die beiden Spalten?

Du möchtest also in der dritten Spalte die Summe aller Zeilen haben, die zu einem Tag gehören, wobei der Tag von 12 Uhr bis nächsten Tag 12 Uhr geht? Damit würden also bei allen rot eingefärbten Zeilen die gleiche Zahl in Spalte 3 stehen und bei den blauen eine andere Zahl (oben wären es also "9").

Tranceport · 2. Februar 2017

also ich würde die beiden Spalten zusammenwerfen und 12 Stunden abziehen. Dann ZÄHLENWENN ;-)

Fabian0787 · 2. Februar 2017

Es sind 2 Spalten in Excel im Format 1x Datum, 1x Uhrzeit.

wahli · 2. Februar 2017

Vom Prinzip her müsstest du eine Fallunterscheidung einbauen, welche vor und nach 12 Uhr unterscheidet und anhand dessen ein Datum generiert. Vor 12 Uhr würde das Datum bleiben, nach 12 Uhr würde das Datum um 1 wachsen. Damit hättest du dann eine Liste mit korrigiertem Datum (um 12 Stunden verschoben). Auf diese Liste kannst du dann ganz normal mit group by und count arbeiten und bekommst die dritte Spalte. Allerdings musst du dann noch die ursprünglichen Spalten (Datum, Uhrzeit) hinzufügen.

Alternativ kannst du einen Timestamp aus Datum + Uhrzeit generieren, dann 12 Stunden hinzufügen und hast dann wieder die korrigierte Timestamp-Liste. Usw.

Fabian0787 · 2. Februar 2017

wahli schrieb:
Welche DB?
Welche Datentypen haben die beiden Spalten?

Du möchtest also in der dritten Spalte die Summe aller Zeilen haben, die zu einem Tag gehören, wobei der Tag von 12 Uhr bis nächsten Tag 12 Uhr geht? Damit würden also bei allen rot eingefärbten Zeilen die gleiche Zahl in Spalte 3 stehen und bei den blauen eine andere Zahl (oben wären es also "9").

Ganz genau. Mir geht es nur um die Anzahl der Werte für den definierten Zeitbereich. Das ganze muss gar nicht in jeder Zelle stehen sondern es reicht auch, wenn die Summe nur einmalig dargestellt wird (z.B. die letzte Zelle).

wahli · 2. Februar 2017

Excel? Ok, dann bin ich raus :-)

Aber das Prinzip müsste dort auch gehen.

Fabian0787 · 2. Februar 2017

wahli schrieb:
Excel? Ok, dann bin ich raus :-)

Aber das Prinzip müsste dort auch gehen.

Wenn du mir eine andere Art der Berechnung geben kannst dann würde ich auch versuchen das dort umzusetzen

sgrisius · 2. Februar 2017

C2 = 1
c3 =

=if(and(a3-1=a2,hour(b2)>12,hour(b3)<13),c2+1,if(and(a2=a3,or(and(hour(b3)<13,hour(b2)<13),and(hour(b3)>12,hour(b2)>12))),c2+1,1))

Ergänzung (2. Februar 2017)

PS, musst sicher sein dass die erste als Datum und die zweite als Zeit formatiert sind

wahli · 2. Februar 2017

Mach mal folgendes:

Spalte C in der ersten Zeile:
12:00:00

Spalte D in der ersten Zeile:
=SUMME(A1+B1-$C$1)
Zelle nach unten kopieren

Spalte E in der ersten Zeile:
=DATUM(JAHR(D1); MONAT(D1); TAG(D1))
Zelle nach unten kopieren

Spalte F in der ersten Zeile (hier steht dein gewünschter Wert):
=ZÄHLENWENN(E:E;E1)
Zelle nach unten kopieren

Die Spalten C, D und E kannst du ausblenden, dann hast du genau die Lösung!!!

@sgrissius
Ich glaube, dass der Wert in Spalte 3 für einen Tag immer gleich sein muss. Bei dir zählt das hoch.

sgrisius · 2. Februar 2017

@Wahli

Da das war nicht genau angegeben.

Sollte das, das Ziel sein, Formel durch

=IF(AND(A3-1=A2,HOUR(B2)>12,HOUR(B3)<13),C2,IF(AND(A2=A3,OR(AND(HOUR(B3)<13,HOUR(B2)<13),AND(HOUR(B3)>12,HOUR(B2)>12))),C2,C2+1))

ersetzen

wahli · 2. Februar 2017

@sgrisius
Geht das mit der deutschen Excelversion?
Wenn nicht, kann man das auch bestimmt schnell umschreiben.

sgrisius · 2. Februar 2017

Müsste auch mit dem deutschen Excel gehen

Frazer1 · 2. Februar 2017

Das müsste auch gehen:

Code:

=SUMMENPRODUKT((($B$1:$B$65535 = WENN(C2 <= ZEITWERT("12:00:00"); DATUM(JAHR(B2);MONAT(B2);TAG(B2)-1); B2))*($C$1:$C$65535 > ZEITWERT("12:00:00")))+(($B$1:$B$65535 = WENN(C2 <= ZEITWERT("12:00:00"); B2;  DATUM(JAHR(B2);MONAT(B2);TAG(B2)+1)))*($C$1:$C$65535 <= ZEITWERT("12:00:00"))))

Wobei man hier halt ein mal die ganzen B2 und C2 richtig setzten muss!

wahli · 2. Februar 2017

@sgrisius
Microsoft Office Standard 2007: da geht es nicht

Frazer1 · 2. Februar 2017

Bei mir gehen auch nur deutsche Schlüsselwörter! (Excel 2016)

GunMum · 2. Februar 2017

Moin Fabian,
wenn du Excel >=2010 verwendest geht das gut mit Power Query:

Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type date}, {"Spalte2", type time}}),
    #"Zusammengeführte Spalten" = Table.CombineColumns(Table.TransformColumnTypes(#"Geänderter Typ", {{"Spalte1", type text}, {"Spalte2", type text}}, "de-DE"),{"Spalte1", "Spalte2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Zusammengeführt"),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Zusammengeführte Spalten",{{"Zusammengeführt", type datetime}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ1", "Benutzerdefiniert", each Date.AddDays([Zusammengeführt],-0.5)),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte",{"Benutzerdefiniert"}),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Entfernte Spalten", "Benutzerdefiniert", each #datetime(Date.Year([Zusammengeführt]),Date.Month([Zusammengeführt]),Date.Day([Zusammengeführt]),Time.Hour([Zusammengeführt])-6,1,1)),
    #"Entfernte Spalten1" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte1",{"Benutzerdefiniert"}),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Entfernte Spalten1", "Benutzerdefiniert", each [Zusammengeführt]-#duration(0,12,0,0)),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte2",{{"Benutzerdefiniert", type date}}),
    #"Gruppierte Zeilen" = Table.Group(#"Geänderter Typ2", {"Benutzerdefiniert"}, {{"Anzahl", each Table.RowCount(_), type number}})
in
    #"Gruppierte Zeilen"

Das Ergebnis: 11.05. = 22, 12.05. = 9

sgrisius · 3. Februar 2017

Hab die Schlüsselwörter übersetzt. Versucht mal bitte ob das klappt

=WENN(UND(A3-1=A2,STUNDE(B2)>12,STUNDE(B3)<13),C2,WENN(UND(A2=A3,ODER(UND(STUNDE(B3)<13,STUNDE(B2)<13),AN D(STUNDE(B3)>12,STUNDE(B2)>12))),C2,C2+1))

wahli · 3. Februar 2017

@sgrisius
Geht nicht. Vermutlich liegt es an den Kommas. Das müssen im deutschen Strichpunkte sein:
=WENN(UND(A3-1=A2;STUNDE(B2)>12;STUNDE(B3)<13);C2;WENN(UND(A2=A3;ODER(UND(STUNDE(B3)<13;STUNDE(B2)<13);AND(STUNDE(B3)>12;STUNDE(B2)>12)));C2;C2+1))

Dann geht es aber bei mir auch nicht. Da steht dann "#NAME?" in der Zelle.

sgrisius · 3. Februar 2017

Anbei als attachement

Werte-Anzahl anhand von Datum + Uhrzeit

Cadet 4th Year

Admiral

Captain

Cadet 4th Year

Admiral

Cadet 4th Year

Admiral

Cadet 4th Year

Lt. Junior Grade

Admiral

Lt. Junior Grade

Admiral

Lt. Junior Grade

Lieutenant

Admiral

Lieutenant

Lt. Junior Grade

Lt. Junior Grade

Admiral

Lt. Junior Grade

Anhänge

Ähnliche Themen