Datumsformel IF und Wenn Problem

[Bert1001]

Hallo compubast,
wenn ich in der Formel den letzten Parameter von 1 auf 0 ändere funktionierts bei mir.

=SVERWEIS(TEXT(A1;"MMMM");Tabelle2.A1:B12;2;1)

ändern zu

=SVERWEIS(TEXT(A1;"MMMM");Tabelle2.A1:B12;2;0)

mfg

[balu]

Hallo compubast,

und wenn Du die Formel wie folgt änderst, dann wird automatisch zwischen Vollzeit und Teilzeit unterschieden.

Code: Alles auswählen

=SVERWEIS(TEXT($A$1;"MMMM");$Tabelle2.$A$1:$C$12;WENN(B3=40;2;3);0)

Gruß
balu

[compubast]

Hallo Ihr Experten,
vielen vielen Dank für die vielen Tipps und Hinweise. Super. Mit den Formeln habt Ihr mich alle ein großes Stück weitergebracht.
Ich habe die letzte Formel von balu erfolgreich eingearbeitet. Und mit den bislang vorhanden Daten klappt es gut.
Nun habe ich heute morgen einen weiteren Stundenzettel erhalten , der auch noch berücksichtigt werden muß. Ich habe die restlichen Wochenstunden in der Beispieltabelle eingetragen und sie aktualisert. Und gemäß dem Vorbild der Formel von Balu habe ich versucht die Formel zu erweitern um die restlichen Abfragen. Ich habe gedacht , wenn ich die Zeilen des Arbeitsblattes 2 in der Formel erweitere und einfüge dann klappt das. Hat es aber leider nicht.
Ich habe die Formel wie folgt abgeändert bzw. ergänzt :
=SVERWEIS(TEXT($A$1;"MMMM");$Tabelle2.$A$1:$C$12;WENN(B4=40;2;3);0);$Tabelle2.$A$1:$M$12;WENN(B4=12,5;2;3);0);WENN(B4=25;2;3);0)

Bin daher nochmals auf Euren Expertenrat angewiesen.
Bitte helft mir noch einmal .

Vielen Dank schon mal im voraus.
Compubast
http://www.kymco-winsen.de.tl

[compubast]

Hallo,
vielen Dank für die neue Formel. Ich habe sie gleich eingesetzt und kam leider nicht ganz zu dem gewünschten Ergebnis. Im Feld C3 wird der Fehler 501 angezeigt. Die Zahlen , die im Arbeitsblatt 2 hinterlegt sind , sind noch nicht alle fertig berechnet. Selbstverständlich haben Sie recht, wer weniger als 40 Std. in der Woche arbeitet , kann nicht die gleichen Stunden haben wie jemand der nur 12,5 Std. in der Woche arbeitet. Irgendwo steckt in der Formel noch ein Fehler. Leider fehlt mir die Erfahrung in diesem Bereich . Bin daher für jeden Hinweis und jede Hilfestellung sehr dankbar.
Jeder ist Spezialist auf seinem Gebiet - und das hier ist leider nur bedingt mein Bereich.

Vielen Dank für die bisherige Hilfe, wir nähern uns dem Ziel .
Viele Grüße
Compubast
www.kymco-winsen.de.tl

[balu]

Hallo compubast,

wie paljass schon erwähnte, fehlen uns noch so einige angaben. Mir ist z.B. aufgefallen, dass Du in deiner modifizierten Formel mit 2 verschiedenen Bereichen arbeitest.

=SVERWEIS(TEXT($A$1;"MMMM");$Tabelle2.$A$1:$C$12;WENN(B4=40;2;3);0);
$Tabelle2.$A$1:$M$12;WENN(B4=12,5;2;3);0);WENN(B4=25;2;3);0)

Hat das was zu bedeuten?

Du weißt schon, dass die WENN-Abfrage innerhalb der SVERWEIS (der dritte Parameter) für den Spaltenindex innerhalb der Suchmatrix $A$1:$C$12 zuständig ist?


Da ich jetzt keinen großartigen Bock auf Fragen habe , benutze ich nun mal meine Glaskugel "SempronBG"

Du hast den neuen Stundenzettel für die 12,5 und die 25 Stunden im Tabellenblatt "Tabelle2" in den spalten D und E eingetragen. Die Spalte A hast Du nicht geändert, und die Zeile 14 hast du ergänzt. Und zwar so, das in D14 die 12,5 und in E14 die 25 steht. Nun musst Du noch in B14 und C14 das "Std." löschen, damit dort nur die reinen Zahlen stehen.

Nun willst Du in "Tabelle1" in der Spalte C (Soll-std.im Monat) die passenden Stunden zu den Werten aus der Spalte B (Wochen-stunden) eingetragen bekommen.
Und in diesem Fall müsste die letzte Formel von paljass rein theoretisch wie folgt geändert werden.

