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.

 

[philaugust2004]

I think we are going to need an example of the spreadsheet

 

[AlanY]

your required time of age is in B7 and converted in yr, month and day in Row 8

Excel 2012
	A	B	C	D
1	Today	2017	6	26
2	Birthday	1986	11	29
3	Hired date	2012	6	12
4	Last Work day	2017	6	11
5	Years Employed	5.0
6
7	Time of age	25.6
8		25	6	28
Sheet3

Cell Formulas
Range	Formula
B1	=YEAR(TODAY())
B5	=(DATE(B4,C4,D4)-DATE(B3,C3,D3))/365.25
B7	=(DATE(B1,C1,D1)-DATE(B2,C2,D2))/365.25-B5
B8	=INT((DATE(B1,C1,D1)-DATE(B2,C2,D2))/365.25-B5)
C1	=MONTH(TODAY())
C8	=INT(((DATE(B1,C1,D1)-DATE(B2,C2,D2))/365.25-B5-B8)*12)
D1	=DAY(TODAY())
D8	=((DATE(B1,C1,D1)-DATE(B2,C2,D2))/365.25-B5)*365.25-(B8+C8/12)*365.25

 

[Newbienew]

@AlanY First thank you much for your help. I did the calculation by hand I got 26 years 6 months 28 days. I believe I messed up on my post entry. I put 31 and didn't move it down. Making it 30 instead. lol Sorry about that. If I plug what you posted in row 23 "which i didn't label" this will work the same way or will i have to change everything to match those columns and rows. I want to be sure as that is how the sheet is built. And how can i do this for the 3 part. Where its todays date minus dated hired. Respectfully and thank you again

 

[AlanY]

you mean like this?

Excel 2012
	A	B	C	D
1
2	Today	2017	6	27
3	Birthday	1986	11	29
4	Hired date	2012	6	12
5	Last Work day	2017	6	11
6	Years Employed	5.0
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23	Time of age	25	6	29
Sheet1

Cell Formulas
Range	Formula
B6	=(DATE(B5,C5,D5)-DATE(B4,C4,D4))/365.25
B23	=INT((DATE(B2,C2,D2)-DATE(B3,C3,D3))/365.25-B6)
C23	=INT(((DATE(B2,C2,D2)-DATE(B3,C3,D3))/365.25-B6-B23)*12)
D23	=((DATE(B2,C2,D2)-DATE(B3,C3,D3))/365.25-B6)*365.25-(B23+C23/12)*365.25

 

[Newbienew]

