Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 10:08:11
Summieren von Zellen mit bedingter Formatierung
26.04.2024 09:08:00
Export alle Tabellenblätter als csv - in bestimmten Ordner
26.04.2024 08:56:40
Über alle Spalten nur €-Werte zählen
26.04.2024 08:08:24
Pivot Vormonatsveränderung
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1484to1488
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1484to1488
1484to1488
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Ausnahme bei Arbeitstagen

Ausnahme bei Arbeitstagen
25.03.2016 18:26:27
Stefan
Hallo!
Und zwar hab ich folgendes Problem, es geht um die ARBEITSTAG.INTL Funtion im Excel.
Ich habe die Feiertage im Bereich $M$11:$R$23 definiert. Jetzt möchte ich im Bereich G23:I24, wenn ich da ein Datum eintrage, dass dieses dann nicht als Feiertag sondern als Arbeitstag gerechnet wird.
Der Grund ist der, manchmal kommt es vor das zb. ein Mitarbeiter an einem Feiertag arbeiten muss, und ein anderer wieder nicht, und die Spalten wo die Feiertage definiert sind wird dann versteckt.
Meine Formel sieht so aus.
ARBEITSTAG.INTL(B5-1;C5;"0000000";$M$11:$R$23)))
Ich hoffe mir kann jemand helfen. Ich lade mal meine Tabelle zum besseren Verständnis hoch.
https://www.herber.de/bbs/user/104609.xlsx

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: durch "Schachteln" der Funktion in Formel ...
25.03.2016 19:10:33
...
Hallo Stefan,
... genau genommen nicht exakt aber hinreichend sollte sein, in D5 und nach unten kopierbar:
=WENN((B5="")+(C5="");"";ARBEITSTAG.INTL(B5-1;C5-ZÄHLENWENNS($G$23:$I$24;">="&B5;$G$23:$I$24; ";"0000000";$M$11:$R$23))
Gruß Werner
.. , - ...

AW: durch "Schachteln" der Funktion in Formel ...
26.03.2016 16:57:12
Stefan
Hallo Werner!
Vielen Dank, funktioniert jetzt echt super. Auch das man diesen Teil vereinfachen kann, wusste ich vorher nicht Danke!
WENN((B5="")+(C5="");""
Jetzt hab ich mir noch überlegt, manchmal wäre es sinnvoll das er überhaupt keine Feiertage rechnet und das würde ich gerne mit einem Makro lösen.
Ich würde es hinbekommen, wenn ich 2 Buttons machen wo er mir einfach den Bereich von den Feiertagen löscht und mit einem 2 Button wo er Sie mir wieder hin kopiert.
Gibt es da die Möglichkeit in Excel das mit einem Button zu lösen? Einmal drücken schaltet die Feiertage aus, nochmal am selben Button drücken schaltet die Feiertage wieder ein? Und der Button benennt sich dann auch um. Feiertag ein/Feiertage aus?

Anzeige
AW: ich würde es auch mit Formel lösen ...
26.03.2016 17:32:06
...
Hallo Stefan,
... und zwar indem man nicht löscht und wieder hin kopiert sondern einfach, indem Du die Formel etwas erweiterst und über z.B. ein Formularsteuerelement Deiner Wahl es steuerst.
Als Übergabezelle habe ich beispielsweise in der Formel nachfolgend die Zelle E1 angenommen und als Übergabewert 0 (= ohne Feiertage) bzw. 1 (=mit).
Die notwendige Formelerweiterung hab ich nachfolgend fett markiert.
=WENN((B5="")+(C5="");"";ARBEITSTAG.INTL(B5-1;C5-(E1=1)*ZÄHLENWENNS($G$23:$I$24;">="&B5; $G$23:$I$24;"WENN(E1; $M$11:$R$23;0)))
Gruß Werner
.. , - ...

Anzeige
AW: ich würde es auch mit Formel lösen ...
26.03.2016 19:02:11
Stefan
Ok das ist natürlich die viel bessere Lösung und funktioniert mal super. Wie mache ich das aber mit der Zelle daneben die mir die Feiertage zählen soll? Da sollte er mir ja dann eigentlich auch 0 Feiertage anzeigen.
=WENN(B5="";"";WENN(C5="";"";ZÄHLENWENNS($M$11:$R$23;">="&B5;$M$11:$R$23;" Ich habe dann in meiner Tabelle unten noch mehrere Formeln für die Zeiträume (nicht in der Vorlage von mir enthalten), da steh ich auch an wie ich die Formel abändern muss.
=WENN(C17="";"";NETTOARBEITSTAGE.INTL(B17;C17;"0000000";$M$11:$U$23)+ZÄHLENWENNS($G$23:$I$24; ">="&B17;$G$23:$I$24;" und eine 2. Tabelle
=WENN(C17="";"";NETTOARBEITSTAGE(B17;C17;$M$11:$U$23)+ZÄHLENWENNS($G$23:$I$24;">="&B17;$G$23:$I$24; "

Anzeige
AW: teils einfache Multiplikation ausreichend ...
27.03.2016 09:12:04
...
Hallo Stefan,
... in F5: =WENN((B5="")*(C5="");"";ZÄHLENWENNS($M$11:$R$23;">="&B5;$M$11:$R$23;"*E1
Analog hätte übrigens auch schon in D5 (erster Formelteil) ausgereicht:
=WENN((B5="")+(C5="");"";ARBEITSTAG.INTL(B5-1;C5-E1*ZÄHLENWENNS($G$23:$I$24;">="&B5; $G$23:$I$24;"WENN(E1; $M$11:$R$23;0)))
Allerdings um einen (Feiertags-)bereich nicht zur Geltung zu bringen, muss Du weiterhin WENN() einsetzen, wie im oberen letzten Formelteil.
Beides kannst/musst Du auch in Deinen anderen Formeln analog anwenden.
Gruß Werner
.. , - ...

Anzeige
AW: teils einfache Multiplikation ausreichend ...
27.03.2016 15:45:55
Stefan
Danke für die Antwort Werner!
Ich bekomme es aber leider nicht ganz hin. Hab jetzt ein paar Sachen in meiner Tabelle geändert zb. E1 auf E2 geändert, und hab auch den Bezug wo die Feiertage sind auf ein extra Arbeitsblatt kopiert.
Es kommt jetzt eine Fehlermeldung, wenn in B5 und C5 nichts eingetragen ist, obwohl sich ja die Zelle auf B5 und C5 beziehen, dass wenn diese leer sind er nicht rechnen soll. Irgendwo hab ich jetzt einen Denkfehler in der Formel.
Darunter schaffe ich es auch nicht die Funktion mit E2 einzubauen:
=WENN((B17="")+(C17="");"";NETTOARBEITSTAGE.INTL(B17;C17;"0000000";Feiertage!$B$3:$J$17) +ZÄHLENWENNS($G$23:$I$24;">="&B17;$G$23:$I$24;" Ich hab dir mal eine neue Vorlage hochgeladen. Ich schaffe es leider alleine nicht. Kann du bitte nochmal drüber schauen ist sicher nur eine Kleinigkeit für dich.
Danke!
https://www.herber.de/bbs/user/104622.xlsx

Anzeige
AW: Klammersetzung war inkorrekt ...
27.03.2016 16:25:21
...
Hallo Stefan,
... das entscheidende, warum die Fehlerwerte mit den Formeln aufgetreten sind: Ich hatte offensichtlich einen Klammerschreibfehler in meiner Formelvorgabe (*E$2 muss vor die letzte Klammer!) Sorry, war wahrscheinlich nicht wirklich munter.
Bitte aber noch beachten, in Excel ist WAHR ungleich 1. Erst durch eine mathematische Operation wie z.B. einer Multiplikation mit 1 oder einem Addieren vor 0 wird aus WAHR*1=WAHR+0=1.
Richtig wäre es nun wie folgt (Formeln nach unten kopierbar):
 BCDEFGHI
2   WAHR    
3        
4BeginnTageErgebnis FT   
501.01.20163001.02.2016 2   
6        
7        
8        
9        
10        
11        
12        
13        
14        
15        
16BeginnEndeTageFT    
1701.12.201508.12.2015071    
18        
19        
20        
21        
22     Ausnahmen Feiertage
23        
24        
25        

Formeln der Tabelle
ZelleFormel
D5=WENN((B5="")+(C5=""); "";ARBEITSTAG.INTL(B5-1;C5-$E$2*ZÄHLENWENNS(G$23:I$24;">="&B5; G$23:I$24;"<="&ARBEITSTAG.INTL(B5-1;C5;"0000000";Feiertage!B$3:J$17)); "0000000";WENN(E$2; Feiertage!B$3:J$17;0)))
F5=WENN((B5="")*(C5=""); "";ZÄHLENWENNS(Feiertage!B$3:$J$17;">="&B5;Feiertage!B$3:J$17;"<="&D5)*E$2)
D17=WENN((B17="")+(C17=""); "";NETTOARBEITSTAGE.INTL(B17;C17;"0000000";WENN(E$2;Feiertage!B$3:J$17;0))+ZÄHLENWENNS(G$23:I$24;">="&B17;G$23:I$24;"<="&C17)*E$2)
E17=WENN((D17=""); "";ZÄHLENWENNS(Feiertage!B$3:J$17;">="&B17;Feiertage!B$3:J$17;"<="&C17)*E$2)

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
F101. / Formel ist =#BEZUG!="Freitag"Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: Klammersetzung war inkorrekt ...
28.03.2016 15:57:09
Stefan
Hallo Werner!
Vielen Dank ist echt genial, funktioniert jetzt super. Was das Wahr und Unwahr betrifft, ich hab es einfach mit der Checkbox verbunden, wüsste nicht wie ich das sonst besser machen kann. Konnte bis jetzt beim Testen aber noch kein Problem feststellen.
Einen kleinen Fehler hab ich noch in meiner Tabelle und zwar in G23:I24 kann ich ja die Tage eintragen, wo trotz eines Feiertages gearbeitet werden muss.
Jetzt sollte aber auch wenn ich dort was eintrage, zb. in E17 dann bei Feiertagen 0 angezeigt werden bzw. der Tag weggerechnet werden.
Vielleicht kannst du da auch noch einen Blick drauf werfen :-)

Anzeige
AW: noch Nicht-Berücksichtigung best. Tage ...
29.03.2016 08:03:05
...
Guten Morgen Stefan,
... so Formeln nach unten kopierbar):
Tabelle

 BCDEFGHI
2   WAHR    
3        
4BeginnTageErgebnis FT   
501.12.20153102.01.2016 2   
6        
7        
8        
9        
10        
11        
12        
13        
14        
15        
16BeginnEndeTageFT    
1701.12.201502.01.2016312    
18        
19        
20        
21        
22     Ausnahmen Feiertage
23     08.12.2015  
24      26.12.2015 

Formeln der Tabelle
ZelleFormel
D5=WENN((B5="")+(C5=""); "";ARBEITSTAG.INTL(B5-1;C5-$E$2*ZÄHLENWENNS(G$23:I$24;">="&B5; G$23:I$24;"<="&ARBEITSTAG.INTL(B5-1;C5;"0000000";Feiertage!B$3:J$17)); "0000000";WENN(E$2; Feiertage!B$3:J$17;0)))
F5=WENN((B5="")*(C5=""); "";(ZÄHLENWENNS(Feiertage!B$3:J$17;">="&B5;Feiertage!B$3:J$17;"<="&D5)-ZÄHLENWENNS(G$23:I$24;">="&B5;G$23:I$24;"<="&D5))*E$2)
D17=WENN((B17="")+(C17=""); "";NETTOARBEITSTAGE.INTL(B17;C17;"0000000";WENN(E$2;Feiertage!B$3:J$17;0))+ZÄHLENWENNS(G$23:I$24;">="&B17;G$23:I$24;"<="&C17)*E$2)
E17=WENN((D17=""); "";(ZÄHLENWENNS(Feiertage!B$3:J$17;">="&B17;Feiertage!B$3:J$17;"<="&C17)-ZÄHLENWENNS(G$23:I$24;">="&B5;G$23:I$24;"<="&D5))*E$2)

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
F101. / Formel ist =#BEZUG!="Freitag"Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: noch Nicht-Berücksichtigung best. Tage ...
30.03.2016 19:01:03
Stefan
Hallo Werner!
Ein kleiner Fehler ist in der Formel von E17, die hat sich jetzt auf B5 und D5 bezogen. So müsste Sie jetzt richtig sein.
=WENN((D17=""); "";(ZÄHLENWENNS(Feiertage!B$3:J$17;">="&B17;Feiertage!B$3:J$17;"="&B17;G$23:I$24;" Auf jeden Fall, vielen vielen Dank! Echt toll dass ich das jetzt Dank dir hinbekommen hab!
Lg Stefan

AW: gut dass Du aufgepasst hast ...
30.03.2016 19:33:51
...
Hallo Stefan,
... da habe ich gestern Morgen wohl zu schematisch kopiert und dann vergessen den Bezug anzupassen.
Gruß Werner
.. , - ...

Links zu Excel-Dialogen

CheckBox

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige