Eine mehr oder we­ni­ger typ­is­che Arbeit­szeit-Berech­nung (ohne we­sent­li­che For­matierung, ohne Pau­sen und hier sehr spar­tanisch) sieht beispiel­sweise so aus:

Bis auf die Über­schrift fast unfor­matierte Ta­bel­le

In Spal­te D wird die Arbeit­szeit mit fol­gen­der For­mel berech­net: =C2-B2 und so­lan­ge kei­ne Ein­ga­ben ge­macht wer­den, ste­ht in Spal­te D auch das Ergeb­nis, dass 0:00 Stun­den gear­beit­et wor­den sind. – Das mag ja noch ange­hen, denn es stimmt ja auch vom Prin­zip her. Es kann aber irri­tieren, wenn auch an Woch­enen­den oder an­de­ren frei­en Ta­gen die­ser Wert dort ste­ht. Gle­ich­es gilt für Tage, die es in dem Mo­nat gar nicht gibt.

So wirk­lich un­schön wird es, wenn in der Frü­he der Arbeits­be­ginn schon ein­ge­ge­ben wird und (natür­lich) das Ende noch of­fen ist:

Das Arbeit­sende ist noch nicht einge­tra­gen

Die berech­nete Arbeit­szeit wird als „Garten­za­un” dar­ge­stellt. Das ist im Prin­zip auch lo­gisch, denn eine lee­re Zel­le wird als Null gew­ertet und 0:00–8:50 er­gibt ein neg­a­tives Resul­tat. Und Neg­a­tiv-Zeit­en wer­den in Ex­cel (Win­dows) grund­sät­zlich in die­ser Form dar­ge­stellt. Mac-User ken­nen die­ses Prob­lem übri­gens nicht. Es gibt ver­schiedene Wege, das mit den Null-Wer­ten zu umge­hen. Teils per Zahlen­for­mat, über die bed­ingte For­matierung oder mit ein­er entsprechen­den For­mel.

Es exis­tiert eine Da­tei für die Arbeit­szeit­berech­nung, wel­che für die­se Übung et­was mod­i­fiziert wur­de. La­den Sie die­ses File her­un­ter, um die Erk­lärun­gen bess­er nachvol­lziehen zu kön­nen. Beacht­en Sie bit­te: Die entsprechen­den Änderun­gen be­zie­hen sich auss­chließlich auf Spal­te E (Arbeit­szeit). An­de­re Spal­ten sind in der For­matierung be­las­sen wor­den, die für den entsprechen­den Bei­trag sin­nvoll war. Kei­ne Re­gel ohne Aus­nahme, aber die kommt spä­ter und wird detail­liert be­schrie­ben.

▲ nach oben …

Der Ur­zu­stand

Die Zei­len 6:17 sind so be­las­sen wor­den, wie sie in der Ursprungs­datei vorhan­den sind. Un­ter der Zei­le 17 (und weit­eren Zei­len) sind von mir Trenn­stri­che einge­fügt. Sie die­nen auss­chließlich der bes­se­ren Über­sicht der „Blö­cke”, wel­che den Mo­nat in Arbeitss­chritte für die­se Übung un­ter­tei­len.

In den Zei­len 18:21 ste­ht die typ­is­che, ganz ein­fache For­mel; die ein­zi­ge For­matierung beste­ht hier dar­in, dass eine Uhr­zeit Typ hh:mm dar­ge­stellt wird. In E20 ist (natür­lich) das Ergeb­nis 0:00, was nicht unbe­d­ingt stö­ren muss. Allerd­ings: Es war der freie Sam­stag. Und in E21 wird deut­lich, dass neg­a­tive Zeit­en in der Win­dows-Ver­sion nicht „sau­ber” dar­ge­stellt wer­den: ###. Und das hat natür­lich nichts mit dem arbeits­freien Son­ntag zu tun … 😉 Ab Zei­le 22 gibt es noch weit­ere Beson­der­heit­en; dar­auf wird dann jew­eils ge­zielt einge­gan­gen.

▲ nach oben …

Be­ding­te For­ma­tie­rung

Eine dur­chaus pro­bate Meth­ode, Neg­a­tivergeb­nisse und Null­w­erte bei Zeit­en unsicht­bar zu ma­chen, ist die Bed­ingte For­matierung. Der Bere­ich E22:E25 ist ein Bei­spiel da­für. Die Op­tik stimmt (erst ein­mal), die kor­rek­ten Ergeb­nisse ste­hen den­noch in den Zel­len drin. – Das gro­ße Aber: Sie kön­nen den so behan­del­ten Zel­len kei­ne indi­vidu­elle Hin­ter­grund­farbe „ver­passen”. Und was vielle­icht nicht so ganz gra­vie­rend ist: Man­che Druck­er ha­ben die Mög­lich­keit, jede Far­be (ein­schließlich weiß) oder auch Tex­te schwarz zu druck­en. Da kommt der „Schwin­del” wie­der zum Vor­schein 😎 .

For­mel-Lö­sung (1)

