Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema RefEdit
BildScreenshot zu RefEdit RefEdit-Seite mit Beispielarbeitsmappe aufrufen

Von bis Bereich


Betrifft: Von bis Bereich von: Christian
Geschrieben am: 15.09.2017 19:54:46

Moin alle zusmamen,

ich möchte die Zeile 5 in meiner Beispieldatei anhand der Kalenderwoche mit einer formel automatisch befüllen lassen.

https://www.herber.de/bbs/user/116282.xlsx

Also in Abhängigkeit der 1 KW aus dem Jahr 2017 den Datumsbereich von bis anzeigen lassen.

Jahr 2017 1 KW Von 02.01 bis 08.01

Habt ihr Ideen?

Viele Grüße
Christian

  

Betrifft: AW: auf Basis einer Formel von FP ... von: ... neopa C
Geschrieben am: 15.09.2017 20:26:02

Hallo Christian,

... z.B. in A5 (mit entsprechendem Datumsformat):

=("4.1."&$A1)+$A3*7-6-REST("2.1."&$A3;7)+KÜRZEN((SPALTE()-1)/2;)*7+REST(SPALTE(B1);2)*6

und Formel nach rechts kopieren.

Gruß Werner
.. , - ...


  

Betrifft: Mega :) Danke Werner von: Christian
Geschrieben am: 15.09.2017 20:47:40

Danke Werner, genau das habe ich gesucht


  

Betrifft: das stimmt so NICHT von: WF
Geschrieben am: 15.09.2017 21:02:07

Hi,

unabhängig von der Jahreszahl beginnt es immer mit dem 02.01. (2017 stimmt's zufällig)

in A5:
=("4.1."&A1)+A3*7-7-REST("2.1."&A1;7)

in B5:
=A5+1+(REST(SPALTE();2)=0)*5
nach rechts kopieren

WF


  

Betrifft: Guter Beitrag, vielen Dank :) eine Frage von: Christian
Geschrieben am: 15.09.2017 22:23:27

Wie kommt ihr in euren Formeln auf die 4.1?


  

Betrifft: AW: Guter Beitrag, vielen Dank :) eine Frage von: Rolf.dW
Geschrieben am: 15.09.2017 22:48:50

Hallo Christian,
nur so zur Info: Der erste Arbeitstag in 2016 war z.B. der 04.01.2016.

Gruß, Rolf


  

Betrifft: das sind so Tüfteleien von anderen von: WF
Geschrieben am: 15.09.2017 23:50:40

Den Ursprung und die Entwicklung kannst Du hier bei uns nachverfolgen:
http://www.excelformeln.de/formeln.html?welcher=186
Da gibt's noch was Kürzeres.

WF


  

Betrifft: AW: mein Korrekturbeitrag hierzu ... von: ... neopa C
Geschrieben am: 16.09.2017 09:23:21

Hallo Christian,

... ich hatte mir gestern Abend offensichtlich zu wenig Zeit für meinen Vorschlag hier genommen und mich u.a. dadurch verhaspelt. sorry. Das ich dabei auch noch den Originalformelteil von Franz (FP) verschandelt hatte, tut mir besonders leid. Ich möchte hiermit wenigsten noch eine korrekte Ein-Formel-Variante auf Basis der Original-FP-Formel bereitstellen.

In A5: =("4.1."&$A1)+$A3*7-7-REST("2.1."&$A1;7)+(REST(SPALTE()+1;2))*6

und diese Formel nach rechts kopieren.

Die in excelformeln.de als kürzere angegebene Formel von "?" ist mE nicht dessen Originalformel sondern lautete noch kürzer so: =7*DM((7&1-A1)/7+A3;0)+177. Diese Formel könnte wahrscheinlich wie viele Formeln von "?" (der seinen Namen nicht in excelformeln.de veröffentlicht sehen möchte) mit Hilfe von VBA entwickelt sein.

Mit dessen Formel würde die Formel für Deine Anfrage somit wie folgt lauten:

=7*DM((7&1-$A1)/7+$A3;0)+177+(REST(SPALTE()+1;2))*6 und diese nach rechts kopieren.


Gruß Werner
.. , - ...


  

