Top-Themen

Aktuelle Themen (A bis Z)

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit

gelöst Nachtzuschlag-Zeit in Excel mit Dezimalzahlen

Mitglied: winget

winget (Level 1) - Jetzt verbinden

26.07.2011, aktualisiert 22:32 Uhr, 9069 Aufrufe, 9 Kommentare

Nachtzuschlag-Zeit unter Berücksichtigung von Dezimalzahlen.

Hallo zusammen,
es gibt bereits viele Foren, wo dieses Thema bearbeitet wurde. Allerdings nicht unter Berücksichtigung von Dezimalzahlen (Uhrzeiten als Industrieminuten).
D.H. die Zellen sind nicht als Uhrzeit formatiert sondern als Dezimalzahlen. Alles wird bereits in Industrieminuten umgerechnet.

Ich habe bereits eine halbe Lösung, aber ich komme nicht weiter.
Nachtzuschlag beginnt um 22,00 und endet um 5,00.

Beispiel:
A1=22,00
A2=5,00

A3=20,00 (Beginn Arbeitszeit)
B3=6,00 (Ende Arbeitszeit)
Pause ist noch nicht berücksichtig aber das ist auch nicht das Problem

Wenn ich folgende Formel benutze:
=WENN(UND(A3>=A1;B3<=A2);24-A3+B3;WENN(UND(A3<=A1;B3>=A2);24-A1+A2;WENN(UND(A3<=A1;B3<=A2);24-A1+B3;"")))
funktioniert so weit, wenn folgende Bedingungen nicht zutreffen:
1. Die Arbeitszeit endet nicht zwischen 22,00 und 23,99
2. Die Arbeitszeit befindet sich bis 22,00
Das ist alles logisch, aber ich weiß nicht wie ich die Zeit zwischen 22,00 und 5,00 eingrenzen kann.

Für Lösungen bzw. Vorschläge wäre ich euch sehr dankbar.

Gruß
winget
Mitglied: Dirmhirn
26.07.2011 um 22:22 Uhr
Hi!

Hast schon einmal probiert, die Berechung auf mehrerer Spalten aufzuteilen? Am Ende dann einfach die summe jeder Zeile und die Spalten kannst ja verstecken.

sg Dirm
Bitte warten ..
Mitglied: winget
26.07.2011 um 22:34 Uhr
Zitat von Dirmhirn:
Hi!

Hast schon einmal probiert, die Berechung auf mehrerer Spalten aufzuteilen? Am Ende dann einfach die summe jeder Zeile und die
Spalten kannst ja verstecken.

sg Dirm

Ich verstehe nicht wie du das meinst. Eine kurze Erklärung wäre hilfreich.
Bitte warten ..
Mitglied: bastla
26.07.2011 um 23:03 Uhr
Hallo winget!

Für die meisten Fälle sollte das etwa so klappen [Edit] auch Zeiten, die gänzlich vor der "Nacht" liegen, berücksichtigt und noch etwas vereinfacht [/Edit]:
=REST(24-A3+B3-(A3>=A2)*(A3<=A1)*(A1-A3)-(B3>=A2)*(B3<=A1)*(B3-A2);24)*NICHT(((B3>=A2)*(B3<=A1)*(A3>=A2)*(A3<=A1)*(A3<=B3)))
Nicht funktionieren würde eine Arbeitszeit, die in der Nacht beginnt und in der darauf folgenden Nacht endet, also etwa von 3 Uhr bis 1 Uhr oder von 0 bis 23 ...

Kurz zur Funktionsweise:
Mit
24-A3+B3
wird die gesamte Arbeitszeit ermittelt - wenn sich ein Wert > 24 ergibt, wird dieser mittels der REST()-Funktion auf einen Wert unter 24 reduziert.
(A3>=A2)*(A3<=A1)*(A1-A3)
zieht davon die vor der "Nacht" liegende Zeit (also zB 20 bis 22 Uhr) und
(B3>=A2)*(B3<=A1)*(B3-A2)
die Zeit nach der "Nacht" (zB 5 bis 6 Uhr) wieder ab, wobei anstelle von WENN() und UND() die Verknüpfung per Multiplikation erfolgt (verwende ich - fast schon gewohnheitsmäßig - nicht zuletzt wegen der Einsatzmöglichkeiten in Array-Formeln lieber als zB für die zweite Formel die Schreibweise
WENN(UND(B3>=A2;B3<=A1);B3-A2;0)
einzusetzen).
Schließlich muss noch geprüft werden, ob sowohl Beginn als auch Ende der Arbeitszeit in der "Tageszeit" liegen - für diesen Fall muss die Anzahl der Nachtstunden 0 ergeben:
NICHT(((B3>=A2)*(B3<=A1)*(A3>=A2)*(A3<=A1)*(A3<=B3)))
Grüße
bastla
Bitte warten ..
Mitglied: Xolger
26.07.2011 um 23:04 Uhr
Hallo winget,

in welchen Bereichen sollen sich denn die Anfangs- und Endzeiten bewegen?

