Excel Formeln - Datum?

20.08.2021, 15:29

Frage Nummer 2:

Da ich jetzt endlich eine Lösung für das Datum bekommen habe - danke nochmal dafür - stehe ich jetzt vor meinem zweiten Problem. Ich weiß gar nicht ob das so funktioniert wie ich mir das denke, aber eventuell kann mir jemand weiterhelfen. und zwar,... (siehe Bild 2) möchte ich wie ein "rollenden Dienstplan" erstellen und zwar so, dass jede Woche eine andere Farbe bei einem anderen Dienst ist. Ich habe das jetzt mal per Hand gemacht, wie es aussehen könnte und dahinter ist auch ein Konzept - ich hoffe man erkennt es nach einem kleinen Augenblick. Schöne wäre es auch wenn ich einen anderen Monat einstelle, dass es die letzte Zeile vom davorigen Monat erkennt und dann diese Formatierung einfach weiter führt.

Da ich weiß, dass es als Außenstehender immer schwierig ist zu verstehen, gibt mir doch gerne mal Rückmeldung, ob es überhaupt verständlich ist.

P.S. da es gerade nur 8 Kinder gibt, sind die Farben gelb und Orange nicht beleget, habe sie nur schonmal für später eingefügt, kann aber diese jetzt noch nicht in diesen Plan verwenden, da ja gerade dann keiner diese Dienste abdecken kann.

3 Antworten

Vom Fragesteller als hilfreich ausgezeichnet
DanKirpan
Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer
Microsoft Excel
18.08.2021, 09:07

Hallo,

wenn ich dich recht verstehe kannst du dafür dies verwenden:

  • =TEXT(DATUM($E$7;MONAT("1."&$C$7);1)-WOCHENTAG(DATUM($E$7;MONAT("1."&$C$7);1);3)+(ABRUNDEN(ZEILE()/4;0)-3)*7;"TT.MM.JJJJ")&ZEICHEN(10)&" - "&ZEICHEN(10)&TEXT(DATUM($E$7;MONAT("1."&$C$7);1)-WOCHENTAG(DATUM($E$7;MONAT("1."&$C$7);1);3)+(ABRUNDEN(ZEILE()/4;0)-3)*7+6;"TT.MM.JJJJ")

mit Text() werden die Datumswerte in das "01.03.2021"-Format umgewandelt. Um das Datum zu bestimmen wird zunächst der Montag der Woche mit dem ersten des Monats ermittelt:

  • DATUM($E$7;MONAT("1."&$C$7);1)-WOCHENTAG(DATUM($E$7;MONAT("1."&$C$7);1);3)

für die Folgewochen wird dazu ein Vielfaches von 7 abhängig der Zeile addiert

  • +(ABRUNDEN(ZEILE()/4;0)-3)*7

im zweiten Datum() werden zusätzlich 6 Tage addiert um den letzten Tag der Woche zu bestimmen. Die

  • &ZEICHEN(10)&" - "&ZEICHEN(10)&

sind für das Verketten beider Datumswerte, Zeichen(10) steht hierbei für einen Zeilenumbruch an der Stelle aus Symetriegründen, falls deine Spalte breit genug ist, kannst du sie auch weglassen.
ginobiz 
Fragesteller
 20.08.2021, 13:11

Vielen Lieben Dank! Genau das habe ich gesucht, sie haben mir den Tag gerettet!

Danke :D

0
ginobiz 
Fragesteller
 20.08.2021, 15:30
@DanKirpan

Vielleicht kannst du mir ja auch bei meinem anderen Problem helfen.

0
DanKirpan  20.08.2021, 16:29
@ginobiz

Ich denke schon. Die Dienste sollen sich ja regelmäßig abwechseln, alos z.b wer Küchendienst 1 in Woche 1 hat hat in Woche 2 garantiert Treppe/Shuhe und Küchendienst 2 hat dann Mülldienst. Also würde ich mir nur einmal die Mühe machen die Dienste tatsächlich zu vergeben und sie in den anderen Spalten dann nur über einen Bezug reinholen.

Z.B für Küchendienst:

  • =ABS(8-REST(DATUM($B$4;MONAT("1."&$B$2);1)-WOCHENTAG(DATUM($B$4;MONAT("1."&$B$2);1);3)+(ABRUNDEN((ZEILE()+2)/2;0)-1)*7;$B$5))

Kurzfassung: Vergebe abhängig des Datums einen Wert zwischen 1 und der Zahl in B5 (Kinderanzahl)

