Supportnet / Forum / Tabellenkalkulation

Hallo und Guten Morgen, seit Tagen suche ich nach einer Lösung für folgendes Problem: Wie kann ich aus diesen beiden Daten die Arbeitstage ohne Wochenende und ohne Feiertage berechnen? Fr, 05.10.2007 Fr, 12.10.2007 das Ergebnis hier müßte 5 lauten. Also auch keine Feiertage mitzählen. Wenn im o.g. Datum der 7.10. ein Feiertag wäre, dann müßte das Ergebnis 4 sein. Mit dieser Formel errechne ich hier im Beispiel die beiden o.g. datum: =KKLEINSTE(WENN((REST(D1066+SPALTE('Kalender 2008'!1:1);7)>1)*(ZÄHLENWENN('Kalender 2008'!G2:G12;D1066+SPALTE('Kalender 2008'!1:1))=0);D1066+SPALTE('Kalender 2008'!1:1));F6) Diese Formel ist mir geliehen worden, die ist nicht auf meinem Mist gewachsen. Aber sie funktioniert korrekt. Aber wie kann ich diese Formel ändern, dass sie mir die Differenz ausrechnet und das Ergebnis auch Negativ darstellen kann? Dies hier ist das dritte Excel-Forum das ich gefunden habe, in den andern beiden konnte mir keiner helfen. Danke im voraus für jede Hilfe!!!! Maryza

Hallo Maryza,

Arbeitstage ausrechnen von Datum bis Datum
z.B. mit der Funktion Nettoarbeitstage. Du musst aber vorher im Menü Extra-Add-In-Manager die Analyse-Funktionen aktivieren.
=NETTOARBEITSTAGE(A1;A30;{"13.4.01";"16.4.01"})
A1 =erster Tag
A30=letzter Tag
In den Klammern die Feiertage
lt Excel-Hilfe so: (A1, A2 die Datumangaben)
=NETTOARBEITSTAGE(A1;A2;{"24.12.1999";"03.01.2000"})
besser: lege einen Bereich fest(z.B."Feiertage") , in den du alle Feiertage schreibst und dann

Gruß Hajo

Hallo Maryza,
in diesem Fall ist ausnahmsweise die Hilfefunktion in Excel ganz brauchbar. Schau dort mal unter Tage und dann unter Nettoarbeitstage nach.

In Deinem Beispiel müsste der Aufbau wie folgt sein
Beginndatum; Enddatum; Feiertag

a1 = 05.10.2007; a2 =12.10.2007, a3=08.10.2007
(der 07.10. ist bereits ein Sonntag und damit kein doppelter Abzug möglich für einen Feiertag!)

die Formel wäre hier:
=nettoarbeitstage(a1;a2;a3)

ggf. noch -1 anhängen, wenn der Starttag nicht mitgerechnet werden soll, denn sonst wären es ohne Feiertag bei mir 6 Arbeitstage.

Ich hoffe Dir hiermit dienlich gewesen sein zu können...
Gruß Helmut

Hi Maryza,

wenn bei dir das Add-in "Analysefunktion" installiert und aktiviert ist, dann sollte das mit der Funkiton NETTOARBEITSTAGE gehen.
Um allerdings die Feiertage zu berücksichtigen, musst Du dir hierfür eine Feiertagsliste anlegen, bzw. berechnen lassen.

Die Formel würde dann so aussehen:

=NETTOARBEITSTAGE(deine Formel für Datum1;deine Formel für Datum2;Bereich in dem die Feiertage stehen)

ABER!!! bei deinem obigen Beispiel wäre das Ergebnis 6, da ja von Freitag bis Freitag 6 Arbeitstage sind.

Das Ergebnis wäre auch 6, wenn der 7.10. ein Feiertag wäre, da dieser dann auf einen Sonntag fällt und somit nicht gezählt wird.

CU Aliba

Hallo Maryza,

wenn die Formel auf allen Rechnern funktionieren soll, egal ob die Analysefunktionen aktiv sind oder nicht, würde ich folgende Formel vorschlagen.

=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A1&":"&B1));2)<=5)*1)-SUMMENPRODUKT(($L$1:$L$20>=A1)*($L$1:$L$20<=B1)*(WOCHENTAG($L$1:$L$20;2)<6))

A1 = Anfangsdatum
B1 = Enddatum

Die Liste mit den Feiertagen befindet sich im Bereich L1:L20.
Diesen Bereich kannst Du auch verlegen, musst aber die Formel entsprechend anpassen.

Gruß
Rainer

Hallo Rainer,

ich gebe auf! Ich habe deine Formel bei mir rein kopiert und die Zellbezüge entsprechend geändert. Ergebnis: Wert.
Ich vermute, dass es irgendeine Exceleinstellung gibt, die das verhindert. Ich habe die Zellformatierungen und Zellschutz geprüft und finde nichts.
Ich weiß nicht mehr weiter. Wenn man hier zumindest eine Datei hochladen könnte, damit mal jemand die Einstellungen prüfen kann.

