{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["BAH CALC","MHA Job Aid","Instructions","Standard_NST","Error Calculation"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":1,"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":"BAH CALC","index":0,"n_rows":22,"n_cols":8,"rows_kept":22,"cut":false,"hidden":true,"merged":[[1,1,1,2],[1,5,1,6],[18,1,20,6],[21,1,23,6]]}
[]
[null,"Semester Modifier",null,null,null,"Quarter Modifier "]
[null,"Term Begin Date",null,null,null,"Term Begin Date"]
[null,"No Pay Date",null,null,null,"No Pay Date"]
[null,"Break Begin",null,null,null,"Break Begin"]
[null,"Break No Pay ",null,null,null,"Break No Pay"]
[null,"Weeks in Term",{"$f":"IF(OR(C3=\"\",C4=\"\"),\"\",ROUND(D7/1,0))","v":""},{"$f":"((B2-B1)-(B4-B3))/7","v":{"$error":"#VALUE!"}},null,"Weeks in Term",{"$f":"IF(OR(G3=\"\",G4=\"\"),\"\",ROUND(H7/1,0))","v":""},{"$f":"((G4-G3)-(G6-G5))/7","v":0}]
[null,"Total Hours ",null,null,null,"Total Hours "]
[null,"Hour Type ","Semester",null,null,"Hour Type ","Quarter"]
[null,"FT Modifier",null,null,null,"FT Modifier"]
[null,"Credit Hour Eq.",{"$f":"IFERROR(C8*18/C7,\"\")","v":""},null,null,"Credit Hour Eq.",{"$f":"IFERROR(G8*12/G7,\"\")","v":""}]
[null,"Rate of Pursuit",{"$f":"IFERROR(ROUND(D12/10,0)*10/100,\"\")","v":""},{"$f":"MIN(100%,C8*18/C7/C10)*100","v":{"$error":"#VALUE!"}},null,"Rate of Pursuit",{"$f":"IFERROR(ROUND(H12/10,0)*10/100,\"\")","v":""},{"$f":"MIN(100%,G8*12/G7/G10)*100","v":{"$error":"#VALUE!"}}]
[null,"FT BAH per Zip Code ",null,null,null,"FT BAH per Zip Code "]
[null,"Percent of Eligibility ",null,null,null,"Percent of Eligibility "]
[null,"BAH Rate ",{"$f":"IFERROR(C13*C14*C12,\"\")","v":""},null,null,"BAH Rate ",{"$f":"IFERROR(G13*G14*G12,\"\")","v":""}]
[null,null,null,null,null,null,null,1]
[null,null,null,null,null,null,null,0.9]
[null,null,null,null,null,null,null,0.8]
[null,"**If the Credit Hour Eq. is RED, STOP!!! The claimant is half-time or less and not due BAH.**",null,null,null,null,null,0.7]
[null,null,null,null,null,null,null,0.6]
[null,null,null,null,null,null,null,0.5]
[null,"** If the Credit Hour Eq. is GREEN the claimant is due BAH at the ROP shown. (Typically anything greater than 6 credit hour equivalency should pay BAH when  processed in LTS.)",null,null,null,null,null,0.4]

{"sheet":"MHA Job Aid","index":1,"n_rows":25,"n_cols":12,"rows_kept":25,"cut":false,"hidden":false,"merged":[[0,1,0,10],[1,1,1,2],[1,5,1,6],[1,9,1,10],[24,1,24,10]]}
[null,"Monthly Housing Allowance Job Aid"]
[null,"Enrollment Term Modifier",null,"Term",null,"Enrollment Term Modifier",null,"Term",null,"Enrollment Term Modifier",null,"Term"]
[null,"Term Type*",null,{"$f":"IF(C3=\"Semester\", 18, IF(C3=\"Quarter\", 12, \"\"))","v":""},null,"Term Type*",null,{"$f":"IF(G3=\"Semester\", 18, IF(G3=\"Quarter\", 12, \"\"))","v":""},null,"Term Type*",null,{"$f":"IF(K3=\"Semester\", 18, IF(K3=\"Quarter\", 12, \"\"))","v":""}]
[null,"Student Benefit Level*",null,{"$f":"IF(C3=\"Semester\", Standard_NST!$B$2, IF(C3=\"Quarter\",Standard_NST!$B$6, \"\"))","v":""},null,"Student Benefit Level*",null,{"$f":"IF(G3=\"Semester\", Standard_NST!$B$2, IF(G3=\"Quarter\",Standard_NST!$B$6, \"\"))","v":""},null,"Student Benefit Level*",null,{"$f":"IF(K3=\"Semester\", Standard_NST!$B$2, IF(K3=\"Quarter\",Standard_NST!$B$6, \"\"))","v":""}]
[null,"Facility Code",null,{"$f":"IF(C3=\"Semester\",Standard_NST!$B$3, IF(C3=\"Quarter\", Standard_NST!$B$7, \"\"))","v":""},null,"Facility Code",null,{"$f":"IF(G3=\"Semester\",Standard_NST!$B$3, IF(G3=\"Quarter\", Standard_NST!$B$7, \"\"))","v":""},null,"Facility Code",null,{"$f":"IF(K3=\"Semester\",Standard_NST!$B$3, IF(K3=\"Quarter\", Standard_NST!$B$7, \"\"))","v":""}]
[null,"Facility Zip Code",null,null,null,"Facility Zip Code",null,null,null,"Facility Zip Code"]
[null,"DoD/VA rate",null,null,null,"DoD/VA rate",null,null,null,"DoD/VA rate"]
[null,"Associated MHA rate*",null,null,null,"Associated MHA rate*",null,null,null,"Associated MHA rate*"]
[null,"Term Begin Date*",null,null,null,"Term Begin Date*",null,null,null,"Term Begin Date*"]
[null,"Term End Date*",null,null,null,"Term End Date*",null,null,null,"Term End Date*"]
[null,"NST Break Begin",null,"validation error",null,"NST Break Begin",null,"validation error",null,"NST Break Begin",null,"validation error"]
[null,"MHA Rate of Pursuit",null,{"$f":"IF(AND(C9=\"\",C10=\"\",C11=\"\",C12=\"\"),FALSE,OR(OR(C9=\"\",C10=\"\"),OR(C9>C10),OR(AND(NOT(C11=\"\"),C12=\"\"),AND(C11=\"\",NOT(C12=\"\"))),OR(C11>C12),AND(NOT(AND(C11=\"\",C12=\"\")),OR(C11<C9,C12<C9,C11>C10,C12>C10))))","v":false},null,"NST Break End",null,{"$f":"IF(AND(G9=\"\",G10=\"\",G11=\"\",G12=\"\"),FALSE,OR(OR(G9=\"\",G10=\"\"),OR(G9>G10),OR(AND(NOT(G11=\"\"),G12=\"\"),AND(G11=\"\",NOT(G12=\"\"))),OR(G11>G12),AND(NOT(AND(G11=\"\",G12=\"\")),OR(G11<G9,G12<G9,G11>G10,G12>G10))))","v":false},null,"NST Break End",null,{"$f":"IF(AND(K9=\"\",K10=\"\",K11=\"\",K12=\"\"),FALSE,OR(OR(K9=\"\",K10=\"\"),OR(K9>K10),OR(AND(NOT(K11=\"\"),K12=\"\"),AND(K11=\"\",NOT(K12=\"\"))),OR(K11>K12),AND(NOT(AND(K11=\"\",K12=\"\")),OR(K11<K9,K12<K9,K11>K10,K12>K10))))","v":false}]
[null,"Total Hours*",null,null,null,"Total Hours*",null,null,null,"Total Hours*"]
[null,"Full-Time Modifier*",null,"weeks in term",null,"Full-Time Modifier*",null,"weeks in term",null,"Full-Time Modifier*",null,"weeks in term"]
[null,"Weeks in Term",{"$f":"IF(AND(C9=\"\",C10=\"\"), \"\",IF(D12,\"DATE ERROR\",ROUND(D15/1,0)))","v":""},{"$f":"IF(NOT(OR(C11=\"\",C12=\"\")),((C10-C9+1)-(C12-C11+1))/7,IF(NOT(OR(C9=\"\",C10=\"\")),(C10-C9+1)/7,0))","v":0},null,"Weeks in Term",{"$f":"IF(AND(G9=\"\",G10=\"\"), \"\",IF(H12,\"DATE ERROR\",ROUND(H15/1,0)))","v":""},{"$f":"IF(NOT(OR(G11=\"\",G12=\"\")),((G10-G9+1)-(G12-G11+1))/7,IF(NOT(OR(G9=\"\",G10=\"\")),(G10-G9+1)/7,0))","v":0},null,"Weeks in Term",{"$f":"IF(AND(K9=\"\",K10=\"\"), \"\",IF(L12,\"DATE ERROR\",ROUND(L15/1,0)))","v":""},{"$f":"IF(NOT(OR(K11=\"\",K12=\"\")),((K10-K9+1)-(K12-K11+1))/7,IF(NOT(OR(K9=\"\",K10=\"\")),(K10-K9+1)/7,0))","v":0}]
[null,"Standard/NST",{"$f":"D16","v":""},{"$f":"IF(AND(C4=\"\",C5=\"\",C6=\"\",C7=\"\",C8=\"\",C9=\"\",C10=\"\",C11=\"\",C12=\"\",C13=\"\",C14=\"\"),\"\",IF(C3=\"\",\"Select Semester/Quarter\",IF(OR(D12,AND(C9=\"\",C10=\"\")),\"\",IF(D15<(D4),\"Non-Standard\",IF(D15>(D5),\"Non-Standard\",\"Standard\")))))","v":""},null,"Standard/NST",{"$f":"H16","v":""},{"$f":"IF(AND(G4=\"\",G5=\"\",G6=\"\",G7=\"\",G8=\"\",G9=\"\",G10=\"\",G11=\"\",G12=\"\",G13=\"\",G14=\"\"),\"\",IF(G3=\"\",\"Select Semester/Quarter\",IF(OR(H12,AND(G9=\"\",G10=\"\")),\"\",IF(H15<(H4),\"Non-Standard\",IF(H15>(H5),\"Non-Standard\",\"Standard\")))))","v":""},null,"Standard/NST",{"$f":"L16","v":""},{"$f":"IF(AND(K4=\"\",K5=\"\",K6=\"\",K7=\"\",K8=\"\",K9=\"\",K10=\"\",K11=\"\",K12=\"\",K13=\"\",K14=\"\"),\"\",IF(K3=\"\",\"Select Semester/Quarter\",IF(OR(L12,AND(K9=\"\",K10=\"\")),\"\",IF(L15<(L4),\"Non-Standard\",IF(L15>(L5),\"Non-Standard\",\"Standard\")))))","v":""}]
[null,"Credit Hour Eq.",{"$f":"D17","v":""},{"$f":"IF(C13=\"\",\"\",IFERROR(IF(C16=\"Standard\", C13, C13*D3/C15),\"\"))","v":""},null,"Credit Hour Eq.",{"$f":"H17","v":""},{"$f":"IF(G13=\"\",\"\",IFERROR(IF(G16=\"Standard\", G13, G13*H3/G15),\"\"))","v":""},null,"Credit Hour Eq.",{"$f":"L17","v":""},{"$f":"IF(K13=\"\",\"\",IFERROR(IF(K16=\"Standard\", K13, K13*L3/K15),\"\"))","v":""}]
[null,"Rate of Pursuit",{"$f":"D19","v":""},null,null,"Rate of Pursuit",{"$f":"H19","v":""},null,null,"Rate of Pursuit",{"$f":"L19","v":""}]
[null,"MHA Rate of Pursuit",{"$f":"IFERROR(ROUND(D19/10,0)*10/100,\"\")","v":""},{"$f":"IFERROR(MIN(100%,D17/C14)*100,\"\")","v":""},null,"MHA Rate of Pursuit",{"$f":"IFERROR(ROUND(H19/10,0)*10/100,\"\")","v":""},{"$f":"IFERROR(MIN(100%,H17/G14)*100,\"\")","v":""},null,"MHA Rate of Pursuit",{"$f":"IFERROR(ROUND(L19/10,0)*10/100,\"\")","v":""},{"$f":"IFERROR(MIN(100%,L17/K14)*100,\"\")","v":""}]
[null,"Estimated MHA",{"$f":"IF(OR(D20=\"\", C4=\"\"),\"\",IF(C19<0.51,0,D20))","v":""},{"$f":"IFERROR(C8*C4*C19, \"\")","v":""},null,"Estimated MHA",{"$f":"IF(OR(H20=\"\", G4=\"\"),\"\",IF(G19<0.51,0,H20))","v":""},{"$f":"IFERROR(G8*G4*G19, \"\")","v":""},null,"Estimated MHA",{"$f":"IF(OR(L20=\"\", K4=\"\"),\"\",IF(K19<0.51,0,L20))","v":""},{"$f":"IFERROR(K8*K4*K19, \"\")","v":""}]
[null,"*required field"]
[]
[]
[]
[null,"The location where the individual physically participates in the majority of their classes will be highlighted in GREEN. "]

{"sheet":"Instructions","index":2,"n_rows":28,"n_cols":2,"rows_kept":28,"cut":false,"hidden":false,"merged":[[3,1,3,2],[1,0,1,2]]}
[]
["This is NOT a calculator. These are instructions only."]
[]
[null,"Enrollment Term Modifier"]
["Select Semester or Quarter from the drop-down menu(required)","Term Type*"]
["Select the beneficiary benefit level from the drop-down menu (required)","Student Benefit Level*"]
["For accuracy while looking up MHA rates, enter the facility code (optional)","Facility Code"]
["Enter the facility zip code (optional)","Facility Zip Code"]
["Select VA MHA rate or Dod MHA rate (optional)","DoD/VA rate"]
["Enter the full MHA rate (VA or DoD depending on eligibility)(required)","Associated MHA rate*"]
["Enter the term begin date (mm/dd/yyyy)(required)","Term Begin Date*"]
["Enter the term end date (mm/dd/yyyy)(required)","Term End Date*"]
["Enter the non-standard term no pay break begin date (mm/dd/yyyy)(non-standard term ONLY)","NST Break Begin"]
["Enter the non-standard term no pay break end date (mm/dd/yyyy)(non-standard term ONLY)","MHA Rate of Pursuit"]
["Enter the number of credit hours the beneficiary is taking (required)","Total Hours*"]
["Enter the full time modifer (required)","Full-Time Modifier*"]
["Calculated field: displays number of weeks in the term","Weeks in Term"]
["Calculated field: displays whether the term is standard or non-standard","Standard/NST"]
["Calculated field: displays credit hours (standard) or credit hour equivalent (non-standard)","Credit Hour Eq."]
["Calculated field: displays rate of pursuit (used to determine where majority of classes are)","Rate of Pursuit"]
["Calculated field: displays rate of pursuit used to calculate Monthly Housing Allowance","MHA Rate of Pursuit"]
["Calculated field: displays ESTIMATED Monthly Housing Allowance","Estimated MHA"]
[null,"*required field"]
[]
[]
["INFORMATION: When multiple terms are entered the user is able to identify the term with the highest Rate of Pursuit (RoP) as the field for the term with the highest RoP will display a green background. When the RoP is the same, both will display a green backgound.  VA will pay the higher MHA rate only when RoP is the same.  Otherwise, VA will always pay the MHA rate where a student is attending a majority of their training. "]
[]
["DISCLAIMER: This tool will provide an estimate but does not combine the RoPs of multiple terms when displaying an estimated MHA rate for ovelapping terms. Any results should not be guaranteed.  Actual system rules and calcualtions are much more complex. "]

{"sheet":"Standard_NST","index":3,"n_rows":7,"n_cols":2,"rows_kept":7,"cut":false,"hidden":true,"merged":[]}
["Semesters"]
["lower limit",{"$f":"14+4/7","v":14.571428571428571}]
["upper limit",{"$f":"19+3/7","v":19.428571428571427}]
[]
["Quarter"]
["lower limit",{"$f":"10","v":10}]
["upper limit",{"$f":"13","v":13}]

{"sheet":"Error Calculation","index":4,"n_rows":39,"n_cols":8,"rows_kept":39,"cut":false,"hidden":true,"merged":[[0,1,0,4],[1,1,1,2],[21,1,21,2]]}
[null,"Colmery Act Section 107 Job Aid"]
[null,"Semester Modifier"]
[null,"Student Benefit Level",0.9]
[null,"Facility Code",null,null,null,"All blank",{"$f":"AND(C8=\"\",C9=\"\",C10=\"\",C11=\"\")","v":true}]
[null,"Facility Zip Code",null,null,null,null,"Error"]
[null,"DoD/VA rate",null,null,null,"Test 1",{"$f":"OR(C8=\"\", C9=\"\")","v":true},"c8 or c9 blanks"]
[null,"Associated MHA rate",1200,null,null,"Test 2",{"$f":"OR(C8>C9)","v":false},"c8/c9 valid"]
[null,"Term Begin Date",null,null,null,"Test 3",{"$f":"OR(AND(NOT(C10=\"\"), C11=\"\"), AND(C10=\"\", NOT(C11=\"\")))","v":false},"break not filled out completely"]
[null,"No Pay Date"]
[null,"Break Begin",null,"validation error",null,"Test 4",{"$f":"AND(C10=\"\", C11=\"\")","v":true},"break is empty"]
[null,"Break No Pay ",null,{"$f":"OR(OR(C8=\"\", C9=\"\"), OR(C8>C9), OR(AND(NOT(C10=\"\"), C11=\"\"), AND(C10=\"\", NOT(C11=\"\"))),OR(C10>C11), AND(NOT(AND(C10=\"\", C11=\"\")), OR(C10<C8, C11<C8, C10>C9, C11>C9)))","v":true},null,"Test 5",{"$f":"OR(C10<C8, C11<C8, C10>C9, C11>C9)","v":false},"break is outside enrollment period"]
[null,"Weeks in Term",{"$f":"IF(D11,\"DATE ERROR\",ROUND(D13/1,0))","v":"DATE ERROR"},null,null,"Test 6",{"$f":"OR(C10>C11)","v":false},"break not valid"]
[null,"Total Hours ",12,{"$f":"((C9-C8)-(C11-C10))/7","v":0}]
[null,"Hour Type ","Semester",null,null,null,"TEST"]
[null,"Standard/NST",{"$f":"D15","v":"Non-Standard"},{"$f":"IF(D13<(14+4/7), \"Non-Standard\", IF(D13>(19+3/7), \"Non-Standard\", \"Standard\"))","v":"Non-Standard"},null,null,{"$f":"IF(G4,FALSE,OR(G6,G7,G8,G12,AND(NOT(G10),G11)))","v":false},"short version"]
[null,"FT Modifier",12,null,null,null,{"$f":"IF(G4,FALSE,OR(OR(C8=\"\",C9=\"\"),OR(C8>C9),OR(AND(NOT(C10=\"\"),C11=\"\"),AND(C10=\"\",NOT(C11=\"\"))),OR(C10>C11),AND(NOT(AND(C10=\"\",C11=\"\")),OR(C10<C8,C11<C8,C10>C9,C11>C9))))","v":false},"long version"]
[null,"Credit Hour Eq.",{"$f":"IFERROR(IF(C15=\"Standard\", C13, C13*18/C12),\"\")","v":""},{"$f":"IFERROR(IF(C15=\"Standard\", C13, C13*18/C12),\"\")","v":""}]
[null,"Rate of Pursuit",{"$f":"IFERROR(ROUND(D18/10,0)*10/100,\"\")","v":""},{"$f":"MIN(100%,D17/C16)*100","v":{"$error":"#VALUE!"}}]
[null,"Estimated MHA",{"$f":"IF(D19=\"\", \"\", D19)","v":""},{"$f":"IFERROR(C7*C3*C18, \"\")","v":""}]
[null,null,null,null,null,null,"if all blank = false"]
[null,null,null,null,null,null,"if validation error = true"]
[null,"Quarter Modifier "]
[null," Facility Code"]
[null,"Facility Zip Code"]
[null,"Term Begin Date"]
[null,"No Pay Date"]
[null,"Break Begin"]
[null,"Break No Pay"]
[null,"Weeks in Term",{"$f":"IF(OR(C25=\"\",C26=\"\"),\"\",ROUND(D29/1,0))","v":""},{"$f":"IF(AND(C25<>\"\",C26<>\"\",C27<>\"\",C28<>\"\"),((C26-C25)-(C28-C27))/7,0)","v":0}]
[null,"Total Hours "]
[null,"Hour Type ","Quarter"]
[null,"FT Modifier"]
[null,"Credit Hour Eq.",{"$f":"IFERROR(C30*12/C29,\"\")","v":""}]
[null,"Rate of Pursuit",{"$f":"IFERROR(ROUND(D34/10,0)*10/100,\"\")","v":""},{"$f":"MIN(100%,C30*12/C29/C32)*100","v":{"$error":"#VALUE!"}}]
[]
[]
[]
[]
[null,"The location where the individual physically participates in the majority of their classes will be highlighted in GREEN. "]