PDA

Vollständige Version anzeigen : Arbeitszeitkonto


eric2014
27.06.2014, 07:21
Hallo, ich möchte mit Excel meine Arbeitszeit berechnen lassen. Da man mit negativen Zeitwerten nicht rechnen kann habe ich für Plus/Minus jeweils eine eigene Spalte vorgesehen.

Kommen -> D3 bis D...
Gehen -> E3 bis E...
Pause -> F3 bis F...
Soll Arbeitszeit-> G3 bis G...
Ist Arbeitszeit -> H3 bis H...
Tages Minus -> I3 bis I...
Tages Plus -> J3 bis J...
Saldo Minus -> K3 bis K...
Saldo Plus -> L3 bis L...

Ich bekomme mein Tages-Plus bzw. Tages-Minus in den Spalten I bzw. J jeweils als Positiven Zeitwert angezeigt.
Die jeweilige Zelle in der kein Wert am entsprechenden Tag hinzukommt bleibt leer.

Formel in I =WENN(H5-G5<0;(H5-G5)*-1;"")
Formel in J =WENN(H5-G5>0;H5-G5;"")

Nun möchte ich aber zusätzlich mein gesamtes Saldo in K bzw. L (blau markiert) angezeigt bekommen. Benötige dafür eine Formel damit das aktuelle Saldo angezeigt wird. Bei Minusstunden in K und bei Plusstunden in L.
Die jeweilige Zelle in der kein Wert am entsprechenden Tag hinzukommt soll leer bleiben.

Mc Santa
27.06.2014, 08:15
Hallo,

ich könnte mir das etwa so vorstellen, das Saldo am Wochenende musst du über bedingte Formatierung ausblenden.
<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:52px;" /><col style="width:103px;" /><col style="width:103px;" /><col style="width:103px;" /><col style="width:103px;" /><col style="width:103px;" /><col style="width:103px;" /><col style="width:103px;" /><col style="width:103px;" /><col style="width:103px;" /><col style="width:170px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;">Saldo</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Hilfsspalte</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Kommen</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Gehen</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Pause</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Soll</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Ist</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">'-</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">'+</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">'-</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">'+</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">07:02</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">15:48</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:30</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">08:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">08:16</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0:16</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">0:16</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1,11111111111111E-02</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">06:55</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">15:22</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:30</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">08:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">07:57</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0:03</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">0:13</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">9,02777777777786E-03</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">06:53</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">15:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:30</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">08:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">07:37</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0:23</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">0:10</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">-6,94444444444431E-03</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">06:50</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">15:05</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:30</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">08:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">07:45</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0:15</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">0:25</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">-1,73611111111109E-02</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">07:10</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">15:07</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:30</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">07:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">07:27</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0:27</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">0:02</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1,38888888888905E-03</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">0:02</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1,38888888888905E-03</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">0:02</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1,38888888888905E-03</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">06:52</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">15:21</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:30</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">08:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">07:59</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0:01</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">0:01</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">6,94444444444553E-04</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">11</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">06:53</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">15:29</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:30</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">08:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">08:06</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0:06</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">0:07</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">4,8611111111112E-03</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">12</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">06:48</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">15:05</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:30</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">08:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">07:47</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0:13</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">0:06</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">-4,16666666666654E-03</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">13</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">06:50</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">15:54</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:30</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">08:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">08:34</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0:34</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">0:28</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1,94444444444446E-02</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">07:03</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">14:11</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">00:30</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">07:00</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">06:38</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0:22</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#99CCFF ;; text-align:center; ">0:06</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">4,16666666666682E-03</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>H3:H14</td><td>=E3-D3-F3</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>I3:I14</td><td>=WENN(G3&lt;H3;"";G3-H3)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>J3:J14</td><td>=WENN(H3&gt;G3;H3-G3;"")</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>K3:K14</td><td>=WENN(M3&lt;0;-M3;"")</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>L3:L14</td><td>=WENN(M3&gt;0;M3;"")</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>M3:M14</td><td>=H3-G3+M2</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>Hilft dir das weiter?
VG

eric2014
27.06.2014, 10:06
Vielen Dank, hat mir super geholfen.