PDA

Vollständige Version anzeigen : Zeitberechnung - Formel gesucht


Kneti
10.07.2001, 13:40
Hallochen !

Ich bin auf der Suche nach einer Formel für die Berechnung von Nachtstunden.
Ich gebe unter B2 z.B. die Anfangszeit 18:00 ein. Unter C2 in der nächsten Spalte trage ich immer die Endzeit ein. Z.B. 3:00.

Nun möchte ich mit den beiden Uhrzeiteingaben in einer neuen Spalte die Nachtstunden anzeigen lassen. Also nur die Zeit die zwischen 22:00 und 4:00 angefallen ist. Die Nachtstunden können also nie höher als 6 Std. sein...
Es kann aber auch sein das von 4-12:00 gearbeitet wird und dann darf natürlich keine Nachtstunde angezeigt werden.

Ich bin kurz vorm Haare raufen nach dieser Formel und hoffe das ihr ein Antwort habt!

Den Beiträgen hier in diesem Forum nach zu urteilen, wird das sicher klappen, ich verstehe ja vieles bei den Antworten nicht mal. Und ich dachte ich kann mit Excel ganz gut umgehen - doch was man mit Excel so noch alles anstellen kann wovon ich nichts weiß...*seufs"
Gruß Kneti

MarioR
10.07.2001, 16:33
Hallo Kneti,

wenn ich das Problem richtig verstanden habe, müsste Folgendes gehen:
=WENN(B2>C2;GANZZAHL(24-24*(B2-C2));0)

Damit erhälst Du die Stundenzahl zwischen den Zeitpunkten, Minuten werden nicht berechnet.

Mario

Kneti
10.07.2001, 16:59
Hallo Mario,

vielen Dank für die Bemühungen, aber leider brauche ich auch die minuten !!!

Weil die Arbeitszeiten oft so sind:
Anfang 17:23
Ende 01:07

Wie Du siehst unbedingt notwendig - hast Du da auch noch einen Tip?
Danke schon mal
Kneti

jinx
10.07.2001, 17:02
Moin

@ Mario: Bei einer Anfangszeit von 18 und einer Endzeit von 3 weist Excel2000 nach Deiner Formel -336 aus!?!?!? Ersetzt man den Multiplikator durch Minus, erscheinen 15; gemäß Vorgabe von Kneti sollten dann aber 5 ausgewiesen werden.

@ Kneti: bau Dir durch diverse WENN-DANN sowie UND bzw. ODER Abfragen das System auf (bin zur Zeit bei sieben Schachtelungen, dafür stimmt Ergebnis aber); bis eben jedenfalls, als Du mit den Minuten rübergekommen bist...

Ich hoffe aber, mir fällt noch eine etwas elegantere Lösung als dieser nicht miteinander verknüpfbare Moloch ein...

und .. gone with the wind
jinx

Kneti
10.07.2001, 17:27
@ jinx
vielen Dank - bin schon ganz gespannt - wie ein Flitzebogen eine neue Formel auszuprobieren. Ich hatte es auch schon mal mit zwei Spalten probiert die im Endeffekt dann zusammen gerechnet werden. Klappte auch nur wenn ich dann einen Frühdienst ohne Nachstunden hatte bekam ich wieder Fehlermeldungen...*haarerauf*
Gruß Kneti

MarioR
10.07.2001, 18:50
Hallo nochmal

@Jinx
Das ist natürlich richtig, wenn Du die Zeiten als Zahlen (also 18 und 3) eingibst.
Das richtige Ergebnis erhälst Du, wenn Du 18:00 bzw. 3:00 in die Zellen schreibst ;)

@Kneti
Ich hab nochmal rumprobiert - das ist mein Ergebnis:
=WENN(B2>C2;1-(B2-C2);0)
Und die Zelle muß formatiert werden. Es gibt da Standardformate unter Uhrzeit. Oder Du erstellst ein benutzerdefiniertes Format z.B. [h]:mm.

(Alles unter Excel 97 probiert)

