Office: (Office 365) Arbeitstage zählen bis zu einem bestimmten Datum ...

Hallo,
ich habe von meinem Kollegen eine Anfrage erhalten, ob ich nicht mal ausrechnen kann, wie lange er noch Arbeiten muss bis er in Rente gehen kann. Beispieldaten.:
Sein letzter Tag wäre 31.12.2026, Urlaub 32 Tage, Feiertage von Land Brandenburg.
Ich habe folgendes versucht:
=+NETTOARBEITSTAGE.INTL(B2;B1;1;I3:L34)

B1=Zieldatum
B2=aktuelles Datum
I3:L34 wären die noch zu nehmenden Urlaubstage und Feiertage von 2025 sowie Urlaubstage und Feiertage von 2026.

Komme da bei Stand Heute (29.07.25) auf 302 Arbeitstage.

Nun meine Frage, ist die Formel richtig angesetzt oder gibt's da bessere Lösungen.
Muss dazu sagen, bitte ohne VBA Lösungen.
Vielen Dank für jegliche Tipps und Hinweise.

 

.. ich komme, Stand Heute bis 31.12.26, ohne VBA und mit PQ, bei insgesamt 64 noch zu berücksichtigenden Urlaubstagen (falls also noch kein Urlaub genommen wurde) auf 298 Arbeitstage (mit Brandenburger Feiertagen und ohne Sa/So).

 

Hi Fred0,

die Formel ergibt mit Feiertagen 362 Arbeitstage. Abzüglich Jahresurlaub 2026 von 32Tagen wären es 330.
Da niemand hier weiß, wieviel noch zu nehmende Urlaubstage vorliegen, kannst Du selbst prüfen ob das Ergebnis stimmt, sollte also passen.

 

Moin!
Ich komme auf das gleiche Ergebnis wie Edgar.
(heute natürlich 329)

	A	B	C	D	E	F	G
1	heute	Rente	Urlaub	Rest_AT		01.01.2025	01.01.2026
2	30.07.2025	31.12.2026	32	329		18.04.2025	03.04.2026
3						20.04.2025	05.04.2026
4						21.04.2025	06.04.2026
5						01.05.2025	01.05.2026
6						29.05.2025	14.05.2026
7						08.06.2025	24.05.2026
8						09.06.2025	25.05.2026
9						03.10.2025	03.10.2026
10						31.10.2025	31.10.2026
11						25.12.2025	25.12.2026
12						26.12.2025	26.12.2026

Zelle	Formel
F1	=LET(Jahre;SEQUENZ(;2027-JAHR(HEUTE());JAHR(HEUTE())); FFT;DATUM(Jahre;1;{1;121;276;304;359;360})+WENN(MONAT(DATUM(Jahre;2;29))=2;{0;1;1;1;1;1}); OFT;RUNDEN((TAG(MINUTE(Jahre/38)/2+55)&".4."&Jahre)/7;)*7-6+{-2;0;1;39;49;50}; SORTIEREN(VSTAPELN(FFT;OFT)))
A2	=HEUTE()
D2	=NETTOARBEITSTAGE(A2;B2;F1#)-C2

Gruß Ralf

 

In Freds Formel steht I3:L34, das sind 32 Zeilen (=Urlaub?).
"Richtiger" dürfte unser Ansatz sein, den Urlaub von den ermittelten Nettoarbeitstagen abzuziehen.

 

bei 32 Tagen Urlaub komme ich auch aufs gleiche Ergebnis. Ich hatte lediglich den vollen Urlaub beider Jahre abgezogen...

 

Ja, sorry, hatte ich überlesen!
Ich käme jedoch nicht auf die Idee, so etwas mittels PQ zu machen.
Ohne Hilfstabelle Feiertage auch in einer einzigen Formel:

	A	B	C
1	letzter_AT	Urlaub	Rest_AT
2	31.12.2026	32	329

In C2:

Code:

=LET(Jahre;SEQUENZ(;2027-JAHR(HEUTE());JAHR(HEUTE()));
FFT;DATUM(Jahre;1;{1;121;276;304;359;360})+WENN(MONAT(DATUM(Jahre;2;29))=2;{0;1;1;1;1;1});
OFT;RUNDEN((TAG(MINUTE(Jahre/38)/2+55)&".4."&Jahre)/7;)*7-6+{-2;0;1;39;49;50};
FT;SORTIEREN(VSTAPELN(FFT;OFT));
NETTOARBEITSTAGE(HEUTE();A2;FT)-B2)

Gruß Ralf

 

Ich hatte es halt schon fertig vorliegen..
Deine Feiertagsberechnung ohne Hilfstabelle sieht ja recht abeneuerlich aus. Ist die jetzt auf Brandenburg zugeschnitten oder kann man die auch für Bayern, Bawü, Hessen,.... verwenden?

 

Hi,

kann man nicht für alle Bundesländer verwenden, es fehlen z.B. der 6.1. für BW, BY, S-A, der internationale Frauentag, Fronleichnam usw.

 

Hallo Excel-Fan's,

diese 3 Zwischenschritte:
FFT;DATUM(Jahre;1;{1;121;276;304;359;360})+WENN(MONAT(DATUM(Jahre;2;29))=2;{0;1;1;1;1;1});
OFT;RUNDEN((TAG(MINUTE(Jahre/38)/2+55)&".4."&Jahre)/7;)*7-6+{-2;0;1;39;49;50};
FT;SORTIEREN(VSTAPELN(FFT;OFT));

kann man sich schenken, wenn man eine bundeslandtypische Excel-Feiertagstabelle pflegt und hier: NETTOARBEITSTAGE(HEUTE();A2;FT)-B2)
statt FT die Feiertagstabelle übergibt.

Gruß von Luschi
aus klein-Paris

 

Ist ja alles richtig, nur …
… meine "schlanke" Formel funktioniert auch korrekt, wenn ein heute 20-Jähriger ermitteln will, wie viele Tage es bis zum Renteneintritt am 30.11.2072 sind.
Den Urlaubsanspruch nehme ich mal mit 30 Tagen an, ermittelt mittels 30 / 12 * Monate

	A	B	C
1	letzter_AT	Urlaub	Rest_AT
2	30.11.2072	1420	10541

Zelle	Formel
B2	=DATEDIF(HEUTE();A2;"m")*30/12
C2	=LET(Jahre;SEQUENZ(;JAHR(A2)-JAHR(HEUTE())+1;JAHR(HEUTE())); FFT;DATUM(Jahre;1;{1;121;276;304;359;360})+WENN(MONAT(DATUM(Jahre;2;29))=2;{0;1;1;1;1;1}); OFT;RUNDEN((TAG(MINUTE(Jahre/38)/2+55)&".4."&Jahre)/7;)*7-6+{-2;0;1;39;49;50}; FT;SORTIEREN(VSTAPELN(FFT;OFT)); NETTOARBEITSTAGE(HEUTE();A2;FT)-B2)

Die ist konkret auf Brandenburg zugeschnitten.
Für andere Länder muss die Matrix der festen Feiertage sowie die der beweglichen ergänzt bzw. verkürzt werden.
Aber wie geschrieben:
Die Ergebnis-Matrix spillt je nach Dauer des Zeitraums beliebig in der Breite (aka Jahre).
Edit:
Dies ist deshalb wichtig, weil es feste Feiertage gibt, die (auch) je nach Jahr mal an einem Wochenende liegen und deshalb nicht berücksichtigt werden dürfen.

Gruß Ralf

 

Was glaubst Du, wie abenteuerlich für mich manch PQ-M-Code aussieht?

Im Prinzip ist es aber einfach und logisch.
{1;121;276;304;359;360} ist der Vektor mit den Daten der festen Feiertage.
=DATUM(2025;1;121) ergibt in Nicht-Schaltjahren den 1. Mai.
Der 304. Januar ist der 30.11. (Reformationstag), Allerheiligen wäre der 305.

Deshalb benötige ich einen Schaltjahr-Korrektur-Vektor.
+WENN(MONAT(DATUM(Jahre;2;29))=2;{0;1;1;1;1;1})
(wenn DATUM(2024;2;29) im Februar liegt, habe ich ein Schaltjahr, weshalb der 1. Mai nicht der 121. Januar ist, sondern der 122.)

Ähnliches gilt für die beweglichen Feiertage, die ja alle von Ostern abhängen {-2;0;1;39;49;50}.
Mit Fronleichnam wäre das dann {-2;0;1;39;49;50;60}
Da die beweglichen Feiertage immer nach dem 28./29. Februar liegen, schenke ich mir dort die Schaltjahreskorrektur.
Ich habe hier mal die bekannte Osterformel von Excelformeln.de genommen, die in diesem Jahrhundert korrekt rechnet.
Ich habe in meinem Fundus auch ein "Monster" welches ein Gleichungssystem von Gauß (erweitert durch Lichtenberg) auswertet.

Two Cents
Ralf

 

Upps, vielen Dank für die vielen Beiträge, da muss ich mich erstmal durcharbeiten.
Wenn noch Fragen sein sollten, bitte melden.

Melde mich später noch mal.