Wenn solch ein Prob­lem per For­mel ge­löst wird, dann ist es in den meis­ten Fäl­len eine Lö­sung, wel­che die­ser in E28 äh­nelt: =WENN(B28<C28;C28-B28-D28;""). Das kann aber schief ge­hen, wenn eine weit­ere For­mel auf das Ergeb­nis zu­greift und eine Zahl er­war­tet. G28 und H28 zei­gen das ganz deut­lich auf. Und natür­lich ist in den bei­den Spal­ten auch in Zei­le 37 der gle­iche Fehler­w­ert. Übri­gens: Die Mel­dung #WERT! be­sagt, dass ein Wert er­war­tet wird und ein solch­er nicht gefun­den wur­de. Hier ist es der Leer­String "", der ja ein Text der Län­ge null ist. Wäre die Zel­le wirk­lich leer, dann wür­de die Zel­le als Null gew­ertet aber den­noch leer dar­ge­stellt wer­den.

▲ nach oben …

For­mel-Lö­sung (2)

Ehe Sie sich Hoff­nun­gen ma­chen: Es bleibt beim Leer­String als Ergeb­nis, mit all sei­nen Nach­tei­len. Aber die For­mel ist vielle­icht et­was trans­par­enter und für Sie un­ter Umstän­den auch leich­ter anpass­bar: =WENN(ANZAHL(B29:C29)>1;C29-B29-D29;""). Wenn also min­destens die bei­den ers­ten Spal­ten mit Zah­len aus­ge­füllt sind, dann soll berech­net wer­den, son­st wird "" zu­rück ge­ge­ben.

Den­noch gibt es eine sin­nvolle For­mel-Lö­sung, wenn auch weit­er­führend. Dei Fehler­mel­dun­gen taucht­en ja in den bei­den Dif­ferenz-Spal­ten auf, also Spal­ten G:H. Dort kön­nen Sie näm­lich auch anset­zen, um die Fehler­mel­dung zu unter­drück­en. In G29 und auch H29 se­hen Sie, dass die For­mel eine an­de­re ist als in der Zei­le dar­über. Hier ste­ht (im Bei­spiel Spal­te G): =WENN(E29<>"";(E29-F29)*24;"") und nicht nur die ein­fache Berech­nung. Es wird erst abge­fragt, ob der In­halt der Spal­te E kein Leer­String ist; dann wird berech­net, son­st wird auch hier ein Leer­String aus­gegeben. Ein klein­er Um­weg, aber eine dur­chaus trag­bare Lö­sung.

▲ nach oben …

Per Zah­len­for­mat

Die wohl ele­gan­teste Lö­sung ist, das Zahlen­for­mat in den entsprechen­den Bere­ichen anzu­passen. Die Berech­nun­gen erfol­gen so, dass dur­chaus Null­w­erte als Ergeb­nis her­auskom­men kön­nen. Aber das Zahlen­for­mat ist so ein­ge­stellt, dass nur pos­i­tive Wer­te an­ge­zeigt wer­den. Und das be­deu­tet, dass auch Neg­a­tiv-Zeit­en wie beispiel­sweise am 26. Feb­ru­ar in Spal­te E nicht als „Lat­ten­za­un” an­ge­zeigt wer­den.

Se­hen Sie sich ein­mal das Zahlen­for­mat in E30 an. Es ist ein be­nut­zer­de­fi­nier­tes For­mat und lässt nur pos­i­tive Wer­te zu: hh:mm;;. Auch Nul­len bzw. der Wert 0 als Ergeb­nis wer­den unter­drückt. Den­noch kön­nen mit dem In­halt der Zel­le weit­ere Berech­nun­gen durchge­führt wer­den.

Wenn Sie die­se For­matierung in die Zei­len 31:35 über­tra­gen, wer­den Sie erken­nen, was ich mei­ne. Das Minus-Ergeb­nis vom 26. Feb­ru­ar wird unsicht­bar, wie auch die rest­li­chen Zei­len. Und schau­en Sie sich ein­mal die For­mel in E32 an. Sie ist fast mit jen­er in E28 iden­tisch, aber eben nur fast. Statt des Leer­strings wird nun eine Null zu­rück ge­ge­ben. Und da die Null eine Zahl ist, kommt es trotz son­st gle­ich­er Bedin­gun­gen nicht zur Fehler­mel­dung in den Spal­ten G:H.

▲ nach oben …

Re­sü­mee

Ide­al­er­weise ver­wen­den Sie eine For­mel zur Berech­nung, wel­che Ih­nen 0 zu­rück gibt, wenn noch nicht alle Zeit­en ein­ge­ge­ben wor­den sind oder nicht gear­beit­et wur­de. Das di­rekt hier­über auf­ge­zeig­te Zahlen­for­mat sorgt für ein „bes­se­res” Ausse­hen der Dat­en.

Soll zwin­gend eine Pau­se ein­ge­ge­ben wer­den, erforder­lichen­falls auch 0:00, dann ist die For­mel mit der AN­ZAHL()-Funk­tion bess­er ein­set­zbar. In dem Fall muss der Wert für die An­zahl der Wer­te auf >2 geän­dert wer­den. Ist dann kei­ne Pausen­zeit ein­ge­ge­ben, erfol­gt kei­ne Berech­nung bzw. die Arbeit­szeit wird auf 0:00 geset­zt. Mit ei­nem WENN()-Kon­strukt kön­nte hier auch ein De­fault-Wert einge­set­zt wer­den.

▲ nach oben …

Rück­mel­dun­gen / Feed­back ger­ne per Mail an mich (G.​Mumme@​Excel-​ist-​sexy.​de)

Hat Ih­nen der Bei­trag ge­fal­len?
Er­leich­tert die­ser Bei­trag Ihre Ar­beit?

Dann wür­de ich mich über ei­nen Bei­trag Ihrer­seits z.B. 2,00 € freu­en … (← Klick mich!)