Excel 2003 Tage zählen

Computer: Software & Programmierung Office Software
#21

hi,

In Q steht irgendwas, aber nicht die Arbeitstage.
Dez. '23 sind 17 AT, eingetragen sind 22.
Das wäre doch auch viel besser, wenn man sich nach den Wochentagen richtet und die Formeln einmal runter zieht. oder nicht?

AJ erscheint mir seltsam. Rechne die Wochenstunden mal manuell zusammen. Da stimmt doch was bei den letzten 2 Wochen nicht.

na egal:
Versuch mal:
=WENN(DATUM(JAHR(A2);MONAT(A2)+1;1)-1=A2;(ZÄHLENWENN(INDIREKT("J"&(ZEILE()-TAG(DATUM(JAHR(A2);MONAT(A2)+1;1)-2))&":J"&ZEILE());">0")-ZÄHLENWENN(INDIREKT("M"&(ZEILE()-TAG(DATUM(JAHR(A2);MONAT(A2)+1;1)-2))&":M"&ZEILE());"*"));"")

Nicht irgendwo reinkopieren!!!
Diese Formel ist für Zeile 2 bestimmt und NUR für Zeile 2. Danach füllen.
Oder die Zeilennummern der Formel alle manuell anpassen und dann füllen.
Das ist gerade der Sinn, dass sie unabhängig ist und in jeder Zeile im Prinzip gleich ist. bis eben auf die Bezugszelle.
die Nummer in der Formel muss immer mit der Zeile überein stimmen. Mehr nicht.

Unsicher ist, ob Zählenwenn(Bereich;"*") klappt als beliebiger Text.
OpenOffice unterstützt das so nicht, Excel sollte aber.
Da ich aber kein Excel besitze, kann ich’s nicht testen.

grüße
lipi

#22

diese Formel gibt mir das Ergebnis von Q wieder. Q kannst du auf dem foto nicht sehen, da ich Q erst ab 2015 angelegt hatte und noch nicht unten angekommen bin.

die zeiten und arbeitstage stimmen. kurze erklärung dazu…

der dez 23 hat (inzwischen angepasst 23 Arbeitstage)

21 Reguläre Arbeitstage, zzgl. 2 Samstage, inkl. 2 Feiertage und 2 Urlaubstage.

Die Feiertage, Urlaubstage, Stundenausgleich, Schulungen und Krank sind mit der vollen Stundenzahl versehen, da diese rellevant ist für den 16 und 24 Wochen Stundenschnitt.

Davon ausgenommen ist nur der „Freizeitausgleich“

Tatsächlich anwesend war ich an 18 dieser 23 Tage.

Die spalte AJ stimmt, was du siehst sind die stunden in rechenbaren zahlen… wenn ich es so richtig ausgedrückt habe. habe dir jetzt mal die andere spalte sichtbar gemacht. „Rechenbare“ stundenwerte benötige ich um die Stundenlisten mit der Firma abzugleichen, die geben nur diese aus.

ist für mich inzwischen auch nicht mehr ganz einfach das alles zuzuordnen… diese tabelle existiert inzwischen schon über 12 Jahre. sie ist seitdem einmal gekürzt worden(halbiert) und gefühlte 1000x angepasst worden… das machts natürlich nicht einfacher. Aber sie ist auch sehr koplex.

arbeitstage2
arbeitstage21185×627 369 KB

hast du einen funktionierenden hoster? kann dir die tage gerne die ganze datei senden damit du durch das chaos durchsiehst… :wink:

#23

hi,

ich glaub, ich verstehe gerad eher weniger, was eigentlich nun gewollt ist.

und war es nicht das Ziel, dies eben zu vereinfachen?

grüße
lipi

#24

Puh, immer mehr Fragen.
Wovon ich ausgehe:

  • Tage mit Eintragung in Spalte M haben immer Arbeitszeiten
  • Du möchtest die letzten 12 ganzen Monate haben, nicht die letzten 11 ganzen und den aktuellen Monat oder die letzten 12 ganzen und den aktuellen Monat, also momentan 01.01.2023 bis 31.12.2023 und nächsten Monat dann 01.02.2023 bis 31.01.2024