<font size="1" face="Century Gothic">Moderatorenanmerkung: die Überarbeitung dieses Beitrages ist im Zuge der Arbeiten zu sehen, die durch den Wechsel der Forensoftware zum 01.01.2003 verursacht wurden.

Es wurden in diesem Beitrag Links korrigiert, die auf falsche Adressen zeigten...</font>

Kneti
10.07.2001, 19:24
hallo mario,

leider bekomme ich mit meinem Excel 2000 nur folgende Ausgabe: #wert

Ich verstehe die Formel sowieso nicht.
also ich habe mal 17:00 bis 2:00 eingegeben
nach deiner Formel heißt das:
=WENN(B2>C2;1-(B2-C2);0)
wenn 17:00 größer ist als 2:00 - was wahr ist dann soll 1-(17-2)sonst 0
Du kannst glaube ich nicht über Mitternacht rückwärtig abziehen. wie willst Du 1-15=-14 darstellen...welche Rechengrundlage hast Du da? Ich würde es gern auch begreifen will ich damit nur sagen !
Na jedenfalls klappt es leider nicht !

MarioR
10.07.2001, 19:49
Hallo Kneti,

wie gesagt, ich kann mich leider nur auf Excel 97 beziehen. Ich glaub aber nicht, das MS da grundlegende Sachen geändert hat (wobei, man weiss ja nie).

Also zur Erklärung:
Excel stellt Uhrzeiten intern als Bruchteil eines Tages dar. Das heisst
0:00 Uhr => 0
12:00 Uhr => 0,5
24:00 Uhr => 1
17:00 Uhr => 0,7083..
2:00 Uhr => 0,0833..
Excel berechnet also 1-(0,7083-0,0833)=0,375!
In Stunden umgerechnet 0,375*24=9.

Überprüfe bitte mal die Formatierung von B2 und C2. Die müssen als Uhrzeit formatiert sein, nicht als Text.

Mario

Morli
10.07.2001, 19:52
Hallo Kneti,

versuchs mal mit der folgenden Formel:

=WENN(B2>0,916667;1-B2;0,083334)+WENN(C2>0,166667;0,166667;C2)

Die Zelle in der du die Formel einträgst muß als Uhrzeit formatiert sein.

Gruß

Rainer ;)

<font size="1" face="Century Gothic">Moderatorenanmerkung: die Überarbeitung dieses Beitrages ist im Zuge der Arbeiten zu sehen, die durch den Wechsel der Forensoftware zum 01.01.2003 verursacht wurden.

Es wurden in diesem Beitrag Links korrigiert, die auf falsche Adressen zeigten...</font>

jinx
10.07.2001, 20:00
Moin:

Home from the storm!

@ Mario: In Deiner ersten Formel ist es - glaube ich - ziemlich egal, wie die Zahlen formatiert sind, da die mittlere Anweisung nach meinen Angaben immer 24 - 24*(18-3) heißt, und Adam rechnet da nun mal -336 aus...

@ kneti: Danke für die Mail - aber da ich selbst immer lautstark propagiere, Antworten übers Forum zu geben, die Antwort hier (auch wenn die Suche im Access-Forum die letzten Tage verschweigt).
Mario hat recht mit der Formatierung der Zellen auf Uhrzeitformate; ich hatte leider gewisse Probleme, meiner Version meine Wünsche deutlich zu machen (ich wollte schließlich Stunden und keine Daten und Zeiten!!).

B2: Anfangszeit
C2: Endzeit
D2: Start Nachtstunden
E2: Ende Nachtstunden
F2: =WENN(C2>E2;E2+1;C2+1)-WENN(B2(größer)D2;D2;B2)
Natürlich muss oben statt (größer) stehen: >
Aber das unterband das Forum ganz elegant, bitte entsprechend ersetzen.

Weist bei mir zur Zeit die richtigen Werte aus, aber lasse mich gerne eines Besseren belehren.

cu
jinx

Irgendwie will mich das System im Regen stehen lassen und die Formel nicht komplett übertragen....... :o
Na ja, im 4. Anlauf klappt´s dann ja schon ;)

