Hallo Wissende,
ich habe folgendes Problem: Ich führe einen Stundenzettel, in dem in Spalte A das Datum steht und in Spalte B der Wochentag. Irgendwo weiter hinten stehen die Soll-Stundenzahlen für die einzelnen Tage. Dort soll nun für die Wochenenden „0“ eingetragen werden. Die Formel lautet =WENN(B13=„Fr“;4;WENN(B13=„Sa“;0;WENN(B13=„So“;0;6,5))). Nun würde ich eigentlich gern vermeiden, am Anfang jedes Monats den ersten Wochentag per Hand einzutragen und diesen runterzuziehen. Schöner fände ich so was wie =A13 mit Formatierung „TTT“ in B13, aber dann funktioniert die Formel für die Sollstunden nicht mehr (ich glaube, die bedingte Formatierung auch nicht, aber das habe ich eh über den Sollstundenwert gelöst: =$K13=0).
Gibt es einen Weg ohne Makro, dass WENN den Wochentag erkennt?
Vielen Dank schon mal!
Verena
Ich bin mir zwar nicht so ganz sicher, aber das von dir Verlangte könnte sich auch über einen Kriterienbereich + SVerweis lösen lassen *grübel und nachdenk*
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Moin,
benutze doch die Funktion WOCHENTAG. Die gibt Dir für ein Datum den Wochentag zurück (normal ist 1=Sonntag, aber das kann man einstellen, Details siehe in der Hilfe zur Funktion).
Dann kannst Du einfach benutzen:
=WENN(WOCHENTAG(A1)=1;…)
Gruß
Kubi
=WENN(B13=„Fr“;4;WENN(B13=„Sa“;0;WENN(B13=„So“;0;6,5))).
Nun würde ich eigentlich gern vermeiden, am Anfang jedes
Monats den ersten Wochentag per Hand einzutragen und diesen
runterzuziehen. Schöner fände ich so was wie =A13 mit
Formatierung „TTT“ in B13, aber dann funktioniert die Formel
für die Sollstunden nicht mehr (ich glaube, die bedingte
Hallo Verena,
=WENN(ODER(TEXT(B13;„TTT“)=„Sa“;TEXT(B13;„TTT“)=„So“);0;6,5)
Gruß
Reinhard
Hallo Verena,
die einfachste Lösung wäre eigentlich, auf die Spalte mit dem Wochentag zu verzichten, da sie auch tatsächlich überhaupt nicht nötig ist.
dann sieht die Lösung wie folgt aus:
in A13 steht das Datum,
die Formatierung kann gleich so gestaltet werden, dass der Wochentag für den User erkennbar ist. Die von Excel vorgegebene Möglichkeit unter Datum in der Form: *Mittwoch, 14. März 2001 oder auch benutzerdefinierte Formen wie TTT, TT.MM.JJJJ sind optisch oft nicht so ansprechend.
Meist möchte man wegen der Übersichtlichkeit Wochentag und Datum deutlich voneinander getrennt.
Für die Spalte A bietet sich eine benutzerdefinierte Formatierung in folgender Form
TT.MM.JJJJ, * TTT
oder
TTT, * TT.MM.JJJJ
an.
Bei beiden Varianten sind die Leerzeichen vor und hinter dem * wichtig (funktioniert sonst nicht)
Spalte B ist damit überflüssig.
Die Formel für die Stundenausgabe ist dann z.B.:
=WENN(ODER(WOCHENTAG(A13;1)=1;WOCHENTAG(A13;1)=7);0;6,5)
Gruß
Marion
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
=WENN(ODER(WOCHENTAG(A13;1)=1;WOCHENTAG(A13;1)=7);0;6,5)
Hallo Marion,
viele Wege führen nach Rom und es macht mir Laune mal andere unbekannte Wege zu gehen:smile:
=(SUCHEN(TEXT(A13;„TTT“);„MoDiMiDoFrSaSo“)
Hallo Reinhard,
super (Stern)
jetzt wird’s interessant
ich hab auch noch was:
=(REST(UNTERGRENZE(A2;1);7)>=2)*6,5
Jetzt bist du wieder dran 
Lieben Gruß
Marion
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
=(REST(UNTERGRENZE(A2;1);7)>=2)*6,5
Jetzt bist du wieder dran
Hallo Marion,
*aargs* jetzt haste mich erwischt, dazu noch mit für mich unfairen Mitteln, Untergrenze kannte ich nicht, was ist das, wer braucht das, noch nie benutzt 
Okay, ich mach mich schlau was Untergrenze bedeutet, kein Problem.
Und wenn von mir nix mehr kommt, dann fand ich nix kürzeres.
Gruß
Reinhard
Hallo Verena,
einzelnen Tage. Dort soll nun für die Wochenenden „0“
eingetragen werden. Die Formel lautet
=WENN(B13=„Fr“;4;WENN(B13=„Sa“;0;WENN(B13=„So“;0;6,5))).
Gibt es einen Weg ohne Makro, dass WENN den Wochentag erkennt?
Vielen Dank schon mal!
Verena
Hatte gar nicht so genau auf deine Formel geachtet *verlegenlächeln* - tut mir leid, aber dann doch noch gemerkt *erleichtert*: am Freitag nur 4 Stunden , deshalb jetzt noch eine Formel (die anderen sind ja auch ganz nett - aber nicht die Lösung). Hofentlich hattest du deswegen keinen Stress.
für: Mo-Do=6,5h, Fr=4h, WE=0h
=WENN(REST(A13;7)=6;4;WENN(REST(A13;7)
Hallo Reinhard,
*aargs* jetzt haste mich erwischt, dazu noch mit für mich
unfairen Mitteln,
wieso unfair
Untergrenze kannte ich nicht, was ist das,
wer braucht das, noch nie benutzt
geht mir auch so *lach*
es beginnt Spass zu machen, aber
wo biste denn
viele Wege führen nach Rom und es macht mir Laune mal andere
unbekannte Wege zu gehen:smile:
also gut, ich leg noch einen vor
wie wär’s mit
=(CODE(TEXT(A13;„TTT“))=2)*6,5
=(CODE(TEXT(A13;„TTT“))1)*6,5
ja, hat mir Spass gemacht, aber Verena hatte auch den Freitag mit in der Formel, ist irgendwie untergegangen
Lieben Gruß
Marion
Sammel-Dank und Fragen
Vielen Dank für die vielen Anregungen, Sternchen!
Kubi: Toll, das mit dem Wochentag kannte ich noch gar nicht.
Duell Reinhard - Marion 1:1 *g*
Aber ein paar Fragen hab ich noch, da ich gern Sachen nicht nur passend anwende, sondern auch verstehe.
Marion: Danke, das mit dem Oder trau ich mich immer nicht, wegen fehlender Syntaxkenntnisse. Ich nehme an, WOCHENTAG(A13;1) heißt, dass das ein Sonntag ist? ( Danke, Kubi, für die Info mit WOCHENTAG!)
Könntest Du bitte so nett sein und mir erklären,
a) was hier eigentlich passiert?=(REST(UNTERGRENZE(A2;1);7)>=2)*6,5
b) was Code ist und warum unter 80? _=(CODE(TEXT(A13;„TTT“))
c) wie das hier mit dem Rest funktioniert? (Rest wovon?) =(REST(A13;7)>1)*6,5 bzw. =WENN(REST(A13;7)=6;4;WENN(REST(A13;7)
Reinhard: Was bedeutet bei =(SUCHEN(TEXT(A13;„TTT“);„MoDiMiDoFrSaSo“)_
Hallo Verena,
nicht, wegen fehlender Syntaxkenntnisse. Ich nehme an,
WOCHENTAG(A13;1) heißt, dass das ein Sonntag ist?
nö, die 1 bedeutet dass die Woche von So-Sa geht, also So=1,Mo=2,…Sa=7
Deshalb nehem ich die 2, dann gilt Woche von Mo-So, Mo=1,…So=7
a) was hier eigentlich
passiert?=(REST(UNTERGRENZE(A2;1);7)>=2)*6,5
K.A. ich habe Untergrenze noch nicht kapiert )
b) was Code ist und warum unter 80?
=(CODE(TEXT(A13;„TTT“))
Schreibe mal in A32
=Zeichen(zeile())
und ziehe das bis A255 runter. Dann siehst du welches Zeichen welchen Code hat. Geprüft wird nur das erste Zeichen eines Strings wie „Mi“, also das M.
Und wenn das unter 80 hat ist es halt kein S(a) oder S(o)
c) wie das hier mit dem Rest funktioniert? (Rest wovon?)
=(REST(A13;7)>1)*6,5 bzw.
Der Rest der Division A13/7
=(SUCHEN(TEXT(A13;„TTT“);„MoDiMiDoFrSaSo“)1
A3: =(REST(A13;7)>1)*6,5
dann siehste besser warum was da steht, mache ich genauso bei Riesenformeln. Und bei Riesenformeln, grad bei Fehlersuche, mache ich das dann so um sie zu verkleinern und um noch den Überblick zu behalten:
A1: =REST(A13;7)
A2: =a1>1
A3: =A2*6,5
Lieben Gruß
Reinhard
Hallo Verena,
Aber ein paar Fragen hab ich noch, da ich gern Sachen nicht
nur passend anwende, sondern auch verstehe.
Die meisten Funktionen sind in der Anwendung sehr einfach. In der Excel-Hilfe sind diese Funktionen auch ausreichend erklärt. Außerdem bietet Excel unter EXTRAS, FORMELÜBERWACHUNG, FORMELAUSWERTUNG ein wunderbares Hilfsmittel, um den Inhalt einer Formel auszuwerten und zu verstehen, was passiert.
Grundsätzlich werden alle Klammerausdrücke von innen nach außen berechnet.
Marion: Danke, das mit dem Oder trau ich mich immer
nicht, wegen fehlender Syntaxkenntnisse. Ich nehme an,
siehe Hilfe
Die Zahlen 1(wahr) und 0(falsch) werden von ODER() wie die entsprechenden Wahrheitswerte behandelt. Jede beliebige andere Zahl wird als wahr behandelt
WOCHENTAG(A13;1) heißt, dass das ein Sonntag ist?
Es kommt darauf an, welches Datum in A13 steht.
WOCHENTAG() siehe Hilfe
( Danke, Kubi, für die Info mit WOCHENTAG!)
Könntest Du bitte so nett sein und mir erklären,
a) was hier eigentlich
passiert?=(REST(UNTERGRENZE(A2;1);7)>=2)*6,5
REST(,) siehe Hilfe
UNTERGRENZE( siehe Hilfe
UNTERGRENZE() rundet auf eine ganze Zahl ab (da Schritt=1), UNTERGRENZE() ist nur nötig, wenn in der Zelle ein Datum steht, das - wenn es als Zahl formatiert wäre - Nachkommastellen liefert. Also auch wenn man es nicht sieht, enthält das Datum eine Uhrzeit ungleich 0:00:00. In dieser Form kommt es meist nur durch automatische Prozesse wie Downloads oder mit Funktionen wie HEUTE() in die Zelle. Somit setzt UNTERGRENZE() das Datum praktisch auf 0:00:00 Uhr. Wird das Datum manuell eingetragen oder sicher ohne Zeit übernommen, kann man auf die Funktion verichten.
REST() dividiert die Zahl, die für das Datum steht durch 7 und gibt den Rest dieser Division raus. Ist das Ergebnis >=2, also eine wahre Aussage (entspricht =1) wird mit 6,5 multipliziert, ist die Aussage falsch (=0), wird mit 0 multipliziert
b) was Code ist und warum unter 80?
=(CODE(TEXT(A13;„TTT“))
CODE() siehe HIlfe
Code =(REST(A13;7)>1)*6,5 bzw.
siehe auch weiter ober unter WAHR() und REST()
diese Formel ermittelt für alle Wochentage den gleichen Faktor für die Stundenermittlung (Freitag wird nicht extra berücksichtigt)
=WENN(REST(A13;7)=6;4;WENN(REST(A13;7)
normale verschachtelte WennAbfrage - sollte für dich kein Problem sein und ist dir auch bekannt (deine Anfrage enthielt bereits so eine Formel).
Trotzdem für andere Interessierte ganz kurz:
wenn Div durch 7 den Rest 6 ergibt (=Freitag)
dann 4 (Stunden)
sonst
wenn Div durch 7
> **Reinhard:** Was bedeutet bei
> =(SUCHEN(TEXT(A13;"TTT");"MoDiMiDoFrSaSo")
Hallo Verena, hallo Reinhard,
a) was hier eigentlich
passiert?=(REST(UNTERGRENZE(A2;1);7)>=2)*6,5K.A. ich habe Untergrenze noch nicht kapiert
OBERGRENZE(
UNTERGRENZE(
Parameter:
ist die Zahl, die gerundet werden soll
ist das Vielfache, auf das gerundet werden soll
Ein fehlender Parameter liefert #WERT!
UNTERGRENZE(17,3;1) rundet 17,3 auf ein Vielfaches von 1 ab. Ergebnis =17
UNTERGRENZE(17,3;5) rundet 17,3 auf ein Vielfaches von 5 ab. Ergebnis 15
UNTERGRENZE(17,3;11) rundet 17,3 auf ein Vielfaches von 11 ab. Ergebnis 11.
OBERGRENZE() rundet entsprechend auf.
Negative Zahlen werden gleich behandelt, dabei müssen aber und immer gleiche Vorzeichen haben. Unterschiedliche Vorzeichen liefern #Zahl!
UNTERGRENZE(-17,3;-13) rundet auf ein Vielfaches von -13. Ergebnis ist -13.
UNTERGRENZE(-1772,53;-15). Ergebnis: -1770
Eigentlich ganz einfach.
Lieben Gruß
Marion
Eigentlich ganz einfach.
Hallo Marion,
danke dir für deine Bemühungen, aber Untergrenze und ich wir müssen uns noch annähern ))
Und wenn ich die richtig kapiert habe dann wird es ja ein Leichtes sein auch die Obergrenze zu kapieren.
Abgesehen davon gibt es in Excel eine Menge Funktionen, grad finanztechnischer Art, aber auch andere, da werde ich im Traum nicht dran denken mich mit denen zu beschäftigen, außer ich bräuchte es für eine Auftragsprogrammierung, dann ziehe ich mir das halt rein oder besser ich frage dann gezielt nach einer Funktion nach wenn ich die aus Finanztechnischen Hintergründen her nicht verstehe.
Und bei Diagrammfragen frage ich dich *gg*
PS: Ich habe auch noch einige Formelvarianten, aber die sind auf ner Diskette und der mistige PC hier weigert sich die zu lesen, demnächst also. Und es war schon immer so, daß einige Diskettenlaufwerke sich gegenseitig nicht lesen können.
In die eine Variante habe ich Wahl eingebaut,ansonsten nur Variationen des schon Bekannten, Wochentag, Rest, usw.
Lieben Gruß
Reinhard
Hallo Reinhard,
Eigentlich ganz einfach.
glaubs mir, du darfst nur nicht denken, dass das kompliziert ist. Dann blockierst du dich
danke dir für deine Bemühungen, aber Untergrenze und ich wir
müssen uns noch annähern
Und wenn ich die richtig kapiert habe dann wird es ja ein
Leichtes sein auch die Obergrenze zu kapieren.
genau
UNTERGRENZE() heißt erst mal nur abrunden
- eine beliebige Zahl bzw. ein Bezug
- dazu brauch man das kleine (oder große) Einmaleins
=UNTERGRENZE(42;5)
heißt: abrunden auf ein Vielfaches von 5
Vielfaches von 5: 5,10,15,20,25,30,35,40,…
42 wird abgerundet auf 40, das ist das nächste Vielfache von 5 (am nächsten an 42)
=UNTERGRENZE(42;5) ergibt somit 40
Hier verweise ich auf deine Anregung an Verena, die Formel in Excel auszuprobieren
PS: Ich habe auch noch einige Formelvarianten, aber die sind
auf ner Diskette und der mistige PC hier weigert sich die zu
lesen, demnächst also. Und es war schon immer so, daß einige
Diskettenlaufwerke sich gegenseitig nicht lesen können.
Ich glaubs dir, du musst mir nichts beweisen, du hast mir viel voraus besonders in vba - ich hab auch nicht alle Funktionen im Kopf, dazu gibt es die Hilfe. Jeder hat seine Stärken. Es hat Spass gemacht, kürzere oder andere Ausdrücke zu finden, aber mich hat gewurmt, dass ich das mit Freitag übersehen hatte. Ich denke Verena ist zufrieden und damit ist für mich die Aufgabe erledigt, abgehakt und nicht mehr interessant.
Ganz lieben Gruß
Marion
Ich bin mehr als zufrieden!
Danke Thank you Merci Grazie Tesekkürler
Herzlichen Dank für die vielen Anregungen und die geduldigen Lektionen! Mir raucht zwar der Kopf, aber wenigstens hab ich jetzt was, um mich das Wochenende über zu beschäftigen 
Und einen Teil des lästigen Suchens nach den passenden ASCII-Codes wurde mir so ganz nebenbei auch noch erspart )
Also, mir habt Ihr eine große Freude gemacht … bitte weiter so. Und ich hoffe, ich kann Euch auch irgendwann, irgendwo mal weiterhelfen.
LG, Verena
Hi Verena,
Herzlichen Dank für die vielen Anregungen und die geduldigen
Lektionen! Mir raucht zwar der Kopf, aber wenigstens hab ich
jetzt was, um mich das Wochenende über zu beschäftigen
mir geht es so, lange Beitragsfolgen die mich interessieren drucke ich mir aus, das mit dem am Bildschirm scrollen ist für mich nix.
Und einen Teil des lästigen Suchens nach den passenden
ASCII-Codes wurde mir so ganz nebenbei auch noch erspart
Naja, bin ja Gentleman und habe mir jedweden Hinweis darauf ob deine F1 Taste kaputt ist und den auf rtfm erspart *selbstlob* ))
Also, mir habt Ihr eine große Freude gemacht … bitte weiter
so. Und ich hoffe, ich kann Euch auch irgendwann, irgendwo mal
weiterhelfen.
Kannst du, was ist hIghQ ?
Klar könnte ich danach googeln, aber vielleicht interessiert das noch Andere.
Lieben Gruß
Reinhard
=(CODE(TEXT(A13;„TTT“))=2)*6,5
=(CODE(TEXT(A13;„TTT“))1)*6,5
Hallo Marion,
heute hat es mit der Diskette geklappt, auf die kurze Rest-Formel kam ich auch nach einigen (Ver-) Wirrungen:smile:
=(REST(UNTERGRENZE(A1;1);7)>=2)*6,5
=6,5*(SUCHEN(LINKS(TEXT(A1;„TTT“);1);„SMDF“)>1)
=6,5*(1-(REST(A1;7)7)*1) 'steckt ein Denkfehler drin, klappt aber, Fehler ist mir bekannt
=WAHL(REST(A1;7)+1;1;0;1;1;1;1;1;0)*6,5
=6,5*(SUCHEN(REST(A1;7);„0123456“)>2)
=(WOCHENTAG(A1;2)1)*6,5
Und ich sehe das nicht als Wettstreit, sondern als ein für alle Beteiligten effektiven lehrreichen Brain-storming Austausch.
In einem anderen Excel Forum warf mal jmd. die für die Praxis völlig egale Frage auf, wie denn die größte Zahl lautet, die Excel darstellen kann ohne in den 10^x Modus zu schalten.
Ich habe da nur staunend zugeschaut mit welchen Tricks sich da die Profis der größten Zahl näherten und näherten. Auf manche Angehensweisen wäre ich nie kommen.
Und da wurde kein geheimnisvoller Makrocode benutzt, nö, ganz normale Funktionen, aber halt trickreich eingesetzt.
Genauso wie du die „Code“-Funktion benutzt hast, die ich auch kenne, aber ich kam halt nicht darauf, diese Funktion einzusetzen.
Und wie Computer eine 64Bit Zahl im Speicher, incl. Vorzeichenbit usw. ablegen, ist denen allen bekannt, es ging darum wie Excel das handelt, also was die größte erreichbare Zahl in Excel ist.
Es war sehr lehrreich für mich.
Bei Interesse kann ich mal schauen ob ich die Beitragsfolge wiederfinde und dir mailen.
Gruß
Reinhard
hIghQ - off topic
Lieber Reinhard,
Hi Verena,
mir geht es so, lange Beitragsfolgen die mich interessieren
drucke ich mir aus, das mit dem am Bildschirm scrollen ist für
mich nix.
Ich lad immer gleich den ganzen Artikelbaum runter *g* - die Reihenfolge ist zwar nicht schlüssig, aber egal.
Und einen Teil des lästigen Suchens nach den passenden
ASCII-Codes wurde mir so ganz nebenbei auch noch erspartNaja, bin ja Gentleman und habe mir jedweden Hinweis darauf ob
deine F1 Taste kaputt ist und den auf rtfm erspart *selbstlob*
Wie meinst Du das? Soll ich als Abendlektüre mal eben so auf Verdacht alle Formeln durchlesen, die eventuell zu meiner gerade aktuellen Suche passen könnten? (Und selbst da wäre ich bei „Zeichen“ wohl nicht hängen geblieben …) Nee, nee, bei aller Liebe - da kann ich mich grad noch beherrschen. Und bei Euren Beiträgen war ich irgendwie vom Aussehen her ganz selbstverständlich davon ausgegangen, dass das von Euch selbst entwickelte Formeln sind, die man eh im „fm“ nicht findet. Tut mir Leid, wenn ich da im Irrtum war.
Und ich hoffe, ich kann Euch auch irgendwann, irgendwo mal
weiterhelfen.Kannst du, was ist hIghQ ?
Klar könnte ich danach googeln, aber vielleicht interessiert
das noch Andere.
Ich trau’s mich ja kaum zu sagen, nachdem Du mich oben so zurechtgestaucht hast - hIghQ ist ein Verein für Hochintelligente … (Lach nicht! Auch Hochintelligente kommen manchmal nicht auf das Nächstliegende!) Näheres findest Du unter http://www.highq-ev.de/ oder im Forum (http://www.highq-ev.de/hIghQ-Forum/).
LG, Verena