Irgendeine neue Spalte als Hilfsspalte ab Zeile 2:

=WENN((A2<(DATUM(JAHR(HEUTE());MONAT(HEUTE());1)))*A2>=(DATUM(JAHR(HEUTE())-1;MONAT(HEUTE());1));ANZAHL2(D2)-ANZAHL2(M2);"")

Und in der Zelle wo das Ergebnis ausgegeben werden soll einfach SUMME() über die Hilfsspalte.

#25

hi Tobias,

ja, wir nähern uns.

ich möchte quasi am ende eines jeden monats die Tatsächlichen Tage haben an denen wirklich gearbeitet wurde der letzten 12 Monate. Alle Tage mit dem Kürzel A in Spalte B abzüglich der spalten R,S,T,U im gewählten Zeitraum. (zusammengefasst in spalte Q)

Tage mit eintrgagungen in M haben auch arbeitszeiten. allerdings hatte ich irgendwann mal die spalte B dafür angelegt. Tage mit dem Kürzel A sind Tage die in die berechnung mit einbezogen werden müssen. Die spalte A bezieht ihre daten aus der spalte J, welche die nummerischen zeiten beinhaltet. Diese sind auch an gearbeiteten Wochenenden vorhanden. Wochenenden haben keinen Eintrag in Spalte M.

arbeitstage4
arbeitstage4672×558 316 KB

Deine Formel bringt leider nix… die zellen bleiben leer.

Eingefügt habe ich sie in AP2 und nach unten gezogen. In AR habe ich dann die summe gesetzt… wobei ich das nicht ganz verstehe, da ja AP auch nix anzeigt… :wink:

arbeitstage3
arbeitstage31203×632 308 KB

@littlepinguin

mit sicherheit lässt sich da noch vieles vereinfachen an der liste. aber diese liste hat nicht nur einfach zeiten zu erfassen… sie hat diverse aufgaben. das macht es jetzt nicht gerade einfacher.

  • berechnung der schnittstunden von 16 und 24 Wochen (wegen der gesetzlichen vorgaben)
  • Ausgabe der Wochen- und Monatsstunden
  • Zählung von Überstunden, Urlaubs- und Krankentagen
  • Ausgabe der Zeiten in Mathematischen Werten(zur kontrolle mit den Lohnscheinen)

und eigentlich gibt sie noch den zu erwartenden verdienst wieder(der sich aus % zuschlägen, prämien und stundenlohn zusammensetzt), aber die daten sind gerade nicht sichtbar, und weit weg von perfekt… :wink:

neu soll werden: Ausgabe der Jährlichen Tatsächlich gearbeiteten Tage

ich weiß, es klingt irgendwie alles sehr umständlich.
eingegeben werden am ende nur anfangszeit, endzeit und pausenlänge.

gelegentlich müssen halt noch sofern vorhanden die daten im bemerkungsfeld eingegeben werden… der rest soll alleine im hintergrung passieren und ausgeblendet sein.

#26

hi,

wie gesagt, ich bin unsicher ob Zählenwenn mit "*" geht.

Wenn aber die Arbeitstage ohnehin in Q berechnet werden, dann nimm doch bitte diese Formel dafür.
Das muss man ja nicht alles neu erfinden, gerade bei einer so unübersichtlichen Tabelle.

Ausführlich:
Ich verstehe den Gedanken der Eingaben nicht.
Wozu gibt es Spalte B?
Da wird A = Arbeitstag auch ausgegeben, wenn Urlaub oder sonstiges war.
Das ergibt sich aus C aber ohnehin. Es scheint aber so, als würde es manuell Editiert werden, wenn am Samstag gearbeitet wird.

Wenn M ne dropdownliste hat, was so aussieht und alle Einträge bekannt sind, könnte man das wohl auch darüber auslesen. So viel wirds ja nicht sein.

Das scheitert bei mir jetzt ganz simpel am nicht vorhandenen Excel 2003. Ich kanns nicht prüfen.
Du könntest mal schauen, ob =Zählenwenn(M3094:M3119;"*") was ausspuckt.
Sonst gibt es noch nen andere Sache, die in Excel 2003 nicht geht.
Ausgeblendet ist jetzt auch J, auf diese Spalte bezieht sich die Formel aber. Auch da könnte ein Fehler liegen.
Jetzt gibt es Plötzlich H mit der Arbeitszeit.

Hier bitte von dir eine Angabe, welche Spalten denn nun verlässlich genutzt werden können.

ja…
ich habe bei mir zum Test mehrere Spalten mit Teilstücken der Formel.
Nur haben wir das Problem, dass ich nicht an deinem Rechner sitz und auch nicht dein Excel habe.
Daher ist es bei der kompletten Formel leider so, dass nicht auf einen speziellen Fehler geschlossen werden kann.

Die Formel soll aber Grundsätzlich die Arbeitstage des Jahres am Ende des Monats ausspucken. So wie gewünscht.
Macht sie bei mir soweit auch.

vergiss das.
Nur taggenaue Berechnungen gehen.
Alles andere wird fehleranfällig.
Nichts, aber auch gar nichts, irgendwie schon vorab berechnen. Es braucht derartige Hilfszellen letztlich auch nicht.
Und wenn doch, dann Taggenau.
Berechnungen zwischendrin sind fürs ‚nach unten ziehen‘ der Tabelle maximaler Scheiss.
Dann lieber jedn Tag berechnen lassen aber nur an bestimmten Tagen anzeigen.

grüße
lipi

#27

Edit

was vielleicht hilfreich ist, damit du überhaupt helfen kannst.

Was die Formel machen soll:

Wenn A übereinstimmt, mit dem Tag vor dem ersten des nächsten nächsten Monats, was quasi dem letzten des aktuellen Monats entspricht, unabhängig ob 28, 30 oder 31,
dann zählt er die Anzahl der Einträge in Spalte J und zieht die Anzahl der Einträge in M ab. (hier steckt vermutlich das aktuelle Problem)

Den Bereich dafür, Beispielsweise (A1001:A1365), holt er sich vom Datum und nutzt dafür dem Umstand, dass Datumsangaben und Zeilen fortlaufend Nummeriert sind.
Hier also ab der aktuellen Zeile, in der die Formel steht, bis Zeilennummer minus letzter Tag des Monats. (das führt zwar dazu, dass sie am 15. des Monats falsch rechnet, aber da wird sie nicht angezeigt)

Dies dann auf das komplette Jahr auszuweiten ist daher einfach.
Monatsweise ist es aber deutlich einfacher zu prüfen. Daher aktuell (und weil ich zu blöd zum lesen war) eben monatsweise.
Wenn das funktioniert, ist das anpassen auf einen anderen Zeitraum nur Formsache.

#28

fehler1
fehler11338×617 407 KB

das wirft dann aber wieder einen fehler auf, da es auch vorkommen kann das ein arbeitstag mit tatsächlicher anwesendheit eine Bemerkung im Bemerkungsfeld hat. (siehe Q)

zum Zählen der Bezahlten Tage , siehe Spalte P

das war nur der filter… der ist teporär.

ja, 7

Erklärung 1
Erklärung 1944×368 272 KB

