Calculation for Job Employement

[Newbienew]

I am trying to do something that seems simple in theory but rather hard to accomplish in excel. I am trying to calculated the time of age based on current date and birthday minus the time a person was employed with the organization. I know this is simple math on paper but challenging for me in excel
The first portion is the manual entry cells.

<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl67 { border: 0.5pt solid windowtext; }.xl68 { text-align: center; border: 0.5pt solid windowtext; }.xl69 { font-weight: 700; text-align: center; border: 0.5pt solid windowtext; }</style>

	A	B		C	D	E
2	Today's date>>>>		2017	6	25	< USING THE NOW FORMULA FOR EACH CELL
3	Birth Day		1986	11	29	MANUAL
4	Hired date		2012	6	12	MANUAL
5	Last Word day		2017	6	11	MANUAL
6	Years Employed		5	0	0	MANUAL
7	IRR					MANUAL

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4827;width:99pt" width="132"> <col style="mso-width-source:userset;mso-width-alt:1974;width:41pt" width="54"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:1792;width:37pt" width="49"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:6582;width:135pt" width="180"> </colgroup><tbody>
</tbody>

In the second/third part. Over all its simple subtraction. With any calculating dates, if the date on top is less than the one on the bottom than time has to be added. Seeing how I am taking 30 days from "J17" to make of that time. If not less than it will be just the number. Cell "J17" is suppose to say 6, one year is to be taken off "I17" making it 17 because of the 1 = 30 days moved over. I just want this to work as simple as it does on paper.
<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl67 { border: 0.5pt solid windowtext; }.xl68 { border: 0.5pt solid windowtext; background: rgb(146, 208, 80) none repeat scroll 0% 0%; }.xl69 { font-size: 14pt; font-weight: 700; text-align: center; }.xl70 { font-size: 8pt; border-width: medium 0.5pt medium medium; border-style: none solid none none; border-color: currentcolor windowtext currentcolor currentcolor; white-space: normal; }.xl71 { white-space: normal; }.xl72 { font-size: 9pt; border-width: medium 0.5pt medium medium; border-style: none solid none none; border-color: currentcolor windowtext currentcolor currentcolor; white-space: normal; }.xl73 { font-size: 9pt; white-space: normal; }.xl74 { font-size: 8pt; border-width: medium 0.5pt medium medium; border-style: none solid none none; border-color: currentcolor windowtext currentcolor currentcolor; white-space: nowrap; }.xl75 { font-size: 8pt; white-space: nowrap; }.xl76 { font-size: 14pt; font-weight: 700; text-align: center; border-width: medium 0.5pt medium medium; border-style: none solid none none; border-color: currentcolor windowtext currentcolor currentcolor; white-space: nowrap; }.xl77 { font-size: 14pt; font-weight: 700; text-align: center; border-width: medium 0.5pt medium medium; border-style: none solid none none; border-color: currentcolor windowtext currentcolor currentcolor; white-space: normal; }.xl78 { font-size: 14pt; font-weight: 700; text-align: center; white-space: normal; }</style>

		I	J	K
TODAY'S DATE	17	2017	17	55
BIRTHDAY	18	1986	11	29
MINUSED	19	31	6	27
PLUS 1 FOR THE DAY	20			27
SUM TOTAL	21	30	6	27
MINUS YEARS EMPLOYED	22	5	0	0
		25	6	27

<colgroup><col style="mso-width-source:userset;mso-width-alt:6582;width:135pt" width="180"> <col style="mso-width-source:userset;mso-width-alt:1865;width:38pt" width="51"> <col style="mso-width-source:userset;mso-width-alt:1974; width:41pt" width="54" span="3"> </colgroup><tbody>
</tbody>

Formula breakdown.
<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl67 { border: 0.5pt solid windowtext; }.xl68 { border: 0.5pt solid windowtext; background: rgb(146, 208, 80) none repeat scroll 0% 0%; }.xl69 { font-size: 14pt; font-weight: 700; text-align: center; }.xl70 { font-size: 8pt; white-space: nowrap; }.xl71 { font-size: 14pt; font-weight: 700; text-align: center; border-width: medium 0.5pt medium medium; border-style: none solid none none; border-color: currentcolor windowtext currentcolor currentcolor; white-space: nowrap; }.xl72 { font-size: 14pt; font-weight: 700; text-align: center; border-width: medium 0.5pt medium medium; border-style: none solid none none; border-color: currentcolor windowtext currentcolor currentcolor; white-space: normal; }.xl73 { font-size: 14pt; font-weight: 700; text-align: center; white-space: normal; }</style>

	I	J	K