Betrifft: AW: sorry, wieder falsch wiel Zeitdruck ... von: ... neopa C
Geschrieben am: 16.09.2017 09:37:01

... muss nämlich jetzt offline gehen.

Gruß Werner
.. , - ...


  

Betrifft: korrigiere die erste Formel von: WF
Geschrieben am: 16.09.2017 09:57:16

=("4.1."&$A1)+$A3*7-6-REST("2.1."&$A1;7)+KÜRZEN((SPALTE()-1)/2;)*7+REST(SPALTE(B1);2)*6-1

hinten noch -1
WF


  

Betrifft: Danke, aktueller Zustand von: Christian
Geschrieben am: 16.09.2017 10:44:42

Vielen Dank für eure zahlreichen Beiträge

In Spalte "Von" Zelle A5 nehme ich aktuell:

=("4.1."&A1)+A3*7-7-REST("2.1."&A1;7)
und in Spalte "Bis" Zelle B5 nehme ich aktuell:
=A5+1+(REST(SPALTE();2)=0)*5
Mit den Ergebnissen für das Jahr 2017 bin ich zufrieden. Würde ich Probleme für das Jahr 2018 bekommen?

Zu einen weiteren Thema das sich heute ergeben hat:

Es geht darum ab Zelle A21 nur jede zweite Spalte in B28 zu übertragen. Das heisst also A21, A23, A25 etc.. (Der Bereich in der die Übertragung stattfinden soll, ist in der Beispieldatei farblich gekennzeichnet)

https://www.herber.de/bbs/user/116289.xlsx

Vielen Dank für euren Support :)

Viele Grüße
Christian


  

Betrifft: INDEX von: lupo1
Geschrieben am: 16.09.2017 12:25:07

B28: =INDEX(22:22;SPALTE(B28)*2-2)
B29: =INDEX(22:22;SPALTE(B29)*2-3)


  

Betrifft: Fehler, wegen blöder Colorierung Deinerseits von: lupo1
Geschrieben am: 16.09.2017 12:26:48

Passe die Formeln selbst an.


  

Betrifft: Vielen Dank :) von: Christian
Geschrieben am: 16.09.2017 12:49:30

Ja das Stimmt, die Farben muss ich noch anpassen. Die Formel läuft wunderbar.

Viele Grüße
Christian


  

Betrifft: AW: Vielen Dank :) von: Rolf.dW
Geschrieben am: 16.09.2017 14:24:25

Hallo Christian,

anbei noch eine Alternativlösung.
https://www.herber.de/bbs/user/116294.xlsm

In der gelb unterlegten Zelle kannst du ein beliebiges Jahr eintragen, die "von/bis"-Daten in der Tabelle ändern sich automatisch.
Für die Zeilen 28 ff. habe ich eine Hilfszeile und eine Hilfsspalte eingefügt. Diese kannst du mit einem kleinen Makro, das ich der Datei beigefügt habe, jederzeit mit der Tastenkombination Strg+z ein- oder ausblenden.

Gruß, Rolf


  

Betrifft: AW: so nun der Korrektheit halber ... von: ... neopa C
Geschrieben am: 16.09.2017 17:59:18

Hallo Christian,

... auch wenn die 2-Formellösung von WF, die Du nun nutzt, auch mE die günstige Lösung Deiner Aufgabe ist, muss und will ich meine Fehler hier erklären und korrigieren, auch wenn es nicht mehr von Interesse ist.

Gestern Abend hatte ich mich im REST()-Formel-Teil der Originalformel vertan. Ich hatte da anstelle auf A1 Bezug auf A3 genommen und dann einfach anstelle -7 mit -6 eine Schnellkorrektur vorgenommen. Dieses gilt so aber nur für 2017.

Die naheliegende Korrektur dessen, wollte ich heute Morgen wiederum nicht tun, weil damit immer noch nicht die Originalformel eingehalten wäre.

Meine dementsprechend korrigierte Formel, hatte ich allerdings heute Morgen nur ab Spalte C zu stehen. In A5:B5 fehlte dagegen noch der nachfolgend angegebene letzte Formelteil. Aber die unvollständige hatte ich hier im thread einkopiert. Diese Rumpfformel galt jedoch nur für A5:B5. Es fehlte +KÜRZEN((SPALTE()-1)/2;)*7 was ich hiermit nachhole. Dieser Formelteil ergibt nämlich in A5:B5 eine Addition von 0 und erst ab Spalte C jeweils plus 7 Tage zu den Vorgängerdaten.

