PDA

Vollständige Version anzeigen : EXEL 2011 Nachtschicht ausrechnen


Alex EXEL
04.07.2014, 09:47
Hallo, kann mir bitte einer Helfen, um die stunden in der Nacht ausrechnen.
Also, ich möchte die Stunden in der nacht ausrechnen zwischen 22:00 - 06:00 Uhr.

Wenn z.B. um 21:00 Uhr Anfang und um 06:00 Uhr ist Ende, dann soll er mir die 8 Stunden ausrechnen.
Wenn z.B. um 14:00 Uhr Anfang und um 23:00 Uhr ist Ende, dann nur 01:00 Stunde.
Wenn z.B. um 22:00 Uhr Anfang und um 05:00 Uhr ist Ende, dann 07:00 Stunden.
Wenn z.B. um 05:30 Uhr Anfang und um 14:00 Uhr ist Ende, dann 00:30 Stunden

Danke voraus.

EarlFred
04.07.2014, 09:51
Hallo voraus,

so?
[C1:]=WENN(A1>B1;REST(MIN(1/4;B1)-MAX(22/24;A1);1);MAX(0;B1-22/24)+MAX(0;1/4-A1))
Beginn steht in A1, Ende in B1.

Grüße
EarlFred

Alex EXEL
04.07.2014, 11:05
Vielen Dank, der rechnet nicht dass was er rechnen soll.
Ich füge den Datei im Anhang ein, in dieses Datei habe ich verschiedne Zeiten und die Formel soll mir in die Zelle ( AM Na ) die Ergebnisse eintragen. Die Ergebnisse was er mir ausrechnen soll, habe ich als Beispiel in die Zelle ( AM Na ) von Hand eingetragen.

Danke

Grüße Alex

EarlFred
04.07.2014, 12:16
Hallo Alex,

der rechnet nicht dass was er rechnen soll.
Wenn die Ergebnisse einer Formel nicht stimmen, beschreibe bitte auch, welches (falsche) Ergebnis die Formel liefert und wie Du sie eingetragen hast.

Die Formel ermittelt die Stundenanzahl als Uhrzeit, wie ich der überwiegenden Darstellung in Deiner Frage entnommen habe:
Wenn z.B. um 22:00 Uhr Anfang und um 05:00 Uhr ist Ende, dann 07:00 Stunden.
Formatiere die Ausgabezelle als Uhrzeit und Du wirst es erkennen.

Willst Du die Stunden als Dezimalzahl, musst Du nur mit 24 multiplizieren.

Um abzufangen, dass Du auch leere Zellen prüfen willst, habe ich noch eine Prüfung vorangestellt:
=(ANZAHL(E4:F4)=2)*WENN(E4>F4;REST(MIN(1/4;F4)-MAX(22/24;E4);1);MAX(0;F4-22/24)+MAX(0;1/4-E4))*24

Grüße
EarlFred

Alex EXEL
04.07.2014, 12:37
Vielen Vielen Dank, es klappt.

Wenn es für Sie nicht schwer ist, können Sie mir Bitte erklärung von dieses Formel aufschreiben?

Ich versuche es zu verstehen, aber es klappt nicht.

Danke Voraus!

Grüß Alex.

EarlFred
04.07.2014, 13:02
Hallo,

kein Problem:

{A}(ANZAHL(E4:F4)=2)
Prüft, ob beide Zellen eine Zahl zum Inhalt haben - nur dann rechnet die Formel richtig. Dieser Teil hat entweder WAHR oder FALSCH zum Ergebnis


{B}*WENN(E4>F4;
REST(MIN(1/4;F4)-MAX(22/24;E4);1);
MAX(0;F4-22/24)+MAX(0;1/4-E4))
prüft zuerst, ob die Anfangsuhrzeit größer als die Enduhrzeit ist. Dies ist der Fall, wenn Du über Mitternacht hinaus arbeitest.

[Einschub Hintergrundwissen:
1 Tag = 24 Stunden; 1 Stunde = 1/24 Tag
Also: 06:00 Uhr = 6 von 24 Stunden = 6/24 = 1/4]