und diese Zahlen dann über bedingte Formatierung in die Farben umwandeln und dabei mit dem benutzerdefiniertem Format ;;; die Zahlen unsichtbar machen. (oder die Namen der Kinder in Anführungszeichen hinenschreiben)

1
ginobiz 
Fragesteller
 21.08.2021, 21:13
@DanKirpan

Hey vielen Lieben Dank erstmal, dass du dich so engagierst mir zu helfen. Heutzutage nicht mehr Selbstverständlich. Allerdings verstehe ich leider nicht ganz so wie ich die Formel anwenden soll bzw. wie du es meinst. Kannst du es mir vielleicht nochmal für Idioten erklären? :D
LG

0
DanKirpan  23.08.2021, 09:39
@ginobiz

Hi bitte :D

Ich kanns versuchen, die Grundidee ist es jedem Dienst eine Zahl zwischen 1 und der Kinderanzahl zuzuordnen und diese Zahl dann nur optisch über bedingte Formatierung den tatsächlichen Kinderfarben zuzuordnen.

Hab gerade noch ein paar Fehler festgestellt gehabt (hab sie am Freitag nicht sehr ausführlich getestet), ursprünglich war geplant die Zahlenreihe nur in einer Spalte der Dienste zu erzeugen und sie über =B10 , =B12 in die anderen Dienste zu kopieren, aber das kommt dann mit anderen Anzahlen als 8 schnell durcheinander.

Daher jetzt nur eine Formel die den ersten Dienst der Woche berechnet in B10:

  • =REST(ABRUNDEN((-7+DATWERT("1"&$B$2&$B$4)-WOCHENTAG(DATWERT("1"&$B$2&$B$4);3)+(ABRUNDEN((ZEILE()+2)/4)-3)*7)/7;0);ANZAHL($F$32:$G$36))+1

Damit es möglich ist das die Vorgängerwoche "erkannt" werden kann, wird die Zahl aus dem Montagsdatum der jeweiligen Woche bestimmt

  • -7+DATWERT("1"&$B$2&$B$4)-WOCHENTAG(DATWERT("1"&$B$2&$B$4);3)+(ABRUNDEN((ZEILE()+2)/4)-3)*7)

Datwert() ist nur eine andere Schreibweise für die bekannte Kombination aus Datum()+Monat() oben (Hab grad nur Open Office hier und das versteht das andere nicht), die -7 dafür das es mit der letzten Woche des Vormonats startet

Rest(Datum/Kinderanzahl)+1 errechnet daraus die Zahl des Kindes für den ersten Dienst. Die Abrunden(Datum/7) ist dazwischen weil ja sonst bei 7 Kindern immer derselbe Rest herauskäme.

Die anderen Dienste der Woche berechnen sich dann direkt daraus:

  • =WENN(B10+7>ANZAHL($F$32:$G$36);B10+7-ANZAHL($F$32:$G$36);B10+7)
  • jeweils in die anderen Dienste, dabei die 3 +7 beliebig anpassen, sodass du jeweils jede Zahl zwischen 1 und 7 addierst (da ja noch 7 andere Dienste zu vergeben sind)

und zuletzt die ganze Zeile nach unten kopieren

Offene Fragen sind noch:

  • Wie sollen die Dienste vergeben werden wenn es weniger als 8 Kinder sind?
  • Kann sich die Kinderanzahl im Lauf des Monats ändern und falls ja wie wirkt sich das auf die Dienstvergabe aus? (Gebe allerdings schon jetzt zu das mir momentan keine Lösung einfällt wie man da die letzte Woche der alten Anzahl fixieren könnte)
0
ginobiz 
Fragesteller
 28.08.2021, 23:13
@DanKirpan

@DanKirpan,
besteht die Möglichkeit darin, dir meine Liste irgendwie zu kommen zulassen, sodass du diese vervollständigen kannst? Leider bin ich anscheine dazu zu doof :'D
Um deine fragen zu beantworten:

  • das es weniger als 8 Kinder werden ist eher unwahrscheinlich, aber sollte dies passieren müsste ich die Tabelle umstrukturiert und jeder Dienst kann nur noch einzeln besetzt werden.
  • die kann sich ändern, aber das ist nicht schlimm, wenn das nicht beachtet wird. Notfalls könnte ich für diesen Monat auch eine Diensteliste per Hand erstellen.
0
DanKirpan  30.08.2021, 10:49
@ginobiz