A - Datum
B - Bezahlter Tag (Arbeitstag) =WENN(J3105<>"";„A“;„W“)
D,E, F - Eingabefelder für die Uhrzeit
G - Sollarbeitszeit (Manuell befüllt für jahre im vorraus, muss geändert werden wenn sich der tarifvertrag ändert)
H - Istarbeitszeit =WENN(D3105<>"";(E3105-D3105)-F3105;"")
I - Sollarbeitszeit Nummerisch =WENN(D3105<>"";(G3105&24);"")
J - Istarbeitszeit Nummerisch =WENN(D3105<>"";(H3105&24);"")
K - Überstunden Nummerisch =WENN(D3105<>"";(J3105-I3105);"")
L - Überstunden =WENN(D3105="";"";WENN(H3105<>G3105;H3105-G3105;WENN(H3105=G3105;"")))
M - Bemerkungen (wiederkehrend)
O - Monatsstunden =SUMME(J3077:J3107)
P - Arbeitstage (Theoretisch) =ZÄHLENWENN(B3077:B3107;„A“)
Q - Arbeitstage Anwesend =SUMME(P3107-R3107-S3107-T3107-U3107)
R - Urlaubstage =ZÄHLENWENN(M3077:M3107;„URLAUB“)
S - Feiertage =ZÄHLENWENN(M3077:M3107;„FEIERTAG“)
T - Freizeitabbau =ZÄHLENWENN(M3077:M3107;„FREIZEITABBAU“)
U - Krank =ZÄHLENWENN(M3077:M3107;„KRANK“)
AD - Arbeitsstunden zur schnittberechnung =WENN(M3105=„URLAUB“;8;WENN(M3105=„FEIERTAG“;8;WENN(M3105=„KRANK“;8;WENN(M3105=„FREIZEITABBAU“;0;J3105))))
AJ - Wochenarbeitsstunden Nummerisch =SUMME(AD3101:AD3107)
AK - 16 Wochen schnitt NUmmerisch =SUMME(AJ3002:AJ3107)/16
AL - 24 Wochen schnitt Nummerisch =SUMME(AJ2940:AJ3107)/24
AN - Überstunden =SUMME(O3107-(P3107&Bedingungen!B6))
AO - Zulagen für Überstunden =SUMME(O3107-(P3107&8))
hab das sternchen in den formeln mal durch & ersetzt… wurde im beitrag nicht angezeigt

so, jetzt habe ich mal versucht das alles zu erklären… sehr viel inputt… :slight_smile:

#29

hi,

dann bin ich jetzt mal ratlos.
Bei mir geht die Formel.
bleibt noch, dass A eigentlich ein Text ist.

kannst du mal ein Bild zeigen, auf dem die Formel ersichtlich ist, also die Zelle ausgewählt ist? J und M bitte eingeblendet.
Was sagt: =DATUM(JAHR(A15);MONAT(A15)+1;1)-1

erlaub die Frage: bereitest du die Tabelle manuell vor?
Wie erreichst du, dass sich =ZÄHLENWENN(M3077:M3107;„URLAUB“) dem jeweiligen Monat anpasst?

oben gibt’s den Menüpunkt „Vorformatierter Text“ das </> Zeichen.
dann wird ein Text als Code behandelt und Sternchen (für kursiv) nicht verarbeitet sondern dargestellt.

grüße
lipi

#30

meintest du diese formel?

Formel3
Formel31350×635 389 KB

31.07.2015

wird manuell angepasst. Leider habe ich dafür noch keine lösung gefunden. alle felder bis auf datum und wochentage verschieben sich beim runter zeihen immer wieder. wenn du dafür eine lösung hast, wäre ich dir dankbar.

genauso kann man auch die arbeitstage zählen lassen, aber ich hatte gehofft das es diesmal eine elegantere lösung gibt.

TEST

    =WENN(DATUM(JAHR(A3046);MONAT(A3046)+1;1)-1=A3046;(ZÄHLENWENN(INDIREKT("J"&(ZEILE()-TAG(DATUM(JAHR(A3046);MONAT(A3046)+1;1)-2))&":J"&ZEILE());">0")-ZÄHLENWENN(INDIREKT("M"&(ZEILE()-TAG(DATUM(JAHR(A3046);MONAT(A3046)+1;1)-2))&":M"&ZEILE());"*"));"")
#31

hi,

ok gut.

da stehen doch die Tage des Monats drin.
An der richtigen Stelle und soweit überschaubar auch die korrekte Anzahl.
Was meintest du mit ‚bleiben leer‘?

so wie hier auch, den Monat errechnen. kannst du im Grunde 1:1 übernehmen.
Beispielhaft: statt =ZÄHLENWENN(M3077:M3107;„URLAUB“)
einfach =WENN(DATUM(JAHR(A2);MONAT(A2)+1;1)-1=A2;ZÄHLENWENN(INDIREKT("M"&(ZEILE()-TAG(DATUM(JAHR(A2);MONAT(A2)+1;1)-2))&":M"&ZEILE());"URLAUB");"")

