{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Arbeitszeit","Umrechnungen"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":4,"comments":2,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Arbeitszeit","index":0,"n_rows":39,"n_cols":20,"rows_kept":39,"cut":false,"hidden":false,"merged":[[10,2,10,4],[10,6,11,6],[10,7,11,7],[10,8,11,8],[10,9,11,9],[5,4,6,4],[6,12,7,12],[6,13,7,13],[6,14,7,14],[7,4,8,4],[13,14,14,14],[14,2,14,3],[15,6,16,6],[17,2,17,9],[18,2,18,9],[12,6,13,6],[12,7,13,7],[12,8,13,8],[12,9,13,9],[13,12,14,12],[13,13,14,13]]}
[]
[null,null,"Tour ausfüllen:","Format hh:mm","AB / Pause / AE",null,null,null,"Berechnung in Minuten      "," Industriestunden",null,null,"Zulagen berechnet für:"]
[null,null,null,null,null,null,null,"hh:mm","Minuten","IND h",null,null,{"$f":"WEEKDAY($N$3)","v":"1900-01-06"},{"$f":"TODAY()","v":"2020-11-27"},null,null,null,{"$f":"DATE(YEAR($N$3),MONTH($N$3),DAY($N$3))","v":"2020-11-27"},"wenn Feiertag = So",{"$f":"IF(ISNA(VLOOKUP($R$3,Feiertage,1,0))=TRUE,WEEKDAY($R$3),IF(VLOOKUP($R$3,Feiertage,3,0)=1,1,WEEKDAY($R$3)))","v":"1900-01-06"}]
[null,null,"Berechnung",null,null,null,"Arbeitsschicht AS",{"$f":"IF($I$4=\"\",\"\",$I$4/1440)","v":"08:53:00"},{"$f":"IF(AND(ISNUMBER($D$5),ISNUMBER($D$10)),IF($D$5<$D$10,($D$10-$D$5)*1440,(1-$D$5+$D$10)*1440),\"\")","v":533},{"$f":"IF(ISERROR($I$4/60),\"\",$I$4/60)","v":8.8833333333333329},null,null,null,null,null,null,null,{"$f":"DATE(YEAR($N$3),MONTH($N$3),DAY($N$3))+1","v":"2020-11-28"},"wenn Feiertag = So",{"$f":"IF(ISNA(VLOOKUP($R$4,Feiertage,1,0))=TRUE,WEEKDAY($R$4),IF(VLOOKUP($R$4,Feiertage,3,0)=1,1,WEEKDAY($R$4)))","v":"1900-01-07"}]
[null,{"$f":"WEEKDAY(N3)","v":"1900-01-06"},"Arbeitsbeginn","16:30:00",null,{"$f":"IF(AND(ISNUMBER($D$5),ISNUMBER($D$10)),ROUND($I$4-$E$13-$E$14,0),0)","v":388},{"$f":"IF($I$4=\"\",\"Arbeitszeit\",IF(AND($I$5>0,$I$4-$E$13-$E$14<360),\"Arbeitszeit = nur \"&$F$5&\"'   >> 360'\",\"Arbeitszeit\"))","v":"Arbeitszeit"},{"$f":"$I$5/1440","v":"06:28:00"},{"$f":"ROUND(IF($I$4=\"\",0,IF($F$5<360,360,$F$5)),0)","v":388},{"$f":"$I$5/60","v":6.4666666666666668},null,null,{"$f":"IF($B$5=7,\"Zulage für Nachtarbeit 18:00 - 06:00\",\"Zulage für Nachtarbeit 20:00 - 06:00\")","v":"Zulage für Nachtarbeit 20:00 - 06:00"},null,null,null,null,"Feiertage:"]
[null,null,"Pause 1 von","20:00:00",null,{"$f":"IF($A$16=1,0,IF(AND($E$13>=180,$A$17=2),0,IF($E$13>60,$E$13-60,0))+IF(AND($E$14>=180,$A$18=2),0,IF(AND($E$13>60,$E$13<180),$E$14,IF(AND($E$13>0,$E$13<=60,$E$14>0),$E$13+$E$14-60,0))))","v":85},{"$f":"IF($F$6<>$I$6,\"Pausen > 60' LP    = \"&$F$6&\"' - \"&TEXT($F$6-$I$6,0),\"Pausen > 60' LP\")","v":"Pausen > 60' LP"},null,{"$f":"IF(AND($F$5<360,$F$5+$F$6<=360),0,IF(AND($F$5<360,$F$5+$F$6>360),$F$5+$F$6-360,$F$6))","v":85},{"$f":"$I$6/60","v":1.4166666666666667},null,null,{"$f":"IF(AND(OR($D$5>$D$10,$D$5=0),$D$10<=6/24),$D$10,IF(AND($D$5>$D$10,$D$10>6/24),6/24,IF(AND($D$5<6/24,$D$10>6/24),6/24-$D$5,IF(AND($D$5<6/24,$D$10<=6/24),$D$10-$D$5,0))))+IF($B$5<7,IF($I$4=\"\",0,IF(AND($D$5>20/24,$D$5<$D$10),$D$10-$D$5,IF($D$5>20/24,1-$D$5,IF($D$5>$D$10,4/24,IF($D$10>20/24,$D$10-20/24,0))))))+IF($B$5=7,IF($I$4=\"\",0,IF(AND($D$5>18/24,$D$5<$D$10),$D$10-$D$5,IF($D$5>18/24,1-$D$5,IF($D$5>$D$10,6/24,IF($D$10>18/24,$D$10-18/24,0))))),0)","v":"05:23:00"},"hh:mm",null,null,null,{"$f":"YEAR($N$3+1)","v":2020},"Name","Sonntag = 1"]
[null,null,"Pause 1 bis","21:50:00",null,{"$f":"IF(AND(A17=0,E13>0),\"  P? = Heim\",\"\")","v":""},"Auswärtspausen  (15%)",null,{"$f":"ROUND(IF(OR(AND($E$13>=180,$A$17=2),AND($A$17=1,$A$16=1)),$E$13*15%,IF(AND($A$17=1,$E$13>60,A16=1),$E$13*15%,IF(AND($A$17=1,$E$13>60),9,IF(AND($A$17=1,$E$13<=60),$E$13*15%,0))))+IF(OR(AND($E$14>=180,$A$18=2),AND($A$18=1,$A$16=1)),$E$14*15%,IF(AND($A$18=1,$E$14>60,$A$16=1),$E$14*15%,IF(AND($A$18=1,$E$14>0,$E$13<=60),(60-$E$13)*15%,0))),1)","v":9},{"$f":"$I$7/60","v":0.15},null,null,{"$f":"ROUND($M$6*24,2)","v":5.38},6,{"$f":"ROUND($M$7*$N$7*2,1)/2","v":32.299999999999997},null,null,{"$f":"DATE($R$6,1,1)","v":"2020-01-01"},"Neujahr",1]
[null,null,"Pause 2 von","22:00:00",null,null,"ND 1  (0:00 - 4:00 / 5:00  30%)",null,{"$f":"ROUND(IF(AND(OR($D$5>$D$10,$D$5=0),$D$10<=5/24),$D$10*432,IF(AND($D$5>$D$10,$D$10>5/24),90,IF(AND($D$5<4/24,$D$10>5/24),(5/24-$D$5)*432,IF(AND($D$5<4/24,$D$10<=5/24),($D$10-$D$5)*432,0))))-IF(AND($D$6>$D$7,$D$7<=5/24),$D$7*432,IF(AND($D$6<$D$7,$D$6<=5/24,$D$7<=5/24),($D$7-$D$6)*432,IF(AND($D$6<$D$7,$D$6<=5/24),(5/24-$D$6)*432,IF(AND($D$6>$D$7,$D$7>=5/24),90,0))))-IF(AND($D$8>$D$9,$D$9<=5/24),$D$9*432,IF(AND($D$8<$D$9,$D$8<=5/24,$D$9<=5/24),($D$9-$D$8)*432,IF(AND($D$8<$D$9,$D$8<=5/24),(5/24-$D$8)*432,IF(AND($D$8>$D$9,$D$9>=5/24),90,0))))+$E$15*30%,1)","v":24.9},{"$f":"$I$8/60","v":0.41499999999999998},null,null,null,null,null,null,null,{"$f":"DATE($R$6,1,2)","v":"2020-01-02"},"Berchtoldstag",1]
[null,null,"Pause 2 bis","22:35:00",null,{"$f":"IF(AND(A18=0,E14>0),\"  P? = Heim\",\"\")","v":""},"ND LF  (0:00 - 6:00  10%)",null,{"$f":"IF($A$16=1,0,ROUND(IF(AND(OR($D$5>$D$10,$D$5=0),$D$10<=6/24),$D$10*144,IF(AND($D$5>$D$10,$D$10>6/24),36,IF(AND($D$5<6/24,$D$10<=6/24),($D$10-$D$5)*144,IF($D$5<6/24,(6/24-$D$5)*144,0))))-IF(AND($D$6>$D$7,$D$7<=6/24),$D$7*144,IF(AND($D$6<$D$7,$D$6<=6/24,$D$7<=6/24),($D$7-$D$6)*144,IF(AND($D$6<$D$7,$D$6<=6/24),(6/24-$D$6)*144,IF(AND($D$6>$D$7,$D$7>=6/24),36,0))))-IF(AND($D$8>$D$9,$D$9<=6/24),$D$9*144,IF(AND($D$8<$D$9,$D$8<=6/24,$D$9<=6/24),($D$9-$D$8)*144,IF(AND($D$8<$D$9,$D$8<=6/24),(6/24-$D$8)*144,IF(AND($D$8>$D$9,$D$9>=6/24),36,0))))+$E$15*10%,1))","v":8.3000000000000007},{"$f":"$I$9/60","v":0.13833333333333334},null,null,null,null,null,null,null,{"$f":"R10-2","v":"2020-04-10"},"Karfreitag",1]
[null,{"$f":"IF(AND(ISNUMBER($D$5),ISNUMBER($D$10),$D$5>$D$10),WEEKDAY($N$3+1),\"\")","v":"1900-01-07"},"Arbeitsende","01:23:00",null,null,"ND 2  (20:00 - 0:00  10%)",null,{"$f":"ROUND(IF($I$4=\"\",0,IF(AND($D$5>20/24,$D$5<$D$10),($D$10-$D$5)*144,IF($D$5>20/24,(1-$D$5)*144,IF($D$5>$D$10,24,IF($D$10>20/24,($D$10-20/24)*144,0)))))-IF(AND($D$6<=20/24,$D$7<$D$6),24,IF(AND($D$6<20/24,$D$7<20/24),0,IF(AND($D$6<=20/24,$D$7>20/24),($D$7-20/24)*144,IF(AND($D$6>20/24,$D$7>20/24),($D$7-$D$6)*144,IF(AND($D$7<$D$6,$D$6>20/24),(1-$D$6)*144,0)))))-IF(AND($D$8<=20/24,$D$9<$D$8),24,IF(AND($D$8<20/24,$D$9<20/24),0,IF(AND($D$8<=20/24,$D$9>20/24),($D$9-20/24)*144,IF(AND($D$8>20/24,$D$9>20/24),($D$9-$D$8)*144,IF(AND($D$9<$D$8,$D$8>20/24),(1-$D$8)*144,0))))),1)","v":9.5},{"$f":"$I$10/60","v":0.15833333333333333},null,null,"Sonntagszulage",null,null,null,null,{"$f":"DOLLAR((DAY(MINUTE($R$6/38)/2+55)&\".4.\"&$R$6)/7,)*7-6","v":"2020-04-12"},"Ostersonntag",1]
[null,null,{"$f":"IF(OR(AND(E13<180,A17=2),AND(E14<180,A18=2)),\" Pause < 180' = keine Übernachtung: Pausenart ändern!\",IF(OR(AND(E13>=180,A17=1),AND(E14>=180,A18=1)),\" Bei Auswärtspause Übernachtung: Pausenart ändern!\",\"\"))","v":""},null,null,null,"Arbeitszeit Total",{"$f":"I11/1440","v":0.36437499999999995},{"$f":"SUM($I$5:$I$10)","v":524.69999999999993},{"$f":"SUM($J$5:$J$10)","v":8.7449999999999992},null,null,{"$f":"IF(ISNA(VLOOKUP($R$3,Feiertage,1,0))=TRUE,\"Arbeitsbeginn = \"&TEXT(WEEKDAY($R$3),\"TTTT\"),IF(VLOOKUP($R$3,Feiertage,3,0)=1,\"Arbeitsbeginn = Feiertag\",\"\"))","v":"Arbeitsbeginn = Freitag"},null,null,null,null,{"$f":"R10+1","v":"2020-04-13"},"Ostermontag",1]
[null,null,{"$f":"IF(A16=1,\"\",IF(OR(AND(E13>0,E13<30),AND(E14>0,E14<30))=TRUE,\"Lf-Pause ist kleiner 30min: Zeitangaben löschen !\",IF(AND(E13=0,E14>0),\"Pause 1 fehlt !\",\"\")))","v":""},null,null,null,null,null,null,null,null,null,{"$f":"IF(ISNA(VLOOKUP($R$4,Feiertage,1,0))=TRUE,\"Arbeitsende = \"&TEXT(WEEKDAY($R$4),\"TTTT\"),IF(VLOOKUP($R$4,Feiertage,3,0)=1,\"Arbeitsende = Feiertag\",\"\"))","v":"Arbeitsende = Samstag"},null,null,null,null,{"$f":"R10+39","v":"2020-05-21"},"Auffahrt",1]
[null,null,"Pause 1","in Minuten",{"$f":"IF(OR(D6=\"\",D7=\"\"),0,ROUND(IF(D6<D7,(D7-D6)*1440,IF(D6>D7,(1-D6+D7)*1440)),0))","v":110},null,"Arbeitszeit Total aufgerundet\n>> PIPER",{"$f":"ROUND(I13,0)/1440","v":0.36458333333333331},{"$f":"ROUNDUP(SUM($I$5:$I$10),0)","v":525},{"$f":"I13/60","v":8.75},null,null,{"$f":"(IF($I$4=\"\",,IF($D$5=\"\",0,IF($T$3=1,(IF($D$5<$D$10,$D$10-$D$5,1-$D$5)-IF(AND($D$5>$D$10,$D$6<$D$7,$D$6<$D$5),0,IF($D$6<$D$7,$D$7-$D$6,IF($D$6>$D$7,1-$D$6,0)))-IF(AND($D$5>$D$10,$D$8<$D$9,$D$8<$D$5),0,IF($D$8<$D$9,$D$9-$D$8,IF($D$8>$D$9,1-$D$8,0)))),0))+IF(AND($T$4=1,$D$5>$D$10),($D$10-IF(AND($D$5>$D$10,$D$6<$D$7,$D$6<$D$5),$D$7-$D$6,IF($D$6>$D$7,$D$7,0))-IF(AND($D$5>$D$10,$D$8<$D$9,$D$8<$D$5),$D$9-$D$8,IF($D$8>$D$9,$D$9,0))),0))+IF(AND($T$3=1,$D$5<5/24,$I$5>0),$E$15/1440,0)+IF(AND($D$5>$D$10,$T$4=1),$E$15/1440,0))","v":"00:00:00"},"hh:mm",null,null,null,{"$f":"R10+49","v":"2020-05-31"},"Pfingstsonntag",1]
[null,null,"Pause 2","in Minuten",{"$f":"IF(OR(D8=\"\",D9=\"\"),0,ROUND(IF(D8<D9,(D9-D8)*1440,IF(D8>D9,(1-D8+D9)*1440)),0))","v":35},null,null,null,null,null,null,null,{"$f":"ROUND($M$13*24,2)","v":0},16,{"$f":"ROUND($M$14*$N$14*2,1)/2","v":0},null,null,{"$f":"R10+50","v":"2020-06-01"},"Pfingstmontag",1]
[null,null,"P > 60' von 00:00 - 05:00 h",null,{"$f":"ROUND((IF(AND(E13>=180,A17=2),0,IF(AND(E13>60,A16<>1),(IF(AND(D6>D7,(D6+1/24)<=1),D7,IF(AND(D6>D7,(D6+1/24)>1),(D7+1-(D6+1/24)),IF(AND(D6<=4/24,D7<=5/24),(D7-(D6+1/24)),IF(AND((D6+1/24)<=5/24,D7>5/24),(5/24-(D6+1/24)),0))))),0))+IF(AND(E14>=180,A18=2),0,IF(A16<>1,IF(AND(D8>D9,E13<=60,(D8-E13/1440+1/24)>1),(D9-(D8-E13/1440-23/24)),IF(OR(AND(D8>D9,E13<=60,(D8-E13/1440+1/24)<=1),AND(D8>D9,E13>60)),D9,IF(AND(D8<5/24,D9>=5/24,E13<60),(5/24-(D8-E13/1440+1/24)),IF(AND(D8<5/24,D9>=5/24,E13>=60),(5/24-D8),IF(AND(E14>0,D8<5/24,D9<5/24,E13<60),(D9-(D8-E13/1440+1/24)),IF(AND(E14>0,D8<5/24,D9<5/24,E13>=60),(D9-D8),0)))))))))*1440,0)","v":0},null,"Nachtdienst 3 (> 55j.) in Min.",{"$f":"IF($I$4=\"\",0,$I$8/3)","v":8.2999999999999989},{"$f":"IF($I$4=\"\",0,ROUNDUP($I$8/3,0))","v":9},{"$f":"$H$15/60","v":0.13833333333333331},null,null,null,null,null,null,null,{"$f":"R10+60","v":"2020-06-11"},"Fronleichnam",0]
[2,null,{"$f":"IF(AND(ISNUMBER($D$5),ISNUMBER($D$6),D6-D5+N(D6<D5)<1/16),\"AB bis zur Pause nicht 90 Minuten !\",\"\")","v":""},null,null,null,"Berechnungen IND h ohne Rundung",null,null,null,null,null,null,null,null,null,null,{"$f":"DATE($R$6,8,1)","v":"2020-08-01"},"1. August",1]
[1,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"DATE($R$6,11,1)+7*4-MOD(DATE($R$6,11,1)+4,7)-1","v":"2020-11-23"},"Zibelemärit",0]
[1,null,{"$f":"IF($I$5>540,\"Hinweis AZ > 540': Keine zwei solchen Touren in Folge  /  AS maximal 11 Std.  /  Tour nicht 24:00 - 04:00\",\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"DATE($R$6,12,25)","v":"2020-12-25"},"Weihnachten",1]
[2,null,{"$f":"IF($I$5>540,\"Abweichungen sind im Rahmen betriebliche Mitwirkung (Mitentscheid) oder in Absprache mit dem betroffenen Personal möglich\",\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"DATE($R$6,12,26)","v":"2020-12-26"},"Stephanstag",1]
[null,null,"Alle Angaben ohne Gewähr !",null,null,null,null,null,null,null,null,null,"© Christian Meyer     Lf SBB Bern",null,null,null,null,null,null,1]
[]
[null,"Legende:"]
[null,"Arbeitsbeginn / -ende",null,"00:00","Zellformat hh:mm"]
[null,"Zelle Eingabe möglich",null,"X","Diverse Eingaben oder Auswahl"]
[null,"Stunden:Minuten / Min.",null,"00:00  /  0.00","Zellformat hh:mm  oder  Minuten 0.00"]
[null,"Industriestunden",null,"0.00","Ungerundet, Anzeige 2 oder 6 Stellen nach Komma  /  Für Zulagen in Spalte M = 2 Stellen gerundet"]
[null,"Berechnung von Zulagen",null,1E-3,"Zellformat Buchhaltung"]
[null,"Hinweis 1",null,"Abc","z.B. Feiertag"]
[null,"Hinweis 2",null,"Abc","Pause / Arbeitszeit"]
[null,"Hinweis 3",null,"Abc","Pause / Übernachtung"]
[null,"Hinweis 4",null,"Abc","Pause Art nicht ausgewählt = Pause \"Heim\""]
[]
[null,"Hinweise:"]
[null,"Damit die Funktionen [Daten löschen / Kalender / Heute] + \"Rundung IND h\" bedienbar sind: Sicherheitswarnung > Inhalt aktivieren (Makros VBA zulassen)"]
[]
[null,"Version 3 vom 01.12.2019"]
[null,"Änderungen:"]
[null,"- Anpassung Auswärtspause 15% statt Zuschlag"]
[null,"- Pausen für \"Andere\" statt \"Lokführer\" > Möglicherweise fehlerhaft und nicht überprüft"]

{"sheet":"Umrechnungen","index":1,"n_rows":24,"n_cols":9,"rows_kept":24,"cut":false,"hidden":false,"merged":[[1,1,1,3]]}
[]
[null,"Umrechnung hh:mm <> Industriestunden",null,null,null,null,"Sekunden","Ind. Stunden","Ind. X XX"]
[null,"hh:mm",null,"IND H X.XX",null,null,36,0.01,0.01]
[null,0.38263888888888892,">   >",{"$f":"B4*24","v":9.1833333333333336},null,null,"Minuten","Ind. Stunden","Ind. X XX"]
[null,{"$f":"D5/24","v":0.35749999999999998},"<   <",8.58,null,null,{"$f":"H5*60","v":0.6},0.01,0.01]
[null,"Minuten","<   > ","IND H X.XX",null,null,{"$f":"H6*60","v":1.2},0.02,0.02]
[null,1,">   >",{"$f":"B7/60","v":1.6666666666666666E-2},null,null,1,{"$f":"ROUND(G7/60,8)","v":1.6666670000000001E-2},{"$f":"G7/60","v":1.6666666666666666E-2}]
[null,{"$f":"D8*60","v":60},"<   <",1,null,null,2,{"$f":"ROUND(G8/60,8)","v":3.3333330000000001E-2},{"$f":"G8/60","v":3.3333333333333333E-2}]
[null,"von hh:mm","bis hh:mm",null,null,null,{"$f":"H9*60","v":2.4},0.04,0.04]
[null,"18:00:00","01:18:00",null,null,null,3,0.05,0.05]
[null,{"$f":"IF(C10>B10,C10-B10,1-B10+C10)","v":"07:18:00"},"< hh:mm",null,null,null,4,6.6666669999999997E-2,6.6666666666666666E-2]
[null,null,"IND H X.XX >",{"$f":"IF(C10>B10,(C10-B10)*24,(1-B10+C10)*24)","v":7.3000000000000007},null,null,5,8.3333329999999997E-2,8.3333333333333329E-2]
[null,null,"IND H unger. >",{"$f":"IF(C10>B10,(C10-B10)*24,(1-B10+C10)*24)","v":7.3000000000000007},null,null,6,0.1,0.1]
[null,null,null,null,null,null,7,0.11666667,0.11666666666666667]
[null,"X"," > Zellen Eingaben möglich",null,null,null,8,0.13333333,0.13333333333333333]
[null,null,null,null,null,null,9,0.15,0.15]
[null,null,null,null,null,null,10,0.16666666999999999,0.16666666666666666]
[null,null,null,null,null,null,15,0.25,0.25]
[null,null,null,null,null,null,20,0.33333332999999998,0.33333333333333331]
[null,null,null,null,null,null,25,0.41666667000000002,0.41666666666666669]
[null,null,null,null,null,null,30,0.5,0.5]
[null,null,null,null,null,null,45,0.75,0.75]
[null,null,null,null,null,null,59,0.98333333000000001,0.98333333333333328]
[null,null,null,null,null,null,60,1,1]