Echt Mist!

Lieben Dank für die Mühe. Aber ich habe keine Idee mehr.
leer.....

Maryza

Hallo Maryza,

ich verstehe nicht, wo hier ein Problem ist.

Habe die Formel im Anhang eingetragen.



http://www.netupload.de/detail.php?img=9ddb059e965fa99c12431147bcbc...

Gruß
Rainer

Hallo Rainer,

ich habe es gesehen und auch vorher geglaubt. Aber woran kann es liegen, dass ich in meiner Originaltabelle nicht das Ergebnis bekomme. Erst dachte ich, dass irgendwo ein Schreibfehler drin ist. Ich habe 100 Mal die Formel überprüft, sebst mein Mann hat nachgeschaut und sagt die sind identisch.

Meine Daten stehen in E1066, E1076, die Feiertage in AN1070:AN1080.

Jetzt schau dir meine umgebaute Formel an:
=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(E1066&":"&E1067));2)<6)*1)-SUMMENPRODUKT((AN1070:AN1080>=A1)*(AN1070:AN1080<=A2)*(WOCHENTAG(AN1070:AN1080;2)<6))
Diese Formel steht in E1068
Ergebnis #WERT

????
Maryza

Hallo Maryza,

Du hast zwei falsche Bezüge in Deiner Formel.
Habe sie hier berichtigt

=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(E1066&":"&E1067));2)<6)*1)-SUMMENPRODUKT((AN1070:AN1080>=E1066)*(AN1070:AN1080<=E1067)*(WOCHENTAG(AN1070:AN1080;2)<6))

Deine Bezüge standen noch auf A1 und A2

Gruß
Rainer

Leider nein,

die Formel hatte ich vorher schon geändert. Aber da ich hier mit meinem privatem PC online bin und mit dem Firmennotebook nicht, ist es umständlich hier die Dateien hin und her zu transportieren.
In der Formel am Notebook ist es so, wie du es jetzt erneut gepostet hast und es funzt nicht. Mir ist aber noch etwas anderes aufgefallen nämlich, ich habe ein Formel die berücksichtigt nur die Wochenenden und nicht die Feiertage. Wenn ich an der Datei etwas ändere ist statt dem Ergebnis #WERT in der Zelle. Dann mach ich die Datei zu ohne abzuspeichern, öffne sie wieder und die Formel funktioniert wieder.

Morgen früh bringe ich deine Formel übern USB-Stick aufs Notebook und kopiere die rein. Mal sehen

Schönen Abend noch!
LG
Maryza

Hallo Maryza,

anbei die Datei mit Deinen Vorgaben.

http://www.netupload.de/detail.php?img=2618b8fde7e7a5ebec0e176a410f...

Gruß
Rainer

Moin Rainer,

ich weiß nicht, wie ich dir deine Hilfe vergelten kann!
Also, zuerst hatte ich deine Formel in meine Tabelle einkopiert und erhalte als Ergebnis #WERT. Dann habe ich alle Zellinhalte und Formate gelöscht, dann kopiert und voila' , das Ergebnis ist da!
Nun habe ich auch die Uploadfunktion bei netload genutzt und schicke dir mein Original, weil das Ergebnis noch verfeinert werden muss. (Wenn ich da nicht zu viel verlange?)
actual: Mo, 15.10.2007 (Wann ich es gemacht habe)
target: Di, 16.10.2007 (Bis wann ich es hätte machen müßte)delay; 1 (Differenz)

Bei diesem Beispiel aus Spalte E bin ich einen Tag früher fertig als geplant. Die Farbe müßte Grün-1 sein. Nur wenn ich in target verspätet bin, dann darf es Orange oder Rot sein.

Hier ist meine Datei:
http://www.netupload.de/detail.php?img=13a358e563dec9de42db15da9fb3fbdf.xls

LG
Maryza
...die freiwillig Überstunden am Wochenende kloppt ohne Bezahlung.

Hallo Mariza,

habe in den Zeilen 1068, 1072, 10776 die Formeln, und das Zellformat geändert.
Teste mal, ob Du es so wolltest.

http://www.netupload.de/detail.php?img=b028e6350e568306599d81ab8e01...

Gruß
Rainer

Hallo Rainer,

das ist der Hammer!!! Du bist ein Genie und noch ein hilsbereites dazu ;-))

Was das an Arbeitszeitersparnis für uns bedeutet ist schon enorm. Noch besser ist aber, dass sich keine Fehler mehr einschleichen können bei der manuellen Berechnung.

Vielen lieben Dank!
Wie lange braucht man um so was zu können? Letzte Woche habe ich mir Excel für Dummies gekauft, denn ich bin fasziniert von dem Programm und den Möglichkeiten. Ich habe richtig Feuer gefangen.

Danke* Danke* Danke*

Bewertung gebe ich morgen ab, mein heutiges Pensum ist ausgeschöpft. ;o)

