PDA

Vollständige Version anzeigen : Drei Flexible Pausen


PeterGo65
21.07.2014, 00:47
Hallo Liebe Leute,
die Forums-suche hat mir leider nicht geholfen. Vielleicht liegt es auch daran das ich was Excel betrifft ein absoluter Newbie bin. :entsetzt:

Nun zu meinem Problem, ich würde gerne einen Arbeitszeiten Tabelle machen mit folgenden Merkmalen.

Die Arbeitszeiten schwanken sehr. Mal arbeite ich von 9:00 bis 20:00, mal von 14:30 bis 20:00 Uhr usw. Sollte meine Arbeitszeit weniger oder gleich 4 Std. betragen, habe ich keine Pause, arbeite ich aber mehr als 4Std. und weniger oder gleich 6 Std. habe ich 0,5 Std. Pause. Mehr als & Std. undweniger oder gleich 8 Std. habe ich 1 Std. Pause. Alles über 8 Std. ist dann 1,5 Std. Pause.

- 4h keine Pause
4 - 6h = 0,5h Pause
6 - 8H = 1,0h Pause
mehr als 8h = 1,5 Pause

Die Arbeitszeiten können unterschiedlich beginnen, gibt keine Festen Zeiten.

Ich hoffe ich finde hier Hilfe und vielen Dank im voraus

Peter

Hajo_Zi
21.07.2014, 05:02
Hallo Peter,

<br/><b><em>Tabelle3</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:40px;" /><col style="width:126px;" /><col style="width:126px;" /><col style="width:126px;" /><col style="width:126px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">25</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Pause</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">26</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">20:30</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">07:30</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">11:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">01:30:00</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">27</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">20:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">04:01</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">08:01</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">01:30:00</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">28</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">20:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">04:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">08:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">01:00:00</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">29</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">20:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">02:01</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">06:01</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">01:00:00</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">30</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">20:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">02:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">06:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">00:30:00</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">31</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">20:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">00:01</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">04:01</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">00:30:00</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">32</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">20:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">00:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">04:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">00:30:00</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">33</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">20:00</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">23:59</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">03:59</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">00:00:00</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>C26:C33</td><td>=REST(B26-A26;1)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>D26:&nbsp;D33</td><td>=WENN(C26*24&lt;4;0;WENN(C26*24&lt;=6;0,5;WENN(C26*24&lt;=8;1;1,5)))/24</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt;width:800px; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCCCC; text-align:center; font-weight:bold; "><td colspan="3" > Zahlenformate </td></tr><tr valign="top" style="background-color:#FFCCCC; text-align:center; font-weight:bold; "><td> Zelle </td><td> Format </td><td> Wert </td></tr><tr><td>A26 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,854166666666667</td></tr><tr><td>A27:A33 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,833333333333333</td></tr><tr><td>B26 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,3125</td></tr><tr><td>B27 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,167361111111111</td></tr><tr><td>B28 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,166666666666667</td></tr><tr><td>B29 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">8,40277777777778E-02</td></tr><tr><td>B30 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">8,33333333333333E-02</td></tr><tr><td>B31 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">6,94444444444444E-04</td></tr><tr><td>B32 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0</td></tr><tr><td>B33 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,999305555555556</td></tr><tr><td>C26 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,458333333333333</td></tr><tr><td>C27 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,334027777777778</td></tr><tr><td>C28 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,333333333333333</td></tr><tr><td>C29 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,250694444444444</td></tr><tr><td>C30 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,25</td></tr><tr><td>C31 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,167361111111111</td></tr><tr><td>C32 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,166666666666667</td></tr><tr><td>C33 </td> <td style="text-align:center; ">'hh:mm</td> <td style="text-align:right; ">0,165972222222222</td></tr><tr><td>D26:&nbsp;D27 </td> <td style="text-align:center; ">'[$-F400]h:mm:ss AM/PM</td> <td style="text-align:right; ">0,0625</td></tr><tr><td>D28:&nbsp;D29 </td> <td style="text-align:center; ">'[$-F400]h:mm:ss AM/PM</td> <td style="text-align:right; ">4,16666666666667E-02</td></tr><tr><td>D30:&nbsp;D32 </td> <td style="text-align:center; ">'[$-F400]h:mm:ss AM/PM</td> <td style="text-align:right; ">2,08333333333333E-02</td></tr><tr><td>D33 </td> <td style="text-align:center; ">'[$-F400]h:mm:ss AM/PM</td> <td style="text-align:right; ">0</td></tr></table><b>Zellen mit Format Standard werden nicht dargestellt</b><br/><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.09 einschl. 64 Bit</td></tr></table><br/>

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

PeterGo65
21.07.2014, 21:00
Hallo Hajo,
danke für die schnelle Hilfe!. Was mir aufgefallen ist das bei 4 Std. Arbeitszeit eine halbe Std. Pause gegeben wird. Pausenanspruch gibt es erst über 4 Std. also 4:01

Grüße Peter

Frank Furter
21.07.2014, 21:33
hallo Peter, meinst du so..:

=VERWEIS(C26*24;{0;4;6;8};{0;0,5;1;1,5})/24

Hajo_Zi
22.07.2014, 04:59
Hallo Peter,

WENN(C26*24<=4;0;WENN(C26*24<=6;0,5;WENN(C26*24<=8;1;1,5)))/24

<a href="http://Hajo-Excel.de/index.htm" target="_blank" title="Hajo's Excelseiten">Gruß Hajo</a>