Am Einfachsten dürfte es über einen Filesharingdienst sein, bisher hatte ich file-upload.net verwendet, das hat aber einen Hang zu Popupwerbung für Schadsoftware. www.filemail.com sieht vielversprechend aus. Schicke dir dafür eine FA.

  • ok, dann sollte man die entstehenden Doppelvergaben einfach löschen können ohne das Probleme auftreten
  • :? sehe das Problem hier darin das es dann ja auch in den Folgemonaten verschoben werden muss, aber vllt denk ich auch nur zu kompliziert^^
0
DanKirpan  08.09.2021, 15:33
@ginobiz

https://www.filemail.com/d/biomdlzsozxjcvt

Hat funktioniert, hier ist die bearbeitete Datei zurück. Um die Kinderzahl anzupassen musst du nur die Ziffern nacheinander in der Kinderfarbtafel eintragen. Hab deine feste Formatierung mal stehen gelassen, aber wenn du möchtest könntest du sie auch leeren, die bedingte Formatierung gilt auch dafür.

Nach meinen Tests funktionieren die Anzahlen ab 5 Kindern ohne Probleme. (btw 5 bis 12 für mehr müsste man den Bereich in Anzahl() vergrößern)

In Registerkarte 2 hab ich eine Idee versucht wie man die Anzahl im Monat anpassen könnte. Mit den zu erwartenden Kinderanzahlen funktioniert es größtenteils fair.

Bitte :D

0
ginobiz 
Fragesteller
 14.09.2021, 12:27
@DanKirpan

Vielen Lieben Dank für deine großartige Mühe! Ich kann mich gar nicht zuoft bedanken! Es klappt alles Einwandfrei -Dankeschööööönnnn
Ein was doofes hat es jetzt, ich werde immer erstmal auf dich zukommen, wenn ich Probleme mit Excel habe :D - Nein Spaß
Viele Grüße

0
DanKirpan  16.09.2021, 10:32
@ginobiz

:D Bitte, schön das alles funktioniert.

Eine verkraftbare Nebenwirkung :D

Viele Grüße zurück

1
IchMalWiederXY
Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer
Microsoft Excel
17.08.2021, 16:50

Füge diese Formel in A12 ein
=WENN(ISTFEHLER(DATWERT((REIHE(A12)-11)&"."&VERGLEICH($C$7;{"Jan";"Feb"};0)&"."&$E$7));"";DATWERT((REIHE(A12)-11)&"."&VERGLEICH($C$7;{"Jan";"Feb"};0)&"."&$E$7)))
Sorge dafür, dass die Kette "Jan";"Feb" innerhalb der Formel an BEIDEN Stellen entsprechend erweitert wird und ziehe dann diese Formel soweit nach unten dass sie 31 Tage abdeckt.

ginobiz 
Fragesteller
 17.08.2021, 20:44

Danke dafür, aber inwieweit ist dies für mich relevant? bzw. was bewirk das?

0
IchMalWiederXY  18.08.2021, 20:24
@ginobiz

Naja, außer {"Jan";"Feb"} willst du doch auch März...etc abgedeckt haben, oder nicht.
Generell wird aus den Einzelteilen Tag, Monat, Jahr das Datum über "DATWERT" gebaut. Monat und Jahr trägst du ein 'ändert' sich wenn nötig.
Die tage 1 bis 31 sind fest an den Zeilen.

0
Dietwald2016
Nutzer, der sehr aktiv auf gutefrage ist
im Thema Microsoft Excel
17.08.2021, 16:37

"(z.B. in der ersten Spalte vom Datum (Z12-Z15)" 

Mit Deinen Begriffen geht es etwas durcheinander. "Datum" steht nur in einer Spalte, nämlich in Spalte A. Mit der "ersten Spalte" meinst Du die Zeilen 12 bis 15. In der Spalte A heißen die Zellen A12 bis A15 (nicht Z12-Z15). Du nennst die Spalte "Datum", meinst aber Woche. Warum sind die Zeilen 12 bis 15 zusammengefasst? Werden in den anderen Spalte bis zu vier Namen drin stehen? Was soll in A12, A13, A14 und A15 einzeln stehen? In einer Zelle sollte immer nur ein (1) Wert, hier Datum, stehen. Vielleicht also besser "Woche ab" mit Anfangsdatum oder zwei Spalten mit Anfangs- und Enddatum. - Eine automatische Lösung Deines Problems fällt mir so schnell nicht ein.