Zusammengefasst: meine angestrebte Ein-Formel-Lösung ab A5 lautet:
=("4.1."&$A1)+$A3*7-7-REST("2.1."&$A1;7)+(REST(SPALTE()+1;2))*6+KÜRZEN((SPALTE()-1)/2;) *7

Gruß Werner
.. , - ...


  

Betrifft: es gibt auch die KW 53 (z.B. 2020) von: WF
Geschrieben am: 16.09.2017 23:39:45

Hi,

wenn ja: 2 mal Datum / wenn nein: 2 mal leer

in DA5:
=WENN(TAG(CY5)>21;"";CZ5+1+(REST(SPALTE();2)=0)*5)
in DB5:
=WENNFEHLER(DA5+1+(REST(SPALTE();2)=0)*5;"")

Das müsstest Du noch in die Solo-Formel einbauen.

viel Spaß
WF


  

Betrifft: AW: "kritischer" ist allerdings 2032 ... von: ... neopa C
Geschrieben am: 17.09.2017 13:05:12

Hallo WF,

... aber ich würde das anders lösen als von Dir angedacht. Momentan hat ja Christian in Zeile 3 noch eine händische fixe Eingabe. Entweder nimmt er in den entsprechenden Jahren die KW53 noch dazu oder er löst die verbunden Zellen auf und nutzt meine nachfolgende gegenseitig (Zeile 3 und 5) abhängige (allerdings versetzt) Formellösung.

Formeln bis Spalte DB kopieren und in Zeile 3 noch eine bedingte Formatierung mit unten stehender aufgezeigter Formel: =REST(SPALTE();2)=0 und mit dem in der Jeanie nicht darstellbaren benutzerdefinierten Zahlenformat ;;; dafür:

Tabelle1

 ABCDEFGHIJ
12017         
2Dezember         
349 50 51 52   
4vonbisvonbisvonbisvonbis  
5Mo 04.12.2017So 10.12.2017Mo 11.12.2017So 17.12.2017Mo 18.12.2017So 24.12.2017Mo 25.12.2017So 31.12.2017  

Formeln der Tabelle
ZelleFormel
A2=A5
B3=WENN((TEXT(MAX($A5:A5)-7;"JJMT")*1>(RECHTS($A1;2)&1221)*1)+(A3=""); "";A3+REST(SPALTE(); 2))
B4=WAHL((B3="")*2+REST(SPALTE(); 2)+1;"bis";"von";"";"")
A5=WENN(A3="";"";("4.1."&$A1)+$A3*7-7-REST("2.1."&$A1;7)+(REST(SPALTE()+1;2))*6+KÜRZEN((SPALTE()-1)/2;)*7)

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
A1Liste 2015;2016;2017;2018;2019;2020;2025;2026;2032 

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
A31. / Formel ist =REST(SPALTE();2)=0Abc
B31. / Formel ist =REST(SPALTE();2)=0Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...


  

Betrifft: 2032 ist doch egal von: WF
Geschrieben am: 17.09.2017 13:52:46

Hi,

eine "normale" KW 53 fängt mit dem 28.12. an.
In manchen Schaltjahren wie 2004; 2032 ... mit dem 27.12.

Ich frage den TAG(CY5) größer 21 ab - das wäre mindestens 22. plus 7 also KW-Beginn am 29.
Wo ist da ein Problem ?

WF


  

Betrifft: AW: schon, ... von: ... neopa C
Geschrieben am: 17.09.2017 15:00:36

Hallo WF,

... ich hab auch nichts von einem Problem geschrieben und in meiner Lösung in der ich weiterhin nur eine Formel je Zeile benötige vergleiche ich ja auch mit dem 21.12 des Jahres.

Gruß Werner
.. , - ...


  

Betrifft: Ein-Formel-Lösung von: Josef B
Geschrieben am: 18.09.2017 11:37:43

Hallo Werner

