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> [TABLE="width: 574"]
<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>[TR]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl69, width: 132"]A[/TD]
[TD="class: xl69, width: 54"]B[/TD]
[TD="class: xl69, width: 88"] [/TD]
[TD="class: xl69, width: 49"]C[/TD]
[TD="class: xl69, width: 59"]D[/TD]
[TD="class: xl69, width: 74"]E[/TD]
[TD="class: xl69, width: 64"] [/TD]
[TD="class: xl69, width: 180"]
[/TD]
[/TR]
[TR]
[TD="class: xl69"]2[/TD]
[TD="class: xl67"]Today's date>>>>[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]2017[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67, align: right"]25[/TD]
[TD="class: xl68, colspan: 3"]< USING THE NOW FORMULA FOR EACH CELL
[/TD]
[/TR]
[TR]
[TD="class: xl69"]3[/TD]
[TD="class: xl67"]Birth Day[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]1986[/TD]
[TD="class: xl67, align: right"]11[/TD]
[TD="class: xl67, align: right"]29
[/TD]
[TD="class: xl67"] MANUAL
[/TD]
[TD="class: xl67"]
[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl69"]4[/TD]
[TD="class: xl67"]Hired date[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]2012[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67, align: right"]12[/TD]
[TD="class: xl67"] MANUAL
[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl69"]5[/TD]
[TD="class: xl67"]Last Word day[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]2017[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67, align: right"]11[/TD]
[TD="class: xl67"] MANUAL
[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl69"]6[/TD]
[TD="class: xl67"]Years Employed[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]5[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl67"] MANUAL
[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl69"]7[/TD]
[TD="class: xl67"]IRR[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] MANUAL
[/TD]
[TD="class: xl67"]
[/TD]
[TD="class: xl67"] [/TD]
[/TR]
</tbody>[/TABLE]

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> [TABLE="width: 296"]
<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>[TR]
[TD="class: xl74, width: 180"] [/TD]
[TD="class: xl75, width: 51"][/TD]
[TD="class: xl69, width: 54"]I[/TD]
[TD="class: xl69, width: 54"]J[/TD]
[TD="class: xl69, width: 54"]K
[/TD]
[/TR]
[TR]
[TD="class: xl74"]TODAY'S DATE[/TD]
[TD="class: xl76"]17[/TD]
[TD="class: xl67, align: right"]2017[/TD]
[TD="class: xl67, align: right"]17[/TD]
[TD="class: xl67, align: right"]55[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 180"]BIRTHDAY
[/TD]
[TD="class: xl77, width: 51"]18[/TD]
[TD="class: xl67, align: right"]1986[/TD]
[TD="class: xl67, align: right"]11[/TD]
[TD="class: xl67, align: right"]29
[/TD]
[/TR]
[TR]
[TD="class: xl72, width: 180"]MINUSED[/TD]
[TD="class: xl77, width: 51"]19[/TD]
[TD="class: xl67, align: right"]31[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67, align: right"]27
[/TD]
[/TR]
[TR]
[TD="class: xl72, width: 180"]PLUS 1 FOR THE DAY[/TD]
[TD="class: xl77, width: 51"]20[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]27
[/TD]
[/TR]
[TR]
[TD="class: xl72, width: 180"]SUM TOTAL[/TD]
[TD="class: xl77, width: 51"]21[/TD]
[TD="class: xl67, align: right"]30[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67, align: right"]27
[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 180"]MINUS YEARS EMPLOYED
[/TD]
[TD="class: xl78, width: 51"]22[/TD]
[TD="class: xl67, align: right"]5
[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl67, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 180"][/TD]
[TD="class: xl78, width: 51"][/TD]
[TD="class: xl68, align: right"]25
[/TD]
[TD="class: xl68, align: right"]6
[/TD]
[TD="class: xl68, align: right"]27
[/TD]
[/TR]
</tbody>[/TABLE]
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> [TABLE="width: 511"]
<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>[TR]
[TD="class: xl70, width: 102"][/TD]
[TD="class: xl69, width: 150"]I[/TD]
[TD="class: xl69, width: 248"]J[/TD]
[TD="class: xl69, width: 180"]K
[/TD]
[/TR]
[TR]
[TD="class: xl71"]17[/TD]
[TD="class: xl67"]=IF(L17<L18,D2-1,D2)[/TD]
[TD="class: xl67"]=IF(M17>M18,E2-1,E2)+IF(E3>E2,12,E2)[/TD]
[TD="class: xl67"]=IF(F2<M18,F2+30,F2)[/TD]
[/TR]
[TR]
[TD="class: xl72, width: 102"]18[/TD]
[TD="class: xl67"]=D3[/TD]
[TD="class: xl67"]=E3[/TD]
[TD="class: xl67"]=F3[/TD]
[/TR]
[TR]
[TD="class: xl72, width: 102"]19[/TD]
[TD="class: xl67"]=K17-D3[/TD]
[TD="class: xl67"]=+L17-E3[/TD]
[TD="class: xl67"]=M17-F3[/TD]
[/TR]
[TR]
[TD="class: xl72, width: 102"]20[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]=M19+1[/TD]
[/TR]
[TR]
[TD="class: xl72, width: 102"]21[/TD]
[TD="class: xl67"]=IF(L21>L22,K19-1,K19)[/TD]
[TD="class: xl67"]=IF(M21<M22,L19-1,L19)[/TD]
[TD="class: xl67"]=IF(M22>M20,M20+30,M20)[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 102"]22[/TD]
[TD="class: xl67"]=D6[/TD]
[TD="class: xl67"]=E6[/TD]
[TD="class: xl67"]=F6[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 102"][/TD]
[TD="class: xl68"]=K21-D6[/TD]
[TD="class: xl68"]=L21-L22[/TD]
[TD="class: xl68"]=M21-M22[/TD]
[/TR]
</tbody>[/TABLE]





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> [TABLE="width: 287"]
<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>[TR]
[TD="class: xl66, width: 132"][/TD]
[TD="class: xl66, width: 54"][/TD]
[TD="class: xl69, width: 88"]B[/TD]
[TD="class: xl69, width: 49"]C[/TD]
[TD="class: xl69, width: 59"]D
[/TD]
[/TR]
[TR]
[TD="class: xl66"]Date of Tenure[/TD]
[TD="class: xl69"]17[/TD]
[TD="class: xl67, align: right"]2017[/TD]
[TD="class: xl67, align: right"]5[/TD]
[TD="class: xl67, align: right"]25
[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]18[/TD]
[TD="class: xl67, align: right"]2012[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]19[/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="class: xl68, align: right"]-1[/TD]
[TD="class: xl68, align: right"]13[/TD]
[/TR]
</tbody>[/TABLE]

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> [TABLE="width: 434"]
<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>[TR]
[TD="class: xl66, width: 108"][/TD]
[TD="class: xl69, width: 176"]B[/TD]
[TD="class: xl69, width: 136"]C[/TD]
[TD="class: xl69, width: 158"]D[/TD]
[/TR]
[TR]
[TD="class: xl69"]17[/TD]
[TD="class: xl67"]=D2[/TD]
[TD="class: xl67"]=IF(F17>F18,E2-1,E2)[/TD]
[TD="class: xl67"]=IF(F2<F18,F2+30,F2)[/TD]
[/TR]
[TR]
[TD="class: xl69"]18[/TD]
[TD="class: xl67"]=D4[/TD]
[TD="class: xl67"]=E4[/TD]
[TD="class: xl67"]=F4[/TD]
[/TR]
[TR]
[TD="class: xl69"]19[/TD]
[TD="class: xl68"]=D17-D18[/TD]
[TD="class: xl68"]=+E17-E18[/TD]
[TD="class: xl68"]=+F17-F18+COUNTA(D7)[/TD]
[/TR]
</tbody>[/TABLE]


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> [TABLE="width: 421"]
<tbody>[TR]
[TD="class: xl66, width: 113"][/TD]
[TD="class: xl64, width: 132"]A[/TD]
[TD="class: xl64, width: 108"]B[/TD]
[TD="class: xl64, width: 100"]C[/TD]
[TD="class: xl64, width: 108"]D
[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl64"]Today's date>>>>[/TD]
[TD="class: xl65"]2017/11/06[/TD]
[TD="class: xl65"]2017/11/06[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl64"]Birth Day[/TD]
[TD="class: xl65"]1990/10/25[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl64"]Hired date[/TD]
[TD="class: xl65"]2012/06/12[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl64"]Total time employed[/TD]
[TD="class: xl64"]Years[/TD]
[TD="class: xl64"]Months[/TD]
[TD="class: xl64"]Days[/TD]
[/TR]
[TR]
[TD="class: xl66"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]2[/TD]
[/TR]
</tbody>[/TABLE]

=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.