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.

2. Add unused sick leave to total service.
	Years	Months	Days
Creditable Service	20	5	2
Unused Sick Leave	1342	0	7	22
Total Creditable Service	21	12	24

<tbody>
</tbody>

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

<tbody>
</tbody>

 

[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