Zeiten addieren, wenn

c_classen_1e3f97 · 6. Februar 2002 um 06:46

Hallo Zusammen,

derzeit tüftele ich an einer Tabelle (Excel 2000 im Office-Pack)
die mir die Stunden (arbeitszeit) addieren soll.

Teilweise läuft es schon gut, mein Problemchen:
in der Spalte „Wochenarbeitszeit“ soll am „Sonntag“ die
Wochenzeit addiert werden, nicht am Samstag oder Montag
Das wäre einfach, wenn nicht der Sonntag dauernd wandern würde,
mal ist es der 01. Janaur, mal der 5. Januar … also muss ich
ein wenn-dann bauen - aber wie ?

Daran hängt auch, dass manche Wochen nur 1 oder 2, 3, 4
Arbeitstage im Monat haben, z.B. der Sonntag wäre der 4. Januar,
dann sind im Januar nur 2 Arbeitstage ?

Besten Dank für Tipps

Christian

Kristian_Zarse · 6. Februar 2002 um 09:42

Weekday()
Moin Christian,

ich verstehe zwar nicht so ganz, was Du vorhast, aber ich denke, daß Du bisher die Weekday()-Funktion übersehen hast. In der deutschen Version findest Du sie sicher auch sehr schnell in der Funktionsliste. Als Parameter kann man angeben, ob die Woche mit Sonntag oder Montag anfängt.

=Weekday(C4)
=Weekday(C4,1)
=Weekday(C4,2)
=Weekday(C4,3)

Wenn in C4 ein Sonntags-Datum steht, kommt in den Zeilen 1 und 2 eine 1 raus, in Zeile 3 eine 7 und in Zeile 4 eine 6. Guck einfach in die Hilfe.

Kristian

c_classen_1e3f97 · 6. Februar 2002 um 18:42

Hallo Kristian,

ich verstehe zwar nicht so ganz, was Du vorhast, aber ich
denke, daß Du bisher die Weekday()-Funktion übersehen hast.

