Formeländerung bei Feiertag

[Thomas78]

Hallo zusammen, ich bins mal wieder...

ich habe folgende Formel die mir die Spesen für unsere Mitarbeiter berechnet (Mo - Do 24 Euro und Freitag 12 Euro, Feiertage ausgenommen):

=SUMMENPRODUKT((TEXT(N3:AR3;"MMJJ")=TEXT(F2;"MMJJ"))*(N11:AR11="")*(REST(N3:AR3-2;7)<5))-SUMMENPRODUKT((TEXT(N3:AR3;"MMJJ")=TEXT(F2;"MMJJ"))*(N11:AR11="")*(REST(N3:AR3-2;7)<5)*(N3:AR3=Feiertag))

Ich müsste die Formel nun folgendermaßen ändern:

Der Grundsatz bleibt erhalten, aber wenn z.B. der Dienstag ein Feiertag ist, dann gibt es für Montag nur 12,-- Euro statt 24,-- Euro, wenn Mittwoch ein Feiertag ist, dann gibt es für Dienstag nur 12,-- Euro usw...

Kann mit jemand bei der Umstellung der Formel helfen?

Vielen Dank im voraus und schöne Grüße

Thomas

[Thomas78]

Hmh ich kann leider meinen obigen Beitrag nicht editieren, warum auch immer...

Ich habe ganz vergessen die Formel zählt natürlich nur die Tage Mo- Do.
Für die Freitage habe ich folgende Formel zusätzlich:

=SUMMENPRODUKT((TEXT(N3:AR3;"MMJJ")=TEXT(F2;"MMJJ"))*(N11:AR11=2))

Es zeigt mir also an z.b. 4 Freitage im aktuellen Monat und 18 sonstige Arbeitstage. Aus diesem Ergebnis wird dann das Spesenergebnis berechnet.

Ziel wäre also genauer gesagt wenn z.B. Dienstag ein Feiertag ist soll es mir den Montag vorher als Freitag zählen, da hier keine Übernachtung anfällt. Ich hoffe ihr könnt mir noch folgen...

Danke nochmals

Thomas

[RPP63]

Moin Thomas!

Hmh ich kann leider meinen obigen Beitrag nicht editieren, warum auch immer...

Geht nur exakt 60 Minuten nach Erstellung, also knapp daneben. ;)
Hier würde ich tatsächlich eine UDF nehmen, dürfte übersichtlicher sein.
Dazu muss VBA natürlich gestattet sein.

Gruß Ralf

[Thomas78]

Hallo Ralf,

danke für die schnelle Antwort! 

Ich hab mal ein Bild beigefügt wie es momentan aussieht. 
Dumme Frage UDF heisst VBA Code oder wie?

VBA wäre erlaubt, allerdings sollte die Darstellung wie im Bild erhalten bleiben.

Schöne Grüße

Thomas

[lupo1]

Hänge bitte abgestrippte Tabelle mit Wunschergebnissen an. Habe null Bock auf "Feiertage im Kopf denken" (es gibt z.B. 1.+2.Weihnachtstag hintereinander)

[Thomas78]

Hallo anbei die gewünschte Datei.

Im Kalender auf der rechten Seite gibt man u = Urlaub, k = Krank und 2 für 2 Tagestouren ein, die Spesen werden in der Übersicht auf der linken Seite errechnet. Die Feiertage werden aus der Übersicht ab K41 entnommen.
Wenn also ein Feiertag auf einen Wochentag fällt soll Excel prüfen ob der Tag vorher ein Arbeitstag war und wenn ja soll für diesen Tag der Spesensatz nur 12 Euro betragen (Sprich bei Spesen in die erste Spalte gezählt werden.) 

Allerdings auch nur bei Eckert Heinz, Kerl Andre, Rodriguez Philip und Schuß Hans. Die anderen bleiben davon unberührt.

Schöne Grüße Thomas

[schauan]

Hallo,

ich habe jetzt nicht in Deine Tabelle geschaut. Im Prinzip geht das so. Du definierst einen Bereich mit den Feiertagen. Dann prüfst Du einfach nur mit ZÄHLENWENN, ob das nächste Datum im Bereich Feiertage enthalten ist. Bei der Prüfung nimmst Du >0, da auch mal zwei Feiertage auf einem Tag liegen können.  

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B
1	Feiertage	Ist morgen einer
2	01. Jul	Ja

Name	Bezug
Feiertage	=Tabelle1!$A$1:$A$5

Zelle	Formel
B2	=WENN(ZÄHLENWENN(Feiertage;HEUTE()+1)>0;"Ja";"Nein")

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

[Thomas78]

Hallo Andre,

vielen Dank für den Denkanstoß !

Ich schau mal ob ich meine Formeln folgendermaßen ändern kann:

Wenn ein Feiertag im aktuellen Monat auf Mo-Do fällt dann zähl zum bisherigen Ergebnis in Zelle xy die entsprechende Anzahl hinzu und ziehe in Zelle zx die entsprechende Anzahl ab...

Ich schau mal wie weit ich selbst komm (und meld mich falls ich es doch nicht schaffe...)

Vielen Dank und schöne Grüße

Thomas

[Thomas78]

So jetzt bin ich schon so weit...

Ich habe mit folgender Formel zumindest einen Teilerfolg gehabt:

=SUMMENPRODUKT((MONAT(Feiertag)=MONAT(F2))*(WOCHENTAG(Feiertag;2)<6))

Allerdings müsste ich das Ergebnis weiter einschränken. 
Es soll mir die Anzahl der Feiertage (Namensbereich: Feiertag) des Monats (F2) anzeigen die auf einen Dienstag, Mittwoch, Donnerstag oder Freitag fallen.

Kann mir hier bitte jemand nochmal weiterhelfen?

Vielen Dank nochmal !

Thomas

[schauan]

Hallo Thomas,

irgendwie machst Du da was falsch.

Meine Formel schaut so aus:
=WENN(ZÄHLENWENN(Feiertage;HEUTE()+1)>0;"Ja";"Nein")

Du müsstest bei Dir HEUTE() durch die Zelle ersetzen, wo das Datum steht, falls es F2 ist dann durch F2
=WENN(ZÄHLENWENN(Feiertage;F2+1)>0;"Ja";"Nein")

Im Bereich Feiertag stehen ja alle Feiertage des Jahres.

Bei mir war der Bereich so definiert:
Feiertage
=Tabelle1!$A$1:$A$5

Also könnte ich da 5 Feiertage unterbringen. Wenn Du mehr hast, dann ist Dein Bereich entsprechend größer.

Entsprechend bringt
MONAT(Feiertag)
nix gescheites