# Calculating Good Conduct Allowance for inmates

#### zozew

##### New Member
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!

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### zozew

##### New Member
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
.
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
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

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

Will do thanks!

#### Logit

##### Well-known Member
.
Did the formulas work ? Do we need to correct something ?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,827
Messages
5,855,878
Members
431,771
Latest member
CoryMelth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back