<font size="1" face="Century Gothic">Moderatorenanmerkung: die Überarbeitung dieses Beitrages ist im Zuge der Arbeiten zu sehen, die durch den Wechsel der Forensoftware zum 01.01.2003 verursacht wurden.

Es wurden in diesem Beitrag Links korrigiert, die auf falsche Adressen zeigten...</font>

Kneti
10.07.2001, 20:36
@Morli
Deine Formel funktioniert leider nur halb
wenn ich 17:00 bis 3:00 eintrage zeigt es die gewünschten Nachtstunden von 5h an.
Wenn ich aber von 3-12 Uhr angebe, müsste ich eine Nachtstunde bekommen bekomme aber nur 0:00...

@jinx
Deine Formel ist auch nicht schlecht,
vom Aussehen, aber leider wieder nichts.
Ich bekomme die doppelte Menge Nachtstunden raus. Folgendes gemacht:
B2: Anfangszeit 17:00 eingetragen
C2: Endzeit 03:00 eingetragen
(Format Uhrzeit und [hh:mm] und hh:mm alles ausprobiert...)
D2: Start Nachtstunden 20:00 eingetragen
E2: Ende Nachtstunden 6:00 eingetragen
F2: =WENN(C2>E2;E2+1;C2+1)-WENN(B2(größer)D2;D2;B2)
F2: Anzeige 10 Stunden, richtig wäre da ich veränderte Nachtzeiten genommen (20 + 6) aber 20-3Uhr = 7 Nachtstunden...
bei den alten Nachtzeiten 22-4 Uhr kam auch 10 raus wären aber 22-3 = 5 Nachtstunden...

Ich bekomme schon ein richtig schlechtes Gewissen, aber trotzdem hoffe ich doch ihr gebt nicht auf !
Gruß René alias Kneti

Kneti
10.07.2001, 20:45
Kneti geht mit Frauchen was trinken, während ihr so nett seid meine Formel auszutüffteln.
Ich schaue morgen vormittag erst wieder rein will ich damit sagen ;-)
Sagt nicht ich wäre Schuld an eurer schlaflosen Nacht - ich hatte schon mehrere wegen dieser Formel.
Bis morgen
Kneti

jinx
10.07.2001, 20:49
<font size="2" face="Century Gothic">Schäm....

muss natürlich heissen kleiner (also Zeichen <)statt wie von mir so angepriesen größer (zähneknirschen bundesweit!!).

Ich gelobe Besserung; normalerweise kenne ich die Bedeutung der Symbole..

:rolleyes:

<font size="1" face="Century Gothic">Moderatorenanmerkung: die Überarbeitung dieses Beitrages ist im Zuge der Arbeiten zu sehen, die durch den Wechsel der Forensoftware zum 01.01.2003 verursacht wurden.

Es wurden in diesem Beitrag Links korrigiert, die auf falsche Adressen zeigten...</font>

MarioR
10.07.2001, 21:04
... ja ja - manchmal fällt der Groschen arg langsam (hör ich da jemanden lachen?) :p

Folgende Formel hab ich noch schnell ausgeschwitzt (Ärmel hochgekrempelt):
=WENN(B2>C2;WENN(B2>D2;1-B2;1-D2)+WENN(C2>E2;E2;C2);WENN(B2>E2;0;E2-B2)+WENN(C2>D2;C2-D2;0))

Angenehme Träume wünsch ich

<font size="1" face="Century Gothic">Moderatorenanmerkung: die Überarbeitung dieses Beitrages ist im Zuge der Arbeiten zu sehen, die durch den Wechsel der Forensoftware zum 01.01.2003 verursacht wurden.

Es wurden in diesem Beitrag Links korrigiert, die auf falsche Adressen zeigten...</font>

Kneti
11.07.2001, 20:55
Hallochen Leute,

leider erst jetzt geschafft reinzuschauen...

