Supportnet / Forum / Tabellenkalkulation
Erfassung von Betreuungszeiten
Frage
Ich hoffe es kann jemand helfen:
Ich brauch zur monatlichen Erfassung von Betreuungszeiten eine Excel Tabelle mit folgenden Anforderungen:
1. Spalte = 31 Tage (1. 2. 3. usw.)
2. Spalte = monatlich wechselnde Zuordnung der Wochentage zum jeweiligen Datum
3. Spalte = leistungsbereich 1
4. Spalte =Leistungsbereich 2
5.Spalte = leistungsbereich 3
6. Spalte = Leistungsbereich 4
Mit den verschiedenen Wochentagen stehen bis zu drei Pauschalzeiten ( regelmäßige, feste Angebote)in den jeweiligen Spalten der Leistungsbereiche in Verbindung der Verbindung.
In der jeweiligen Leistungsbereichsspalte gibt es dann eine Zweiteilung - einmal Pauschale Zeit und einmal die tägliche variable Zeit.
Ich würde mich freuen wenn jemand weiterhelfen kann. Wäre schon schön wenn mir jemand sagen kann wie automatisiert, monatlich, eine Zuordnung der Wochentage zum Datum erfolgt. Somit müßte die tabelle mit der Wochentagszuordnung nicht monatlich neu geschrieben werden.
Gruß Ingo
Antwort 1 von M.O.
Hallo Ingo,
den Wochentag eines Datum bekommst du, indem du in die Zelle das Datum schreibst und die Zelle dann benutzerdefiniert mit „TTTT“ formatierst.
Wenn du monatliche Blätter erstellen willst, könntest du es z.B. so machen:
In der Zelle B2 steht das Jahr: 2005
In der Zelle B3 steht der Monat: August
In den Zellen A5 bis A 35 stehen deine einzelnen Tage; also in A 5: 1
Füge in die Zelle B 5 die folgende Formel ein: =DATWERT(VERKETTEN(A5;$B$3;$B$2)) und dann formatiere die Zelle benutzerdefiniert mit „TTTT“.
Wenn du die Formel dann nach unten kopierst, werden die Wochentage automatisch geändert, wenn du den Monat oder das Jahr änderst.
Gruß
M.O.
den Wochentag eines Datum bekommst du, indem du in die Zelle das Datum schreibst und die Zelle dann benutzerdefiniert mit „TTTT“ formatierst.
Wenn du monatliche Blätter erstellen willst, könntest du es z.B. so machen:
In der Zelle B2 steht das Jahr: 2005
In der Zelle B3 steht der Monat: August
In den Zellen A5 bis A 35 stehen deine einzelnen Tage; also in A 5: 1
Füge in die Zelle B 5 die folgende Formel ein: =DATWERT(VERKETTEN(A5;$B$3;$B$2)) und dann formatiere die Zelle benutzerdefiniert mit „TTTT“.
Wenn du die Formel dann nach unten kopierst, werden die Wochentage automatisch geändert, wenn du den Monat oder das Jahr änderst.
Gruß
M.O.
Antwort 2 von ingo75
Hallo M.O.,
vielen Dank für die schnelle Antwort - ich probiere es.
gruß ingo75
vielen Dank für die schnelle Antwort - ich probiere es.
gruß ingo75
Antwort 3 von ingo75
Hallo M.O.,
hat bisher gut funktioniert. Habe jetzt eine Monatstabelle, in der sich die Wochentage dem datum anpassen. Wäre sehr schön wenn Du noch weiterhelfen könntest. Wir erfassen Betreuungszeiten pro Klient in einer WG. Die Monatstabelle enthält 4 Leistungsbereiche zu denen die Betreuungszeiten täglich erfaßt werden. Hier gibt es pro Leistungsbereich feste, wochentagsabhängige, pauschale Zeiten, die bei mir in einer tabelle neben dem Monatsblatt stehen. In der Monatstabelle gibt es dann pro Leistungsbereich 2 Spalten. In der ersten sollen die immer gleichen pauschalen zeiten aus meiner "nebentabelle" stehen. Sie sind wie gesagt abhängig vom Wochentag. In der zweiten Spalte des leistungsbereiches müßten dann täglich die variablen Betreuungszeiten pro Klient per hand ergänzt werden.
Ich hoffe meine Beschreibung ist verständlich. Wenn in der Monatstabelle zum Ausdrucken schon die pauschalen zeiten aus der "Nebentabelle" stehen würden wäre es eine Arbeitserleichterung.
Später wäre noch darüber nachzudenken ob täglich über eine Eingabemaske pro Klient und Monat die variablen zeiten am PC eingetragen werden und in der Monatstabelle erscheinen. Aber wie gesagt mit der Monatstabelle zum per Hand ausfüllen wären wir schon ein ganzes Stück weiter.
Gruß Ingo75
hat bisher gut funktioniert. Habe jetzt eine Monatstabelle, in der sich die Wochentage dem datum anpassen. Wäre sehr schön wenn Du noch weiterhelfen könntest. Wir erfassen Betreuungszeiten pro Klient in einer WG. Die Monatstabelle enthält 4 Leistungsbereiche zu denen die Betreuungszeiten täglich erfaßt werden. Hier gibt es pro Leistungsbereich feste, wochentagsabhängige, pauschale Zeiten, die bei mir in einer tabelle neben dem Monatsblatt stehen. In der Monatstabelle gibt es dann pro Leistungsbereich 2 Spalten. In der ersten sollen die immer gleichen pauschalen zeiten aus meiner "nebentabelle" stehen. Sie sind wie gesagt abhängig vom Wochentag. In der zweiten Spalte des leistungsbereiches müßten dann täglich die variablen Betreuungszeiten pro Klient per hand ergänzt werden.
Ich hoffe meine Beschreibung ist verständlich. Wenn in der Monatstabelle zum Ausdrucken schon die pauschalen zeiten aus der "Nebentabelle" stehen würden wäre es eine Arbeitserleichterung.
Später wäre noch darüber nachzudenken ob täglich über eine Eingabemaske pro Klient und Monat die variablen zeiten am PC eingetragen werden und in der Monatstabelle erscheinen. Aber wie gesagt mit der Monatstabelle zum per Hand ausfüllen wären wir schon ein ganzes Stück weiter.
Gruß Ingo75
Antwort 4 von M.O.
Hallo Ingo,
ich glaube, ich weiß was du willst.
Nehmen wir an, deine Nebentabelle beginnt in der Zelle I5 mit Montag. In der Spalte J stehen deine Zeiten, die du zu den einzelnen Wochentagen zugeordnet haben willst.
Dein Tagesdatum steht in A5, der Wochentag in B5. In C5, wo deine pauschale Betreuungszeit stehen soll, schreibst du folgende Formel rein:
=SVERWEIS(TEXT(B5;"TTTT");$I$5:$J$11;2;FALSCH)
Mit der Funktion SVERWEIS kannst du eine Matrix nach einer Übereinstimmung durchsuchen. Die Funktion TEXT ist notwendig, damit die Wochentage, die als Zahl vorliegen und nur mit TTTT formatiert sind, mit den Wochentagen in deiner Tabelle verglichen werden können. Mit $I$5:$J$11 wird angegeben, wo deine Tabelle steht, aus der die Werte gesucht werden sollen. Hier musst du die Formel auf deine Bedürfnisse anpassen. Die 2 gibt an, dass der Wert aus der 2. Spalte der Tabelle, wo deine Betreuungszeiten stehen, genommen werden soll. Und mit „falsch“ wird eine genaue Übereinstimmung bei den Wochentagen gesucht.
Gruß
M.O.
ich glaube, ich weiß was du willst.
Nehmen wir an, deine Nebentabelle beginnt in der Zelle I5 mit Montag. In der Spalte J stehen deine Zeiten, die du zu den einzelnen Wochentagen zugeordnet haben willst.
Dein Tagesdatum steht in A5, der Wochentag in B5. In C5, wo deine pauschale Betreuungszeit stehen soll, schreibst du folgende Formel rein:
=SVERWEIS(TEXT(B5;"TTTT");$I$5:$J$11;2;FALSCH)
Mit der Funktion SVERWEIS kannst du eine Matrix nach einer Übereinstimmung durchsuchen. Die Funktion TEXT ist notwendig, damit die Wochentage, die als Zahl vorliegen und nur mit TTTT formatiert sind, mit den Wochentagen in deiner Tabelle verglichen werden können. Mit $I$5:$J$11 wird angegeben, wo deine Tabelle steht, aus der die Werte gesucht werden sollen. Hier musst du die Formel auf deine Bedürfnisse anpassen. Die 2 gibt an, dass der Wert aus der 2. Spalte der Tabelle, wo deine Betreuungszeiten stehen, genommen werden soll. Und mit „falsch“ wird eine genaue Übereinstimmung bei den Wochentagen gesucht.
Gruß
M.O.
Antwort 5 von Aliba
Hi Ingo,
nachdem ich schon vor der letzten Antwort vom M.O.
bereits schreiben wollte, aber das Telefon mal wieder dazwischen kam, auch von mir noch eine kleine Ergänzung. Der Ansatz vom M.O. ist natürlich völlig in Ordnung, allerdings würde ich die Datumsberechnung bereits in der Spalte A vornehmen. Dadurch bekommst Du nur die Tage angezeigt, die der Monat tatsächlich hat.
Also Formel in A5 und bis A35 nach unten kopiert:
=WENN(ISTFEHLER(DATWERT(VERKETTEN(ZEILE(A1);$B$3;$B$2)));"";DATWERT(VERKETTEN(ZEILE(A1);$B$3;$B$2)))
Das Format für den Bereich A5 bis A35: benuterdefiniert "TT"
Formel in B5 bis B35 nach unten kopiert:
=TEXT(A5;"TTTT")
Dann kannst Du die SVERWEIS - Formel von M.O. leicht abändern und hast das selbe Ergebnis:
=WENN($A5="";"";SVERWEIS($B5;$I$5:$J$11;2;FALSCH))
CU Aliba
PS Erklär mal den Aufbau Deiner Nebentabelle, das müsste ja eine Art Kreuztabelle sein mit Abhängigkeit Wochentag und Leistungsart.
Da könnte man dann anstelle der SVERWEIS-FOrmel evtl. auch eine kopierbare INDEX-Formel erstellen.
CU Aliba
nachdem ich schon vor der letzten Antwort vom M.O.
bereits schreiben wollte, aber das Telefon mal wieder dazwischen kam, auch von mir noch eine kleine Ergänzung. Der Ansatz vom M.O. ist natürlich völlig in Ordnung, allerdings würde ich die Datumsberechnung bereits in der Spalte A vornehmen. Dadurch bekommst Du nur die Tage angezeigt, die der Monat tatsächlich hat.
Also Formel in A5 und bis A35 nach unten kopiert:
=WENN(ISTFEHLER(DATWERT(VERKETTEN(ZEILE(A1);$B$3;$B$2)));"";DATWERT(VERKETTEN(ZEILE(A1);$B$3;$B$2)))
Das Format für den Bereich A5 bis A35: benuterdefiniert "TT"
Formel in B5 bis B35 nach unten kopiert:
=TEXT(A5;"TTTT")
Dann kannst Du die SVERWEIS - Formel von M.O. leicht abändern und hast das selbe Ergebnis:
=WENN($A5="";"";SVERWEIS($B5;$I$5:$J$11;2;FALSCH))
CU Aliba
PS Erklär mal den Aufbau Deiner Nebentabelle, das müsste ja eine Art Kreuztabelle sein mit Abhängigkeit Wochentag und Leistungsart.
Da könnte man dann anstelle der SVERWEIS-FOrmel evtl. auch eine kopierbare INDEX-Formel erstellen.
CU Aliba
Antwort 6 von ingo75
Hallo M.O. und CU Aliba,
freue mich, dass sich jetzt schon 2 Excelkenner mit unserem AZ- nachweis befassen. Selbst habe ich häufig excel genutzt aber nur für einfache berechnungen ohne logische Verknüpfungen usw. Mache eigentlich ja betreuungsarbeit.
Zum AZ Nachweis:
M.O. `s variante zu den sich monatlich ändernden Wochentagen lief gut, habe es aber entsprechend Cu Aliba geändert weil bei Monaten ohne 31 Tage dann in den eigentlich leeren Zellen eine "Wertanfrage" steht. Habe somit auch die veränderte Formel für den Bezug zu meiner nebentabelle benutzt. Leider vergaß ich anzugeben, dass diese tabelle 3 Spalten enthält( Wochentag, pauschalwert 1, pauschalwert 2. Ich brauche also für das Arbeitszeitblatt 2 automatisch einzutragende pauschalwerte. Spalteninhalte sind: 1(datum), 2(Wochentage) 3( pauschalwert leistungsbereich1)4 (Variable Zeit zum per hand eintragen für Leistungsbereich 1) 5( Pausch 2)6(variabel 2) und 7(variabel zeit leistungsbereich 3) und Spalte 8 ( variable zeit Leistungsbereich 4). Ich hoffe ihr seht bei meiner Beschreibung noch durch!
Meine Nebentabelle beginnt mit I 5 für Montag (links oben) und endet mit mit k11 für letzte pauschalzeit in Leistungsbereich 2 (rechts unten).
Bei mir erscheint dann mit Formel 2 von cu aliba in Zelle c5 - hier müßte eigentlich die pauschalzeit aus leistungsbereich 1 für Dienstag stehen ( Monat september) - leider nur "Freitag".
Vielleicht könnt ihr weiterhelfen?!
Gruß ingo75
p.s. Bin jetzt 2 -3 tage nicht am PC - hat also keine Eile
freue mich, dass sich jetzt schon 2 Excelkenner mit unserem AZ- nachweis befassen. Selbst habe ich häufig excel genutzt aber nur für einfache berechnungen ohne logische Verknüpfungen usw. Mache eigentlich ja betreuungsarbeit.
Zum AZ Nachweis:
M.O. `s variante zu den sich monatlich ändernden Wochentagen lief gut, habe es aber entsprechend Cu Aliba geändert weil bei Monaten ohne 31 Tage dann in den eigentlich leeren Zellen eine "Wertanfrage" steht. Habe somit auch die veränderte Formel für den Bezug zu meiner nebentabelle benutzt. Leider vergaß ich anzugeben, dass diese tabelle 3 Spalten enthält( Wochentag, pauschalwert 1, pauschalwert 2. Ich brauche also für das Arbeitszeitblatt 2 automatisch einzutragende pauschalwerte. Spalteninhalte sind: 1(datum), 2(Wochentage) 3( pauschalwert leistungsbereich1)4 (Variable Zeit zum per hand eintragen für Leistungsbereich 1) 5( Pausch 2)6(variabel 2) und 7(variabel zeit leistungsbereich 3) und Spalte 8 ( variable zeit Leistungsbereich 4). Ich hoffe ihr seht bei meiner Beschreibung noch durch!
Meine Nebentabelle beginnt mit I 5 für Montag (links oben) und endet mit mit k11 für letzte pauschalzeit in Leistungsbereich 2 (rechts unten).
Bei mir erscheint dann mit Formel 2 von cu aliba in Zelle c5 - hier müßte eigentlich die pauschalzeit aus leistungsbereich 1 für Dienstag stehen ( Monat september) - leider nur "Freitag".
Vielleicht könnt ihr weiterhelfen?!
Gruß ingo75
p.s. Bin jetzt 2 -3 tage nicht am PC - hat also keine Eile
Antwort 7 von Aliba
Hi Ingo,
prüfe doch bitte noch einmal die SVERWEIS-Formel, ob Du da nicht etwas falsch gemacht hast, denn wenn Du die Formel richtig geschrieben hast, kann lt. deinem beschriebenen Aufbau gar nicht Freitag als Ergebnis kommen.
Was steht wo:
In B2 steht die Jahreszahl
In B3 steht der Monatstext (Januar, Februar...)
In A5 steht die Formel:
=WENN(ISTFEHLER(DATWERT(VERKETTEN(ZEILE(A1);$B$3;$B$2)));"";DATWERT(VERKETTEN(ZEILE(A1);$B$3;$B$2)))
bis Zeile 35 nach unten kopieren. Format benutzerdefiniert: TT
In B5 steht die Formel:
=TEXT(A5;"TTTT")
bis Zeile 35 nach unten kopieren.
In C5 steht die Formel:
=WENN($A5="";"";SVERWEIS($B5;$I$5:$K$11;2;0))
bis Zeile 35 nach unten kopiert.
UND JETZT WICHTIG!!! BITTE PRÜFE DIE FORMATIERUNG DER ZELLEN IN C5 BIS C35.
Ich nehme fast an Du hast hier auch das benutzerdefinierte Format "TTTT" eingestellt. Wahrscheinlich ist dein Pauschwert für Dienstag 6, was dann als "TTTT"-Formatierung der Freitag wäre.
Format sollte Standard oder Zahl sein.
In E5 steht die Formel:
=WENN($A5="";"";SVERWEIS($B5;$I$5:$K$11;2;0))
ebenfalls bis Zeile 35 nach unten kopiert.
UND FORMAT BEACHTEN!!
Nun sollte dann doch alles funzen.
CU Aliba
prüfe doch bitte noch einmal die SVERWEIS-Formel, ob Du da nicht etwas falsch gemacht hast, denn wenn Du die Formel richtig geschrieben hast, kann lt. deinem beschriebenen Aufbau gar nicht Freitag als Ergebnis kommen.
Was steht wo:
In B2 steht die Jahreszahl
In B3 steht der Monatstext (Januar, Februar...)
In A5 steht die Formel:
=WENN(ISTFEHLER(DATWERT(VERKETTEN(ZEILE(A1);$B$3;$B$2)));"";DATWERT(VERKETTEN(ZEILE(A1);$B$3;$B$2)))
bis Zeile 35 nach unten kopieren. Format benutzerdefiniert: TT
In B5 steht die Formel:
=TEXT(A5;"TTTT")
bis Zeile 35 nach unten kopieren.
In C5 steht die Formel:
=WENN($A5="";"";SVERWEIS($B5;$I$5:$K$11;2;0))
bis Zeile 35 nach unten kopiert.
UND JETZT WICHTIG!!! BITTE PRÜFE DIE FORMATIERUNG DER ZELLEN IN C5 BIS C35.
Ich nehme fast an Du hast hier auch das benutzerdefinierte Format "TTTT" eingestellt. Wahrscheinlich ist dein Pauschwert für Dienstag 6, was dann als "TTTT"-Formatierung der Freitag wäre.
Format sollte Standard oder Zahl sein.
In E5 steht die Formel:
=WENN($A5="";"";SVERWEIS($B5;$I$5:$K$11;2;0))
ebenfalls bis Zeile 35 nach unten kopiert.
UND FORMAT BEACHTEN!!
Nun sollte dann doch alles funzen.
CU Aliba
Antwort 8 von ingo75
Hallo CU Aliba,
völlig richtig - Zellen in Spalte c waren falsch formatiert!
Es funktioniert jetzt fast alles. Vielen Dank! Habe nur noch das Problem, dass immer sonnabends "#NV" erscheint. An diesem Tag ist der Pauschalwert auch "0" Aber es steht in der Nebentabelle auch eine 0 und an anderen Tagen gibt es z.T. auch einen Pauschalwert von 0, nur dieser wird dann auch als 0 übernommen und angezeigt. Ich habe schon die fORMEL für Tage wo 0 richtig angezeigt wird mit der Formel für die Sonnabende verglichen - die Bezüge scheinen mir aber richtig. Vielleicht kannst Du helfen?
Gruß Ingo
völlig richtig - Zellen in Spalte c waren falsch formatiert!
Es funktioniert jetzt fast alles. Vielen Dank! Habe nur noch das Problem, dass immer sonnabends "#NV" erscheint. An diesem Tag ist der Pauschalwert auch "0" Aber es steht in der Nebentabelle auch eine 0 und an anderen Tagen gibt es z.T. auch einen Pauschalwert von 0, nur dieser wird dann auch als 0 übernommen und angezeigt. Ich habe schon die fORMEL für Tage wo 0 richtig angezeigt wird mit der Formel für die Sonnabende verglichen - die Bezüge scheinen mir aber richtig. Vielleicht kannst Du helfen?
Gruß Ingo
Antwort 9 von Aliba
Hi Ingo,
anstelle von Sonnabend - Samstag
das sollte es dann sein.
CU Aliba
anstelle von Sonnabend - Samstag
das sollte es dann sein.
CU Aliba
Antwort 10 von ingo75
Hallo CU Aliba,
mit dem Samstag hat es geklappt - danke.
Noch eine Bitte - da unsere Tabelle bald nicht mehr ins Druckformat paßt wäre es schön wenn in der Wochentagsspalte nur die ersten beiden Buchstaben sichtbar wären- geht das? und wie?.
Gruß Ingo75
mit dem Samstag hat es geklappt - danke.
Noch eine Bitte - da unsere Tabelle bald nicht mehr ins Druckformat paßt wäre es schön wenn in der Wochentagsspalte nur die ersten beiden Buchstaben sichtbar wären- geht das? und wie?.
Gruß Ingo75
Antwort 11 von M.O.
Hallo Ingo,
wenn du die Wochentage mit „TTT“ formatierst, werden nur die ersten beiden Buchstaben angezeigt.
Die Formel für die Wochentage muss dann so aussehen: =TEXT(A5;"TTT")
Beachte aber, dass du dann auch bei deiner Liste, aus der du die Betreuungszeiten suchen lässt, die Wochentage entsprechend änderst, sonst bekommst du mit SVERWEIS eine Fehlermeldung.
Gruß
M.O.
wenn du die Wochentage mit „TTT“ formatierst, werden nur die ersten beiden Buchstaben angezeigt.
Die Formel für die Wochentage muss dann so aussehen: =TEXT(A5;"TTT")
Beachte aber, dass du dann auch bei deiner Liste, aus der du die Betreuungszeiten suchen lässt, die Wochentage entsprechend änderst, sonst bekommst du mit SVERWEIS eine Fehlermeldung.
Gruß
M.O.
Antwort 12 von ingo75
Hallo M.O. und CU Aliba,
jetzt funktioniert alles - vielen Dank an Euch!
Gruß Ingo
jetzt funktioniert alles - vielen Dank an Euch!
Gruß Ingo