Code: Alles auswählen

=SVERWEIS(TEXT($A$1;"MMMM");$Tabelle2.$A$1:$E$12;WENN(B4=40;2;WENN(B4=30;3;WENN(B40=12,5;4;WENN(B4=25;5;???))));0)

Die Fragezeichen sollen bedeuten, dass die Formel nicht weiß was sie machen soll, wenn kein übereinstimmender Wert vorhanden ist.
Und ganz ehrlich gesagt, diese Formel ist einfach nur Müll.
Deshalb nehmen wir eine gescheite Formel mit Fehlerabfangen.

Code: Alles auswählen

=WENN(ZÄHLENWENN($Tabelle2.$A$12:$Z$14;$B3);INDEX($Tabelle2.$A$1:$Z$12;VERGLEICH(TEXT($A$1;"MMMM");$Tabelle2.$A$1:$A$12;0);VERGLEICH($B3;$Tabelle2.$A$14:$Z$14;0));"")

Ich habe den Datenbereich in "Tabelle2" bis auf die Spalte Z erweitert, da ich ja nicht weiß wieviele Spalten Du dort wirklich hast.

ZÄHLENWENN schaut erstmal in "Tabelle2" in der Zeile 14 nach, ob dort der gleiche Wert steht wie in B3. Wenn ja, dann geht es mit der INDEX weiter.

INDEX
Der Bezug (Datenbereich) ist $Tabelle2.$A$1:$Z$12
Jetzt wird die Zeilennummer auf Grund des Monats [TEXT($A$1;"MMMM")] innerhalb des Bezugs ermittelt, das geschieht mit VERGLEICH.
Als nächstes wird die Spaltennummer [$B3] auf Grund der Sollstunden innerhalb des Bezugs ermittelt, das geschieht auch mit VERGLEICH.
Vergleich liefert eine Zahl zurück, die in INDEX einmal für die Zeile, und einmal für die Spalte gebraucht wird. Daraus ergibt sich eine Koordinate, und der Wert der im schnittpunkt dieser Koordinate steht, wird nun zurückgegeben.

Sollte ZÄHLENWENN in "Tabelle2" Zeile 14 keine Übereinstimmung mit dem Wert in B3 finden, so wird gar nichts gemacht.

Und nicht vergessen!
In "Tabelle2" in den Zellen B14 bis ??? dürfen nur Zahlen stehen. Sowas wie 40 Std. führt zu keinem Ergebniss.

Meine erste gepostete Idee und Formel war NUR für 2 Spalten gedacht. Bei mehr als 2 Spalten ist dies verfahren nicht wirklich besonders gut geeignet.

Mit der jetzigen Formel müsste nun alles passen. Wenn nicht, dann her mit genaueren Infos und einer zusätzlichen Beispieldatei.



Gruß
balu

[compubast]

Hallo,
wow das sind ja bestechende Formeln. Das muß ich mir morgen erst mal ausdrucken und Schritt für Schritt durchgehen und durchdenken. Vielen vielen Dank dafür. Bitte entschuldigt meinen kleinen "Anredefehler". Selbstverständlich ist "du" im Forum klar und kein Problem. Ich war in Gedanken noch auf Arbeit . Sobald ich die Formel(n) eingesetzt habe und ein Ergebnis habe melde ich mich sofort.
Wenn das klappt , bin ich ein großes Stück weiter, dank euerer aller Hilfe. Ihr seid echte Experten,auf diese Formel wäre ich nie gekommen. Ist leider nicht mein Spezialgebiet.

Vielen vielen Dank.
Compubast

[compubast]

Guten Morgen balu,
ich weiß nicht was ich sagen soll. Bin platt. Die Formel paßt und die Funktion bzw. Darstellung ist genau so wie ich es haben wollte. In jeder Spalte der Tabl. 1 wird genau der richtige Wert angezeigt der in der Tbl.2 im Juli steht. Ich habe in der Tbl. 1 noch eine Ergänzung um einen weiteren MA gemacht und neue Daten in der Tbl.2 hinterlegt und auch das passt reibungslos. Tausend Dank. Auf solch eine Formel wäre ich im Leben nicht gekommen. Ich werde gleich mal das Systemdatum auf August setzen und schauen ob auch hier die Berechnung für den nächsten Monat klappt. Ich denke aber sie wird es.
Auch die Änderung des Systemdatums brachte die Formel nicht aus dem Konzept. Alle Daten wurden korrekt übernommen und angezeigt.

Wünsche noch ein schönes Wochenende,
Vielen Dank an ALLE die mich so unterstützt haben und ihre Zeit damit verbracht haben eine passende Formel zu entwickeln.

