Calculating Good Conduct Allowance for inmates

zozew

New Member
Joined
Mar 15, 2019
Messages
5
Hi,

I have a slight problem, I have an excel doc where I need to calculate an inmates Good Conduct Time Allowance between some dates (GCTA)

If that was all I already managed to do it but... There are some caveats in Oct 2013 the rules changes and the GCTA amount changed. I did all of this already in a vba scrip and it all worked fine but then I found out there was another parameter that messed up my whole calculation again...


So if anyone has the time to give me a push in the right direction here comes a short explanation of the calculation.

General variables
TodayDate = Jan 2019
NewLawDate = Oct 2013

Inmate properties
DateDetained = Jan 2000
DateFinalSentence = Jan 2004


Now...below you see the difference between the old law and the new law for days given to an inmate per month after he has been detained for a number of years.


Old law until October 10th 2013
5 days for each month for first two years
8 days for each month 3rd to 5th year
10 days for each month 6th to 10th year
15 days for each month 11th year and on

New law after Oct 10th 2013
20 days for each month for first two years
23 days for each month 3rd to 5th year
25 days for each month 6th to 10th year
30 days for each month 11th year and on

Untill now it was all good, I check what bracket the inmate falls into and do some adding.

But then I found out that the GCTA doesn't go into affect until the inmate has a finalized date for the sentence meaning GCTA starts being added from the day of the dateFinalSentence but the GCTA "bracket" is calculated from the dateDetained

So for our inmate above he falls into the 4year bracket of the old law to start calculating the GCTA so he skips the 5 days a month and starts at 8days a month directly as he has already been detained 4 years

Then from Jan 2004 until Oct 10 2013 he gets the old law GCTA and then after that he gets the new law GCTA.

It all went south for me when I tried offsetting the calculation with the dateFinalSentence...

Any idea on how to approach this little gem I'm all ears

Cheers!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

zozew

New Member
Joined
Mar 15, 2019
Messages
5
Forgot to say, only input data is dateDetained and dateFinalizedSentence and the output is just a number in days representing the GCTA that has been accumulated...
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,576
.
If the inmate is incarcerated for 4 yrs ...

Does he accumulate GCTA for the first two years at 5 days per month plus two years at 8 days per month ? (OLD LAW)

Or does he accumulate GCTA for all 4 years at 8 days per month ?


Which of the above is used for the NEW LAW ?
 

zozew

New Member
Joined
Mar 15, 2019
Messages
5
Hi logit,

Well it depends under what period he was detained but for inmate in prison now then you would use the new law which starts at 20days per month.

If the innate was detained and sentences before 10/10/2013 then he would get the old law days (5,8,10,15) until he passed the 10/10/2013 and then he would continue in his current bracket but with the higher days /month of the new law

Cheers
 

zozew

New Member
Joined
Mar 15, 2019
Messages
5

ADVERTISEMENT

Sorry logit I misread your reply you are correct on your first assumption. 5 days / month for years 1 and 2 and 8 days for years 3 to 6
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,576
.
Ok ... thank you for the clarification.

This is one of many formulas used in the download workbook :

Code:
=IF(I4<25,5*I4,IF(I4>24,5*24,""))

I am certain someone else can create a single formula that will do everything for you in a single cell. My mind doesn't work that way and formulas are not my strong suite.
Sooooo .... download the workbook and see if my formulas match with your manual calculations.


Download workbook : https://www.amazon.com/clouddrive/share/lBnkukw9WRLMRiP2IE1aTR0nZ1nKmXbfh62aZzR29l
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,576
.
Did the formulas work ? Do we need to correct something ?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,994
Messages
5,526,125
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top