MrExcel Publishing
Your One Stop for Excel Tips & Solutions

I know i`m starting to bore you, but....


Posted by Francisco J. Estaba S. on August 27, 2000 2:33 PM

Dear People,

I have been busy working on the Date calculation data that CELIA has been so nice to provide me, but as i´m a NIL in Mathematics, the task have proved much more arduous than i expected.

Therefore, here i`m asking for help Again. Currently i`m stuck here: I need to substract to the time someone has to spend on Jail the actual time he has already spent. Say, if Felon has been convicted to 8y 9m and 29d, and if he has already spent 3m and 2d in prison, i have to substract those two to get the actual Imprinsoment time. The i need to add that much time to the day of today to know when he is going to get out of Jail.

I also need to know a 1/4 from the conviction time counted after the release date. Ex: The felon got out by 12-5-2004, and was convicted for 8 years. I need to extract 1/4 from that 8years and add it to the 12-5-2004.

And CELIA, if you are reading this, i would like your complete name, as i`m going to quote You when i deliver these work to all my colleagues here in venezuela. It might not be much, as i`m (In fact YOU) doing this for free, but i want at least demonstrate a little respect for your help.


Posted by Celia on August 29, 0100 12:02 AM

Re: Please summarise requirements

: : The Here i go....


Francisco
A.Here is a repeat of my post dated 22 August :-

1.Format cell A1 as "m/d/yy h:mm"
2.Input your start date in cell A1 (either the today's date or the original detention date - whichever you want to use). The date should appear as "8/21/00 0:00".
3.Format cells A2:A6 as General.
4.Input the period of sentence(15 yrs, 2 mths, 3 days, 11 hours, 0 mins) as follows :-
A2 15
A3 2
A4 3
A5 11
A6 0
5.Format cells A7:A10 as "m/d/yy h:mm".
6.Enter the following formula in cell A7 :-
=DATE(YEAR(A1)+A2,MONTH(A1)+A3,DAY(A1)+A4)+TIME(A5,A6,0)
This will produce the end date. It should appear as "10/24/15 11:00".
7.To produce the dates after completion of 1/4, 1/2 and 2/3 of the conviction, enter the following in cells A8:A10 :-
=A1+(A7-A1)/4
=A1+(A7-A1)/2
=A1+(A7-A1)*2/3

To get Release Date 1, enter "Today’s" date in cell A1 per step 2.
To get Release Date 2, enter "Original Conviction Date" in cell A1.

B.To calculate the difference between two dates in Years, Months, and Days, use the formula supplied in my previous posts :-

=DATEDIF(A1,B1,"y")&"y "&DATEDIF(A1,B1,"ym")&"m "&DATEDIF(A1,B1,"md")&"d"

This formula is based upon the start date being in cell A1 and the end date in cell B1.

3.From your point 3, I don’t know whether you want to know how to calculate :-
-1/4 of the Sentence in Years Months and Days, or
-the date equal to the release date plus 1/4 of the Sentence, or
-both.
The formulas to do this are already included in previous posts, but here they are again:-

To get 1/4 of the Sentence, use this :-

=DATEDIF(A1,(A1+(B1-A1)/4),"y")&"y "&DATEDIF(A1,(A1+(B1-A1)/4),"ym")&"m "&DATEDIF(A1,(A1+(B1-A1)/4),"md")&"d "_

This formula is based on cell A1 containing the Sentence start date and cell B1 containing the Sentence end date.

To get the date which equals the release date plus 1/4 of the sentence :-

=A3+(B2-A2)/4

For this formula, the release date would be in A3, the Sentence end date in B2, and the Sentence start date in A2.

By way of seeking a conclusion to our correspondence and in order to avoid the need for all these formulas and calculations, I would like to suggest your giving consideration to the automatic acquittal of all future defendants.

Celia

Posted by Celia on August 28, 0100 3:40 AM

Please summarise requirements


THE FORMULA IS EXACTLY THE SAME. JUST CHANGE THE DATE.
THE FORMULAE ARE EXACTLY THE SAME. JUST CHANGE THE DATE. Another Falied attempt to bring you to Justice... I´ll have to try other means.......


Francisco
See comments above in capitals.
I'm getting a bit confused as to what you still need.

I understand that the starting data for each case is :-
Original Detention Date
Conviction Date
Sentence (Years/Months/Days/Hours/Minutes)

Can you supply a list of ALL the information you need, so we can see what is missing?

We'll get there eventually (maybe!).

Celia


The

Posted by Celia on August 27, 0100 5:27 PM

Francisco
From my previous reply you already have the formulas to produce what you need. My understanding of what you want is :-

Release Date 1 = Today + Sentence
Release Date 2 = Original Detention Date + Sentence
Early Release Dates = Release Date (either 1 or 2) less 3/4,1/2 and 2/3

The only other piece of information you appear to need is the time already detained (I.E. the time from Original Detention to Today). This formula was provided in my reply to your original question posted on 9 Aug :-

The difference between the dates in years/months/days (assuming the Original Detention Date is in cell A1 and Today's Date is in cell B1) :-
=DATEDIF(A1,B1,"y")&"y "&DATEDIF(A1,B1,"ym")&"m "&DATEDIF(A1,B1,"md")&"d"

If you are still missing something, please post again. Perhaps I'm not fully grasping what you are looking for and someone else will be able to help out.

Thanks for your kind offer to bring my name to the attention of the Venezuelan judiciary, but I prefer to retain anonymity and maintain a clean record so to speak.

Celia


Posted by Francisco J. Estaba S. on August 30, 0100 6:28 PM

Re: Please summarise requirements

I can see many things clearly now, and only got one final question.

I need to get How much time does the felon need to spend on Jail counting from the last day he has been on prison (Ex: Today) to the date of his final Release. I know, i know, the formulae is the same and all that.... BUT, I know how to get the Final Release Date, but it is a formulae itself!, not a Date.
See, i got final release date as:
=FECHA(AÑO(G6)+(C31-D46),MES(G6)+(D31-C46),DIA(G6)+(E31-B46))

And when i try to apply the result of that formulae to =DATEDIF(A1,(A1+(B1-A1)/4),"y")&"y "&DATEDIF(A1,(A1+(B1-A1)/4),"ym")&"m "&DATEDIF(A1,(A1+(B1-A1)/4),"md")&"d "

I get that the difference between the two dates is over a 100 Years!. Obviously, Excel doesn not understand what im feeding it.
I would be simpler if i only got two already set dates. But as the last one is the result of a formulae, i have failed to get it right!

Last Question, i Swear!

Posted by Francisco J. Estaba S. on August 27, 0100 6:38 PM

From my previous reply you already have the formulas to produce what you need. My understanding of what you want is :- Release Date 1 = Today + Sentence Release Date 2 = Original Detention Date + Sentence Early Release Dates = Release Date (either 1 or 2) less 3/4,1/2 and 2/3 The only other piece of information you appear to need is the time already detained (I.E. the time from Original Detention to Today). This formula was provided in my reply to your original question posted on 9 Aug :- The difference between the dates in years/months/days (assuming the Original Detention Date is in cell A1 and Today's Date is in cell B1) :- =DATEDIF(A1,B1,"y")&"y "&DATEDIF(A1,B1,"ym")&"m "&DATEDIF(A1,B1,"md")&"d" If you are still missing something, please post again. Perhaps I'm not fully grasping what you are looking for and someone else will be able to help out. Thanks for your kind offer to bring my name to the attention of the Venezuelan judiciary, but I prefer to retain anonymity and maintain a clean record so to speak. Celia

Well, i appreciate your effort CELIA, so i´ll try and write my best English!.

In your Post of aug 9, you provide how to calculate Sentence Time counting from Release date 1, no Release Date 2. I tried to calculate it myself, but have failed until now.

The 1/4, the 1/2 and the 2/3 have to be calculated from release date 2, too.

Also, when someone gets out of jail, we need to put him under the supervision of the state for 1/4 of the time he was sentenced to.

Darn!
Another Falied attempt to bring you to Justice...
I´ll have to try other means.......

Posted by Francisco J. Estaba S. on August 28, 0100 6:57 PM

Re: Please summarise requirements

In your Post of aug 9, you provide how to calculate Sentence Time counting from Release date 1, no Release Date 2. I tried to calculate it myself, but have failed until now. THE FORMULA IS EXACTLY THE SAME. JUST CHANGE THE DATE. The 1/4, the 1/2 and the 2/3 have to be calculated from release date 2, too. THE FORMULAE ARE EXACTLY THE SAME. JUST CHANGE THE DATE. Also, when someone gets out of jail, we need to put him under the supervision of the state for 1/4 of the time he was sentenced to. Darn! : Another Falied attempt to bring you to Justice... : I´ll have to try other means....... Francisco See comments above in capitals. I'm getting a bit confused as to what you still need. I understand that the starting data for each case is :- Original Detention Date Conviction Date Sentence (Years/Months/Days/Hours/Minutes) Can you supply a list of ALL the information you need, so we can see what is missing? We'll get there eventually (maybe!). Celia

The
Here i go....

Until now, i have not managed to get the formulae to obtain Release date 2. In your post of AUg 9 you indicated how to get release date 1, and in your previous post you indicated how to obtain the difference in conviction Time. I tried to substract actual "conviction time" from "Real sentence time" and add it to current day to get Release date 2, without success. So i don`t have release date 2.

2.- I need to know, counting from today, how much time a person need rto spend on prison until his final Release, in Years, months and Days.

3.- Also, when someone gets out of jail, we need to put him under the supervision of the state for 1/4 of the time he was sentenced to.

I know we will get There!!!! Im sure of it (HOPE!)