government sick leave calc/formula

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.

2. Add unused sick leave to total service.
YearsMonthsDays
Creditable Service2052
Unused Sick Leave13420722
Total Creditable Service211224

<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!!! :eek:


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Table of Converting Unused Sick Leave Into Additional Service
Months
Days01234567891011
00174348522696870104412171391156517391913
16180354528702875104912231397157117451919
212186360533707881105512291403157717511925
317191365539713887106112351409158317571931
423197371545719893106712411415158917621936
529203377551725899107312461420159417681942
635209383557731904107812521426160017741948
741215388562736910108412581432160617801954
846220394568742916109012641438161217861960
952226400574748922109612701444161817911965
1058232406580754928110212751449162317971971
1164238412586760933110712811455162918031977
1270244417591765939111312871461163518091983
1375249423597771945111912931467164118151989
1481255429603777951112512991473164618201994
1587261435609783957113113041478165218262000
1693267441615789962113613101484165818322006
1799273446620794968114213161490166418382012
18104278452626800974114813221496167018442018
19110284458632806980115413281502167518492023
20116290464638812986116013331507168118552029
21122296470644817991116513391513168718612035
22128302475649823997117113451519169318672041
231333074816558291003117713511525169918732047
241393134876618351009118313571531170418782052
251463194936678411015118913621536171018842058
261513254996738461020119413681542171618902064
271573315046788521026120013741548172218962070
281623365106848581032120613801554172819022075
291683425166908641038121213861560173319072081

<tbody>
</tbody>
 

SLARRA

Board Regular
Joined
Sep 22, 2007
Messages
91
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top