Wenn dies zutrifft:
Teil 1: MIN(1/4;F4)
Nimm das Minimum aus 06:00 Uhr (1/4) und F4, also entweder das Ende der Nachtarbeitszeit oder den Wert aus F4.
Beispiel: F4 ist 05:00 Uhr, also liegt das Ende der Arbeitszeit voll in der Nachtarbeitszeit. Dann ist dies die Endzeit für den zu berechnenden Zeitraum in der Nachtarbeit.
Ist F4 08:00 Uhr, dann gehören 2 Stunden nicht zur Nachtarbeit, also ist 1/4 maßgeblich.
Teil 2: MAX(22/24;E4)
...und ziehe von der Endzeit die Anfangszeit ab, also das Maximum aus 22 Uhr und E4. Die Logik ist sinngemäß zu der des Teils 1, braucht also sicher nicht erläutert zu werden.
REST(Teil1-Teil2;1)
Dieser Teil ermittelt die Stunden zwischen Anfangs- und Endzeit.

Wenn dies nicht zutrifft (Startzeit ist < Endzeit, z. B. 08:00 Uhr bis 14:00 Uhr):
MAX(0;F4-22/24)+MAX(0;1/4-E4))*24
Teil 1: MAX(0;F4-22/24)
Ziehe von der Endzeit 22:00 ab. Im Fall, dass Du z. B. bis 16:00 Uhr gearbeitet hast, ist diese Differenz negativ. Dann berechne also hierfür 0 Nachtstunden.
Teil 2: MAX(0;1/4-E4)
genau der andere Fall: Ziehe von 06:00 Uhr die Anfangszeit ab. Ist das Ergebnis negativ (weil Du um 08:00 angefangen hast), fallen auch hier 0 Nachtstunden an.
Das Ergebnis aus Teil1+Teil2 sind die Nachtstunden.

Teil {B} liefert also für beide Fälle die Nachtstunden (als Uhrzeit)

{A}*{B} ergibt:
Ergebnis der Prüfung (WAHR oder FALSCH)* Stunden: Bei Wahr = 1*Stunden, bei Falsch = 0*Stunden, jeweils als Uhrzeit.

Das mal 24 ergibt die Zeit als Dezimalzeit - siehe Einschub "Hintergrundwisse" - nur anders herum gerechnet.

Alles klar?

Grüße
EarlFred

Josef B
04.07.2014, 13:21
Hallo EarlFred

Aber alle Bedingungen hast du mit deiner Formel noch nicht berücksichtigt.
Zum Beispiel:
Beginn 00:20 Ende 05:40
Ergebnis bei deiner Formel: 05:40

Gruss Sepp

EarlFred
04.07.2014, 13:22
@Sepp
erwischt! ;)

Und also: Dein Vorschlag zur Verbesserung?

Grüße
EarlFred

Josef B
04.07.2014, 13:47
Hallo Hallo EarlFred

Ich lasse dich noch ein wenig knobeln. Habe im Freien, bevor der Regen kommt noch Arbeiten zu erledigen. :)

Gruss Sepp

EarlFred
04.07.2014, 13:56
Hallo Sepp,

das sind doch alles faule Ausreden!
Ich habe 2/3 der Arbeit gemacht - nun tu doch auch mal was ;)

Grüße
EarlFred

hary
04.07.2014, 14:03
Moin
Und ich bleibe bei: www.excelformeln.de/formeln.html?welcher=9
ist zwar 23 Zeichen laenger, laesst sich aber besser einpflegen.
<br/><br/>
<b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:97px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">22:00</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">06:00</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">00:20</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">05:40</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">5:20</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td><td>R1C1 für Add In</td></tr><tr><td>A3</td><td>=MAX(;MIN(B1+(A1&gt;B1);B2+(A2&gt;B2))-MAX(A1;A2))+MAX(;(MIN(B1;B2+(A2&gt;B2))-A2)*(A1&gt;B1 ))+MAX(;MIN(B1+(A1&gt;B1);B2+0)-A1)*(A2&gt;B2)</td><td>&nbsp;</td><td>&nbsp;=MAX(,MIN(R[-2]C[1]+(R[-2]C&gt;R[-2]C[1]),R[-1]C[1]+(R[-1]C&gt;R[-1]C[1]))-MAX(R[-2]C ,R[-1]C))+MAX(,(MIN(R[-2]C[1],R[-1]C[1]+(R[-1]C&gt;R[-1]C[1]))-R[-1]C)*(R[-2]C&gt;R[-2 ]C[1]))+MAX(,MIN(R[-2]C[1]+(R[-2]C&gt;R[-2]C[1]),R[-1]C[1]+0)-R[-2]C)*(R[-1]C&gt;R[-1] C[1])</td></tr></table><br/><a href='http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip' >http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip</a><br/><a href='http://Hajo-Excel.de/tools.htm' >http://hajo-excel.de/tools.htm</a><br/>XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007<br/>Add-In-Version 14.02 einschl 64 Bit<br/>
gruss hary

