Office: Problem mit Stundenberechnung

Hallo ich schon wieder,

nachdem ich nun dachte all meine Probleme gelöst zu haben und den Stundenzettel mal komplett ausgefüllt hatte war ich glücklich und zu frieden das nun alles so läuft wie ich es mir dachte.

Doch dann kam das große Wundern, da sich doch noch ein/zwei Fehler eingeschlichen haben

Und zwar taucht ein Problem in der Dienststunden / Überstunden Formel auf wenn ich später als der geplante Dienstbeginn anfange oder vor dem geplanten Dienstende gehe

bei späterem Beginn ändert sich die Dienststundenzahl überhaupt nicht
Bei früherem gehen erscheinen keine Dienststunden sondern nur Überstunden

zur Verdeutlichung mal zwei Tabellen

1) so läuft alles

	A	B	C	D	E	F	G	H	I	J
7	Datum	Wochentag	Dienst	Beginn lt. Plan	Ende lt. Plan	Ist Beginn	Ist Ende	Pause	Dienststunden	Überstunden
8	01	Sa	731	09:00	15:00	09:00	15:00		6:00

2) hier taucht der Fehler auf (hier sollte I 5 enthalten und J sollte frei sein)

	C	D	E	F	G	H	I	J
7	Dienst	Beginn lt. Plan	Ende lt. Plan	Ist Beginn	Ist Ende	Pause	Dienststunden	Überstunden
8	731	09:00	15:00	09:00	14:00			23:00

3) zu guter letzt noch meine Formeln mit dem Beispiel späterer Beginn (hier müsste in I eigentlich 5 stehen)

	A	B	C	D	E	F	G	H	I	J
7	Datum	Wochentag	Dienst	Beginn lt. Plan	Ende lt. Plan	Ist Beginn	Ist Ende	Pause	Dienststunden	Überstunden
8	01	Sa	731	09:00	15:00	10:00	15:00		6:00

Zelle	Formel
A8	=E3
B8	=$A8
D8	=WENN(C8="";"";SVERWEIS(C8;Dienstzeiten!B:P;WOCHENTAG(A8;2)*2;0))
E8	=WENN(C8="";"";SVERWEIS(C8;Dienstzeiten!B:P;WOCHENTAG(A8;2)*2+1;0))
G8	=E8
H8	=WENN(ODER(C8={"ID";"Fobi"});((REST(G8-F8;1)>=0,25)*0,5/24+(REST(G8-F8;1)>=3/8)*0,25/24);0)
I8	=WENN(C8="";"";REST(G8-F8;1)-H8-J8)
J8	=WENN(ODER(C8="";D8="Eingabe";E8="Eingabe");0;SUMME(D8-F8)+REST(G8-E8;1))

Ich komme aufeinfach auf keine Lösungsidee ist wohl schon zu spät oder ich kann einfach keine Formel mehr sehen

Vielen Dank und schöne Güße

Korni

 

Hallo,

ich würde sagen, der Aufbau ist nicht gut. Überstunden errechne ich aus den Istzeiten und nicht die Stunden über die Überstunden.

Arbeitsblatt mit dem Namen 'Tabelle1'
	C	D	E	F	G	H	I	J
8	731	09:00	15:00	09:00	15:00		06:00
9	731	09:00	15:00	09:00	14:00		05:00

Zelle	Format	Wert
I8	hh:mm;;	0,25
J8	hh:mm;;	0
I9	hh:mm;;	0,208333333333333
J9	hh:mm;;	0

Zelle	Formel
I8	=(C8<>"")*(REST(G8-F8;1)-H8)
J8	=ISTZAHL(C8)*MAX(0;I8-E8+D8)
I9	=(C9<>"")*(REST(G9-F9;1)-H9)
J9	=ISTZAHL(C9)*MAX(0;I9-E9+D9)

<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]

Gruß

Edgar

 

Hallo Edgar,

Danke erstmal für deinen Tipp.
Ich habe damit ein wenig umher probiert aber so richtig habe ich es noch nicht geschaft das die Formel einwandfrei funktioniert.

Wenn ich Deine Formeln so übernehme tauchen bei mir folgende Probleme auf, die ich bisher nicht mit den Formel beheben konnte.

In der Spalte C können nicht nur Zahlen sondern auch Sachen wie Fobi, U etc stehen.

Wenn ich nun in Spalte C keinen Wert eingetragen habe oder z.B. Fobi erhalte ich mit Deiner Formel in J den Fehler #Wert.

Mit Deiner Formel in I kann ich zwar theoretisch die Dienststunden berechnen , jedoch müssen von diesen die Überstunden abgezogen werden, dies scheint mir mit das ursächliche Problem zu sein. Da wenn ich wie Du es auch getan hast in I auf den Abzug von J verzichte es damit klappt das auch bei früheren oder späterem Ende/Beginn die Zeiten richtig berechnet werden.

Hast Du noch eine Idee wie ich in J die Formel so stricken kann das ich keine Fehlermeldung erhalte wenn andere Daten eingetragen sind und ich in I weiterhin die Überstunden abziehen kann

Gruß
Korni

 

Hallo,

leider kann ich den #Wert-Fehler nicht nachvollziehen. Hier aber mal eine Änderung (in Zeile 10 u. 11 noch die alte Formel für Überstunden):

Arbeitsblatt mit dem Namen 'Tabelle1'
	C	D	E	F	G	H	I	J
8	731	09:00	15:00	08:00	15:00		6:00	0:00
9	u	09:00	15:00	08:00	16:00		6:00	0:00
10		09:00	15:00	08:00	16:00		0:00	0
11	u	09:00	15:00	08:00	16:00		6:00	0

