government sick leave calc/formula

[zakasnak]

Hello! It's been awhile.....

I am looking for formulas to figure out months & days from sick leave accumulated to count toward retirement.

At retirement age, providing I'm not ever sick between now & then, I will have 1342 hours of accumulated sick leave. Anything over a year (2087 hours) is subtracted.

I have a chart provided to me, but would like to use a formula if possible. According to the chart below, I would fall between 7 months, 21 days and 7 months, 22 days. Total odd days are discarded in the final retirement calculation, so the actual days would only count if my creditable service days + sick days was greater than 30.

[TABLE="width: 457"]
<tbody>[TR]
[TD="colspan: 5"]2. Add unused sick leave to total service.[/TD]
[/TR]
[TR]
[TD="colspan: 2"][/TD]
[TD]Years[/TD]
[TD]Months[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Creditable Service[/TD]
[TD]20[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Unused Sick Leave[/TD]
[TD]1342[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Total Creditable Service[/TD]
[TD]21[/TD]
[TD]12[/TD]
[TD]24[/TD]
[/TR]
</tbody>[/TABLE]

QUESTION 1: is there a formula that would find the 7 months & 22 days in the chart below (B4:N34) using the value 1342 (R25)?
QUESTION 2: can someone help me with the formulas for the total creditable service months (>12) & days (>29)?

Current years formula: =IF(SUM(T24:T25)=12,SUM(S24:S25)+1,SUM(S24:S25))

Current months formula: =IF(SUM(T24:T25)>11,SUM(T24:T25)-12,IF(SUM(U24:U25)>28,SUM(T24:T25)+1,SUM(T24:T25))) .... this needs to check if days is >28 & if so, then +1, but if the total is >12, then the cell needs to show 0.... unless adding the one makes it 13 or 14, then it needs to show that number minus 12 (i.e. 1 or 2).

Current days formula (this one works): =IF(SUM(U24:U25)>28,0,SUM(U24:U25))

THANK YOU FOR ANY LIGHT YOU CAN SHED TO HELP ME SEE!!!


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[TABLE="width: 832"]
<tbody>[TR]
[TD="class: xl64, width: 832, colspan: 13"]Table of Converting Unused Sick Leave Into Additional Service[/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl69, width: 768, colspan: 12"]Months[/TD]
[/TR]
[TR]
[TD="class: xl71"]Days[/TD]
[TD="class: xl72, width: 64"]0[/TD]
[TD="class: xl72, width: 64"]1[/TD]
[TD="class: xl72, width: 64"]2[/TD]
[TD="class: xl72, width: 64"]3[/TD]
[TD="class: xl72, width: 64"]4[/TD]
[TD="class: xl72, width: 64"]5[/TD]
[TD="class: xl72, width: 64"]6[/TD]
[TD="class: xl72, width: 64"]7[/TD]
[TD="class: xl72, width: 64"]8[/TD]
[TD="class: xl72, width: 64"]9[/TD]
[TD="class: xl72, width: 64"]10[/TD]
[TD="class: xl73, width: 64"]11[/TD]
[/TR]
[TR]
[TD="class: xl74"]0[/TD]
[TD="class: xl75"]0[/TD]
[TD="class: xl75"]174[/TD]
[TD="class: xl75"]348[/TD]
[TD="class: xl75"]522[/TD]
[TD="class: xl75"]696[/TD]
[TD="class: xl75"]870[/TD]
[TD="class: xl75"]1044[/TD]
[TD="class: xl75"]1217[/TD]
[TD="class: xl75"]1391[/TD]
[TD="class: xl75"]1565[/TD]
[TD="class: xl75"]1739[/TD]
[TD="class: xl76"]1913[/TD]
[/TR]
[TR]
[TD="class: xl74"]1[/TD]
[TD="class: xl75"]6[/TD]
[TD="class: xl75"]180[/TD]
[TD="class: xl75"]354[/TD]
[TD="class: xl75"]528[/TD]
[TD="class: xl75"]702[/TD]
[TD="class: xl75"]875[/TD]
[TD="class: xl75"]1049[/TD]
[TD="class: xl75"]1223[/TD]
[TD="class: xl75"]1397[/TD]
[TD="class: xl75"]1571[/TD]
[TD="class: xl75"]1745[/TD]
[TD="class: xl76"]1919[/TD]
[/TR]
[TR]
[TD="class: xl74"]2[/TD]
[TD="class: xl75"]12[/TD]
[TD="class: xl75"]186[/TD]
[TD="class: xl75"]360[/TD]
[TD="class: xl75"]533[/TD]
[TD="class: xl75"]707[/TD]
[TD="class: xl75"]881[/TD]
[TD="class: xl75"]1055[/TD]
[TD="class: xl75"]1229[/TD]
[TD="class: xl75"]1403[/TD]
[TD="class: xl75"]1577[/TD]
[TD="class: xl75"]1751[/TD]
[TD="class: xl76"]1925[/TD]
[/TR]
[TR]
[TD="class: xl74"]3[/TD]
[TD="class: xl75"]17[/TD]
[TD="class: xl75"]191[/TD]
[TD="class: xl75"]365[/TD]
[TD="class: xl75"]539[/TD]
[TD="class: xl75"]713[/TD]
[TD="class: xl75"]887[/TD]
[TD="class: xl75"]1061[/TD]
[TD="class: xl75"]1235[/TD]
[TD="class: xl75"]1409[/TD]
[TD="class: xl75"]1583[/TD]
[TD="class: xl75"]1757[/TD]
[TD="class: xl76"]1931[/TD]
[/TR]
[TR]
[TD="class: xl74"]4[/TD]
[TD="class: xl75"]23[/TD]
[TD="class: xl75"]197[/TD]
[TD="class: xl75"]371[/TD]
[TD="class: xl75"]545[/TD]
[TD="class: xl75"]719[/TD]
[TD="class: xl75"]893[/TD]
[TD="class: xl75"]1067[/TD]
[TD="class: xl75"]1241[/TD]
[TD="class: xl75"]1415[/TD]
[TD="class: xl75"]1589[/TD]
[TD="class: xl75"]1762[/TD]
[TD="class: xl76"]1936[/TD]
[/TR]
[TR]
[TD="class: xl74"]5[/TD]
[TD="class: xl75"]29[/TD]
[TD="class: xl75"]203[/TD]
[TD="class: xl75"]377[/TD]
[TD="class: xl75"]551[/TD]
[TD="class: xl75"]725[/TD]
[TD="class: xl75"]899[/TD]
[TD="class: xl75"]1073[/TD]
[TD="class: xl75"]1246[/TD]
[TD="class: xl75"]1420[/TD]
[TD="class: xl75"]1594[/TD]
[TD="class: xl75"]1768[/TD]
[TD="class: xl76"]1942[/TD]
[/TR]
[TR]
[TD="class: xl74"]6[/TD]
[TD="class: xl75"]35[/TD]
[TD="class: xl75"]209[/TD]
[TD="class: xl75"]383[/TD]
[TD="class: xl75"]557[/TD]
[TD="class: xl75"]731[/TD]
[TD="class: xl75"]904[/TD]
[TD="class: xl75"]1078[/TD]
[TD="class: xl75"]1252[/TD]
[TD="class: xl75"]1426[/TD]
[TD="class: xl75"]1600[/TD]
[TD="class: xl75"]1774[/TD]
[TD="class: xl76"]1948[/TD]
[/TR]
[TR]
[TD="class: xl74"]7[/TD]
[TD="class: xl75"]41[/TD]
[TD="class: xl75"]215[/TD]
[TD="class: xl75"]388[/TD]
[TD="class: xl75"]562[/TD]
[TD="class: xl75"]736[/TD]
[TD="class: xl75"]910[/TD]
[TD="class: xl75"]1084[/TD]
[TD="class: xl75"]1258[/TD]
[TD="class: xl75"]1432[/TD]
[TD="class: xl75"]1606[/TD]
[TD="class: xl75"]1780[/TD]
[TD="class: xl76"]1954[/TD]
[/TR]
[TR]
[TD="class: xl74"]8[/TD]
[TD="class: xl75"]46[/TD]
[TD="class: xl75"]220[/TD]
[TD="class: xl75"]394[/TD]
[TD="class: xl75"]568[/TD]
[TD="class: xl75"]742[/TD]
[TD="class: xl75"]916[/TD]
[TD="class: xl75"]1090[/TD]
[TD="class: xl75"]1264[/TD]
[TD="class: xl75"]1438[/TD]
[TD="class: xl75"]1612[/TD]
[TD="class: xl75"]1786[/TD]
[TD="class: xl76"]1960[/TD]
[/TR]
[TR]
[TD="class: xl74"]9[/TD]
[TD="class: xl75"]52[/TD]
[TD="class: xl75"]226[/TD]
[TD="class: xl75"]400[/TD]
[TD="class: xl75"]574[/TD]
[TD="class: xl75"]748[/TD]
[TD="class: xl75"]922[/TD]
[TD="class: xl75"]1096[/TD]
[TD="class: xl75"]1270[/TD]
[TD="class: xl75"]1444[/TD]
[TD="class: xl75"]1618[/TD]
[TD="class: xl75"]1791[/TD]
[TD="class: xl76"]1965[/TD]
[/TR]
[TR]
[TD="class: xl74"]10[/TD]
[TD="class: xl75"]58[/TD]
[TD="class: xl75"]232[/TD]
[TD="class: xl75"]406[/TD]
[TD="class: xl75"]580[/TD]
[TD="class: xl75"]754[/TD]
[TD="class: xl75"]928[/TD]
[TD="class: xl75"]1102[/TD]
[TD="class: xl75"]1275[/TD]
[TD="class: xl75"]1449[/TD]
[TD="class: xl75"]1623[/TD]
[TD="class: xl75"]1797[/TD]
[TD="class: xl76"]1971[/TD]
[/TR]
[TR]
[TD="class: xl74"]11[/TD]
[TD="class: xl75"]64[/TD]
[TD="class: xl75"]238[/TD]
[TD="class: xl75"]412[/TD]
[TD="class: xl75"]586[/TD]
[TD="class: xl75"]760[/TD]
[TD="class: xl75"]933[/TD]
[TD="class: xl75"]1107[/TD]
[TD="class: xl75"]1281[/TD]
[TD="class: xl75"]1455[/TD]
[TD="class: xl75"]1629[/TD]
[TD="class: xl75"]1803[/TD]
[TD="class: xl76"]1977[/TD]
[/TR]
[TR]
[TD="class: xl74"]12[/TD]
[TD="class: xl75"]70[/TD]
[TD="class: xl75"]244[/TD]
[TD="class: xl75"]417[/TD]
[TD="class: xl75"]591[/TD]
[TD="class: xl75"]765[/TD]
[TD="class: xl75"]939[/TD]
[TD="class: xl75"]1113[/TD]
[TD="class: xl75"]1287[/TD]
[TD="class: xl75"]1461[/TD]
[TD="class: xl75"]1635[/TD]
[TD="class: xl75"]1809[/TD]
[TD="class: xl76"]1983[/TD]
[/TR]
[TR]
[TD="class: xl74"]13[/TD]
[TD="class: xl75"]75[/TD]
[TD="class: xl75"]249[/TD]
[TD="class: xl75"]423[/TD]
[TD="class: xl75"]597[/TD]
[TD="class: xl75"]771[/TD]
[TD="class: xl75"]945[/TD]
[TD="class: xl75"]1119[/TD]
[TD="class: xl75"]1293[/TD]
[TD="class: xl75"]1467[/TD]
[TD="class: xl75"]1641[/TD]
[TD="class: xl75"]1815[/TD]
[TD="class: xl76"]1989[/TD]
[/TR]
[TR]
[TD="class: xl74"]14[/TD]
[TD="class: xl75"]81[/TD]
[TD="class: xl75"]255[/TD]
[TD="class: xl75"]429[/TD]
[TD="class: xl75"]603[/TD]
[TD="class: xl75"]777[/TD]
[TD="class: xl75"]951[/TD]
[TD="class: xl75"]1125[/TD]
[TD="class: xl75"]1299[/TD]
[TD="class: xl75"]1473[/TD]
[TD="class: xl75"]1646[/TD]
[TD="class: xl75"]1820[/TD]
[TD="class: xl76"]1994[/TD]
[/TR]
[TR]
[TD="class: xl74"]15[/TD]
[TD="class: xl75"]87[/TD]
[TD="class: xl75"]261[/TD]
[TD="class: xl75"]435[/TD]
[TD="class: xl75"]609[/TD]
[TD="class: xl75"]783[/TD]
[TD="class: xl75"]957[/TD]
[TD="class: xl75"]1131[/TD]
[TD="class: xl75"]1304[/TD]
[TD="class: xl75"]1478[/TD]
[TD="class: xl75"]1652[/TD]
[TD="class: xl75"]1826[/TD]
[TD="class: xl76"]2000[/TD]
[/TR]
[TR]
[TD="class: xl74"]16[/TD]
[TD="class: xl75"]93[/TD]
[TD="class: xl75"]267[/TD]
[TD="class: xl75"]441[/TD]
[TD="class: xl75"]615[/TD]
[TD="class: xl75"]789[/TD]
[TD="class: xl75"]962[/TD]
[TD="class: xl75"]1136[/TD]
[TD="class: xl75"]1310[/TD]
[TD="class: xl75"]1484[/TD]
[TD="class: xl75"]1658[/TD]
[TD="class: xl75"]1832[/TD]
[TD="class: xl76"]2006[/TD]
[/TR]
[TR]
[TD="class: xl74"]17[/TD]
[TD="class: xl75"]99[/TD]
[TD="class: xl75"]273[/TD]
[TD="class: xl75"]446[/TD]
[TD="class: xl75"]620[/TD]
[TD="class: xl75"]794[/TD]
[TD="class: xl75"]968[/TD]
[TD="class: xl75"]1142[/TD]
[TD="class: xl75"]1316[/TD]
[TD="class: xl75"]1490[/TD]
[TD="class: xl75"]1664[/TD]
[TD="class: xl75"]1838[/TD]
[TD="class: xl76"]2012[/TD]
[/TR]
[TR]
[TD="class: xl74"]18[/TD]
[TD="class: xl75"]104[/TD]
[TD="class: xl75"]278[/TD]
[TD="class: xl75"]452[/TD]
[TD="class: xl75"]626[/TD]
[TD="class: xl75"]800[/TD]
[TD="class: xl75"]974[/TD]
[TD="class: xl75"]1148[/TD]
[TD="class: xl75"]1322[/TD]
[TD="class: xl75"]1496[/TD]
[TD="class: xl75"]1670[/TD]
[TD="class: xl75"]1844[/TD]
[TD="class: xl76"]2018[/TD]
[/TR]
[TR]
[TD="class: xl74"]19[/TD]
[TD="class: xl75"]110[/TD]
[TD="class: xl75"]284[/TD]
[TD="class: xl75"]458[/TD]
[TD="class: xl75"]632[/TD]
[TD="class: xl75"]806[/TD]
[TD="class: xl75"]980[/TD]
[TD="class: xl75"]1154[/TD]
[TD="class: xl75"]1328[/TD]
[TD="class: xl75"]1502[/TD]
[TD="class: xl75"]1675[/TD]
[TD="class: xl75"]1849[/TD]
[TD="class: xl76"]2023[/TD]
[/TR]
[TR]
[TD="class: xl74"]20[/TD]
[TD="class: xl75"]116[/TD]
[TD="class: xl75"]290[/TD]
[TD="class: xl75"]464[/TD]
[TD="class: xl75"]638[/TD]
[TD="class: xl75"]812[/TD]
[TD="class: xl75"]986[/TD]
[TD="class: xl75"]1160[/TD]
[TD="class: xl75"]1333[/TD]
[TD="class: xl75"]1507[/TD]
[TD="class: xl75"]1681[/TD]
[TD="class: xl75"]1855[/TD]
[TD="class: xl76"]2029[/TD]
[/TR]
[TR]
[TD="class: xl74"]21[/TD]
[TD="class: xl75"]122[/TD]
[TD="class: xl75"]296[/TD]
[TD="class: xl75"]470[/TD]
[TD="class: xl75"]644[/TD]
[TD="class: xl75"]817[/TD]
[TD="class: xl75"]991[/TD]
[TD="class: xl75"]1165[/TD]
[TD="class: xl80"]1339[/TD]
[TD="class: xl75"]1513[/TD]
[TD="class: xl75"]1687[/TD]
[TD="class: xl75"]1861[/TD]
[TD="class: xl76"]2035[/TD]
[/TR]
[TR]
[TD="class: xl74"]22[/TD]
[TD="class: xl75"]128[/TD]
[TD="class: xl75"]302[/TD]
[TD="class: xl75"]475[/TD]
[TD="class: xl75"]649[/TD]
[TD="class: xl75"]823[/TD]
[TD="class: xl75"]997[/TD]
[TD="class: xl75"]1171[/TD]
[TD="class: xl80"]1345[/TD]
[TD="class: xl75"]1519[/TD]
[TD="class: xl75"]1693[/TD]
[TD="class: xl75"]1867[/TD]
[TD="class: xl76"]2041[/TD]
[/TR]
[TR]
[TD="class: xl74"]23[/TD]
[TD="class: xl75"]133[/TD]
[TD="class: xl75"]307[/TD]
[TD="class: xl75"]481[/TD]
[TD="class: xl75"]655[/TD]
[TD="class: xl75"]829[/TD]
[TD="class: xl75"]1003[/TD]
[TD="class: xl75"]1177[/TD]
[TD="class: xl75"]1351[/TD]
[TD="class: xl75"]1525[/TD]
[TD="class: xl75"]1699[/TD]
[TD="class: xl75"]1873[/TD]
[TD="class: xl76"]2047[/TD]
[/TR]
[TR]
[TD="class: xl74"]24[/TD]
[TD="class: xl75"]139[/TD]
[TD="class: xl75"]313[/TD]
[TD="class: xl75"]487[/TD]
[TD="class: xl75"]661[/TD]
[TD="class: xl75"]835[/TD]
[TD="class: xl75"]1009[/TD]
[TD="class: xl75"]1183[/TD]
[TD="class: xl75"]1357[/TD]
[TD="class: xl75"]1531[/TD]
[TD="class: xl75"]1704[/TD]
[TD="class: xl75"]1878[/TD]
[TD="class: xl76"]2052[/TD]
[/TR]
[TR]
[TD="class: xl74"]25[/TD]
[TD="class: xl75"]146[/TD]
[TD="class: xl75"]319[/TD]
[TD="class: xl75"]493[/TD]
[TD="class: xl75"]667[/TD]
[TD="class: xl75"]841[/TD]
[TD="class: xl75"]1015[/TD]
[TD="class: xl75"]1189[/TD]
[TD="class: xl75"]1362[/TD]
[TD="class: xl75"]1536[/TD]
[TD="class: xl75"]1710[/TD]
[TD="class: xl75"]1884[/TD]
[TD="class: xl76"]2058[/TD]
[/TR]
[TR]
[TD="class: xl74"]26[/TD]
[TD="class: xl75"]151[/TD]
[TD="class: xl75"]325[/TD]
[TD="class: xl75"]499[/TD]
[TD="class: xl75"]673[/TD]
[TD="class: xl75"]846[/TD]
[TD="class: xl75"]1020[/TD]
[TD="class: xl75"]1194[/TD]
[TD="class: xl75"]1368[/TD]
[TD="class: xl75"]1542[/TD]
[TD="class: xl75"]1716[/TD]
[TD="class: xl75"]1890[/TD]
[TD="class: xl76"]2064[/TD]
[/TR]
[TR]
[TD="class: xl74"]27[/TD]
[TD="class: xl75"]157[/TD]
[TD="class: xl75"]331[/TD]
[TD="class: xl75"]504[/TD]
[TD="class: xl75"]678[/TD]
[TD="class: xl75"]852[/TD]
[TD="class: xl75"]1026[/TD]
[TD="class: xl75"]1200[/TD]
[TD="class: xl75"]1374[/TD]
[TD="class: xl75"]1548[/TD]
[TD="class: xl75"]1722[/TD]
[TD="class: xl75"]1896[/TD]
[TD="class: xl76"]2070[/TD]
[/TR]
[TR]
[TD="class: xl74"]28[/TD]
[TD="class: xl75"]162[/TD]
[TD="class: xl75"]336[/TD]
[TD="class: xl75"]510[/TD]
[TD="class: xl75"]684[/TD]
[TD="class: xl75"]858[/TD]
[TD="class: xl75"]1032[/TD]
[TD="class: xl75"]1206[/TD]
[TD="class: xl75"]1380[/TD]
[TD="class: xl75"]1554[/TD]
[TD="class: xl75"]1728[/TD]
[TD="class: xl75"]1902[/TD]
[TD="class: xl76"]2075[/TD]
[/TR]
[TR]
[TD="class: xl77"]29[/TD]
[TD="class: xl78"]168[/TD]
[TD="class: xl78"]342[/TD]
[TD="class: xl78"]516[/TD]
[TD="class: xl78"]690[/TD]
[TD="class: xl78"]864[/TD]
[TD="class: xl78"]1038[/TD]
[TD="class: xl78"]1212[/TD]
[TD="class: xl78"]1386[/TD]
[TD="class: xl78"]1560[/TD]
[TD="class: xl78"]1733[/TD]
[TD="class: xl78"]1907[/TD]
[TD="class: xl79"]2081[/TD]
[/TR]
</tbody>[/TABLE]

 

[SLARRA]

Hi Carole...

Tho perhaps a bit clunky, this may work for you:

Excel Workbook
	C	D	E	F	G
4			Years	Months	Days
5		Creditable Service:	20	5	2
6	Unused Sick Leave (hours):	1342	0	7	22
7		Total Creditable Service:	21	0	24
Sheet3

Excel 2010

Cell Formulas
Range	Formula
E6	=QUOTIENT(D6,2087)
E7	=SUM(E5:E6)+QUOTIENT((SUM(F5:F6)+QUOTIENT(SUM(G5:G6),30)),12)
F6	=IF(ROUND((MOD(MOD(D6,2087),(2087/12))/(2087/12))*(365/12),0)=30,1+QUOTIENT(MOD(D6,2087),(2087/12)),QUOTIENT(MOD(D6,2087),(2087/12)))
F7	=MOD(SUM(F5:F6)+QUOTIENT(SUM(G5:G6),30),12)
G6	=IF(ROUND((MOD(D6,(2087/12))/(2087/12))*(365/12),0)=30,0,ROUND((MOD(D6,(2087/12))/(2087/12))*(365/12),0))
G7	=MOD(SUM(G5:G6),30)

User enters her data - creditable service in years, months, days, and unused sick leave in accrued hours - in highlighted cells. The unused sick leave is converted to years, months, and days, based on a formula that replicates the outputs of that table you referenced.

The math seems a little wonky to me, probably because the government has determined that more than 29 days equals (at least) a full month. Moreover, the accrued sick leave hours are (roughly) converted to calendar days, not work days. Regardless, the outputs seem to align with your expectations (I hope!).

SDL