{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Explications","Simulateur","Calcul IJ","Parametres"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":3,"comments":1,"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":"Explications","index":0,"n_rows":11,"n_cols":2,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"Bonjour,"]
[]
[null,"Pour le fonctionnement du calcul, il faut d'abord sélectionner le salarié en colonne B (24 à26) de l'onglet simulateur, "]
[null,"puis le type d'absence en colonne E et qui déclenche la mise à jour par la liste déroulante."]
[]
[null,"Le fonctionnement suppose que l'on ne change rien à l'ordre des lignes ou colonnes, ni aux noms des onglets, sauf si vous arrivez à changer la macro. "]
[null,"Par contre, en paramètres, vous pouvez ajouter votre base de salariés, jusqu'à 100."]
[]
[null,"Cordialement"]

{"sheet":"Simulateur","index":1,"n_rows":51,"n_cols":11,"rows_kept":51,"cut":false,"hidden":false,"merged":[[49,1,50,1],[39,2,39,3],[40,2,40,3],[49,2,49,3],[50,2,50,3],[43,2,43,3],[44,2,44,3],[45,2,45,3],[46,2,46,3],[47,2,47,3],[48,2,48,3],[39,1,40,1],[41,1,42,1],[41,2,41,3],[42,2,42,3],[43,1,44,1],[47,1,48,1],[37,1,38,1],[36,2,36,3],[28,5,28,6],[30,1,30,2],[29,5,29,6],[9,1,9,2],[10,1,10,2],[28,1,28,2],[20,5,20,6],[45,1,46,1]]}
[]
[null,"L","M","M","J","V","S","D",null,"mois","Octobre"]
[null,{"$f":"IF(WEEKDAY(DATE(_Annee,MONTH(_Mois&1),1),2)=COLUMN()-1,DATE(_Annee,MONTH(_Mois&1),1),IF(A6>0,A6+1,0))","v":"00:00:00"},{"$f":"IF(WEEKDAY(DATE(_Annee,MONTH(_Mois&1),1),2)=COLUMN()-1,DATE(_Annee,MONTH(_Mois&1),1),IF(B3>0,B3+1,0))","v":"00:00:00"},{"$f":"IF(WEEKDAY(DATE(_Annee,MONTH(_Mois&1),1),2)=COLUMN()-1,DATE(_Annee,MONTH(_Mois&1),1),IF(C3>0,C3+1,0))","v":"00:00:00"},"00:00:00",{"$f":"IF(WEEKDAY(DATE(_Annee,MONTH(_Mois&1),1),2)=COLUMN()-1,DATE(_Annee,MONTH(_Mois&1),1),IF(E3>0,E3+1,0))","v":"00:00:00"},"2022-10-01","2022-10-02",null,"Année",2022]
[null,{"$f":"H3+1","v":"2022-10-03"},{"$f":"B4+1","v":"2022-10-04"},"2022-10-05","2022-10-06","2022-10-07","2022-10-08","2022-10-09",null,null,{"$f":"DATE(_Annee,MONTH(_Mois&1),1)","v":"2022-10-01"}]
[null,{"$f":"H4+1","v":"2022-10-10"},"2022-10-11","2022-10-12","2022-10-13","2022-10-14","2022-10-15","2022-10-16"]
[null,{"$f":"H5+1","v":"2022-10-17"},"2022-10-18","2022-10-19","2022-10-20","2022-10-21","2022-10-22","2022-10-23"]
[null,{"$f":"IFERROR(IF(MONTH(H6+1)=MONTH(_Mois&1),H6+1,\"\"),\"\")","v":"2022-10-24"},{"$f":"IFERROR(IF(MONTH(B7+1)=MONTH(_Mois&1),B7+1,\"\"),\"\")","v":"2022-10-25"},"2022-10-26","2022-10-27","2022-10-28","2022-10-29","2022-10-30"]
[null,{"$f":"IFERROR(IF(MONTH(H7+1)=MONTH(_Mois&1),H7+1,\"\"),\"\")","v":"2022-10-31"},"","","","","",""]
[]
[null,"Ancienneté du salarié",null,"Liste déroulante (conditions d'indemnisation)",null,null,"Jours ouvrés ",{"$f":"NETWORKDAYS(DATE(_Annee,MONTH(_Mois&1),1),EOMONTH(DATE(_Annee,MONTH(_Mois&1),1),0),_Feries)","v":21}]
[null,"Complément de salaire",null,"Liste déroulante (oui/non)",null,null,"Jours ouvrables ",{"$f":"NETWORKDAYS.INTL(DATE(_Annee,MONTH(_Mois&1),1),EOMONTH(DATE(_Annee,MONTH(_Mois&1),1),0),11,_Feries)","v":26}]
[null,null,null,null,null,null,"Jours calendaires ",{"$f":"DATEDIF(DATE(_Annee,MONTH(_Mois&1),1),EOMONTH(DATE(_Annee,MONTH(_Mois&1),1),0)+1,\"d\")","v":31}]
[]
[null,"Calcul IJSS Sécurité Sociale"]
[]
[null,"Plafond IJ maladie",null,49.68,null,null,null,"M-1","M-2","M-3"]
[null,"Plafond IJ maternité / paternité",null,89.03,null,null,null,1000,1000,1000]
[null,"Plafond IJ accident du travail",null,205.84,"jusqu'au 28ème jour",null,"Plafond",{"$f":"1678.95*1.8","v":3022.11},{"$f":"1678.95*1.8","v":3022.11},{"$f":"1678.95*1.8","v":3022.11}]
[null,null,null,274.45999999999998,"A compter du 29ème jour"]
[]
[null,null,null,null,null,"SS",null,"Employeur"]
[null,"Salarié","Début d'arret","Fin d'arret","Type d'arret","Carence\n SS","indemnisation 1","Carence\n emp.","Indemnisation 2"]
[null,"DDDDDDD georges","2022-09-01","2022-09-15","Maladie",{"$f":"IF(E23<>\"Maladie\",\"\",3)","v":3},478.51397260273973,{"$f":"IF(E23<>\"Maladie\",\"\",7)","v":7},199.38082191780825]
[null,"DDDDDDD georges","2022-09-01","2022-09-15","Accident du travail","",690.09,"",690.09]
[null,"DDDDDDD georges","2022-09-01","2022-09-15","Maternité Paternité","",945.06509589041116,"",945.06509589041116]
[null,null,null,null,null,"",{"$f":"IFERROR(IF(D26=\"\",\"\",IF(#REF!=\"non\",D26-C26-F26,D26-C26)+1),\"\")","v":""},"",{"$f":"IFERROR(IF(E26=\"\",\"\",IF(F26=\"non\",E26-#REF!-H26,E26-#REF!)+1),\"\")","v":""}]
[null,null,"Total",null,null,null,{"$f":"SUBTOTAL(109,G23:G26)","v":2113.6690684931509}]
[]
[null,"SJB (Mal-Mat-Pat)",null,{"$f":"SUM(H17:J17)/91.25","v":32.876712328767127},null,"IJSS brute totale",null,{"$f":"D31*G23","v":7865.9831112779138}]
[null,"Gain journalier (AT)",null,0,null,"IJSS nette totale",null,{"$f":"H29*0.933","v":7338.9622428222938}]
[null,"IJSS Brute journalière",null,{"$f":"IF($D$29*50%<49.68,D29*50%,49.68)","v":16.438356164383563}]
[]
[null,"Calcul complément employeur"]
[]
[null,"Conditions d'idemnisation :"]
[]
[null,"Ancienneté","Durée d’indemnisation",null,null,"Calcul montant perçu si le salarié avait été présent (prendre en compte le délai de carence pour le calcul de la période)"]
[null,"De 1 à 5 ans","30 jours à 90 %",null,null,"Période du 08/08 au 15/08 soit 8 jours : "]
[null,null,"30 jours à 66,66 %",null,null,"(4000 / nbre de jours ouvrés)*nbre de jours d'absence (hos samedi et dimanche) soit "]
[null,"De 6 à 10 ans","40 jours à 90 %"]
[null,null,"40 jours à 66,66 %"]
[null,"De 11 à 15 ans","50 jours à 90 %",null,null,"L'employeur complète à hauteur de 90 %"]
[null,null,"50 jours à 66,66 %",null,null,{"$f":"F40*90%","v":0}]
[null,"De 16 à 20 ans","60 jours à 90 %"]
[null,null,"60 jours à 66,66 %"]
[null,"De 21 à 25 ans","70 jours à 90 %"]
[null,null,"70 jours à 66,66 %"]
[null,"De 26 à 30 ans","80 jours à 90 %"]
[null,null,"80 jours à 66,66 %"]
[null,"À partir de 31 ans","90 jours à 90 %"]
[null,null,"90 jours à 66,66 %"]