Zelle	Formel
I8	=(C8<>"")*MIN(REST(E8-D8;1);REST(G8-F8;1)-H8)
J8	=ISTZAHL(C8)*MAX(0;I8-E8+D8)
I9	=(C9<>"")*MIN(REST(E9-D9;1);REST(G9-F9;1)-H9)
J9	=ISTZAHL(C9)*MAX(0;I9-E9+D9)
I10	=(C10<>"")*MIN(REST(E10-D10;1);REST(G10-F10;1)-H10)
J10	=ISTZAHL(C10)*MAX(0;I10-E10+D10)
I11	=(C11<>"")*MIN(REST(E11-D11;1);REST(G11-F11;1)-H11)
J11	=ISTZAHL(C11)*MAX(0;I11-E11+D11)

Gruß

Edgar

 

Hallo Edgar,

kann sein das ich eine Brille benötige aber hast Du nicht in J8 bis J11 jeweils die gleiche Formel stehen.


Für Spalte I habe ich Deine Formel mit einer kleinen Abänderung übernommen bei mir steht nun
WENN(C8="";"";MIN(REST(E8-D8;1);REST(G8-F8;1)-H8))


zum verständniss für mich was besagt in deiner Formel das <> (zwar schon ab und an gesehen aber selbst noch nicht mit gearbeitet)

in J komme ich jedoch nicht weiter, wenn ich das mittlerweile richtig analysiert habe liegt das problem darin wenn in D-G keine Zeiten eingetragen sind das es zum Fehler kommt. was natürlich passieren kann da nur die Tage wo ich dienst habe werte enthalten.
Desweiteren weiß ich nicht ob ich es schon angemerkt hatte aber wenn ich C "Fobi" oder "ID" steht darf in J auch keine Berechnung erfolgen.

Hilft das evtl weiter , da ich langsam mit dieser Blödenformel echt verzweifel und mir auch nach zig mal umher probieren keine funktionierende formel gelingt.


Gruß
Korni


P.S. gehört zwar nicht zu dem Thema , aber da Du ja sehr fit in Excel zu sein scheinst , stell ich mal die Frage so mitten rein.
Ich berechne unteranderem noch Nachtstunden und andere zulagen.
Gibts eine Möglichkeit den berechneten wert auf zwei Zeilen aufzuteilen?
Sprich wenn in M8 das Ergebniss der Nachtstunden z.B. 10 wäre (wobei 4 Std vor 00:00 Uhr sind und 6 Std nach 00:00) dann soll in Zeile M8 - 4 stehen und in M9 - 6.
eine genaue Formel bräuchte ich da momentan noch nicht , mich würde nur interessieren ob so was geht und ob es schwierig ist, sowas einzubinden.

 

Hallo,

hier die Unterschiede zwischen Deiner und meiner Formel:

Arbeitsblatt mit dem Namen 'Tabelle1'
	C	D	E
21		FALSCH	WAHR
22	4	WAHR	FALSCH
23		0	1
24	4	1	0

Zelle	Formel
D21	=C21<>""
E21	=WENN(C21="";WAHR;FALSCH)
D22	=C22<>""
E22	=WENN(C22="";WAHR;FALSCH)
D23	=(C23<>"")*1
E23	=WENN(C23="";WAHR;FALSCH)*1
D24	=(C24<>"")*1
E24	=WENN(C24="";WAHR;FALSCH)*1

<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]

Du siehst, auf WENN kann man verzichten.

Irgendwie habe ich wohl in J die Formeln nicht geändert, ist aber nicht dramatisch, da die vorhandene ja funktioniert.

Gibt es. Dazu gibt es in unterschiedlichen Foren auch schon Lösungen. Oder hier: http://www.excelformeln.de/formeln.html?welcher=9

Gruß

Edgar

 

Hallo Edgar,

vielen Dank für Deine Hilfe. Ich glaube nun zu wissen warum ich mit Deiner Formel in meiner Tabelle nicht ganz klar gekommen bin. Hatte Deine Formeln in einer leeren Tabelle ausprobiert und wie du gesagt hast funktionieren sie.

Ich stellte dann fest der Fehler mit dem #Wert bei mir auftaucht wenn in C kein Wert eingegeben ist da ich in D und E mit einem SVERWEIS arbeite. Wenn ich die Formel dort lösche erscheint der Fehler nicht mehr.

Ich gebe zu das ich es weder verstehe warum dem so ist noch wie man es korrigieren könnte.

Für Deine Mühe, Hilfe und Geduld Danke ich Dir sehr.

Ich hatte bezgl. der Probleme in einem anderen Forum ebenfalls nochmals die Frage gestellt und soweit ich das bisher gesehen habe scheint eine der Lösungen zu funktionieren.

http://www.office-loesung.de/ftopic451757_0_0_asc.php#1885867

Was nicht bedeuten soll das Deine Lösung schlechter ist oder nicht funktioniert. Nur mit meinen bescheidenen Excel Kenntnissen kann ich die eher nachvollziehen, was mir das arbeiten damit etwas leichter fällt. Da ich aber auch dazu lernen will werde ich mich auch noch mal in ruhe mit Deiner Lösung beschäftigen, da sie eindeutig übersichtlicher ist von der Formel länge und sich dadurch ja auch Fehler vermeiden lassen.

Also nochmals Danke und eine schöne Woche.

Gruß
Korni

 

Hallo Korni,

das Problem liegt in der SVERWEIS-Formel und ist ganz einfach zu lösen:

Code:

=WENN(C8="";0;SVERWEIS(C8;Dienstzeiten!B:P;WOCHENTAG(A8;2)*2;0))
=WENN(C8="";0;SVERWEIS(C8;Dienstzeiten!B:P;WOCHENTAG(A8;2)*2+1;0))

Wenn das Ergebnis "" ist, können weitere Formeln nur #Wert liefern.

Gruß

Edgar