zakasnak
Active Member
- Joined
- Sep 21, 2005
- Messages
- 307
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.
<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!!!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<tbody>
</tbody>
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>