Excel-Formeln in OOo

[Beinhart63]

Moin, Moin zusammen,

hab natürlich (wie sollte es auch anders sein) ein Problem:

Zur Dienstzeiterfassung hatte ich mir eine xls-Datei erstellt, die auch als solches in Excel funktioniert. Da ich aber auf OOo umsteigen will möchte ich die Datei auch weiter nutzen, nach OOo konvertieren. Leider musste ich feststellen, dass die bedingte Formatierung nicht übernommen wurde und allerdings nur eine Formel zur Feiertagsberechnung: (=WENN(H5<>"";WENN(ISTZAHL(VERGLEICH((TEXT(D5;"TT.MM.JJ")*1);FT_1;0));N5;0)+WENN(ISTZAHL(VERGLEICH((TEXT(E5;"TT.MM.JJ")*1);FT_1;0));O5;0);"")

Ich habe in einer Extraspalte die Feiertage des Jahres aufgelistet und lasse berchnen an welchen Feiertagen ich arbeite (auch über Nacht > vor 0:00 Uhr und nach 0:00 Uhr).
Leider steht in meiner Berechnungstabelle #Name?

Kann mir da jemand helfen?

OOo 2.1; Excel 2003; Win VISTA Ultimate

[Toxitom]

Hey Beinhart63,

tia, die Fehlermeldung rührt wahrscheinlich von dem Ausdruck

...;FT_1;...

der gleich zweimal in der Formel auftaucht.
Was soll das sein? Ich könnte damit auch nix anfangen?
Wenn es sich um einen "benannten Bereich" handelt, prüf mal, ob es diesen wirklich gibt.

Gruss
Thomas

[Beinhart63]

@Toxitom,

du hast Recht dieser Ausdruck steht so auch bei Excel nicht, sondern ...;FT;... , hab das umgeändert, brachte aber auch keine Änderung.

FT steht für die Feiertagstabelle in der Spalte S ab Teile 4.

Wie gesagt bei Excel klappts, soviel dazu, dass Formeln nicht 1:1 funktionieren.

Ich kann ja hier keine Daten hochladen, ich würde sie dir per e-Mail schicken...?!

[gripslund]

Hallo,

=WENN(UND(NETTOARBEITSTAGE($C5;$C5;$FREI.$C$2:$FREI.$C$15)=0;N5;N5>M5;M5>=$M5;WOCHENTAG($C5)<>1;WOCHENTAG($C5)<>7);N5-M5;WENN(UND(NETTOARBEITSTAGE($C5;$C5;$FREI.$C$2:$FREI.$C$15)=0;N5;N5<M5;WOCHENTAG($C5)<>1;WOCHENTAG($C5)<>7);1-M5;0))+WENN(UND(NETTOARBEITSTAGE($C6;$C6;$FREI.$C$2:$FREI.$C$15)=0;N5;LINKS($D5)="N";WOCHENTAG($C6)<>1;WOCHENTAG($C6)<>7);WENN(UND(NETTOARBEITSTAGE($C6;$C6;$FREI.$C$2:$FREI.$C$15)=0;N5;N5<M5;WOCHENTAG($C6)<>1;WOCHENTAG($C6)<>7);N5;N5-M5);0)

Das benutze ich. Dabei stehen in "FREI.C2:C15" die Feiertage (extra Blatt), desweiteren:
c5 - Datum
m5 - Beginn der Arbeitszeit
n5 - Feierabendzeit

Es werden nur Feiertage berücksichtigt, die auf einen Werktag fallen, also nicht Samstag oder Sonntag.

Bedingte Formatierung:
1. WOCHENTAG(C5)=7 -> hellrot
2. WOCHENTAG(C5)=1 -> rot
3. NETTOARBEITSTAGE(C5;C5;$FREI.$C$2:$FREI.$C$15)=0 -> dunkelrot

Ich hoffe, das hilft ein wenig

gripslund

[Beinhart63]

@gripslund Hallo,

ohne das jetzt mal ausprobiert zu haben die Frage: Berücksichtigt das auch wenn ich über Nacht arbeite, also Feiertag bis 0:00 Uhr dann kein Feiertag mehr und umgekehrt?

[Stephan]

Bei mir funktioniert die Formel in Calc (oder besser liefert zunächst einmal kein e Fehlermeldung) sobald sie sich in einer Datei mit dem benannten Bereich "FT_1" befindet.

Bedingte Formatierungen sollten auch ohne Weiteres gehen, einzig könnte es Probkeme geben falls DEine Bedingungen Formeln sind und dort im Konkreten Inkompatibilitäten bestehen.

Was für Bedingungen für die bedingte Formatierung verwendest Du?



Gruß
Stephan

[gripslund]

Hallo Beinhart63,

meine Formel berücksichtigt, wenn Du in den Feiertag "hinein"-arbeitest (ab 00:00) oder wenn Du "heraus"-arbeitest (bis 00:00). Dazu verwende ich (siehe Formel) noch die Zelle D5, in der das linke Zeichen ein "N" für Nachtschicht sein muss.
Ich verbürge mich aber nicht für die in ALLEN Lebenslagen korrekte Funktion - bei mir geht's aber bisher.

Wenn doch jmd. einen Fehler findet - bitte melden, Danke.

Gruß.

gripslund

[Karolus]

Hallo Gripslund

Respekt ! Hast du dieses Formelmonster selbst geschrieben ?

Ohne das ich da jetzt gleich vollständig durchblicke, fällt mir eine kleine Optimierungsmöglichkeit auf, in der Formel befinden sich mehrmals die beiden Parameter:
WOCHENTAG($C5)<>1;WOCHENTAG($C5)<>7
die kannst du schadlos durch :
WOCHENTAG($C5;2)<6
ersetzen.

Gruß Karo

[Beinhart63]

@ Stephan

Bedingte Formatierung in Excel z.B. für Sonntage "=WENN($B5="So";1;0)" Frabe ROT.

@Karolus und gripslund

das funktioniert soweit ganz gut, danke dafür!

Kommt nur noch etwas hinzu undzwar, sollen die Std. die "Feiertags" gearbeitet werden oder wurden und die "Sonntags" gearbeitet werden oder wurden addiert werden, getrennt versteht sich. Auch vor und nach 0:00 Uhr.

[Stephan]

Bedingte Formatierung in Excel z.B. für Sonntage "=WENN($B5="So";1;0)" Frabe ROT.

Dann kann ich nur bestätigen das das funktionieren sollte. (ggf. ist es ein Fehler der ausschließlich due Version 2.1 betrifft, konkret weiß ich davon aber nichts).
(Es funktioniert natürlich nur wenn "So" in der Zelle steht und nicht etwa ein als Wochentag formatiertes Datum, aber das ist in Excel genauso.)

Diese Formatirung ist im Übrigen nicht sehr sinnvoll, da ja keine Reaktion des Zellwertes erfolgen soll, also reicht:
=WENN($B5="So")



Gruß
Stephan

[Beinhart63]

@ Stephan,

ich habe vergessen zu erwähnen, dass die bedingte Formatierung "=WENN($B5="So";1;0)" auf mehrere Spalten in der gleichen Zeile angewndet wird, sodass 6 Spalten eingefärbt werden. Sonst funktioniert das nur mit einer Spalte (in Excel).

Hier die Formel in Excel für "Sonntagsarbeit": =WENN(H5<>"";WENN(WOCHENTAG(D5;2)=7;N5;0)+WENN(WOCHENTAG(E5;2)=7;O5;0);"")
funktioniert in OOo auch nicht

[gripslund]

Hallo Beinhart63,

meine Formel für Sonntagsarbeit lautet:

=WENN(WOCHENTAG($C5)=1;WENN(UND(N5;N5>M5;M5>=$M5);N5-M5;WENN(UND(N5;N5<M5);1-M5;0));0)+WENN(WOCHENTAG($C5+1)=1;WENN(UND(N5;M5+$AU5+$AW5>1);WENN(UND(N5;N5<M5);N5;N5-M5));0)

C5 - Datum
M5 - Anfang (Zeit)
N5 - Ende (Zeit)
AU5 - Arbeitszeit
AW5 - Pause

Das sind die von mir verwendeten Zellen, dazwischen sind noch andere (Feiertag, Samstag, Sonntag,...). Deshalb erscheint die Pause erst in AW5!


Hallo Karolus,

ja, die "Feiertagsformel" ist von mir. Hat aber auch lange, lange, .... gedauert, bis sie funktionierte
Die Sache mit "WOCHENTAG($C5;2)<6" ist ja aber auch genial! Hätte ich auch drauf kommen können. Vielen Dank für den Tipp, werde ich integrieren.

Habt Spaß, und fahrt vorsichtig!

gripslund

[Stephan]

ich habe vergessen zu erwähnen, dass die bedingte Formatierung "=WENN($B5="So";1;0)" auf mehrere Spalten in der gleichen Zeile angewndet wird, sodass 6 Spalten eingefärbt werden.

weiß ich jetzt nicht wozu das wichtig ist, eine bedingte Formatierung bezieht sich auf Zellen egal ob diese in ihrer Summe ganze Spalten repräsentieren oder nicht.

Sonst funktioniert das nur mit einer Spalte (in Excel).

tut mir leid, aber was soll mir dieser Satz sagen?

Hier die Formel in Excel für "Sonntagsarbeit": =WENN(H5<>"";WENN(WOCHENTAG(D5;2)=7;N5;0)+WENN(WOCHENTAG(E5;2)=7;O5;0);"")
funktioniert in OOo auch nicht

Inwiefern - liefert die eine Fehlermeldung oder kein richtiges Ergebnis oder was sonst?
Ich kann hier zunächst nichts Auffälliges bemerken.



Gruß
Stephan

[Beinhart63]

Hallo gripslund,

meine Tabelle beinhaltet folgendes:

D5 - Beginn (Datum & Zeit; Format: 01.01.07 00:00)
E5 - Ende (Datum & Zeit; Format: 01.01.07 00:00)
H5 Std. =WENN(D5*E5>0;(E5-D5)*24;""))