Von solchen Ein-Formel-Lösung im Zusammenhang mit Datumsberechnungen habe ich noch nie viel gehalten.
Solche Formel-Konstrukte sind oft sehr aufwändig fehlerfrei zu erstellen, und für den Anwender schwieriger zu verstehen. Deine Lösungen bestätigen meine Vorbehalte gegen "Durchgängige" Formeln.
Nach anfänglicher Fehlern bei KW 1bis 52, ist es dir selbst als Formelfreak wieder nicht auf Anhieb gelungen, eine funktionierende Formel für KW1 bis 52 oder 53 zu erstellen.
Im Kalenderjahr 2000 gibt es sogar einen Totalausfall.

Gruss Sepp


  

Betrifft: AW: sehe ich prinzipiell auch so ... von: ... neopa C
Geschrieben am: 18.09.2017 15:26:49

Hallo Sepp,

... in meinem Beitrag am 16.09. 17:59 schrieb ich ja auch an Christian:
"...auch wenn die 2-Formellösung von WF, die Du nun nutzt, auch mE die günstige Lösung Deiner Aufgabe ist..."

Unabhängig davon erkenne ich in meiner Formel für A5 momentan keinen maßgeblichen Fehler. Das es da für das uralt Kalenderjahr 2000 einen "Totalausfall" gibt, ist doch nochmal, weil die Formel A5 von der Formel in A3 abhängig ist. Und diese ist nicht für Kalenderjahre vor dem aktuellen gedacht/geplant und dementsprechend auch nicht so ausgelegt.

Gruß Werner
.. , - ...


  

Betrifft: so ein Blödsinn von: WF
Geschrieben am: 18.09.2017 16:19:36

Der Microsoft-Urknall war 1900.
Weitere Datumseinschränkungen sind dann wohl die jetzt nur gültige neopa-Zeitrechnung.

WF


  

Betrifft: AW: ... was Du manchmal so schreibst owT von: ... neopa C
Geschrieben am: 18.09.2017 19:47:36

Gruß Werner
.. , - ...


  

Betrifft: Ich meinte natürlich die Zeile 3 von: Josef B
Geschrieben am: 18.09.2017 17:48:45

Hallo Werner

Ich hatte das leider nicht ganz genau beschrieben, die Formel in Zeile 5 hattest du ja inzwischen korrigiert. Aber deine neue Formel in B3:DB3 ist eben wieder fehlerhaft.
Ausser dem Totalausfall von 2000 gibt es auch Fehler mit der 53 KW bei 2014 und 2025 u.s.w.

Gruss Sepp


  

Betrifft: AW: jetzt verständlich ... von: ... neopa C
Geschrieben am: 18.09.2017 19:46:43

Hallo Sepp,

... denn die Anpassung nur für das Jahr 2000 wäre mE hier nach wie vor nicht notwendig gewesen, zumal diese für sich betrachtet in der Formel einfach zu realisieren ist.

Ich hab mich wirklich noch nie richtig mit der KW-Problematik auseinandergesetzt (werde es auch jetzt nicht tun) und hatte für die Formel in B3 nur eine Prüfung auf die Jahre mit 53 KWen vorgenommen.

Aus einem schnellen Jahresenddatumslisting mit den mit KALENDERWOCHE() ermittelten KWen hab ich jetzt folgende Annahme getroffen. Wenn der 29.12 auf einen Montag fällt, beginnt da die 1. KW des Folgejahres. Ist dem so? Wenn dem so ist, die Formel in B3 jetzt nur schnell angepasst ohne nach einem Optimum zu suchen.

=WENN((TEXT(MAX($A5:A5)-7;"JJJJMT")*1>($A1&1221)*1)+(A3="")+(TEXT(A5;"TTTMMT")="So1228");""; A3+REST(SPALTE();2))
(M) ein Schnelltest ergab dafür nun die richtigen KWen. Oder?

Gruß Werner
.. , - ...


  

Betrifft: AW: jetzt ist alles richtig o.w.T. von: Josef B
Geschrieben am: 18.09.2017 21:37:44


Gruss Sepp


  

Betrifft: AW: jetzt ist alles richtig o.w.T. von: Josef B
Geschrieben am: 18.09.2017 21:37:56


Gruss Sepp


Beiträge aus den Excel-Beispielen zum Thema "Von bis Bereich"