Calculating time formula

[jodfie]

Hi all,
I need help. I work for a Police Dept and have to make a spreadsheet. I have an example below of the sheet.
I need to calculate the date of someone's parole ending and possible... a countdown from the current date to that date.
Unknown 64 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​
1​	First Name​	Middle Name​	Last Name​	Conviction Date​	Parole Date​	Time Incarcerated​	Sentence End Date​	Total Sentence​	Charge 1​	Sentence​	Charge 2​	Sentence​	Notes
2​	john​	d​	doe​	11/23/16​	5/4/18​	=IF(DATEDIF(IF(E2<D2,E2,D2),IF(E2>D2,E2,D2),"y")=0,"",DATEDIF(IF(E2<D2,E2,D2),IF(E2>D2,E2,D2),"y")&IF(DATEDIF(IF(E2<D2,E2,D2),IF(E2>D2,E2,D2),"y")=1," year "," years ")&"")&IF(DATEDIF(IF(E2<D2,E2,D2),IF(E2>D2,E2,D2),"ym")=0,"",DATEDIF(IF(E2<D2,E2,D2),IF(E2>D2,E2,D2),"ym")&IF(DATEDIF(IF(E2<D2,E2,D2),IF(E2>D2,E2,D2),"ym")=1," month "," months ")&"")&IF(INT(DATEDIF(IF(E2<D2,E2,D2),IF(E2>D2,E2,D2),"md")/7)=0,"",INT(DATEDIF(IF(E2<D2,E2,D2),IF(E2>D2,E2,D2),"md")/7)&IF(INT(DATEDIF(IF(E2<D2,E2,D2),IF(E2>D2,E2,D2),"md")/7)=1," week "," weeks ")&"")&IF(MOD(DATEDIF(IF(E2<D2,E2,D2),IF(E2>D2,E2,D2),"md"),7)=0,"",MOD(DATEDIF(IF(E2<D2,E2,D2),IF(E2>D2,E2,D2),"md"),7)&IF(MOD(DATEDIF(IF(E2<D2,E2,D2),IF(E2>D2,E2,D2),"md"),7)=1," day "," days ")&"")​	=DATE(YEAR(E2)+F2,MONTH(E2)+F2,DAY(E2)+F2)​	=SUM($J2+$L2) & " Years"​	Attmpt Burglary​	4 Years​	Agg Assault​	4 Years​
3​	Joseph​	a​	doe​	4/9/15​	5/7/28​	=IF(DATEDIF(IF(E3<D3,E3,D3),IF(E3>D3,E3,D3),"y")=0,"",DATEDIF(IF(E3<D3,E3,D3),IF(E3>D3,E3,D3),"y")&IF(DATEDIF(IF(E3<D3,E3,D3),IF(E3>D3,E3,D3),"y")=1," year "," years ")&"")&IF(DATEDIF(IF(E3<D3,E3,D3),IF(E3>D3,E3,D3),"ym")=0,"",DATEDIF(IF(E3<D3,E3,D3),IF(E3>D3,E3,D3),"ym")&IF(DATEDIF(IF(E3<D3,E3,D3),IF(E3>D3,E3,D3),"ym")=1," month "," months ")&"")&IF(INT(DATEDIF(IF(E3<D3,E3,D3),IF(E3>D3,E3,D3),"md")/7)=0,"",INT(DATEDIF(IF(E3<D3,E3,D3),IF(E3>D3,E3,D3),"md")/7)&IF(INT(DATEDIF(IF(E3<D3,E3,D3),IF(E3>D3,E3,D3),"md")/7)=1," week "," weeks ")&"")&IF(MOD(DATEDIF(IF(E3<D3,E3,D3),IF(E3>D3,E3,D3),"md"),7)=0,"",MOD(DATEDIF(IF(E3<D3,E3,D3),IF(E3>D3,E3,D3),"md"),7)&IF(MOD(DATEDIF(IF(E3<D3,E3,D3),IF(E3>D3,E3,D3),"md"),7)=1," day "," days ")&"")​	​	​	​	4 Years​	​	4 Years​
4​	jane​	d​	doe​	1/1/18​	5/19/18​	=IF(DATEDIF(IF(E4<D4,E4,D4),IF(E4>D4,E4,D4),"y")=0,"",DATEDIF(IF(E4<D4,E4,D4),IF(E4>D4,E4,D4),"y")&IF(DATEDIF(IF(E4<D4,E4,D4),IF(E4>D4,E4,D4),"y")=1," year "," years ")&"")&IF(DATEDIF(IF(E4<D4,E4,D4),IF(E4>D4,E4,D4),"ym")=0,"",DATEDIF(IF(E4<D4,E4,D4),IF(E4>D4,E4,D4),"ym")&IF(DATEDIF(IF(E4<D4,E4,D4),IF(E4>D4,E4,D4),"ym")=1," month "," months ")&"")&IF(INT(DATEDIF(IF(E4<D4,E4,D4),IF(E4>D4,E4,D4),"md")/7)=0,"",INT(DATEDIF(IF(E4<D4,E4,D4),IF(E4>D4,E4,D4),"md")/7)&IF(INT(DATEDIF(IF(E4<D4,E4,D4),IF(E4>D4,E4,D4),"md")/7)=1," week "," weeks ")&"")&IF(MOD(DATEDIF(IF(E4<D4,E4,D4),IF(E4>D4,E4,D4),"md"),7)=0,"",MOD(DATEDIF(IF(E4<D4,E4,D4),IF(E4>D4,E4,D4),"md"),7)&IF(MOD(DATEDIF(IF(E4<D4,E4,D4),IF(E4>D4,E4,D4),"md"),7)=1," day "," days ")&"")​	​	=SUM(J4,L4) & " Years"​	​	8 Years​	​	2 Years​