Wenn du schon Lösungsvorschläge mit dem Uhrzeitformat hast,
dann hilft die evtl, folgende Formel weiter:
=ZEIT(C2,(C2-ABRUNDEN(C2,0))*100,0) 'in C2 steht z.B. 21,10 --> 09:10 PM
Ich bin mal von der Zeitangabe auf Minuten gerundet ausgegangen.

Gruß
Xolger
Bitte warten ..
Mitglied: Xolger
26.07.2011 um 23:14 Uhr
Hallo bastla,

Respekt für diese Formel, auf die Idee (A3>=A2)*(A3<=A1) als Wahr(1)/Falsch(0) Auswertung
bin ich nocht gekommen, aber die Möglichkeit ist Spitze.
Da hat sich der Abend am Rechner ja wieder gelohnt.


Gruß Xolger
Bitte warten ..
Mitglied: bastla
26.07.2011 um 23:17 Uhr
Hallo Xolger!
Da hat sich der Abend am Rechner ja wieder gelohnt.
Freut mich - so soll's ja sein ...

Grüße
bastla
Bitte warten ..
Mitglied: bastla
27.07.2011 um 01:16 Uhr
... und jetzt noch die hoffentlich komplette (und in der UND()/ODER()-Variante vielleicht doch etwas lesbarere ) Version:
=REST(
WENN(ODER(UND(A3<=A2;B3<=A2;A3<=B3);UND(A3>=A1;B3>=A1;A3<=B3);UND(A3>=A1;B3<=A2));24+B3-A3;
WENN(ODER(UND(A3<=A2;B3>=A2;B3<=A1);UND(A3>=A1;B3>=A2;B3<=A1));24+A2-A3;
WENN(ODER(UND(A3<=A2;B3<=A2;A3>=B3);UND(A3>=A1;B3>=A1;A3>=B3));24+A2-A3+B3-A1;
WENN(ODER(UND(A3>=A2;A3<=A1;B3>=A1);UND(A3>=A2;A3<=A1;B3<=A2));24+B3-A1;
WENN(UND(A3<=A2;B3>=A1);A2-A3+B3-A1;
WENN(UND(A3>=A2;A3<=A1;B3>=A2;B3<=A1;B3>=A3);0;
24+A2-A1
))))));24)
Bitte beachten: Die zur besseren Lesbarkeit (in Excel mit Alt+Enter) eingefügten Zeilenumbrüche erfordern, dass zum Übertragen in eine Excel-Zelle diese zuerst per Doppelklick oder mit F2 in den Bearbeitungsmodus geschaltet werden muss - erst dann kann die gesamte Formel eingefügt werden.

[Edit] Zur Sicherheit doch auch noch am Stück:
=REST(WENN(ODER(UND(A3<=A2;B3<=A2;A3<=B3);UND(A3>=A1;B3>=A1;A3<=B3);UND(A3>=A1;B3<=A2));24+B3-A3;WENN(ODER(UND(A3<=A2;B3>=A2;B3<=A1);UND(A3>=A1;B3>=A2;B3<=A1));24+A2-A3;WENN(ODER(UND(A3<=A2;B3<=A2;A3>=B3);UND(A3>=A1;B3>=A1;A3>=B3));24+A2-A3+B3-A1;WENN(ODER(UND(A3>=A2;A3<=A1;B3>=A1);UND(A3>=A2;A3<=A1;B3<=A2));24+B3-A1;WENN(UND(A3<=A2;B3>=A1);A2-A3+B3-A1;WENN(UND(A3>=A2;A3<=A1;B3>=A2;B3<=A1;B3>=A3);0;24+A2-A1))))));24)
[/Edit]

Grüße
bastla
Bitte warten ..
Mitglied: winget
27.07.2011 um 10:14 Uhr
Zitat von bastla:
Hallo winget!

Für die meisten Fälle sollte das etwa so klappen [Edit] auch Zeiten, die gänzlich vor der
"Nacht" liegen, berücksichtigt und noch etwas vereinfacht [/Edit]
:
01.
 type="plain">=REST(24-A3+B3-(A3>=A2)*(A3<=A1)*(A1-A3)-(B3>=A2)*(B3<=A1)*(B3-A2);24)*NICHT(((B3>=A2)*(B3<=A1)*(A3>=A2)*(A3<=A1)*(A3<=B3)))
02.
> 
Nicht funktionieren würde eine Arbeitszeit, die in der Nacht beginnt und in der darauf folgenden Nacht endet, also etwa von 3
Uhr bis 1 Uhr oder von 0 bis 23 ...

