Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

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

Frage Microsoft Microsoft Office

Mitglied: winget

winget (Level 1) - Jetzt verbinden

26.07.2011, aktualisiert 22:32 Uhr, 7666 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 ..
Neuester Wissensbeitrag
CPU, RAM, Mainboards

Angetestet: PC Engines APU 3a2 im Rack-Gehäuse

(1)

Erfahrungsbericht von ashnod zum Thema CPU, RAM, Mainboards ...

Ähnliche Inhalte
Microsoft Office
Prüfung von Intervallen und Textausgabe in Excel (2)

Frage von traller zum Thema Microsoft Office ...

LAN, WAN, Wireless
Windows 10 - Nach einiger Zeit Internetausfall (13)

Frage von Irepeps zum Thema LAN, WAN, Wireless ...

Microsoft Office
Darstellung einer Produktionsmenge in einer Excel Übersicht! (4)

Frage von Magnus32x1 zum Thema Microsoft Office ...

Batch & Shell
gelöst Loginzeiten aus dem Ereignisprotokoll in Excel schreiben (1)

Frage von l-Ne0n zum Thema Batch & Shell ...

Heiß diskutierte Inhalte
DSL, VDSL
DSL-Signal bewerten (13)

Frage von SarekHL zum Thema DSL, VDSL ...

Switche und Hubs
Trunk für 2xCisco Switch. Wo liegt der Fehler? (10)

Frage von JayyyH zum Thema Switche und Hubs ...

Windows Server
Mailserver auf Windows Server 2012 (9)

Frage von StefanT81 zum Thema Windows Server ...

Backup
Clients als Server missbrauchen? (9)

Frage von 1410640014 zum Thema Backup ...