17	=IF(L17<L18,D2-1,D2)	=IF(M17>M18,E2-1,E2)+IF(E3>E2,12,E2)	=IF(F2<M18,F2+30,F2)
18	=D3	=E3	=F3
19	=K17-D3	=+L17-E3	=M17-F3
20			=M19+1
21	=IF(L21>L22,K19-1,K19)	=IF(M21<M22,L19-1,L19)	=IF(M22>M20,M20+30,M20)
22	=D6	=E6	=F6
	=K21-D6	=L21-L22	=M21-M22

<colgroup><col style="mso-width-source:userset;mso-width-alt:1865;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:2742;width:113pt" width="150"> <col style="mso-width-source:userset;mso-width-alt:4534;width:186pt" width="248"> <col style="mso-width-source:userset;mso-width-alt:3291;width:135pt" width="180"> </colgroup><tbody>
</tbody>

Part Three, this is going to be similar to what is above.
<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl67 { border: 0.5pt solid windowtext; }.xl68 { border: 0.5pt solid windowtext; background: rgb(146, 208, 80) none repeat scroll 0% 0%; }.xl69 { font-size: 14pt; font-weight: 700; text-align: center; }</style>

		B	C	D
Date of Tenure	17	2017	5	25
	18	2012	6	12
	19	5	-1	13

<colgroup><col style="mso-width-source:userset;mso-width-alt:4827;width:99pt" width="132"> <col style="mso-width-source:userset;mso-width-alt:1974;width:41pt" width="54"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:1792;width:37pt" width="49"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> </colgroup><tbody>
</tbody>

Formula Breakdown
<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl67 { border: 0.5pt solid windowtext; }.xl68 { border: 0.5pt solid windowtext; background: rgb(146, 208, 80) none repeat scroll 0% 0%; }.xl69 { font-size: 14pt; font-weight: 700; text-align: center; }</style>

	B	C	D
17	=D2	=IF(F17>F18,E2-1,E2)	=IF(F2<F18,F2+30,F2)
18	=D4	=E4	=F4
19	=D17-D18	=+E17-E18	=+F17-F18+COUNTA(D7)

<colgroup><col style="mso-width-source:userset;mso-width-alt:1974;width:81pt" width="108"> <col style="mso-width-source:userset;mso-width-alt:3218;width:132pt" width="176"> <col style="mso-width-source:userset;mso-width-alt:2486;width:102pt" width="136"> <col style="mso-width-source:userset;mso-width-alt:2889;width:119pt" width="158"> </colgroup><tbody>
</tbody>

I am sorry for the detailed break down or trying to explain it all. Don't meany any disrespect by my explanation.

 

[Newbienew]

Looking for some help

 

[Newbienew]

I have been trying to work on this for sometime and this is what i have so far. It works but to an extent. With the current dates I have put in I get a negative 2. If you could paste what i have and the code you might be able to see what i am talking about to provide some help.


<style>table { }tr { }col { }br { }td { padding: 0px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl64 { font-weight: 700; text-align: center; border: 0.5pt solid windowtext; }.xl65 { font-weight: 700; text-align: center; border: 0.5pt solid windowtext; }.xl66 { font-weight: 700; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; }.xl67 { font-weight: 700; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; }</style>

	A	B	C	D
1	Today's date>>>>	2017/11/06	2017/11/06
2	Birth Day	1990/10/25
3	Hired date	2012/06/12
4	Total time employed	Years	Months	Days
5		4	2	2

<tbody>
</tbody>

=DATEDIF(B2,IF(COUNTA(B1),B1,C1),"Y")-B5&" Years, "&DATEDIF(B2,IF(COUNTA(B1),B1,C1),"YM")-C5&" Months, "&DATEDIF(B2,IF(COUNTA(B1),B1,C1),"MD")-D5+1&" Days +1 added"

 

[philaugust2004]

I think you would be better of starting a new thread but linking back to this one for background information.