EXCEL Formel gesucht. Für bestimmte Uhrzeiten sollen Stunden und Minuten in eine Dezimalzahl stunde umgewandelt werden?
Hallo. Ich bin Berufskraftfahrer und ich schreibe mit Excel täglich meine Stunden auf.
Nun möchte ich, dass Excel alles selbst rechnet, brauche aber eine passende Formel für...
Die Spalte G soll nur in den Zeiten von 20Uhr bis 24Uhr und von 4Uhr bis 6Uhr als Dezimalzahl angezeigt werden, die Spalte H nur in der Zeit zwischen 0uhr und 4Uhr
Wäre nett, wenn mir einer die Formel aufschreiben kann.
Danke.
8 Antworten
Ungeachtet der Vorschläge in den Antworten, hier die Formeln für G9 und H9
F9:
=WENN(C9+E9=0;"";WENN(UND((C9+RUNDEN(D9/60;2))>(E9+RUNDEN(F9/60;2));(E9+RUNDEN(F9/60;2)-4)>0);MIN(2;(E9+RUNDEN(F9/60;2)-4));MAX(0;6-(C9+RUNDEN(D9/60;2))))+WENN((C9+RUNDEN(D9/60;2))>(E9+RUNDEN(F9/60;2));MIN(4;24-(C9+RUNDEN(D9/60;2)));MAX(0;(E9+RUNDEN(F9/60;2)-20))))
H9:
=WENN(C9+E9=0;"";WENN((C9+RUNDEN(D9/60;2))>(E9+RUNDEN(F9/60;2));MIN(4;(E9+RUNDEN(F9/60;2)));0))
Ich habe mal angenommen, dass Du für Einsatzzeiten zwischen 0 und 4Uhr 25% Zuschlag bekommst, wenn der Einsatz nach 24Uhr beginnt.
So, nochmal ich
G16
=(E16+F16/60+24*((E16+F16/60<C16+D16/60))-MAX(MIN(E16+F16/60+24*((E16+F16/60<C16+D16/60))-6;14);0)-MAX(MIN(E16+F16/60+24*((E16+F16/60<C16+D16/60))-30;14);0))-(C16+D16/60-MAX(MIN(C16+D16/60-6;14);0))-H16
H16
=(MIN(E16+F16/60+24*((E16+F16/60<C16+D16/60));4)+MAX(MIN(E16+F16/60+24*((E16+F16/60<C16+D16/60))-24;4);0))-MIN(C16+D16/60;4)
Fehler und Irrtümer vorbehalten
Hallo,
Nehmen wir an, Du schreibst eine Uhrzeit in Zelle A1, die Du vorher auf Uhrzeit, 13:30, formatiert hast (Zelle mit rechter Maustaste anklicken und auf Zellen formatieren gehen).
Nun soll in B1 diese Uhrzeit als 12,50 erscheinen, also als Dezimalbruch.
Du formatierst die Zelle auf Zahl und schreibst folgendes hinein:
=A1*24
Fertig.
Wenn Du viele Uhrzeiten hast, die Du umwandeln mußt, kannst Du die Formatierungen und Formeln entsprechend kopieren.
Einfach mit der linken Maustaste die rechte untere Ecke der Zellen anklicken und mit gedrückter Maustaste so weit hinunterziehen, wie Du es brauchst.
Bei den Uhrzeiten Stunden und Minuten durch einen Doppelpunkt trennen.
Herzliche Grüße,
Willy
Frage:
Welchen Zuschlag bekommst Du für 0 bis 4 Uhr, wenn Du erst um 1 Uhr beginnst?
Was ist die maximal Arbeitszeit?
Generell ist es ein wenig umständlich gelöst, geschickter wäre es, für die Zeiten jeweils EINE Zelle mit Uhrzeitformat zu verwenden, anstatt 2 für Stunden oder Minuten.
Wie auch immer: Meine Lösung benötigt ein paar Hilfszellen (es ginge auch ohne, aber dann wird es extrem unübersichtlich!)
Q16: =C16+D16/60
R16: =WENN(E16<C16;E16+24;E16)+F16/60
S16: =MAX(Q16;20)
T16: =MIN(R16;24)
U16: =MAX(Q16;4)
V16: =MIN(R16;6)
G16: =WENN(T16>S16;T16-S16;0)
H16: =WENN(U16>V16;U16-V16;0)
Ich konnte es jetzt nicht vollständig testen, aber das müsste funktionieren.
Die jeweils letzte Zahl in den Spalten S bis V sind deine Grenzen (20:00;24:00;4:00;6:00).
So, wenn ich mich bei der Umkopiererei nicht vertan habe, sollten die Formeln so aussehen.
G16:
=WENN(MIN(WENN(E16<C16;E16+24;E16)+F16/60;24)>MAX(C16+D16/60;20);MIN(WENN(E16<C16;E16+24;E16)+F16/60;24)-MAX(C16+D16/60;20);0)
H16:
=WENN(MAX(C16+D16/60
;4)
>MIN(WENN(E16<C16;E16+24;E16)+F16/60
;6)
;MAX(C16+D16/60
;4)
-MIN(WENN(E16<C16;E16+24;E16)+F16/60
;6)
;0)
Ich rate dringend davon ab, alles so in eine Zelle zu packen, aber bitte: viel Spaß damit!
Falls etwas nicht stimmt: in dieser Form tue ich mir das korrigieren nicht an.
ich habe jetzt alles getestet sieht supper aus aber zeile H ist ein fehler....
genau das meinte ich: das in dieser Form zu korrigieren, oder gar, wenn sich später etwas ändert, anzupassen, ist die Hölle.
Probiere es mal damit:
=WENN(MAX(C16+D16/60
;4)
<MIN(WENN(E16<C16;E16+24;E16)+F16/60
;6)
;-MAX(C16+D16/60
;4)
+MIN(WENN(E16<C16;E16+24;E16)+F16/60
;6)
;0)
das problem ist ich muss die Zeilen wie die sind so behalten da der buchhalter es so haben will
aber ich darf formeln und so was alles reinpacken damit der automatisch rechnet
ich hab mich aber schon dran gewöhnt die so einzugen
anfag ende
H min H min
7 30 18 30