Calculating Good Conduct Allowance for inmates

[zozew]

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!

 

[zozew]

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]

.
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]

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]

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]

.
Ok ... thank you for the clarification.

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

=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

 

[zozew]

Will do thanks!

 

[Logit]

.
Did the formulas work ? Do we need to correct something ?