PDA

Vollständige Version anzeigen : Ausrechnen von Arbeitszeiten


Hotshot666
06.07.2015, 13:50
Guten Tag alle zusammen,

Ich bin Schüler und habe momentan einen Ferienjob. Hier soll ich nun meine Arbeitszeiten in einem Excelfile eintragen. Zudem wurde mir aufgetragen, dass die Differenzen zur Soll-Zeit jeweils auch gleich ausgerechnet werden sollen.
Das berechnen der Arbeitszeit hat mir keine Mühe bereitet. Aber die Abweichung von der Soll-Zeit bereitet mir ein bisschen Mühe, da ich jeweils keine negativen Abweichungen errechnen kann. Im Beispiel File habe ich mein Problem kurz dargestellt. Hierbei hätte ich gerne, dass es jeweils negative Zahlen gibt, wenn ich zu wenig gearbeitet habe und dass es positive Werte gibt, wenn ich zu viel gearbeitet habe. Somit könnte ich nämlich sofort berechnen, wie viele Stunden pro Woche zu viel oder zu wenig gemacht worden sind.

Ich hoffe, es kann mir jemand helfen und bedanke mich schon mal ganz herzlich für die entgegengebrachte Hilfe im Voraus.

GMG-CC
06.07.2015, 14:14
Moin,