{"sheet":"Calcul IJ","index":2,"n_rows":16,"n_cols":4,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
[null,"Maladie","Accident du travail","Maternité Paternité"]
["Plafond SS",3022.11,343.02,3428]
["Plafond IJSS brute",49.68,"205,84 (28 premiers jours)",89.03]
[null,null,"274,46 (à compter du 29ème j)"]
["DDDDDDD georges",{"$f":"VLOOKUP(Simulateur!$B$23,Parametres!$A$2:$C$100,2,FALSE)","v":2500},{"$f":"VLOOKUP(Simulateur!$B$23,Parametres!$A$2:$C$100,2,FALSE)","v":2500},{"$f":"VLOOKUP(Simulateur!$B$23,Parametres!$A$2:$C$100,2,FALSE)","v":2500}]
["Etape 1 :","Calcul SJB","Calcul Gain journalier","Calcul SJB"]
[null,"3 derniers salaires / 91,25","(dernier mois / 30.42) - 21%","3 derniers salaires / 91,25"]
[{"$f":"+Simulateur!B23","v":"DDDDDDD georges"},{"$f":"VLOOKUP(Simulateur!$B$23,Parametres!$A$2:$C$10,3,FALSE)/91.25","v":85.479452054794521},{"$f":"VLOOKUP(Simulateur!$B$23,Parametres!$A$2:$C$10,2,FALSE)/30.42*0.79","v":64.924391847468769},{"$f":"VLOOKUP(Simulateur!$B$23,Parametres!$A$2:$C$10,3,FALSE)/91.25","v":85.479452054794521}]
["Etape 2 : IJ Brute","SJB*50 %","28 premiers jours","SJB - 21%"]
[null,{"$f":"+B8/2","v":42.739726027397261},"salaire brut / 30,42 * 60%",{"$f":"+D8*0.79","v":67.528767123287679}]
[null,null,{"$f":"+C5/30.42*0.6","v":49.30966469428008}]
[null,null,"À partir du 29eme jour"]
[null,null,"salaire brut / 30,42 * 80%"]
[null,null,{"$f":"+C5/30.42*0.8","v":65.746219592373436}]
["Etape 3 : IJ Nette","IJ brute à 93,3%","IJ brute à 93,3%","IJ brute à 93,3%"]
[null,{"$f":"$B$10*0.933","v":39.876164383561644},null,{"$f":"$D$10*0.933","v":63.004339726027411}]

{"sheet":"Parametres","index":3,"n_rows":14,"n_cols":11,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
["salarié","salaires du\n dernier mois","salaires des \ntrois derniers mois",null,"Mois",null,"Jour férié","Date",null,"oui","Maladie"]
["DDDDDDD georges",2500,7800,null,"Janvier",null,"Jour de l'an",{"$f":"DATE(_Annee,1,1)","v":"2022-01-01"},null,"non","Accident du travail"]
["ZZZZ Jean ",1800,{"$f":"3*B3","v":5400},null,"Février",null,"Pâques",{"$f":"ROUND(DATE(_Annee,4,MOD(234-11*MOD(_Annee,19),30))/7,0)*7-6","v":"2022-04-17"},null,null,"Maternité Paternité"]
["FFFFF Jeanine ",2000,6550,null,"Mars",null,"Lundi de Pâques",{"$f":"H3+1","v":"2022-04-18"}]
[null,null,null,null,"Avril",null,"Fête du travail",{"$f":"DATE(_Annee,5,1)","v":"2022-05-01"}]
[null,null,null,null,"Mai",null,"Victoire 39/45",{"$f":"DATE(_Annee,5,8)","v":"2022-05-08"}]
[null,null,null,null,"Juin",null,"Ascension",{"$f":"H4+38","v":"2022-05-26"}]
[null,null,null,null,"Juillet",null,"Pentecôte",{"$f":"H4+49","v":"2022-06-06"}]
[null,null,null,null,"Août",null,"Lundi de Pentecôte",{"$f":"H4+50","v":"2022-06-07"}]
[null,null,null,null,"Septembre",null,"Fête Nationale",{"$f":"DATE(_Annee,7,14)","v":"2022-07-14"}]
[null,null,null,null,"Octobre",null,"Assomption",{"$f":"DATE(_Annee,8,15)","v":"2022-08-15"}]
[null,null,null,null,"Novembre",null,"Toussaint",{"$f":"DATE(_Annee,11,1)","v":"2022-11-01"}]
[null,null,null,null,"Décembre",null,"Armistice 14/18",{"$f":"DATE(_Annee,11,11)","v":"2022-11-11"}]
[null,null,null,null,null,null,"Noël ",{"$f":"DATE(_Annee,12,25)","v":"2022-12-25"}]