MrExcel Publishing
Your One Stop for Excel Tips & Solutions

apologize for steppin over other question and still need help with dates formulae!


Posted by Francisco J. Estaba S. on August 21, 2000 5:41 PM

Hi, as i stated before, i´m a Judge in Venezuela who is confronting problems creating a Worksheet to calculate times of conviction.
Celia has helped Before, but as i was creating the Worksheet i discovered that then i had to introduce the time of the imprinsonment (?) as follows:
Years Months Days Hours Minutes
1 5 22 2 30

Then i have to add that much time to a "given" date as 22/8/00
Also, i have to take 2/3, 1/3, 1/2, 1/4 to the initial amount of years, months days, hours and minutes and add it too to the second date, that is the day the convict initiates his conviction.
Problems is that this is not all. I also have to calculate the amount of time elapsed since the initial detention of the convict to the day of today, and that date becomes the "given" date i mentioned before...
ARGH!



Posted by Celia on August 22, 0100 4:40 PM


Francisco
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

Celia

Posted by Celia on August 21, 0100 6:08 PM

Francisco
Sorry, but I can’t follow what you need to do.
What data do you have before doing any calculations? Is it as follows?
A.Date of detention
B.Today’s date
C.Start date of sentence
D.Period of sentence

Please confirm the data and try to set out step by step what you need to do with it.

Celia

Posted by Francisco J. Estaba S. on August 21, 0100 7:57 PM

Ok, here it is

A Court said that citizen "A" has been found guilty of homicide, and therefore is sent to Jail for 15 Years, 2 months, 3 Days and 11 hours. i have to add that much time to the day of today, so i can say that this citizen will leave Jail by October 24 of the year 2015, at 11:00 o´clock. Also, if this same citizen has been arrested prior to the trial i have to take into account the time he has already spent on jail and discount it from its punishment. So, if he has been in jail, prior to the trial, for two months, and is convicted for 15 Years, 2 months, 3 Days and 11 hours, i have to take those two months and start counting the time to his final release, that would be on August 24 of the year 2015, at 11:00 o´clock.

Also, i need to say this citizen, when he will accomplish the 1/4, 1/2 and 2/3 of his conviction, counted from this day.

Please, i know that my eenglisssh is not the best, but i have to manage with what i got, don´t i?. My government is not very helpful providing computer solutions to our troubles, and as a matter of fact, i have to provide the computers to my court and also i pay for my connection to the internet!

Don´t you need an honest Judge over there??????

TIA