Arbeitsstunden berechnen (Index/Wochentag)

[mrthiemann]

Hallo Community,

ich stehe vor dem Problem, dass ich Arbeitszeiten für unterschiedliche Tage errechnen lassen muss.

"Eigentlich" keine große Sache.

Aber ich habe unterschiedliche Arbeitszeiten....

Montag - Freitag // 7:00 - 19:00 Uhr

Samstag + Sonntag // 7:00 - 14:00 Uhr

Hierfür habe ich mir eine intelligente Tabelle angelegt (Wochentage)


---------------------------------------------------------------------------------------------------------------
Jetzt ist es so, dass wenn Montags bis Freitags vor oder nach der regulären Arbeitszeit gearbeitet wird, dass dann eine Überarbeit stattfindet.
Wenn G223 < Montag 7:00 Uhr ; dann zeit berechnen [Spalte N]
Wenn H223 > Montag 19:00 Uhr; dann Überstunden berechnen [Spalte N]

Samstags gibt es einen Zuschlag von 20 % [Spalte L]
Wenn dann noch vor oder nach der regulären Zeit gearbeitet wird, findet zusätzlich eine Überarbeit statt. [Spalte N]

Sonntags gibt es einen Zeitzuschlag von 25% [Spalte M]
Wenn dann noch vor oder nach der regulären Zeit gearbeitet wird, findet zusätzlich eine Überarbeit statt. [Spalte M]


Ich bekomme es wohl hin, ein paar verschiedene Abfragen zu berechnen....aber beim besten willen nicht alles.


Ergo benötige ich eure Hilfe.
VBA darf in diesem Fall nicht verwendet werden.

edit: mittlerweile habe ich so ein chaos in diesem Abschnitt, dass ich total den überblick verloren habe... :16:
---------------------------------------------------------------------------------------------------------------

AZKMA1 (1)

A

B

C

D

E

F

G

H

I

J

K

L

M

N

214

Datum

Wochentag

Feiertag

Grund

Dienstzeit Verschiebung

geleistete Stunden

Pause

Hilfsspalte für Überarbeit

Zeitzuschlag:

Zeitzuschlag:

Zeitzuschlag:

215

Samstag

Sonntag

Überarbeit

216

0,2

0,25

0,25

217

1,25

218

April 2017

von

bis

von

bis

von

bis

ausserhalb Rahmenarbeitszeit

219

01

Samstag

0:00

0:00

0:00

0:00

220

0:00

0:00

0:00

0:00

0:00

221

02

Sonntag

0:00

0:00

0:00

0:00

222

0:00

0:00

0:00

0:00

0:00

223

03

Montag

0:00

0:00

0:00

0:00

224

0:00

0:00

0:00

0:00

0:00

verbundene Zellen

A214:A217

B214:B217

C214:C217

D214: D217

E214:F217

G214:H217

I214:J217

K214:K217

A219:A220

B219:B220

C219:C220

D219: D220

G220:H220

I220:J220

A221:A222

B221:B222

C221:C222

D221: D222

G222:H222

I222:J222

A223:A224

B223:B224

C223:C224

D223: D224

G224:H224

I224:J224

verwendete Formeln
Zelle	Formel	Bereich	N/A
L216:N216	=SVERWEIS(L215;'Blatt 1'!$A$96:$B$99;2)
N217	='Blatt 1'!$B$99
A219	=A218
A221,A223	=A219+1
B219,B221,B223	=A219
C219,C221,C223	=WENN(ISTFEHLER(SVERWEIS(B219;Feiertage2;2;FALSCH));"";(SVERWEIS(B219;Feiertage2;2;FALSCH)))
K219,K221,K223	=SUMME(WENN(ISTLEER(G219);"0";(WENN(G219<INDEX(Wochentage;WOCHENTAG(B219;2);2);INDEX(Wochentage;WOCHENTAG(B219;2);2)-G219;"0")));WENN(ISTLEER(H219);"0";(WENN(H219>INDEX(Wochentage;WOCHENTAG($B219;2);3);$G220;"0"))))
L219,L221,L223	=RUNDEN(WENN(ODER(ISTLEER($H219);$H219<INDEX(Wochentage;WOCHENTAG(B219;2);3));"0:00";WENN(WOCHENTAG($B219;2)=6;$K219;0))*24;0)/24
M219,M221,M223	=RUNDEN(WENN(ODER(ISTLEER($H219);WOCHENTAG($B219;2)=7);G220)*24;0)/24
N219,N221,N223	=AUFRUNDEN(WENN(ODER($G219=0;$H219=0;);0;(WENN(ISTZAHL($K220);$K220*24;(WENN(WOCHENTAG($B219;2)=6;$K219*24;(WENN(WOCHENTAG($B219;2)=7;$G220*24;(WENN($G219<INDEX(Wochentage;WOCHENTAG($B219;2);2);$K219*24;WENN($H219>INDEX(Wochentage;WOCHENTAG($B219;2);3);$K219*24))))))))));0)/24
G220,I220,G222,I222,G224,I224	=REST(H219-G219;1)
K220,K222,K224	=WENN(ISTFEHLER(SVERWEIS(A219;Feiertage2;2;FALSCH));"";G220)
L220,L222,L224	=(((L219*$L$6)*24)/24)
M220,M222,M224	=(((M219*$M$6)*24)/24)
N220,N222,N224	=(WENN(ISTZAHL(K220);(((N219*$N$7)*24)/24);((N219*$N$6)*24)/24))

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 25.09 einschl. 64 Bit

