{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Annual Leave Calculator","Time Converter","XXXXX"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":26,"comments":0,"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":"Annual Leave Calculator","index":0,"n_rows":23,"n_cols":38,"rows_kept":23,"cut":false,"hidden":false,"merged":[[19,1,19,30],[19,31,19,36],[20,1,20,30],[20,31,20,36],[22,1,22,50],[16,1,16,36],[13,1,13,30],[17,1,17,30],[17,31,17,36],[18,1,18,30],[18,31,18,36],[15,1,15,36],[13,37,14,50],[9,1,9,32],[9,33,9,36],[5,12,5,16],[2,1,2,50],[5,6,5,11],[5,17,5,22],[5,34,5,39],[5,23,5,33],[5,1,5,5],[4,1,4,14],[3,19,4,32],[13,31,13,36],[14,31,14,36],[14,1,14,30],[12,1,12,36],[6,1,6,39],[8,46,8,49],[10,26,10,31],[10,1,10,25],[7,1,7,17],[7,18,7,21],[8,1,8,45]]}
[]
[]
[null,"Annual Leave Calculator"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"April 2023 Onwards"]
[null," POST DETAILS (this post only)"]
[null,"Start Date",null,null,null,null,null,null,null,null,null,null,"End Date",null,null,null,null,null,null,null,null,null,null," Weekly Contractual Hours"]
[null,"       Tick here to confirm this calculation is for a NJC employee. For other Terms & Conditions please contact HR."]
[null,"Does the employee leave during this period?"]
[null,"Does the employee have over 5 years Local Government service at the start of this period?  (excluding time only on a casual contract)"]
[null,"Does the employee reach 5 years Local Government service part way through this period?"]
[null,"What date did the employee reach 5 years Local Government service?"]
[]
[null," LEAVE RECORD"]
[null," How many hours leave has the employee taken this year?",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(Cell_Leave_To_Be_Taken>0,\"If this changes before the leave date you must inform Payroll immediately or the employee will not be paid correctly.\",\"\")","v":""}]
[null," How many additional hours leave is the employee expected to take before leaving?"]
[]
[null," LEAVE ENTITLEMENT IN HOURS"]
[null," Standard Full Time Annual Leave Entitlement",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(Cell_XXXXX_Initial_Form_Fully_Completed=FALSE,\"\",IF(Cell_5_Years_Service_Ind=\"Yes\",237.6,IF(AND(Cell_5_Years_Service_Ind=\"No\",Cell_5_Years_Service_Mid_Period_Ind=\"No\"),201.6,IF(AND(Cell_5_Years_Service_Ind=\"No\",Cell_5_Years_Service_Mid_Period_Ind=\"Yes\"),201.6+(36/12*Cell_XXXXX_Full_Months_Worked_At_Higher_Years_Service)))))","v":""}]
[null," Pro-Rata'd Leave Entitlement  (based on the period employed and contractual hours worked)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(Cell_XXXXX_Initial_Form_Fully_Completed=FALSE,\"\",IF(AND(Cell_5_Years_Service_Ind=2,Cell_5_Years_Service_Mid_Period_Ind=1),201.6/12*Cell_XXXXX_Full_Months_Worked_This_Year/36*Cell_Weekly_Contractual_Hours+(36/12*Cell_XXXXX_Full_Months_Worked_At_Higher_Years_Service),AF18/12*Cell_XXXXX_Full_Months_Worked_This_Year/36*Cell_Weekly_Contractual_Hours))","v":""}]
[null," Leave Taken To Date (inc leave to be taken)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(Cell_XXXXX_Initial_Form_Fully_Completed=FALSE,\"\",SUM(Cell_Leave_Taken_This_Year,Cell_Leave_To_Be_Taken))","v":""}]
[null,{"$f":"IF(AF21=\"\",\"\",IF(ROUND(AF19-AF20,2)<0,\" Overtaken Leave Entitlement To Be Recovered On The Termination Form\",\" Leave Entitlement To Be Included On The Termination Form\"))","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(Cell_XXXXX_Initial_Form_Fully_Completed=FALSE,\"\",(IF(AF19-AF20<0,-(AF19-AF20),(AF19-AF20))))","v":""}]
[]
[null,{"$f":"IF(OR(AND(AF21>-0.01,AF21<0.01),AF21=\"\"),\"\",IF(AF19-AF20>0,IF(OR(Cell_Leaver_Check_Ind=\"Yes\",DAY(Cell_End_Date)<>31,MONTH(Cell_End_Date)<>3),\"Council policy states that an employee must make every endeavour to take leave entitlement before their termination date.\",\"The employee should aim to take all their outstanding leave before the end of the holiday year.\"),IF(AND(AF19-AF20<0,Cell_Leave_To_Be_Taken=0),\"The employee has overtaken their leave entitlement. The oustanding balance will be deducted from their final pay if possible, otherwise any overpayment will be invoiced.\",IF(AND(AF19-AF20<0,Cell_Leave_Taken_This_Year>AF19),\"This employee has overtaken their leave entitlement and has extra leave booked in the future. \"&\"Please cancel any additional leave booked to avoid a higher overpayment. If the employee still needs the leave they will need to apply for unpaid leave. \"&\"The oustanding balance from the overtaken holidays that the employee has already taken will be deducted from their final pay if possible, otherwise any overpayment will be invoiced.\",\"This employee is set to overtake their leave entitlement. \"&\"Please cancel any additional leave booked to avoid an overpayment and invoice. If the employee still needs the leave they will need to apply for unpaid leave.\"))))","v":""}]