Kurz zur Funktionsweise:
Mit
24-A3+B3
> 
wird die gesamte Arbeitszeit ermittelt - wenn sich ein Wert > 24 ergibt, wird dieser mittels der REST()-Funktion auf einen Wert
unter 24 reduziert.
(A3>=A2)*(A3<=A1)*(A1-A3)
> 
zieht davon die vor der "Nacht" liegende Zeit (also zB 20 bis 22 Uhr) und
(B3>=A2)*(B3<=A1)*(B3-A2)
> 
die Zeit nach der "Nacht" (zB 5 bis 6 Uhr) wieder ab, wobei anstelle von WENN() und UND() die Verknüpfung per
Multiplikation erfolgt (verwende ich - fast schon gewohnheitsmäßig - nicht zuletzt wegen der Einsatzmöglichkeiten
in Array-Formeln lieber als zB für die zweite Formel die Schreibweise
WENN(UND(B3>=A2;B3<=A1);B3-A2;0)
> 
einzusetzen).
Schließlich muss noch geprüft werden, ob sowohl Beginn als auch Ende der Arbeitszeit in der "Tageszeit"
liegen - für diesen Fall muss die Anzahl der Nachtstunden 0 ergeben:
NICHT(((B3>=A2)*(B3<=A1)*(A3>=A2)*(A3<=A1)*(A3<=B3)))
> 
Grüße
bastla

Hut ab! Super Lösung!
Du hast immer auf meinen Beiträge geantwortet und auch Lösungen gehabt.
Vielleicht machst du das mit links, aber ich finde schon außergewöhlich gut.
Vielen Dank
Bitte warten ..
Mitglied: bastla
27.07.2011 um 10:34 Uhr
Hallo winget!
Vielleicht machst du das mit links
Ich mache es vor allem gerne, und daher freue ich mich, dass es für Dich passt ...

Grüße
bastla
Bitte warten ..
Ähnliche Inhalte
Microsoft Office
Excel Zeit aus Zeit berechnen!?
gelöst Frage von linguinMicrosoft Office1 Kommentar

Hallo Hoffe die Frage ist halbwechs verständlich. Folgendes Problem. Arbeitszeit: von 21:30 Uhr bis 5:30 Uhr = 8h Klar ...

Microsoft Office

Excel - Summe einer Formel mit einer enthaltenen Reihe mit Dezimalzahl

Frage von TraxtormerMicrosoft Office10 Kommentare

Hallo zusammen, es geht um eine Berechnung im Excel, bei der eine Rolle ein Band mit einer gewissen Anzahl ...

Microsoft Office

Excel - Automatische Zeit Datumsaktualisierung einer Zelle

gelöst Frage von bugmenot2Microsoft Office8 Kommentare

Hallo, eine kurze Frage. Ich möchte wenn eine Zelle bearbeitet wurde, dass automatisch ein Datum/Zeitstempel in einer anderen Zelle ...

Entwicklung

Projekt Zeit Tracker

Frage von Barti22Entwicklung5 Kommentare

Halli Hallo, das hier ist meine erste Frage in diesem Forum. Ich benötige ein Programm was das Aufschreiben von ...

Neue Wissensbeiträge
Router & Routing

Zyxel VMG 1312 B30A - Firmwareupdate nur im "privaten" Firefox Modus möglich

Tipp von the-buccaneer vor 3 StundenRouter & Routing

Hatte hier ein Zyxel 1312 das ums verrecken kein Firmwareupdate durchführen wollte. Datei ist auswählbar, dann kommt der Fortschrittskreisel ...

Windows 7

Windows 7 u. Server 2008 (R2) SHA-2-Update kommt am 12. März 2019

Information von kgborn vor 1 TagWindows 75 Kommentare

Kleine Info für die Admins der oben genannten Maschinen. Ab Juli 2019 werden Updates von Microsoft nur noch mit ...

Firewall
PfSense 2.5.0 benötigt doch kein AES-NI
Information von ChriBo vor 3 TagenFirewall2 Kommentare

Hallo, Wie sich einige hier erinnern werden hat Jim Thompson in diesem Aritkel beschrieben, daß ab Version 2.5.0 ein ...

Internet
Copyright-Reform: Upload-Filter
Information von Frank vor 4 TagenInternet1 Kommentar

Hallo, viele Menschen reden aktuell von Upload-Filtern. Sie reden darüber, als wären es eine Selbstverständlichkeit, das Upload-Filter den Seitenbetreibern ...

Heiß diskutierte Inhalte
Hardware
IT-Werkzeugkoffer bis 50,- EUR
gelöst Frage von departure69Hardware44 Kommentare

Hallo. Ich bin als IT-Systembetreuer einer Gemeinde zusätzlich auch der IT-Systembetreuer einer Grund- und Hauptschule. Dort muß ich jedoch ...

Netzwerke
Verteilung von Programmdaten außerhalb des internen Netzwerkes
Frage von mertaufmbergNetzwerke27 Kommentare

Guten Morgen liebe Administratoren, ich versuche zurzeit eine möglichst sichere und einfache Lösung zu suchen, um ein Programmverzeichnis über ...

Netzwerkmanagement
Richtfunknetzwerk mit vielen Hops stabiler gestalten
Frage von turti83Netzwerkmanagement22 Kommentare

Hallo, in meinem Dorf habe ich vor ca. einem Jahr ein Backbone aufgebaut um die Nachbarschaft mit Internet zu ...

Hyper-V
Intel MSC Raid 5 Rebuild
Frage von DannysHyper-V19 Kommentare

Hallo Community, Ich habe einen Modul Server von Intel in Betrieb. Dort ist eine Festplatte aus dem Raid 5 ...