Sheet: Parole List

 

[jodfie]

Re: Need help calculating time formula

also if you know a shorter formula for time incarcerated it would be appreciated! i found it googling how to get the years months weeks days between D and E.

 

[jodfie]

Re: Need help calculating time formula

bump please for help!

 

[Dave Patton]

Re: Need help calculating time formula

Excel 2010
	A	B	C	D	E	F
1	First Name	Middle Name	Last Name	Conviction Date	Parole Date	Time Incarcerated
2	john	d	doe	23-Nov-16	4-May-18	1 Year(s), 5 Month(s), 11 Day(s)
3	Joseph	a	doe	9-Apr-15	7-May-28	13 Year(s), 0 Month(s), 28 Day(s)
4	jane	d	doe	1-Jan-18	19-May-18	0 Year(s), 4 Month(s), 18 Day(s)
5	john	d	doe	23-Nov-16	4-May-18	1.44 Year(s)
3c

Cell Formulas
Range	Formula
F2	=IF(E2="","",DATEDIF(D2,E2,"Y")&" Year(s), "&DATEDIF(D2,E2,"YM")&" Month(s), "&DATEDIF(D2,E2,"MD")&" Day(s)")
F3	=IF(E3="","",DATEDIF(D3,E3,"Y")&" Year(s), "&DATEDIF(D3,E3,"YM")&" Month(s), "&DATEDIF(D3,E3,"MD")&" Day(s)")
F4	=IF(E4="","",DATEDIF(D4,E4,"Y")&" Year(s), "&DATEDIF(D4,E4,"YM")&" Month(s), "&DATEDIF(D4,E4,"MD")&" Day(s)")
F5	=YEARFRAC(D5,E5,3)

What do you need to calculate?
What rules and criteria apply?
Are both the start date and end date counted?
What type of answer is required?

 

[jodfie]

What do you need to calculate?
I need to calculate the difference in time from sentence to parole - the total sentencing time ex. 1 year incarcerated - 8 years sentenced = 7 years on parole
What rules and criteria apply?
I’m not sure what you mean by the question?
Are both the start date and end date counted?
Yes they are.
What type of answer is required?
I need the date of the above equation given of 7 years from date of parole and also if possible a daily updating countdown to that date in days weeks months years.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H
1	First Name	Middle Name	Last Name	Conviction Date	Parole Date	Time Incarcerated	Year
2	john	d	doe	23-Nov-16	4-May-18	1 Year(s), 5 Month(s), 11 Day(s)	7	4-May-25
3c

Cell Formulas
Range	Formula
H2	=DATE(YEAR(E2)+G2,MONTH(E2),DAY(E2))

A clear description of the challenge and a few examples with results is often required.

 

[jodfie]

I’m sorry what you mean by the last statement can you elaborate?

 

[jodfie]

I think I figured out part of what you’re asking. The years number in G1 is going to be 2 or more cells that need to be summed and may have years or months in them. Then it needs to be subtracted.

 

[jodfie]

here's an example of what I'm going for.

https://prntscr.com/jkwhbz

 

[jodfie]

Bump for more help? please!