schau mal hier bei Excel-ist-sexy (http://www.excel-ist-sexy.de/downloads/dl-ohne-vba/datum-und-zeit/) nach, der 3. oder 4. Link (Minus-Zeiten extrem einfach) sollte dir helfen. Als Ergänzung auch noch mehr zum Thema Minus-Zeiten (http://www.excel-ist-sexy.de/minus-zeiten/).

Pit987
06.07.2015, 14:20
Hallo Hotshot666!

Für negative Zeitberechnung gibt es mehrere Möglichkeiten.
Schaue hier und entscheide selbst:
http://www.online-excel.de/excel/singsel.php?f=25

Anbei Deine Datei.

Einen schönen Tag noch
Pit

Wilfried07
06.07.2015, 14:51
Hallo!

Hilft dir das weiter? Alle Zeiten in extra Spalten.
Achte auf die Formtierung hh:mm;;

<b>Tabelle6</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:96px;" /><col style="width:94px;" /><col style="width:85px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >44</td><td style="color:#0000ff; ">kommen</td><td style="color:#0000ff; ">gehen</td><td style="color:#0000ff; ">Soll</td><td style="color:#0000ff; ">Ist</td><td style="color:#0000ff; ">Plusstunden</td><td style="color:#0000ff; ">Minusstunden</td><td style="color:#0000ff; ">Dezimal +/-</td><td style="color:#0000ff; text-align:center; ">nicht rechnen</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >45</td><td style="text-align:center; ">07:00</td><td style="text-align:center; ">13:00</td><td style="text-align:center; ">09:00</td><td style="background-color:#ffcc99; text-align:center; ">06:00</td><td style="background-color:#00ff00; color:#333333; text-align:center; ">&nbsp;</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">03:00</td><td style="background-color:#ccffff; color:#333333; text-align:center; ">-3,00</td><td style="background-color:#ff99cc; color:#333333; text-align:center; ">-3:00</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >46</td><td style="text-align:center; ">09:05</td><td style="text-align:center; ">14:12</td><td style="text-align:center; ">08:00</td><td style="background-color:#ffcc99; text-align:center; ">05:07</td><td style="background-color:#00ff00; color:#333333; text-align:center; ">&nbsp;</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">02:53</td><td style="background-color:#ccffff; color:#333333; text-align:center; ">-2,88</td><td style="background-color:#ff99cc; color:#333333; text-align:center; ">-2:53</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >47</td><td style="text-align:center; ">22:30</td><td style="text-align:center; ">04:30</td><td style="text-align:center; ">08:00</td><td style="background-color:#ffcc99; text-align:center; ">06:00</td><td style="background-color:#00ff00; color:#333333; text-align:center; ">&nbsp;</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">02:00</td><td style="background-color:#ccffff; color:#333333; text-align:center; ">-2,00</td><td style="background-color:#ff99cc; color:#333333; text-align:center; ">-2:00</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >48</td><td style="text-align:center; ">07:10</td><td style="text-align:center; ">16:00</td><td style="text-align:center; ">06:00</td><td style="background-color:#ffcc99; text-align:center; ">08:50</td><td style="background-color:#00ff00; color:#333333; text-align:center; ">02:50</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">&nbsp;</td><td style="background-color:#ccffff; color:#333333; text-align:center; ">2,83</td><td style="background-color:#ff99cc; color:#333333; text-align:center; "> 2:50</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >49</td><td style="text-align:center; ">10:00</td><td style="text-align:center; ">12:00</td><td style="text-align:center; ">06:00</td><td style="background-color:#ffcc99; text-align:center; ">02:00</td><td style="background-color:#00ff00; color:#333333; text-align:center; ">&nbsp;</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">04:00</td><td style="background-color:#ccffff; color:#333333; text-align:center; ">-4,00</td><td style="background-color:#ff99cc; color:#333333; text-align:center; ">-4:00</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >50</td><td >&nbsp;</td><td >&nbsp;</td><td style="color:#333333; font-weight:bold; text-align:center; ">37:00</td><td style="background-color:#ffcc99; color:#333333; font-weight:bold; text-align:center; ">27:57</td><td style="background-color:#00ff00; color:#333333; font-weight:bold; text-align:center; ">02:50</td><td style="background-color:#ffcc99; color:#333333; font-weight:bold; text-align:center; ">11:53</td><td style="background-color:#ccffff; color:#333333; font-weight:bold; text-align:center; ">-9,05</td><td style="background-color:#ff99cc; color:#333333; font-weight:bold; text-align:center; ">-9:03</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >51</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >52</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="color:#0000ff; ">hh:mm;;</td><td style="color:#0000ff; ">hh:mm;;</td><td >&nbsp;</td><td >&nbsp;</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >D45</td><td >=REST(B45-A45;1)</td></tr><tr><td >E45</td><td >=WENN(C45&lt;D45;ABS<span style=' color:008000; '>(D45-C45)</span>;0)</td></tr><tr><td >F45</td><td >=WENN(C45&gt;D45;ABS<span style=' color:008000; '>(C45-D45)</span>;0)</td></tr><tr><td >G45</td><td >=(D45-C45)*24</td></tr><tr><td >H45</td><td >=WENN(D45&lt;C45;"-";" ")&TEXT(ABS<span style=' color:008000; '>(D45-C45)</span>;"[h]:mm")</td></tr><tr><td >H50</td><td >{=WENN(SUMME<span style=' color:008000; '>(<span style=' color:#0000ff; '>(TEIL<span style=' color:#ff0000; '>(H45:H49;2;99)</span>)</span>*WENN<span style=' color:#0000ff; '>(LINKS<span style=' color:#ff0000; '>(H45:H49)</span>="-";-1;1)</span>)</span>&lt;=0;"-";"")&TEXT(ABS<span style=' color:008000; '>(SUMME<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>(TEIL<span style=' color:#804000; '>(H45:H49;2;99)</span>)</span>*WENN<span style=' color:#ff0000; '>(LINKS<span style=' color:#804000; '>(H45:H49)</span>="-";-1;1)</span>)</span>)</span>;"[h]:mm")}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Enth&auml;lt Matrixformel:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Umrandende </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> nicht miteingeben,<br />sondern Formel mit STRG+SHIFT+RETURN abschlie&szlig;en!</span></td></tr><tr><td ><a href="http://www.online-excel.de/excel/singsel.php?f=26" style="font-family:Arial; font-size:8pt;font-weight:bold;color:#000080;" target='_blank'>Matrix verstehen</a></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

Gruß
Wilfried

Wilfried07
06.07.2015, 15:03
Hallo!

mit Pause

<b>Tabelle6</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:96px;" /><col style="width:94px;" /><col style="width:85px;" /><col style="width:107px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >&nbsp;</td><td style="color:#0000ff; ">&nbsp;</td><td style="color:#0000ff; ">&nbsp;</td><td style="color:#0000ff; ">&nbsp;</td><td style="color:#0000ff; ">&nbsp;</td><td style="color:#0000ff; ">&nbsp;</td><td style="color:#0000ff; ">&nbsp;</td><td style="color:#0000ff; ">Industriezeit</td><td style="color:#0000ff; text-align:center; ">Textstunden </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="color:#0000ff; ">kommen</td><td style="color:#0000ff; ">gehen</td><td style="color:#0000ff; ">Pause</td><td style="color:#0000ff; ">Soll</td><td style="color:#0000ff; ">Ist</td><td style="color:#0000ff; ">Plusstunden</td><td style="color:#0000ff; ">Minusstunden</td><td style="color:#0000ff; ">Dezimal +/-</td><td style="color:#0000ff; text-align:center; ">nicht rechnenbar</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">07:00</td><td style="text-align:right; ">13:00</td><td style="text-align:right; ">00:30</td><td style="text-align:right; ">08:00</td><td style="color:#333333; text-align:right; ">06:00</td><td style="background-color:#00ff00; color:#333333; text-align:right; ">&nbsp;</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">02:30</td><td style="background-color:#ccffff; color:#333333; text-align:right; ">-2,50</td><td style="background-color:#ff99cc; color:#333333; text-align:left; ">-02:30</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">09:05</td><td style="text-align:right; ">14:12</td><td style="text-align:right; ">00:30</td><td style="text-align:right; ">08:00</td><td style="color:#333333; text-align:right; ">05:07</td><td style="background-color:#00ff00; color:#333333; text-align:right; ">&nbsp;</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">03:23</td><td style="background-color:#ccffff; color:#333333; text-align:right; ">-3,38</td><td style="background-color:#ff99cc; color:#333333; text-align:left; ">-03:23</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">08:30</td><td style="text-align:right; ">16:00</td><td style="text-align:right; ">00:30</td><td style="text-align:right; ">08:00</td><td style="color:#333333; text-align:right; ">07:30</td><td style="background-color:#00ff00; color:#333333; text-align:right; ">&nbsp;</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">01:00</td><td style="background-color:#ccffff; color:#333333; text-align:right; ">-1,00</td><td style="background-color:#ff99cc; color:#333333; text-align:left; ">-01:00</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">07:10</td><td style="text-align:right; ">16:00</td><td style="text-align:right; ">00:30</td><td style="text-align:right; ">06:00</td><td style="color:#333333; text-align:right; ">08:50</td><td style="background-color:#00ff00; color:#333333; text-align:right; ">02:20</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">&nbsp;</td><td style="background-color:#ccffff; color:#333333; text-align:right; ">2,33</td><td style="background-color:#ff99cc; color:#333333; text-align:left; "> 02:20</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">19:00</td><td style="text-align:right; ">02:00</td><td style="text-align:right; ">00:30</td><td style="text-align:right; ">06:00</td><td style="color:#333333; text-align:right; ">07:00</td><td style="background-color:#00ff00; color:#333333; text-align:right; ">00:30</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">&nbsp;</td><td style="background-color:#ccffff; color:#333333; text-align:right; ">0,50</td><td style="background-color:#ff99cc; color:#333333; text-align:left; "> 00:30</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="color:#333333; font-weight:bold; text-align:right; ">36:00</td><td style="color:#333333; font-weight:bold; text-align:right; ">34:27</td><td style="background-color:#00ff00; color:#333333; font-weight:bold; text-align:right; ">02:50</td><td style="background-color:#ffcc99; color:#333333; font-weight:bold; text-align:right; ">06:53</td><td style="background-color:#ccffff; color:#333333; font-weight:bold; text-align:right; ">-4,05</td><td style="background-color:#ff99cc; color:#333333; font-weight:bold; text-align:left; ">-04:03</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="color:#0000ff; ">Summenzeil.</td><td style="color:#0000ff; ">[hh]:mm</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="color:#0000ff; ">hh:mm;;</td><td style="color:#0000ff; ">hh:mm;;</td><td >&nbsp;</td><td >&nbsp;</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >E21</td><td >=REST(B21-A21;1)</td></tr><tr><td >F21</td><td >=WENN(D21&lt;E21;ABS<span style=' color:008000; '>(E21-D21-C21)</span>;0)</td></tr><tr><td >G21</td><td >=WENN(D21&gt;E21;ABS<span style=' color:008000; '>(D21-E21+C21)</span>;0)</td></tr><tr><td >H21</td><td >=(E21-D21-C21)*24</td></tr><tr><td >I21</td><td >=WENN(E21&lt;D21;"-";" ")&TEXT(ABS<span style=' color:008000; '>(E21-D21-C21)</span>;"[hh]:mm")</td></tr><tr><td >I26</td><td >{=WENN(SUMMENPRODUKT<span style=' color:008000; '>(<span style=' color:#0000ff; '>(TEIL<span style=' color:#ff0000; '>(I21:I25;2;99)</span>)</span>*WENN<span style=' color:#0000ff; '>(LINKS<span style=' color:#ff0000; '>(I21:I25)</span>="-";-1;1)</span>)</span>&lt;=0;"-";" ")&TEXT(ABS<span style=' color:008000; '>(SUMMENPRODUKT<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>(TEIL<span style=' color:#804000; '>(I21:I25;2;99)</span>)</span>*WENN<span style=' color:#ff0000; '>(LINKS<span style=' color:#804000; '>(I21:I25)</span>="-";-1;1)</span>)</span>)</span>;"[hh]:mm")}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Enth&auml;lt Matrixformel:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Umrandende </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> nicht miteingeben,<br />sondern Formel mit STRG+SHIFT+RETURN abschlie&szlig;en!</span></td></tr><tr><td ><a href="http://www.online-excel.de/excel/singsel.php?f=26" style="font-family:Arial; font-size:8pt;font-weight:bold;color:#000080;" target='_blank'>Matrix verstehen</a></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

Gruß
Wilfried