The first section is separate from the other parts. I am trying to put the formula in the sections according to how the sheet is built already. The first part is the manual entry portion. They just chance the Years employed to Total Time Employed.
<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 { font-weight: 700; text-align: center; border: 0.5pt solid windowtext; }.xl69 { text-align: center; border-width: 0.5pt medium 0.5pt 0.5pt; border-style: solid none solid solid; border-color: windowtext currentcolor windowtext windowtext; }.xl70 { text-align: center; border-width: 0.5pt medium; border-style: solid none; border-color: windowtext currentcolor; }.xl71 { text-align: center; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext currentcolor; }.xl72 { border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl73 { border: 0.5pt solid windowtext; background: rgb(112, 48, 160) none repeat scroll 0% 0%; }</style> [TABLE="width: 571"]
<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:5266;width:108pt" width="144"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <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: xl68, width: 64"] A1[/TD]
[TD="class: xl68, width: 144"]B[/TD]
[TD="class: xl68, width: 88"]C[/TD]
[TD="class: xl68, width: 68"]D[/TD]
[TD="class: xl68, width: 79"]E[/TD]
[TD="class: xl68, width: 74"]F[/TD]
[TD="class: xl68, width: 64"] [/TD]
[TD="class: xl68, width: 180"] [/TD]
[/TR]
[TR]
[TD="class: xl68"]2[/TD]
[TD="class: xl67"]Today's date>>>>[/TD]
[TD="class: xl67, align: right"]2017[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67, align: right"]28[/TD]
[TD="class: xl69, colspan: 3"][/TD]
[/TR]
[TR]
[TD="class: xl68"]3[/TD]
[TD="class: xl67"]Birth Day[/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"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl68"]4[/TD]
[TD="class: xl67"]Hired date[/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"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl68"]5[/TD]
[TD="class: xl67"]Last Word day[/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"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl68"]6[/TD]
[TD="class: xl67"]Total Time Employed[/TD]
[TD="class: xl72, align: right"]5[/TD]
[TD="class: xl72, align: right"]3[/TD]
[TD="class: xl72, align: right"]3[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl68"]7[/TD]
[TD="class: xl67"]IRR[/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
</tbody>[/TABLE]


Below is a section to itself and the red part is where I am trying to place your formula to work in.

<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 { font-size: 14pt; font-weight: 700; text-align: center; }.xl69 { 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; }.xl70 { 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; }.xl71 { font-size: 9pt; white-space: normal; }.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: nowrap; }.xl73 { 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; }.xl74 { font-size: 14pt; font-weight: 700; text-align: center; white-space: normal; }.xl75 { border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl76 { border: 0.5pt solid windowtext; background: red none repeat scroll 0% 0%; }.xl77 { font-size: 14pt; font-weight: 700; text-align: center; white-space: nowrap; }.xl78 { white-space: normal; }.xl79 { 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; }</style> [TABLE="width: 390"]
<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:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:4534;width:93pt" width="124"> <col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> </colgroup><tbody>[TR]
[TD="class: xl79, width: 180"] [/TD]
[TD="class: xl77, width: 51"]I16[/TD]
[TD="class: xl68, width: 75"]J[/TD]
[TD="class: xl68, width: 124"]K[/TD]
[TD="class: xl68, width: 90"]L[/TD]
[/TR]
[TR]
[TD="class: xl79"]TODAY'S DATE[/TD]
[TD="class: xl72"]17[/TD]
[TD="class: xl67, align: right"]2017[/TD]
[TD="class: xl67, align: right"]17[/TD]
[TD="class: xl67, align: right"]58[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 180"]BIRTHDAY[/TD]
[TD="class: xl73, 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: xl70, width: 180"]MINUSED[/TD]
[TD="class: xl73, width: 51"]19[/TD]
[TD="class: xl67, align: right"]31[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67, align: right"]29[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 180"]PLUS 1 FOR THE DAY[/TD]
[TD="class: xl73, width: 51"]20[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 180"]SUM TOTAL[/TD]
[TD="class: xl73, width: 51"]21[/TD]
[TD="class: xl67, align: right"]31[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67, align: right"]30
[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 180"]MINUS YEARS EMPLOYED[/TD]
[TD="class: xl74, width: 51"]22[/TD]
[TD="class: xl75, align: right"]5[/TD]
[TD="class: xl75, align: right"]3[/TD]
[TD="class: xl75, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl78, width: 180"]Total[/TD]
[TD="class: xl74, width: 51"]23[/TD]
[TD="class: xl76, align: right"]26[/TD]
[TD="class: xl76, align: right"]3[/TD]
[TD="class: xl76, align: right"]27[/TD]
[/TR]
</tbody>[/TABLE]



This last section I am also trying to do the same thing but only with Todays date and Date Hired. If there is any IRR time its Plus one for the day, but I believe that could be a CountA or countblank for C7 formula added to the day cell. <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-width: medium medium medium 0.5pt; border-style: none none none solid; border-color: currentcolor currentcolor currentcolor windowtext; }.xl69 { border: 0.5pt solid windowtext; background: red none repeat scroll 0% 0%; }</style> [TABLE="width: 388"]
<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:5266;width:108pt" width="144"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 144"]Todays date[/TD]
[TD="class: xl67, width: 88, align: right"]2017[/TD]
[TD="class: xl67, width: 68, align: right"]5[/TD]
[TD="class: xl67, width: 79, align: right"]28[/TD]
[TD="class: xl66, width: 74"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"]Date Hired[/TD]
[TD="class: xl67, align: right"]2012[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67, align: right"]12[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"]total[/TD]
[TD="class: xl69, align: right"]5[/TD]
[TD="class: xl69, align: right"]-1[/TD]
[TD="class: xl69, align: right"]16[/TD]
[TD="class: xl68"]If IRR time +1 for the day
[/TD]
[/TR]
</tbody>[/TABLE]

I dont know why other areas have color markings, birthday, 17-21 and the blank red at the top. But your formula works amazing i just cant get it to work for me when moving it. I spent have the workday trying to figure it out. I didn't wnat to post again trying to be prideful. I want to say thank you again for you help

 

[AlanY]

see if this is what you're after

Excel 2012
	A	B	C	D	I	J	K	L
1
2	Today	2017	6	29
3	Birthday	1986	11	29
4	Hired date	2012	6	12
5	Last Work day	2017	6	11
6
7
8
9
10
11
12
13
14
15
16
17					TODAY'S DATE	2017	6	29
18					BIRTHDAY	1986	11	29
19					MINUSED	30	6	30
20					PLUS 1 FOR THE DAY			30
21					SUM TOTAL	30	6	60
22					MINUS YEARS EMPLOYED	4	11	29
23					Total	25	6	29
Sheet2

Cell Formulas
Range	Formula
J19	=INT((DATE(J17,K17,L17)-DATE(J18,K18,L18))/365.25)
J22	=INT((DATE(B5,C5,D5)-DATE(B4,C4,D4))/365.25)
J23	=INT((DATE(J19,K19,L19)-DATE(J22,K22,L22))/365.25)
K19	=INT(((DATE(J17,K17,L17)-DATE(J18,K18,L18))/365.25-J19)*12)
K22	=INT(((DATE(B5,C5,D5)-DATE(B4,C4,D4))/365.25-J22)*12)
K23	=INT(((DATE(J19,K19,L19)-DATE(J22,K22,L22))/365.25-J23)*12)
L19	=((DATE(J17,K17,L17)-DATE(J18,K18,L18))/365.25)*365.25-(J19+K19/12)*365.25
L22	=((DATE(B5,C5,D5)-DATE(B4,C4,D4))/365.25-(J22+K22/12))*365.25
L23	=((DATE(J19,K19,L19)-DATE(J22,K22,L22))/365.25)*365.25-(J23+K23/12)*365.25

 

[Newbienew]

This is what i got when i cut and pasted. <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 { font-size: 14pt; font-weight: 700; text-align: center; }.xl69 { 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; }.xl70 { 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; }.xl71 { font-size: 9pt; white-space: normal; }.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: nowrap; }.xl73 { 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; }.xl74 { font-size: 14pt; font-weight: 700; text-align: center; white-space: normal; }.xl75 { border: 0.5pt solid windowtext; background: red none repeat scroll 0% 0%; }.xl76 { font-size: 14pt; font-weight: 700; text-align: center; white-space: nowrap; }.xl77 { white-space: normal; }.xl78 { 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; }.xl79 { border: 0.5pt solid windowtext; background: red none repeat scroll 0% 0%; }</style> [TABLE="width: 390"]
<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:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:4534;width:93pt" width="124"> <col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> </colgroup><tbody>[TR]
[TD="class: xl78, width: 180"] [/TD]
[TD="class: xl76, width: 51"]I16[/TD]
[TD="class: xl68, width: 75"]J[/TD]
[TD="class: xl68, width: 124"]K[/TD]
[TD="class: xl68, width: 90"]L[/TD]
[/TR]
[TR]
[TD="class: xl78"]TODAY'S DATE[/TD]
[TD="class: xl72"]17[/TD]
[TD="class: xl67, align: right"]2016[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 180"]BIRTHDAY[/TD]
[TD="class: xl73, 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: xl70, width: 180"]MINUSED TOTAL[/TD]
[TD="class: xl73, width: 51"]19[/TD]
[TD="class: xl75, align: right"]29[/TD]
[TD="class: xl67, align: right"]7[/TD]
[TD="class: xl79, align: right"]0.6875
[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 180"]PLUS 1 FOR THE DAY[/TD]
[TD="class: xl73, width: 51"]20[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl75, align: right"]1.6875[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 180"]SUM TOTAL[/TD]
[TD="class: xl73, width: 51"]21[/TD]
[TD="class: xl67, align: right"]29[/TD]
[TD="class: xl67, align: right"]7[/TD]
[TD="class: xl75, align: right"]1.6875[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 180"]MINUS YEARS EMPLOYED[/TD]
[TD="class: xl74, width: 51"]22[/TD]
[TD="class: xl67, align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl67, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl77, width: 180"]Total[/TD]
[TD="class: xl74, width: 51"]23[/TD]
[TD="class: xl67, align: right"]24
[/TD]
[TD="class: xl67, align: right"]3
[/TD]
[TD="class: xl75, align: right"]27.6875
[/TD]
[/TR]
</tbody>[/TABLE]

If i do it by hand this is what i got based off todays day

<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 { font-size: 14pt; font-weight: 700; text-align: center; }.xl69 { 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; }.xl70 { 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; }.xl71 { font-size: 9pt; white-space: normal; }.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: nowrap; }.xl73 { 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; }.xl74 { font-size: 14pt; font-weight: 700; text-align: center; white-space: normal; }.xl75 { font-size: 14pt; font-weight: 700; text-align: center; white-space: nowrap; }.xl76 { white-space: normal; }.xl77 { 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; }</style> [TABLE="width: 390"]
<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:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:4534;width:93pt" width="124"> <col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> </colgroup><tbody>[TR]
[TD="class: xl77, width: 180"] [/TD]
[TD="class: xl75, width: 51"]I16[/TD]
[TD="class: xl68, width: 75"]J[/TD]
[TD="class: xl68, width: 124"]K[/TD]
[TD="class: xl68, width: 90"]L[/TD]
[/TR]
[TR]
[TD="class: xl77"]TODAY'S DATE[/TD]
[TD="class: xl72"]17[/TD]
[TD="class: xl67, align: right"]2016[/TD]
[TD="class: xl67, align: right"]18[/TD]
[TD="class: xl67, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 180"]BIRTHDAY[/TD]
[TD="class: xl73, 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: xl70, width: 180"]MINUSED[/TD]
[TD="class: xl73, width: 51"]19[/TD]
[TD="class: xl67, align: right"]30
[/TD]
[TD="class: xl67, align: right"]7
[/TD]
[TD="class: xl67, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 180"]PLUS 1 FOR THE DAY[/TD]
[TD="class: xl73, width: 51"]20[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 180"]SUM TOTAL[/TD]
[TD="class: xl73, width: 51"]21[/TD]
[TD="class: xl67, align: right"]30[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67, align: right"]32[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 180"]MINUS YEARS EMPLOYED[/TD]
[TD="class: xl74, width: 51"]22[/TD]
[TD="class: xl67, align: right"]5[/TD]
[TD="class: xl67, align: right"]3[/TD]
[TD="class: xl67, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl76, width: 180"]Total[/TD]
[TD="class: xl74, width: 51"]23[/TD]
[TD="class: xl67, align: right"]25
[/TD]
[TD="class: xl67, align: right"]3[/TD]
[TD="class: xl67, align: right"]29[/TD]
[/TR]
</tbody>[/TABLE]

I am not sure what happened

 

[Newbienew]

Hello, I am revisiting this posting I made in need of some assistance as it has resurfaced in my workering tickler. Mr. AlanY provide some outstanding assistance and aid. I did happen to use what was provided but I had an issue with getting ti to work fully. I am greatly appreciative for the additional assistance. If you could have any questions please let me know.

 

[Newbienew]

If anyone could re-assist me it be great. Thank You in adance