Projected Release Date Calculation Based on Input

Question

Level 1

4 points

Projected Release Date Calculation Based on Input

Using Numbers, I am trying to determine what coding is required to automatically calculate a prisoners projected release date based on several inputs, Date of Commitment (Sentence Start Date) and Prior Time Credit (Prior Days). The screen shot shows the two inputs. The column headers show the years and the dataset shows the number of days served from Date of Commitment PLUS Prior Time. This sheet is hard set for a 5 year sentence. I am looking for suggestions to automatically add the Year Columns to expand or shrink the years based on the length of sentence. Example, a 2 Year Sentence with a Commitment Date of 2/5/2023 should automatically create a 2023, 2024 and 2025 Column with the EOMonth showing the number of days served. Any thoughts would be greatly appreciated.

Posted on Sep 29, 2023 9:56 AM

Reply

Answer 1

SGIII

Level 8

37,664 points

Oct 1, 2023 5:57 AM in response to TexasCarey

The details are helpful! Here's one way you might calculate the release date based on inputs.

Note the use of Duration values. Numbers has those; Excel doesn't.

The key is to use EDATE to calculate the future date. It takes care of leap years and such.

Then just subtract one date-time from another to get the number of days (Duration) between them.

When entering formulas you of course type * for multiplication, which will display as × .

In A1 enter the formula =TODAY(). I put a dummy today in there for purposes of the example.

I'm sure why you would want all those columns by year.

More on EDATE here:

EDATE - Apple Support (HK)

SG

Reply

Answer 2

SGIII

Level 8

37,664 points

Sep 30, 2023 2:34 AM in response to TexasCarey

I find it hard to follow what is meant by each of the items listed on the left and how those numbers are derived.

I can see 'Total Days' seems to be the difference between the End Date and Date Committed with Prior Time Credit added to the results. But how are the other numbers derived?

Is a five-year sentence a 60-month sentence? Or is it a specific number of days?

Why do you need the year columns? You should be able to derive a future date without those.

I recommend having a look at the built-in templates to see examples effective document design in Numbers, where you can easily have multiple tables on one sheet. Your current version looks very "Excelsy", where you have everything dumped into one table with lots of blank rows, etc.

SG

Reply

Answer 3

TexasCarey Author

Level 1

4 points

Sep 30, 2023 10:50 AM in response to TexasCarey

SGIII, thank you for the reply.

Commit Date is the Date that the person starts their sentence. The length of sentence is a count of the total length of sentence, in DAYS (this is the way that the Federal Bureau of Prisons calculates the entire time). GCT is Good Conduct Time which is the federally mandated time off a sentence that a person earns). Prior Time is the number of days a person received for pre-sentence incarceration, ie, Day of Arrest plus additional time prior to sentencing. On this spread sheet, I am simply trying to see if there is a way to automatically calculate the total time to serve and break it up in to monthly allotments with a running total of Time Served based on End Of Month.

As an example:

Inmate #1 was arrested on 6/6/2010 and was held for 6 days and was released on 6/12/2010 on Bond. Inmate #1 receives a total of 7 Days Prior Time (the 6th, 7th, 8th, 9th, 10, 11th and 12th). Inmate #1 was sentenced on December 10, 2010 to 4 Years in Federal Prison and taken into custody immediately (Commitment Date). The inmate sentence is a total of 4 Years minus credit for Prior Time. The Total Time is 2010-2011 = 365, 2011-2012 = 366 Days (Leap Year), 2012-2013 = 365 and 2013-2014=365 Days. The Total Sentence Time to Serve is 1,461 Days. That’s The Full Term Sentence! Now we have to credit in the Prior Time of 7 Days and the Total FULL Sentence to serve is 1,454 Days. I’m not trying to calculate the GCT Credit into this spreadsheet just yet as that is a simple calc of deducting the GCT from the Full Term Sentence, (54x4=216 Days).

On the Spread Sheet I am trying to show the running total of “Days Served” at the end of the month for each month, stopping on the last day of the last month. What I am seeking is information of whether or not Numbers can automatically calculate the Running End Of Month Calculation, adding the End Of Month field automatically including the number of days served. The last month of a sentence should only include to remaining number of days for that sentence.

I am trying to prevent having to create a spreadsheet that will have a table for years, ie, one tab for 2 years, one tab for 3 years, one tab for 4 years, etc.

Hope I explained it better. Again, Thanks for the help.

Reply

Answer 4

TexasCarey Author

Level 1

4 points

Oct 3, 2023 5:46 AM in response to SGIII

I did indeed. But the formulas don’t solve the problem that I am experiencing. I am looking for a solution to auto placement of the results into the spreadsheet based on the three input items discussed, Date of Committment, Sentence Length and Prior Time.

Reply

Answer 5

SGIII

Level 8

37,664 points

Oct 3, 2023 7:27 AM in response to TexasCarey

Ok, but I think if have actually tried the formulas you would find they do place the projected release date into the table based on the inputs. It's all clearly labeled. The result (projected release date) is on row 12.

Good luck with your project.

SG

Reply