Stimmmt, hatte ich übersehen. Aber leider hilft es bei meinem
Problem nicht wirklich weiter ;-(

ich habe in jeder Zeile
Datum - Wochentag - Beginnzeit - Endzeit - Pause - Tagesstunden -
Wochenstunden

So und wenn nun der Wochentag (Spalte 2) ein Samstag ist, dann
soll in Spalte 7 (Wochenstunden) die Summe der Tagesstunden
stehen. Aber nur am Samstag, nicht Sonntag und Montag . . .

Irgendwie bekomme ich das nicht hin ;-((

Ich muss es variabel setzen, da ja durch den Datumswechsel die
Woche jeweils mit einem anderen Datum startet.
Daran angelehnt kommt das näxte Problemchen, kann Excel „sehen“
ob die Woche alle Tage hat oder (wegen dem Datum) nur 4 oder
weniger Arbeitstage ? Wenn ich fest die letzten 6 Tage ausrechnen
lasse, gerate ich ja aus der Tabelle raus

Danke für Tipps

Christian

Anonym · 6. Februar 2002 um 21:26

So und wenn nun der Wochentag (Spalte 2) ein Samstag ist, dann
soll in Spalte 7 (Wochenstunden) die Summe der Tagesstunden
stehen. Aber nur am Samstag, nicht Sonntag und Montag . . .

ich gehe davon aus, dass in Spalte A das Datum steht:

In der Spalte Wochenarbeitzeit fragst du nun zunächst ab, ob in Spalte A Sonntag ist:
=wenn(wochentag(a1;2)=7;dannkommts)

Nun könntest du eine zusätzliche Spalte mit der Kalenderwoche einbauen. Das ist übrigens zugegebenermaßen keinesweges trivial, ich habe dazu mal eine Formel ausgedacht, siehe ganz unten.

Dann sollen also jede Woche am Sonntag alle Werte addiert werden, die zur gleichen Kalenderwoche gehören. Das machst du mit der Funktion Zählenwenn. Diese Funktion hat diesen Aufbau:

=summewenn(bereich, in dem gesucht werden soll;suchkriterium;bereich, über den summiert werden soll.

das könnte dann in deiner TAbelle so aussehen (ich setze diesmal voraus, dass in Spalte B die Kalenderwoche und in C die tägliche Arbeitszeit steht):

D1=summewenn(b$1:b$30;b1;c$1:c$30)

Diese Formel kannst du dann in der Spalte D hinunterkopieren.

Diese Formel summiert also Werte aus Spalte C auf, und zwar nur die Werte, bei denen in der gleichen Zeile in Spalte A der Wert aus b1 steht. Wenn du Fragen hierzu hast, insbesondere zu den Dollarzeichen, kannst du gerne nochmal nachfragen.

Nun musst du nur noch die beiden obigen Formeln verknüpfen:

D1=wenn(wochentag(a1;2)=7;summewenn(b$1:b$30;b1;c$1:c$30);"")

So, und hier nun die versprochene Formel, mit der du den Wochentag berechnen kannst:

Zelle B1
==WENN(AUFRUNDEN((A1-WENN(WOCHENTAG(DATUM(JAHR(A1);1;1);2)0;AUFRUNDEN((A1-WENN(WOCHENTAG(DATUM(JAHR(A1);1;1);2)

Kristian_Zarse · 7. Februar 2002 um 10:38

Kalenderwoche kompakter

Vielleicht kennt aber auch hier jemand eine elegantere Formel, die dasselbe erreicht.

Ja, meine

Das hier ist die Englische Variante.
Ich weiß nicht, ob Excel das automatisch übersetzt.
Ansonsten müssen die Formeln manuell ins Deutsche übersetzt werden:

Date = Datum
Weekday = Wochentag
Day = Tag
Month = Monat
Year = Jahr
Roundup = Aufrunden
Trunc = Abschneiden oderso (man kann auch Int nehmen)
And = Und
True = Wahr
False = Falsch

In jedem Falle muß jetzt der untere Absatz in eine Textdatei kopiert werden, die man z.B. „Kalenderwoche.csv“ nennt.
Die Endung „.csv“ ist dabei wichtig. Das lädt man dann in Excel und folgt den Erläuterungen.

Kristian

;;;;;;;;;;;;
;Tag;Monat;Jahr;Datum;1. Jan des Jahres;Tages-Differenz;Wochentag 1.1.;Korrektur;Division;Rundung;KW;KW\*
;30;12;2002;"=DATE(D3;C3;B3)";"=DATE(YEAR(E3);1;1)";"=TRUNC(E3)-DATE(YEAR(E3);1;1)";"=WEEKDAY(DATE(YEAR(E3);1;1);2)";=G3+H3;=I3/7;"=ROUNDUP(J3;0)";"=ROUNDUP((WEEKDAY(DATE(YEAR(E3);1;1);2)+TRUNC(E3)-DATE(YEAR(E3);1;1))/7;0)";"=IF(AND(TRUE;DAY(E3)\>26;MONTH(E3)=12;WEEKDAY(E3;2)

Kristian_Zarse · 7. Februar 2002 um 10:40

zur KW siehe unten - o.T. -
o.T.

c_classen_1e3f97 · 7. Februar 2002 um 12:40

Hallo Zusammen,

erstmal Danke für Eure Tipps, teilweise bin ich bisschen schlauer
geworden

Aber diese *.csv bekomme ich nicht ins Laufen (
und mit der Stundentabelle insgesamt bin ich zwar weiter aber
noch nicht am Ziel (

nochmal zur Stundentabelle, in einem Feld steht jetzt
wenn(wochentag(a2;2)=7;summewenn(b2:b30;b2;c2:c30);"")
aber das steht ganz stabil und ergibt nicht das Ergebnis ?

also in der Spalte a steht das Datum, B die kalenderwoche (mit
der „grossen“ Tabelle funktioniert es), in C habe ich die
Tagesstunden und in D das wenn… wie oben. Aber es rechnet
nicht, verflixt ;-(

beste grüsse

christian

Anonym · 7. Februar 2002 um 15:00

nochmal zur Stundentabelle, in einem Feld steht jetzt
wenn(wochentag(a2;2)=7;summewenn(b2:b30;b2;c2:c30);"")
aber das steht ganz stabil und ergibt nicht das Ergebnis ?

Servus Christian

Steht vor dem „wenn“ ein Gleichheitszeichen ("=")?
Das muss sein, damit Excel es als Formel erkennt!

MfG
Roland

Anonym · 7. Februar 2002 um 16:14

nochmal zur Stundentabelle, in einem Feld steht jetzt
wenn(wochentag(a2;2)=7;summewenn(b2:b30;b2;c2:c30);"")
aber das steht ganz stabil und ergibt nicht das Ergebnis ?

Vermutlich hat Roland Recht und du hast das Gleichheitszeichen vergessen.
Die Formel hast du ja runterkopiert, also mit dem kleinen schwarzen Kreuz in der Ecke der Zelle runtergezogen?

Davon abgesehen gibt es aber noch ein Problem, wie du danach bestimmt auch feststellen wirst: Der Übergang zwischen den Monaten. Vermutlich hast du ja für jeden Monat ein eigenes Blatt eingerichtet. Wenn du dahin kommst, kannst du dich ja nochmal melden. Dies zu automatisieren ist wohl nicht ganz so einfach.

Schöne Grüße
Siegfried

Kristian_Zarse · 7. Februar 2002 um 21:53

Guck Dir mal das Beispiel hier an. (9KB)
Hallo Christian,

ich habe eben nochmal schnell eine Tabelle zusammengebastelt,
die vielleicht ungefähr das macht, was Du vorhast. Kann aber
auch sein, daß Du etwas anderes meintest. Vielleicht machst Du es Dir ja schwerer, als es ist (wie ich so oft ).
Da ich hier kein Excel-File reinhängen kann, schicke ich
ein Makro mit, das alles zusammenbaut.

Öffne dazu bitte ein neues, leeres Excel-Dokument.
Drücke Alt-F11, um den VBA-Editor zu öffnen.
Im Projekt-Explorer oben links klickst Du mit der rechten Maustaste auf „VBAProject (Mappe1)“ oder so ähnlich.
Dort wählst Du „Einfügen…/Modul“ aus. Das erscheint dann als weißes Blatt rechts.
Da hinein kopierst Du einfach das komplette Makro, das unten steht.
Du muß es nun nur noch starten. Dazu gibt es drei Möglichkeiten:

Wenn der Curser irgendwo im Code steht, F5 drücken (oder F8, um die einzelnen Schritte nachzuverfolgen).
Oben im VBA-Editor auf das „Play“-Symbol klicken (Dreieck).
Im Excel-Menü „Extras/Makro/Makros…“ das Makro auswählen und starten. Dabei kann der VBA-Editor auch wieder geschlossen sein.

Wenn Du fragen zum Ergebnis hast, stelle sie. Hier nur noch zwei Hinweise:

Die Zellen C2 bis C7 müssen existieren und dürfen keine Zahlen enthalten, damit die Bezüge in den Formeln der C-Spalte nicht ins Leere gehen. Deshalb habe ich einfach die 5 Spalten ganz dünn gemacht.
Als Beispiel-Einträge habe ich einfach Zufallszahlen eingesetzt, die sich bei jeder Änderung in der Tabelle auch ändern. Also nicht wundern, wenn mal andere Zahlen auftauchen.

Viel Spaß und Erfolg!
Kristian

PS: Achso, ganz wichtig: Ich habe vergessen, die bedingte Formatierung zu erwähnen. Markiere mal den Bereich B8:smiley:38 und gehe ins Menü „Format/Bedingte Formatierung…“. Dort schaust Du Dir an, wie es kommt, daß die Sonntage wunderbarerweise immer hervorgehoben werden.

Sub ErstelleListe()
' Makro am 07.02.2002 von Kristian Zarse aufgezeichnet
 Rows("2:6").Select
 Selection.RowHeight = 2.5
 Range("B7").Select
 ActiveCell.FormulaR1C1 = "Datum"
 Range("C7").Select
 ActiveCell.FormulaR1C1 = "Stunden"
 Range("D7").Select
 ActiveCell.FormulaR1C1 = "Summen"
 Range("B8").Select
 ActiveCell.FormulaR1C1 = "2/1/2002"
 Range("B9").Select
 ActiveCell.FormulaR1C1 = "=R[-1]C+1"
 Range("C8").Select
 ActiveCell.FormulaR1C1 = "=IF(WEEKDAY(RC[-1],2)=7,""-"",RAND())"
 Range("D8").Select
 ActiveCell.FormulaR1C1 = "=IF(WEEKDAY(RC[-2],2)=7,SUM(R[-5]C[-1]:R[-1]C[-1]),""-"")"
 Range("C8:smiley:9").Select
 Selection.FillDown
 Range("B9:smiley:38").Select
 Selection.FillDown
 Range("B8:B38").Select
 Selection.NumberFormat = "mm/dd/yy"
 Range("C8:smiley:38").Select
 Selection.NumberFormat = "[h]:mm"
 Range("D40").Select
 ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-2]C)"
 Range("B41:C41").Select
 Selection.HorizontalAlignment = xlCenter
 Selection.Merge
 ActiveCell.FormulaR1C1 = "=R[-33]C"
 Range("B41:C41").Select
 Selection.Cut Destination:=Range("B40:C40")
 Range("B40:C40").Select
 Selection.NumberFormat = "mmmm yyyy:"
 Selection.HorizontalAlignment = xlCenter
 Range("B7:B38").Select
 Range("B38").Activate
 Selection.HorizontalAlignment = xlCenter
 Range("C7:smiley:38").Select
 Selection.HorizontalAlignment = xlRight
 Range("B7:smiley:7").Select
 Selection.Font.Bold = True
 Range("B7:smiley:38").Select
 Selection.Borders(xlDiagonalDown).LineStyle = xlNone
 Selection.Borders(xlDiagonalUp).LineStyle = xlNone
 With Selection.Borders(xlEdgeLeft)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeTop)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeBottom)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeRight)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlInsideVertical)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlInsideHorizontal)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 Selection.Borders(xlDiagonalDown).LineStyle = xlNone
 Selection.Borders(xlDiagonalUp).LineStyle = xlNone
 With Selection.Borders(xlEdgeLeft)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeTop)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeBottom)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeRight)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlInsideVertical)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlInsideHorizontal)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 Range("B7:smiley:7").Select
 Selection.Borders(xlDiagonalDown).LineStyle = xlNone
 Selection.Borders(xlDiagonalUp).LineStyle = xlNone
 With Selection.Borders(xlEdgeLeft)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeTop)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeBottom)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeRight)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlInsideVertical)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 Range("B40:smiley:40").Select
 Selection.Borders(xlDiagonalDown).LineStyle = xlNone
 Selection.Borders(xlDiagonalUp).LineStyle = xlNone
 With Selection.Borders(xlEdgeLeft)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeTop)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeBottom)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeRight)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 Selection.Borders(xlInsideVertical).LineStyle = xlNone
 With Selection.Font
 .Name = "Arial"
 .Size = 14
 .Strikethrough = False
 .Superscript = False
 .Subscript = False
 .OutlineFont = False
 .Shadow = False
 .Underline = xlUnderlineStyleNone
 .ColorIndex = xlAutomatic
 End With
 Selection.Font.Bold = True
 With Selection.Interior
 .ColorIndex = 10
 .Pattern = xlSolid
 End With
 Selection.Font.ColorIndex = 35
 Range("B8:smiley:38").Select
 Range("B8").Activate
 Selection.FormatConditions.Delete
 Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=WENN(WOCHENTAG($B8;2)=7;1;0)"
 With Selection.FormatConditions(1).Font
 .Bold = True
 .Italic = False
 .ColorIndex = 35
 End With
 Selection.FormatConditions(1).Interior.ColorIndex = 10
 Range("B7:smiley:7").Select
 With Selection.Interior
 .ColorIndex = 37
 .Pattern = xlSolid
 End With
 Range("B8").Select
 MsgBox "Fertig." & vbCr & vbCr & "Jetzt bitte mal in Feld B8 ein anderes Datum eingeben" & vbCr & "(sinnvollerweise den Ersten eines Monats).", vbInformation, "Kristian meint:"
End Sub 'ErstelleListe

c_classen_1e3f97 · 8. Februar 2002 um 19:43

D a n k e
Hallo Zusammen,

meine Tabelle läuft ))

neben dem Ergebnis (tabelle läuft) habe ich auch wieder einiges
zum Programm gelernt.

Danke

Christian