Viele Grüße
Compubast
http://www.kymco-winsen.de.tl

PS. Ich traue mich ja schon nicht mehr zu fragen , aber wie kannn ich diese wertvolle Formel vor Änderungen schützen ? Ich möchte nur die gesamte Spalte mit der Formel vor Änderungen schützen. Leider klappte es nur das ich entweder das gesamte Dokument oder die Tabelle oder beides schützen kann. Das hatte dann zur Folge , das ich keine Werte mehr in den anderen Spalten eingeben kann. Im Menüpkt. Format - Zellen - Zellschutz bin ich leider auch nicht weitergekommen. Habe mir schon eine Sicherungskopie angelegt. Danke Compubast

[balu]

Hallo compubast,

freut mich, dass jetzt alles passt, und Du zufrieden bist .

... wie kannn ich diese wertvolle Formel vor Änderungen schützen ?

Vor ungewollten Änderungen gibt es für dieses Problem auch noch eine Lösung.

-> Menü
-> Daten
-> Gültigkeit
Reiter "Kriterien"
-> zulassen - Ganze Zahl
-> Daten - zwischen
-> Minimum - 0
-> Maximum - 0
Reiter "Fehlermeldung"
-> "Fehlermeldung bei Eingabe ungültiger Werte anzeigen" Häkchen rein
-> Aktion - Stopp (ist standardmäßig aktiviert)

Du kannst bei "Titel" und "Fehlermeldung" auch noch einen eigenen Text eingeben, der angezeigt wird, wenn mann versucht etwas in die betroffene Zelle einzugeben. Wenn Du es nicht machst, dann wird halt der Standardtext angezeigt.

OK, und fertig .

Schönen Sonntag.


Gruß
balu

[compubast]

Guten Abend balu,
vielen Dank für die Anleitung zum Schutz der Formel. Warum ist das nur immer alles so kompliziert. Aber zum Glück sitzen hier Experten. Ich habe den Schutz eingerichtet und klappt auch so wie ich es haben möchte.
Die Stundenberechnungstabelle wurde heute bereits mit den realen Stundenzahlen gefüllt und es hat in jeder Zeile erstklassig funktioniert.
Nochmals vielen Dank für alles, an ALLE !!!!
Vielleicht trifft man sich im Forum ja an anderen Stelle wieder,bis dahin

viele Grüße
Compubast
www.kymco-winsen.de.tl

[compubast]

Hallo Experten,
nachdem ich die Tabelle , dank eurer Hilfe , erfolgreich zur Anwendung gebracht habe, wurde der Wunsch von einigen Anwender geäußert, das sie mit der Tabelle die Stunden rückwirkend berechnen wollen.
Sie stellen sich das so vor, das die Tabelle z.B. in der Spalte aktuelles Datum das aktuelle Datum anzeigt und in einer weiteren Spalte (Spaltenbezeichnung Plandatum )direkt daneben steht das Wort Juli .
Somit stehen die Sollarbeitsstunden des Juli da obwohl wir August haben. Und das immer so weiter bis Jahresende. Wobei ich kein aktuelles Datum des Monats benötige , der Monatsname wäre ausreichend. Ich habe bereits einiges Versucht , bin aber leider immer gescheitert.
Ich bitte nochmals um Hilfestellung. Wie muß die Formel bzw. die Tabelle geändert werden ? Vielleicht kann die Tabelle noch um eine weitere Arbeitsmappe erweitert werden , sodass die Berechung im voraus und im vergangenen Monat erfolgen kann . Wahrscheinlich überfordere ich Calc damit, sowas ist bestimmt nicht möglich . Die eine Kollegin möchte die Stunden für den kommenden Monat berechnen und die anderen wollen die Überstunden des vergangenen Monats zusätzlich eingeben und die nächsten wollen beides. Ich raufe mir das Haar. Da hab ich mir ja was eingehandelt. BITTE HELFT MIR NOCHMAL WEITER !!!!
Vielen Dank schon mal im voraus,
compubast
http://www.kymco-winsen.de.tl

[compubast]

Hallo,vielen Dank für die Formel. Ich habe sie gleich in die Tabelle eingetragen. Aber es hat sich nicht wirklich etwas verändert.Ok, in der Spalte B1 steht jetzt Plandatum : Juli. Allerdings haben die Sollstundenhaben sich nicht verändert. Was mach ich nur falsch ???
Die angehängte Tabelle wurde soeben an das gültige Format angepasst, tschuldigung. Hab die falsche Datei im Büro erwischt.

Bitte um weitere Hilfestellung,
vielen Dank schon mal an alle Calc Experten
Compubast
http://www.kymco-winsen.de.tl