Noch einen schönen Sonntag wünsche ich

Maryza

Hallo Maryza,

freut mich, dass ich helfen konnte.

Da Du nun heiss auf Excel bist, solltest Du Dich unbedingt auf dieser Seite umschauen.
Das dort angepriesene Zauberbuch ist ebenfalls zu empfehlen.

http://www.excelformeln.de/formeln.html

Gruß
Rainer

@Admin:

Fass das bitte nicht als Webung auf, das soll nur ein Tipp für lernbegeistere Exceluser sein.

Hallo Maryza,

weiter oben schrieb ich, dass meine Formel so zu sagen ein Ersatz für die Analysefunktion NETTOARBEITSTAGE() ist und deren Aktivierung nicht erfordert.

Deinerseits kam aber nicht zum Ausdruck, ob Du auf dieses Add-In verzichten willst/kannst/musst.

Im Anhang findst Du die gleiche Datei, die statt mit meiner erweiterten Formel, mit NETTOARBEITSTAGE() arbeitet.

Entscheide selbst, welche Variante Du einsetzen willst.

http://www.netupload.de/detail.php?img=aed842b1f4fead25178de3165dc8...

Gruß
Rainer

Hallo Rainer,

ich könnte noch mal deine Hilfe gebrauchen. Ich habe in meiner Tabelle folgende Matrix-Formel:

=KKLEINSTE(WENN((REST(I1066+SPALTE('Kalender 2008'!1:1);7)>1)*(ZÄHLENWENN('Kalender 2008'!$G$2:$G$23;I1066+SPALTE('Kalender 2008'!1:1))=0);I1066+SPALTE('Kalender 2008'!1:1));$L$6)

Worauf bezieht sich dieses 1:1? Ich habe auf dem Tabellenblatt nur unter G die Feiertage?
Wenn ich die Formel runterziehe, also kopiere, dann steht da 5:5, 11:11, 43:43 usw.

wie kann ich da den Bezug fixieren? Also mit dem $ zeichen klappt es nicht, wahrscheinlich weil ich nicht weiß, wo genau ich das $ Zeichen platzieren muss.

LG
Mary

Hallo Mary,

erspar mir bitte die Erkärung, habe Dir dafür die Formeln in die Zeilen 1067, 1071, 1075 der Spalten E-G sowie J-L eingetragen.

Die definierten Namen habe ich gelöscht und dafür die Bezüge zur Zeile 6 der jeweiligen Spalte hergestellt.
Für den Feiertagsbereich habe ich den Namen "Feiertage" (dynamisch) definiert.
D.h. es können, ohne Formeländerung, Feiertage hinzugefügt/gelöscht werden.

Ich hoffe ich habe alles richtig verstanden.

http://www.netupload.de/detail.php?img=5fa51584cd557f3b6179553e63ca...

Gruß
Rainer

Ich betätige mich dann mal als "Leichenfledderer" und grabe dieses recht alte Thema noch mal aus. Leider habe ich das selbe Problem wie marzya. Die Formel gibt nur #Wert aus. Bzw. bei mir gibt sie #Value aus, weil ich mich mit einer englischen Version rumschlagen muss. Hier ist also mal die Formel, die ich nutze:

=SUMPRODUCT((WEEKDAY(CELL(INDIRECT(A1&":"&B1)),1)<=5)*1)-SUMPRODUCT(($C$1:$C$2>=A1)*($C$1:$C$2<=B1)*(WEEKDAY($C$1:$C$2,2)<6))

In den Zellen A1 und B1 sollen das Anfangs- bzw. das Enddatum stehen. In den Zellen C1 bis C2 die Feiertage. Zusätzlich soll die Woche von Sonntag bis Donnerstag gehen. Das Wochenende ist also freitags und Samstags.

Ich hoffe ich habe die Änderungen soweit richtig eingebaut. Leider sagt mir der Excel-Formel-Rechner, dass schon bei der Berechnung von "Indirect" ein Fehler vorliegt.

Wie muss ich denn die Daten eingeben. Mein Excel erkennt ein Datum im Format 04.01.2009 nicht. Ich muss es als 01/04/2009 eingeben.

Vielen Dank schon mal für eure Hilfe!

^{*Threadedit* 05.02.2009, 11:14:32
Admininfo: Führ bitte fremde Threads nicht fort indem du eigene Anfragen anhängst.
Die User werden es dir danken. Siehe FAQ 2, #7}

Hallo problembaer,

stell doch bitte Deine Frage in einem neuen Thread und lade eine Beispielmappe hoch.
Habe keine Lust anhand der dürftigen Infos eine Testdatei zu erstellen.

Gruß
Rainer

• Automatisches rückrechnen eines Datums
• XL ein beliebiges Datum mit dem Heutigen vergleichen und die Tage zählen
• Alter durch Geburtstaum in Wordtabelle errechnen
• Wieviele Wochen seit bestimmtem Datum
• Datum berechnen