Pausen gibt es nicht

N5 - Std. vor 00:00 Uhr =WENN(H5<>"";MIN(H5;(MAX(1;(TEXT(E5;"hh:mm")*1))-(TEXT(D5;"hh:mm")*1))*24);"")
O5 - Std. nach 00:00 Uhr =WENN(H5<>"";WENN(N5<H5;H5-N5;0);"")
P5 - Sonntage =WENN(H5<>"";WENN(WOCHENTAG(D5;2)=7;N5;0)+WENN(WOCHENTAG(E5;2)=7;O5;0);"")
Q5 - Feiertage =WENN(UND(NETTOARBEITSTAGE($C5;$C5;$Feiertage.$B$2:$Feiertage.$B$15)=0;E5;E5>D5;D5>=$D5;WOCHENTAG($C5;2)<6);E5-D5;WENN(UND(NETTOARBEITSTAGE($C5;$C5;$Feiertage.$B$2:$Feiertage.$B$15)=0;E5;E5<D5;WOCHENTAG($C5;2)<6);1-D5;0))+WENN(UND(NETTOARBEITSTAGE($C6;$C6;$Feiertage.$B$2:$Feiertage.$B$15)=0;E5;LINKS($D5)="E";WOCHENTAG($C6)<>1;WOCHENTAG($C6)<>7);WENN(UND(NETTOARBEITSTAGE($C6;$C6;$Feiertage.$B$2:$Feiertage.$B$15)=0;E5;E5<D5;WOCHENTAG($C6)<>1;WOCHENTAG($C6)<>7);E5;E5-D5);0)

Bei Feiertage steht immer noch 0,00

irgendwas stimmt da noch nicht

[Beinhart63]

@gripslund,

noch'n Zusatz: Die Sonntage zeigen auch nur 0,00 an...