@jinx
Guter Versuch mit kleiner, schon waren es richtig 5 h gut gemacht, wenn Du jetzt mal 4:00 bis 12:00 eingibst, erhälst Du 6 Stunden
:-))) das wäre ja super Nachtstunden am Tage... richtig wäre hier die Anzeige entweder 0:00 oder 2:00 ich hatte Ende 6Uhr angegeben also hätte 2h Nacht angezeigt werden müssen. Probiere bitte mal was es bei Dir anzeigt wenn Du 4-12 als Arbeitszeit einträgst und Anfang 20 :00 und Ende 6:00 (des Nachtzeitraums) - nicht das Du die Formel hast und ich nur was falsch formatiert habe...

@Mario Gratulation !!!
Gut geschwitzt, aber nicht genug wollt ich doch eben schreiben, bis ich gemerkt habe das Du jetzt auch noch 2 Spalten extra genommen hast, ES FUNKTUIONIERT auch bei einem Frühdienst!

Also zusammenfassend für alle:
Arbeitszeit in B2 20:00 Anfang
Arbeitszeit in C2 05:00 Ende
Anfang Nachtstunde in D2 22:00
Ende Nachtstunde in E2 4:00
Ergebnis in F2 6:00

In einer Nachtschicht von 20:00 bis 5:00 (9h)sind 6 Nachtstunden enthalten.

Die Formel:
=WENN(B2>C2;WENN(B2>D2;1-B2;1-D2)+WENN(C2>E2;E2;C2);WENN(B2>E2;0;E2-B2)+WENN(C2>D2;C2-D2;0))

Wir und vor allem Kneti dankt
Mario R. für diese Schweißarbeit, doch mein Dank wird auch Dir jinx ewig nachschleichen ;-)
so war dein Denken glaube ich doch auch ein wenig Hilfe für Mario wenn ich an die 2 extra Spalten denke - damit hatte ich es auch versucht nur eben erfolglos.

So ich werde mir die Formel noch mal richtig reinziehen - die geht runter wie Oel :-))

So dann schlaft gut - bis zum nächsten Mal !
Wollt ihr noch mehr solcher Nüsse zum knacken ?
Gruß Kneti


[Dieser Beitrag wurde von Kneti am 12.07.2001 editiert.]

Dietmar
16.01.2002, 13:33
Hallo MarioR,
ich habe die Formel ausprobiert, klappt auch soweit ganz gut, aber es werden nur volle Stunden berechnet.
Ich rechne aber 1/4 Stunden genau ab, und brauche als Ergebnis.
bei Anfang 22:45 Ende 4:00 Ergebnis 5:15
oder
bei Anfang 23:30 Ende 2:45 Ergebnis 3:15

Ich hoffe, Du kannst mir auch hier weiter helfen

Gruß Dietmar

jinx
16.01.2002, 13:53
<font size="2" face="Century Gothic">Moin, Dietmar,

nein - ich bin nicht MarioR...
Daher auch meine Bitte - natürlich kannst Du auch eine Person direkt in diesem Forum ansprechen - aber es gibt genug Leute, die Dir helfen könnten...

In diesem Beitrag ging es um die Berechnung von Nachtarbeitstunden innerhalb eines Arbeitszyklusses.

Nehmen wir folgendes an:
Arbeitsbeginn in A1
Arbeitsende B1
Formel in C1: =WENN(A1>B1;(1+B1)-A1;A1-B1)
dann sollte bei Beginn 21:45 und Ende 03:30 zuerst 0,23958333 und nach Formatierung als benutzerdefiniertes Format hh:mm 05:45 in C1 stehen...bei Deinem Beispiel steht dann dort: bei Anfang 23:30 Ende 02:45 Ergebnis 03:15</font>

Dietmar
16.01.2002, 16:54
Hallo,
ich habe ein neues Problem.
Die Formel funktioniert nur wenn z.B. der
Arbeitsbeginn um 20:30 ist, das Ende um 3:00
Wenn aber der Arbeitsbeginn z.B. um 1:30 ist und das Ende um 9:00 versagt die Formel.

Ich hoffe ihr könnt mir helfen