{"sheet":"Time Converter","index":1,"n_rows":4,"n_cols":4,"rows_kept":4,"cut":false,"hidden":false,"merged":[[1,1,1,3]]}
[]
[null,"TIME TO DECIMAL CONVERTER"]
[null,"Hours","Minutes","Decimal"]
[null,null,null,{"$f":"IF(OR(B4=\"\",C4=\"\"),\"\",B4+(C4/60))","v":""}]

{"sheet":"XXXXX","index":2,"n_rows":13,"n_cols":2,"rows_kept":13,"cut":false,"hidden":true,"merged":[]}
["Initial Form Fully Completed?",{"$f":"IF(OR(Cell_Start_Date=\"\",Cell_End_Date=\"\",Cell_Weekly_Contractual_Hours=\"\",Cell_XXXXX_NJC_Ind=FALSE,AND(Cell_XXXXX_Leaving_31_March_Ind=TRUE,Cell_Leaver_Check_Ind=\"\"),Cell_5_Years_Service_Ind=\"\",AND(Cell_5_Years_Service_Ind=\"No\",Cell_5_Years_Service_Mid_Period_Ind=\"\"),AND(Cell_5_Years_Service_Mid_Period_Ind=\"Yes\",Cell_Date_Hit_5_Years_Service=\"\")),FALSE,TRUE)","v":false}]
[]
["Full/Part/Over 36: ",{"$f":"IF(Cell_Weekly_Contractual_Hours=\"\",0,IF(Cell_Weekly_Contractual_Hours=36,1,IF(Cell_Weekly_Contractual_Hours<36,2,IF(Cell_Weekly_Contractual_Hours>36,3))))","v":0}]
["NJC:",false]
[]
["Date Leaving 31st March:",{"$f":"IF(AND(DAY(Cell_End_Date)=31,MONTH(Cell_End_Date)=3),TRUE,FALSE)","v":false}]
["Leaving Before Year End:",{"$f":"IF(OR(Cell_XXXXX_Leaving_31_March_Ind=FALSE,AND(Cell_XXXXX_Leaving_31_March_Ind=TRUE,Cell_Leaver_Check_Ind=\"Yes\")),TRUE,FALSE)","v":true}]
["Full Months Worked This Year: ",{"$f":"IF(OR(Cell_Start_Date=\"\",Cell_End_Date=\"\"),0,IF(AND(Cell_Start_Date=DATE(YEAR(Cell_Start_Date),MONTH(Cell_Start_Date),1),Cell_End_Date=EOMONTH(Cell_End_Date,0)),(YEAR(Cell_End_Date)-YEAR(Cell_Start_Date))*12+MONTH(Cell_End_Date)-MONTH(Cell_Start_Date)+1,IF(Cell_End_Date=EOMONTH(Cell_End_Date,0),(YEAR(Cell_End_Date)-YEAR(Cell_Start_Date))*12+MONTH(Cell_End_Date)-MONTH(Cell_Start_Date),IF(DAY(Cell_End_Date)-DAY(Cell_Start_Date)>=-1,(YEAR(Cell_End_Date)-YEAR(Cell_Start_Date))*12+MONTH(Cell_End_Date)-MONTH(Cell_Start_Date),(YEAR(Cell_End_Date)-YEAR(Cell_Start_Date))*12+MONTH(Cell_End_Date)-MONTH(Cell_Start_Date)-1))))","v":0}]
["Full Months Worked At Higher Years Service: ",{"$f":"IF(OR(Cell_Date_Hit_5_Years_Service=\"\",Cell_End_Date=\"\"),0,IF(AND(Cell_Date_Hit_5_Years_Service=DATE(YEAR(Cell_Date_Hit_5_Years_Service),MONTH(Cell_Date_Hit_5_Years_Service),1),Cell_End_Date=EOMONTH(Cell_End_Date,0)),(YEAR(Cell_End_Date)-YEAR(Cell_Date_Hit_5_Years_Service))*12+MONTH(Cell_End_Date)-MONTH(Cell_Date_Hit_5_Years_Service)+1,IF(Cell_End_Date=EOMONTH(Cell_End_Date,0),(YEAR(Cell_End_Date)-YEAR(Cell_Date_Hit_5_Years_Service))*12+MONTH(Cell_End_Date)-MONTH(Cell_Date_Hit_5_Years_Service),IF(DAY(Cell_End_Date)-DAY(Cell_Date_Hit_5_Years_Service)>=-1,(YEAR(Cell_End_Date)-YEAR(Cell_Date_Hit_5_Years_Service))*12+MONTH(Cell_End_Date)-MONTH(Cell_Date_Hit_5_Years_Service),(YEAR(Cell_End_Date)-YEAR(Cell_Date_Hit_5_Years_Service))*12+MONTH(Cell_End_Date)-MONTH(Cell_Date_Hit_5_Years_Service)-1))))","v":0}]
[]
["Tax Year: ",{"$f":"IF(Cell_Start_Date=\"\",0,IF(MONTH(Cell_Start_Date)<4,YEAR(Cell_Start_Date)-1,YEAR(Cell_Start_Date)))","v":0}]
["End Date Check: ",{"$f":"IF(AND(Cell_Start_Date<>\"\",Cell_End_Date>Cell_Start_Date,Cell_End_Date<=DATEVALUE(CONCATENATE(\"31/03/\",Cell_XXXXX_Tax_Year+1))),TRUE,FALSE)","v":false}]
["Start & End Date Check: ",{"$f":"IF(AND(Cell_Start_Date<>\"\",Cell_End_Date<>\"\",Cell_Date_Hit_5_Years_Service>=Cell_Start_Date,Cell_Date_Hit_5_Years_Service<=Cell_End_Date),TRUE,FALSE)","v":false}]