[radagast]

heje excelfreunde,

meine persönliche meinung: lieber mrthiemann, ohne dir zu nahe treten zu wollen, so etwas unausgegorenen habe ich noch nicht gesehen.

vorschlag: vorhaben/projekt handisch skizzieren ein Lösungsweg aufbauen und dann das Problem auf mehrere tabellenblätter verteilen. anschließend suksessiv vom gewünschten ergebnisformular oder tabellenkonstrukt aus die berechnungsmethodik aufbauen...
in überschaubaren lösungsschritten/lösungstabellen...

[mrthiemann]

Kritik ist immer gut =) 

Excel ist für mich nur ein Hobby. Learning by doing ist hier meine Devise. 
Ohne auf seine Fehler aufmerksam gemacht zu werden, baut man darauf natürlich auch auf. 

Das Projekt existiert bereits. Hier

[mrthiemann]

Moin Moin =)

irgendwie hab ich eine absolute blockade... ich bitte um Hilfe in Spalte N.

Die Tabelle funktioniert soweit, nur die Berechnung mit dem Samstag gelingt mir nicht.

Wenn ich z.B. als Arbeitszeit 10:00 - 12:00 eingebe, dann wird mir als Wert -01:00 ausgegeben.
Wenn ich den Teil in der Formel änder, damit dort das richtige Ergebnis ausgegeben wird, haut es an einer anderen Stelle nicht mehr hin.

Ich hab schon mein bestes gegeben die Formel auseinander zu nehmen, aber irgendwie naja...



AZKMA1 (1)

	G	H	I	J	L	M	N
21	10:00	12:00			0:00	0:00	-2:00
22	2:00		0:00		0:00	0:00	-1:15

verbundene Zellen

G22:H22

I22:J22

verwendete Formeln
Zelle	Formel	Bereich	N/A
L21	=RUNDEN(WENN(ODER(ISTLEER($H21);$H21<INDEX(Wochentage;WOCHENTAG(B21;2);3));"0:00";WENN(WOCHENTAG($B21;2)=6;$G22;0))*24;0)/24
M21	=RUNDEN(WENN(ODER(ISTLEER($H21);WOCHENTAG($B21;2)=7);G22)*24;0)/24
N21	=AUFRUNDEN(WENN(ODER($G21=0;$H21=0);0;WENN(ODER(WOCHENTAG($B21;2)=7;WOCHENTAG($B21;2)=6);WENN(ISTZAHL($K22);$K22;WENN(WOCHENTAG($B21;2)=7;M21;WENN(WOCHENTAG($B21;2)=6;$H21-INDEX(Wochentage;WOCHENTAG($B21;2);3))));WENN(UND($G21<INDEX(Wochentage;WOCHENTAG($B21;2);2);$H21<INDEX(Wochentage;WOCHENTAG($B21;2);2));$G22;WENN(UND($H21>INDEX(Wochentage;WOCHENTAG($B21;2);3);$G21>INDEX(Wochentage;WOCHENTAG($B21;2);3));$G22;WENN(UND($H21<INDEX(Wochentage;WOCHENTAG($B21;2);3);$G21>INDEX(Wochentage;WOCHENTAG($B21;2);2));$G22;WENN($G21<INDEX(Wochentage;WOCHENTAG($B21;2);2);INDEX(Wochentage;WOCHENTAG($B21;2);2)-$G21;WENN($H21>INDEX(Wochentage;WOCHENTAG($B21;2);3);$H21-INDEX(Wochentage;WOCHENTAG($B21;2);3);0)))))))*24;0)/24
G22	=REST(H21-G21;1)
I22	=J21-I21
L22	=(((L21*$L$6)*24)/24)
M22	=(((M21*$M$6)*24)/24)
N22	=(WENN(ISTZAHL(K22);(((N21*$N$7)*24)/24);((N21*$N$6)*24)/24))

Zahlenformate
Zelle	Format
G21:J21, G22,  I22,   K21:N22	'[h]:mm

Zellen mit Format Standard werden nicht dargestellt

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 25.09 einschl. 64 Bit