jinx
16.01.2002, 17:04
<font size="2" face="Century Gothic">Moin, Dietmar,

dann versuchen wir es mal mit folgendem:
A1 wieder Anfang
B1 wieder Ende
C1 =WENN(A1>B1;(1+B1)-A1;WENN(B1>A1;B1-A1;A1-B1))

Sollten die Arbeitszeiten über 23:59 hinausgehen, muß zusätzlich noch das Datum abgefragt werden...
</font>

[ 16. Januar 2002: Beitrag editiert von: jinx ]</p>

Clemmi
16.01.2002, 19:23
Hey Leute,

zu dem Thema Rechnen mit Uhrzeiten habe ich einen tollen <a href="http://homepages.compuserve.de/klausdioppermann/UhrDatum/induhrdat.html">Internetauftritt</a> gefunden. Sicher findet da auch Kneti eine Lösung.

tschüss Clemmi

MarioR
16.01.2002, 20:21
Hallo Dietmar,

also eigentlich sollte die Formel auf die Sekunde genau rechnen. Deine Viertelstunde dürfte deshalb kein Problem sein.

Formatierung der Zellen als Uhrzeit (h:mm) ist i.O., oder?

Dietmar
17.01.2002, 19:14
Grüße Euch,
mit dieser leicht veränderten Formel komme ich schon ganz gut klar. Hat aber noch einen Schönheitsfehler.
Bei mir gehen die Nachtstd. von 22:00 bis 5:00
Wenn die eigentlich nicht vorkommende Arbeitszeit z.B. von 1:00 bis 3:00 geht, errechnet die Formel nicht 2 Std. sondern 4 Std. (5:00 - 1:00)
Da ich gerne alles perfekt haben möchte kann mir vieleicht jemand weiter helfen.
Hier meine Zellen und die Formel:
R8=Arbeitsbeginn
S8=Arbeitsende
T8=Nachtstd.-Beginn (fest vorgegeben)
U8=Nachtstd.-Ende (fest vorgegeben)

=WENN(R8größerS8;WENN(R8größerT8;1-R8;1-T8)+WENN(S8größerU8;U8;S8);WENN(S8größerT8;S8-T8;0)+WENN(R8kleinerU8;WENN(R8="";0;U8-R8);0))

MarioR
17.01.2002, 19:56
Hallo Dietmar,

ist zwar noch nicht ganz 100%ig, aber für Deine Zwecke müsste es reichen:

=WENN(R8>S8;WENN(R8>T8;1-R8;1-T8)+WENN(S8>U8;U8;S8);WENN(S8>T8;S8-T8;WENN(S8&ltU8;S8-U8;0))+WENN(R8&ltU8;WENN(R8="";0;U8-R8);WENN(R8>T8;T8-R8;0)))

[ 17. Januar 2002: Beitrag editiert von: MarioR ]</p>

Dietmar
17.01.2002, 20:13
Danke für den Tipp,
aber!!
Wenn bei der Arbeitszeit keine Eingabe (Frei)
dann #####
wenn dort noch 00:00 steht bin ich endlich zufrieden.

MarioR
17.01.2002, 20:48
Da nehm ich mal die grobe Kelle: :p

=WENN(ODER(R8="";S8="");0;WENN(R8>S8;WENN(R8>T8;1-R8;1-T8)+WENN(S8>U8;U8;S8);WENN(S8>T8;S8-T8;WENN(S8&ltU8;S8-U8;0))+WENN(R8&ltU8;WENN(R8="";0;U8-R8);WENN(R8>T8;T8-R8;0))))

Dietmar
17.01.2002, 22:21
Hurra,
ich bin zufrieden (fehlerlos)

Danke für deine Mühe

jinx
17.01.2002, 23:29
<font size="2" face="Century Gothic">Moin,

und quasi als Abschluß: früher gab es in solchen Fällen dann auch ab und an mal ein virtuelles Getränk - also Mario: auf Dein Spezielles!!

Leider sind mir durch Eigenkonsum die passenden Grafiken ausgegangen ...</font>