Nachteil: Formel in jeder Zeile, auch wenn unnötig.
Vorteil: Einfach füllbar, ohne irgendwas zu beachten. Findet selbst den Monatsende und passt die Zählweise dem Monat an.

Die Vorteile überwiegen imo hier, dass dafür nutzlos Formen enthalten sind.
Wichtig ist nur, dass sich die jeweilige Formel immer auf die jeweilige Zeile bezieht. Könnte man zwar auch noch anpassen, aber dann wird’s echt unübersichtlich.

Generelle Frage: passt die Zählweise generell so? (abgesehen davon, was du mit leer gemeint hast oder ob du dich da verschaut hast)
Noch aufs Jahr anpassen, statt wie aktuelle, auf den letzten Monat?

… Und komm jetzt nicht mit der Schulung um die Ecke. sowas zu verschweigen ist echt nicht nett.

Edit: aber schlampig zu umgehen. Das würde zum Gesamtkonzept passen.
Dann ziehen wir einfach die Summe der Spalten R:U für den Monat ab. Das sind die Nichtanwesenheitstage, abzüglich diverser Eintragungen in M, die dann offenbar doch Arbeitstage sein sollen.

grüße
lipi

#32

Wenn es nur um die Anzahl der Einträge geht, entspricht M aber einer Zusammenfassung von R, S, T und U, richtig?

Du berechnest das Datum aus der Ist-Arbeitszeit?

Hast du die Formel bis Spaltenende nach unten gezogen oder nur teilweise? Es sollte eigentlich alles leer sein bis auf die letzten ganzen 12 Monate, also momentan 01.01.2023 bis 31.12.2024. In dem Zeitraum sollte überall 1 oder 0 eingetragen sein. Wenn das funktioniert, dann kriege ich die Änderung:

auch noch hin.

#33

nee, das war noch übrig geblieben von tobi seiner formel… :wink:

funktioniert ohne versatz… ist eine gute lösung… danke

passt… :wink:

genau. das ganze brauche ich jetzt monatlich für das letzte jahr…

na aber das war ja das was ich vorher schon immer schrieb. die spalte M ist als indiz für „nicht arbeiten“ unbrauchbar. und urlaubs oder krankheitstage fließen mit den grundstunden (8) komplett in die schnittarbeitszeit ein, das hat der gesetzgeber so beschlossen. einzigste ausnahme ist „Freizeitabbau“

#34

namd,

oh, sorry. Da bin ich durcheinander gekommen.

es ist aber die einzige, mit der man arbeiten kann.
Die Hilfsspalten R bis U ziehen sich das letztlich auch da raus.
Die Formel für den Urlaub auf Krank und co zu übertragen geht für dich, oder?
Ist denke ich ersichtlich, was zu ändern ist.

Für die Arbeitstage müsste es dann sein:
=WENN(DATUM(JAHR(A2);MONAT(A2)+1;1)-1=A2;(SUMME(INDIREKT("P"&MAX((ZEILE()-A2+DATUM(JAHR(A2)-1;MONAT(A2)+1;1));2)&":P"&ZEILE()))-SUMME(INDIREKT("R"&MAX((ZEILE()-A2+DATUM(JAHR(A2)-1;MONAT(A2)+1;1));2)&":U"&ZEILE())));"")

Die Formel bezieht sich jetzt auf die Summe der Spalte P und zieht davon die Summe der Spalten R:U ab. jeweils vom Ende des Monats bis zum Ersten, des nächsten Monats des vorherigen Jahres.

Zählt 1 Jahr zurück, oder bis zum Anfang der Tabelle. Je nachdem, was eher eintritt.
Damit hast du jetzt auch einen großen Bereich dich auszutoben, auch wenn sich noch was ändert.
Das könnte man so mit Hilfsspalten weiter anpassen und Änderungen einfließen lassen. In dem Fall einfach den Bereich R bis U erweitern und dort abziehen oder addieren.

Was ich nun nur unschön finde: du musst sicherstellen, dass P und R bis U korrekt sind.
Wenn du davon absiehst, das manuell zu machen und es als Formel mit runter ziehst, sollte das aber gehen.
Kost ja auch Zeit, das jeden Monat aktuell zu halten.

grüße
lipi