Experiance in year and months

[vjharry]

As per attached file Can get experience in Years and months like "12 Years 10 months".

[TABLE="width: 344"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]S No[/TD]
[TD]Name[/TD]
[TD]Date of Joining[/TD]
[TD]Experience[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Kane[/TD]
[TD="align: right"]18-Nov-02[/TD]
[TD="align: right"]12.81[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Morris[/TD]
[TD="align: right"]18-Nov-02[/TD]
[TD="align: right"]12.81[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bill[/TD]
[TD="align: right"]3-Feb-03[/TD]
[TD="align: right"]12.60[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]James[/TD]
[TD="align: right"]17-Mar-03[/TD]
[TD="align: right"]12.48[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Rohit[/TD]
[TD="align: right"]15-Oct-03[/TD]
[TD="align: right"]11.90[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Tom[/TD]
[TD="align: right"]1-Jan-04[/TD]
[TD="align: right"]11.69[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Michal[/TD]
[TD="align: right"]5-Jan-04[/TD]
[TD="align: right"]11.68[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Try

=TEXT(TODAY()-C2,"y")&" Years "&TEXT(TODAY()-C2,"m")&" Months"

 

[vjharry]

Thanks it worked

 

[gaz_chops]

You're welcome.

 

[vjharry]

if suppose Year is "00" then can we hide it!!

 

[gaz_chops]

See if this is what you want

=IF(YEAR(D5)=YEAR(TODAY()),TEXT(TODAY()-C2,"m")&" Months",TEXT(TODAY()-D5,"y")&" Years "&TEXT(TODAY()-D5,"m")&" Months")

 

[vjharry]

Thanks so much

 

[gaz_chops]

You're welcome.

 

[gaz_chops]

Noticed I made a mistake with the cell references!!

=IF(YEAR(D2)=YEAR(TODAY()),TEXT(TODAY()-D2,"m")&" Months",TEXT(TODAY()-D2,"y")&" Years "&TEXT(TODAY()-D2,"m")&" Months")

 

[gaz_chops]

I found an error if the month is the same as the current month, it returns 12 Months!

Try this one instead

=IF(DATEDIF(D2,TODAY(),"Y")<1,DATEDIF(D2,TODAY(),"YM")&" Months",DATEDIF(D2,TODAY(),"Y")&" Years "&DATEDIF(D2,TODAY(),"YM")&" Months")

Or this one, if months=0 then it only shows years.

=IF(DATEDIF(D2,TODAY(),"Y")<1,DATEDIF(D2,TODAY(),"YM")&" Months",IF(DATEDIF(D2,TODAY(),"YM")<1,DATEDIF(D2,TODAY(),"Y")&" Years",DATEDIF(D2,TODAY(),"Y")&" Years "&DATEDIF(D2,TODAY(),"YM")&" Months"))