# government sick leave calc/formula

#### zakasnak

##### Active Member
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

##### Board Regular
Hi Carole...

Tho perhaps a bit clunky, this may work for you:
Excel Workbook
CDEFG
4YearsMonthsDays
5Creditable Service:2052
6Unused Sick Leave (hours):13420722
7Total Creditable Service:21024
 Sheet3
Excel 2010
Cell Formulas
CellFormula
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

1,082,099
Messages
5,363,129
Members
400,720
Latest member
Pettel

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...