EarlFred
04.07.2014, 14:08
@hary,
pfffft! Selberdenken macht schlau ;)

Tja, das hat man davon, wenn man nicht vorher auf Excelformeln.de schaut... Und das mir (der da sonst immer zuerst nachschaut und auch noch im Hinterkopf hatte, dass es dort eine Formel dafür gibt...)!

Aber hary: DIE Formel erklärst Du dann bitte auch! :D

Schönes Wochenende!

Grüße
EarlFred

hary
04.07.2014, 14:13
Moin
Heiss ich Erklaerbaer? ;-))
Nimm die F9 Taste. LOL
sonnigen gruss hary

EarlFred
04.07.2014, 14:27
Hallo zusammen,

letzter Versuch:
=(ANZAHL(E4:F4)=2)*WENN(E4>F4;REST(MIN(1/4;F4)-MAX(22/24;E4);1);MAX(0;F4-22/24)+MAX(0;1/4-E4)-MAX(0;E4-22/24)-MAX(1/4-F4;0))

Oder wenn in A1 die Anfangszeit der Nachtschicht, in B1 die Endzeit steht:
=(ANZAHL(E4:F4)=2)*WENN(E4>F4;REST(MIN(B1;F4)-MAX(A1;E4);1);MAX(0;F4-A1)+MAX(0;B1-E4)-MAX(0;E4-A1)-MAX(B1-F4;0))

Hoffe, die rechnet jetzt richtig.

Grüße
EarlFred

Josef B
04.07.2014, 21:27
Hallo EarlFred

Du liegst zweimal falsch erstens ist die Arbeiten draussen keine faule Ausreden, und zweitens hilft mir deine 2/3 Arbeit überhaupt nichts, ich musste mich nämlich ganz alleine durch mein Formel–Archiv wühlen. :grins:

Deine neue Formel rechnet in den üblichen Fällen richtig, Probleme hat sie nur noch mit extremen Arbeitszeiten.
Z.B. 23:30 bis 23:00

Hier nun meine Lösung:

=(ANZAHL(E4:F4)=2)*(MAX(;1/4-E4)-MAX(;1/4-F4)-MAX(;E4-22/24)+MAX(;F4-22/24)+(E4>F4)*1/3)

Gruss Sepp

Alex EXEL
05.07.2014, 08:32
Vielen Dank.

MfG. Alex

Alex EXEL
05.07.2014, 13:40
Code:

=(ANZAHL(E4:F4)=2)*(MAX(;1/4-E4)-MAX(;1/4-F4)-MAX(;E4-22/24)+MAX(;F4-22/24)+(E4>F4)*1/3)
Gruss Sepp


Kann man für die Bruchzahlen oder für die ganze MAX Funktion was anderes anwenden?

Danke.

Josef B
05.07.2014, 14:36
Hallo Alex

Kann man für die Bruchzahlen oder für die ganze MAX Funktion was anderes anwenden?
Man kann da sicher noch einiges anpassen. Aber du musst da schon etwas genauer beschreiben, was du möchtest, und warum du die Funktion MAX vermeiden möchtest. (es sind übrigens 4 Max Funktionen in meiner Formel.

Gruss Sepp

Alex EXEL
05.07.2014, 15:16
Die Formel funktioniert gut, nur ich versuche die Formel zu verstehen, klappt aber nich so gut. Deswegen frage ich ob die andere Möglichkeit gibst. Wenn das aber für Sie schwer ist, dann brauchen Sie nicht schreiben. Danke.

EarlFred
09.07.2014, 09:20
@Sepp,

ich ziehe meinen Hut! Womit wieder einmal bewiesen wäre, dass Arbeit an der frischen Luft den Gehirnzellen gut tut (im Gegensatz zur miefigen Büroluft). ;)

1a-Spitzenformel mit Gütezertifikat!

Grüße
EarlFred

Josef B
09.07.2014, 12:56
Hallo EarlFred

Danke für deine netten Worte.
Frische Luft soll ja gesund sein, vor allem wenn diese vom Regen gewaschene ist. :)

Gruss Sepp

Alex EXEL
10.07.2014, 13:52
Hallo Leute. Danke für Ihre Hilfe !!! Ich habe mein Gehirn schon gelüftet, das hilft aber nicht viel.

Alex EXEL
14.07.2014, 14:29
Danke für eure Hilfe noch mal, vielleicht könnt ihr mir das gleiche Formel für zeit von 14:00 - 22:00 Uhr erstellen.

Danke MfG.