Calculation for Job Employement

Newbienew

Active Member
Joined
Mar 17, 2017
Messages
395
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
your required time of age is in B7 and converted in yr, month and day in Row 8


Excel 2012
ABCD
1Today2017626
2Birthday19861129
3Hired date2012612
4Last Work day2017611
5Years Employed5.0
6
7Time of age25.6
825628
Sheet3
Cell Formulas
RangeFormula
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
 
Upvote 0
@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
 
Upvote 0
you mean like this?


Excel 2012
ABCD
1
2Today2017627
3Birthday19861129
4Hired date2012612
5Last Work day2017611
6Years Employed5.0
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23Time of age25629
Sheet1
Cell Formulas
RangeFormula
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
 
Upvote 0
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
 
Upvote 0
see if this is what you're after


Excel 2012
ABCDIJKL
1
2Today2017629
3Birthday19861129
4Hired date2012612
5Last Work day2017611
6
7
8
9
10
11
12
13
14
15
16
17TODAY'S DATE2017629
18BIRTHDAY19861129
19MINUSED30630
20PLUS 1 FOR THE DAY30
21SUM TOTAL30660
22MINUS YEARS EMPLOYED41129
23Total25629
Sheet2
Cell Formulas
RangeFormula
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,127
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top