Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 08:56:18
Textbox in Tabellenblatt ohne Bestätigung in Zelle eintragen
20.04.2024 07:57:54
Tablle fürs E-Auto - Preis wenn kürzer als 60 Min Ladezeit
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1136to1140
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1136to1140
1136to1140
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zeitraum auf Zeitpunkte verteilen

Zeitraum auf Zeitpunkte verteilen
rainer
Hallo!
Ich habe in einer Tabelle ein Start (A2) und ein Enddatum (B2) eines Zeitraumes. Beispielsweise 15.07.2010 bis 26.09.2010. Daneben habe ich eine Zeitachse (E2:P2) die nach Monaten aufgelöst ist.
Ich möchte nun, dass Excel mit einer Formel in die jeweiligen Spalten der Monate (E2:P2) den Wert ausgiebt, wie viele Tage auf diesen Monat fallen.
Für mein Beispiel hiesse das, dass ich im Juli 17 Tage, im Auguast 31 und für September 26 habe.
Fürs Verständnis hab ich noch ein Excel angehängt, das Zeigt welche Werte ausgegeben werden sollten.
Geht Sowas?
Gruss
Rainer
https://www.herber.de/bbs/user/67833.xls
AW: Zeitraum auf Zeitpunkte verteilen
08.02.2010 14:48:02
Björn
Hallo,
mit einer Formel wirst Du das so einfach nicht hinbekommen, denke ich.
Das müsste per VBA einfacher sein. Du brauchst ja eine Schleife, die x Mal den Tag prüft, in welchem Monat er sich befindet und dann den nächsten Tag nimmt. Anzahl ist ja auch variabel.
Keinen blassen Schimmer, wie man sowas in eine Formel bringen soll.
Ansonsten bau Deine Mappe um. Nimm für jeden Tag eine Zelle, also A1 = 15.07.2010, A2 = 16.07.2010, usw.
Dann kannst Du mit Zählenwenn über den Bereich die Tage je Monat einfach zählen.
Gruß
Björn B.
AW: Zeitraum auf Zeitpunkte verteilen
08.02.2010 14:53:56
rainer
Hallo Björn,
Danke für die schnelle Antwort!
Das umbauen der Tabelle ist nicht optimal. Sie muss übersichtlich bleiben. Dann lieber mit VBA. Wie hiesse der Code dann?
Wenn sich das einfacher umsetzten lässt bin ich natürlich offen. Ich dachte mir eine Formel wäre evenetuell einfacher.
Gruss
Rainer
Anzeige
Lösung per VBA
08.02.2010 15:46:12
Björn
Hallo,
es geht wohl doch per Formel... ;-) Aber ich hätte es nicht hinbekommen.
Hier die Lösung per VBA:
Markierung muss in der Zeile sein, in der der Zeitraum steht.
Muss natürlich noch ein bisschen angepasst werden. 
Sub zaehlen()
Dim row As Long
Dim start As Date
Dim ende As Date
Dim Anz As Integer
Dim i As Integer
Dim Monat As Variant
Dim datum As Date
Monat = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
row = ActiveCell.row
start = Cells(row, 1).Value
ende = Cells(row, 2).Value
Anz = ende - start
For i = 0 To Anz
datum = start + i
Monat(Month(datum) - 1) = Monat(Month(datum) - 1) + 1
Next i
MsgBox Monat(1)
Monat(1) = Monat(1) + 1
MsgBox Monat(1)
For i = 0 To 11
Cells(row, 5 + i).Value = Monat(i)
Next i
End Sub
Gruß
Björn B.
Anzeige
Sorry, zweiter Versuch...
08.02.2010 15:48:38
Björn
Hallo, der erste Code war falsch, da habe ich noch 3 Testzeilen drin gehabt, die das Ergebnis im Februar verfälschen.
Hier der richtige Code:
Sub zaehlen()
Dim row As Long
Dim start As Date
Dim ende As Date
Dim Anz As Integer
Dim i As Integer
Dim Monat As Variant
Dim datum As Date
Monat = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
row = ActiveCell.row
start = Cells(row, 1).Value
ende = Cells(row, 2).Value
Anz = ende - start
For i = 0 To Anz
datum = start + i
Monat(Month(datum) - 1) = Monat(Month(datum) - 1) + 1
Next i
For i = 0 To 11
Cells(row, 5 + i).Value = Monat(i)
Next i
End Sub
Gruß
Björn B.
Anzeige
logisch geht das mit ner einfachen Formel !
08.02.2010 16:04:52
WF
Hi Rainer,
füge eine leere Spalte E ein und ergänze dann R1 um den 01.01.2011
in F1 steht:
=MAX(0;MIN($B2-F$1+1;G$1-$A2-SUMME($D2:D2)))
bis Q1 und runter kopieren
Salut WF
also das funktioniert bei mir nicht...
08.02.2010 16:08:35
Björn
Ich erhalte einen Zirkelbezug.
In F1 schreibst du die Formel:
=MAX(0;MIN($B2-F$1+1;G$1-$A2-SUMME($D2:D2)))
Wie kann in F1 eine Formel stehen, die sich auf F1 bezieht?
Gruß
Björn B.
also natürlich in F2 und hinten $E2:E2)))
08.02.2010 16:11:47
WF
.
hinten ... $E2:E2)))
08.02.2010 16:10:27
WF
statt ... $D2:D2)))
gab n' Zirkelbezug
jetzt stimmts :-)
08.02.2010 16:15:54
Björn
Hallo,
da bin ich mal wieder blaß vor Neid...
Eigentlich denke ich immer, dass ich gar nicht so blöd bin, aber dann kommt jmd. wie Du und beweist mir das Gegenteil. Danke ;-)
Kannst du einem Formeldepp wir mir erklären, was die Formel genau macht?
Ich kann es nicht mal nachvollziehen.
Gruß
Björn B.
Anzeige
Was ist denn an...
08.02.2010 16:46:30
Luc:-?
...=MAX(0;MIN($B2-F$1+1;G$1-$A2-SUMME($E2:E2))) so schwer zu verstehen, Björn?
Ganz allgemein beschrieben ermittelt die Fml zuerst die 2 Werte in MIN, wobei sich der erste aus der um 1 erhöhten Differenz eines ab Zeile 2 gelesenen Wertes aus Spalte B und eines ab Spalte F gelesenen Wertes aus Zeile1 ergibt. Der zweite ergibt sich aus der Differenz eines ab Spalte G gelesenen Wertes aus Zeile 1 und eines ab Zeile 2 gelesenen Wertes aus Spalte A, von der noch die Summe der Werte von Zelle E2 (hier stets in Spalte E ab Zeile 2 beginnend) bis zur aktuellen Zelle innerhalb des Gesamteinkopierbereichs der Formel, also mindestens der Wert der Zelle E2, subtrahiert wird.
Mit MIN wird dann der kleinere dieser beiden Werte weiterverwendet. Anschließend wird noch das Maximum von 0 und diesem Wert gebildet, da er offensichtlich nicht <0 wdn soll.
Die relativen Zeilen- bzw Spaltenbezüge bewirken nur, dass sich der jeweils gewählte Zellbezug beim Formel­kopie­ren / -über­tragen entsprechend verschiebt.
Reicht das...? ;-)
Gruß Luc :-?
Anzeige
DANKE!
08.02.2010 16:17:28
rainer
Servus!
Vielen Dank für euere Hilfe!
Ihr seid echt der Hammer. Ich hab jetzt mal die für mich am besten verständliche Variante von WF gewählt. Funktioniert prima. :-)
Grüss aus der Schweiz
rainer
AW: doch noch kurz
08.02.2010 16:34:35
rainer
Hallo nochmals,
Klar, das mit dem Zirkelbezug hab ich bemerkt, konnte es aber selbst beheben. Ich denke ich versteh schon so halbwegs was die Formel macht.
Als Zusatz hab ich nun noch versucht die Werte in F2:Q2 (also die Tage) mit der Zahl 24 (um daraus Stunden zu machen) und der Zelle A7 (darin steht bsp. die Zahl 2) zu multiplizieren. Irgendwie geht das nicht ganz, da er mit dem Min/Max nicht mehr ganz klar kommt!?
Gruss
Rainer
Anzeige
AW: Format
09.02.2010 06:12:18
hary
Hallo Rainer
aendere nur das Format. 

Sheet1
 F
1Jan 10
2744:00

 verwendete Formeln 
 Zelle  Formel 
F2 =MAX(0;MIN($B2-F$1+1;G$1-$A2-SUMME($E2:E2)))

 Zahlenformate 
 Zelle  Format  Wert 
F1 MMM JJ40179
F2 [hh]:mm31
Zellen mit Format Standard werden nicht dargestellt

Tabellendarstellung in Foren

gruss hary
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige