Hi
Did anyone know how to convert a decimal number to years,months & days.
For ex 1.50=1years 6months , llly how to convert 1.56 , or any other decimal into y,m & days.
I tried the below formula but its not working (Cell A1=1.5)
INT(A1) for years
INT(MOD(A1,365)/30) for months
ROUND(MOD((MOD(A1,365)),30),0) for days
Do i need to enable any add ins or options in excel. Plz send me the formula for this.
Im trying this for the below problem to calculate the total experience till date , using the previous exp say(7yrs) and the date of joining.
Prev Exp DOJ Total EXP
7.00 05 June 2006
1.90 22 March 2010
6.40 14 May 2007
4.00 14 September 2010
I tried the below formula
=CONCATENATE(SUM(DATEDIF(B2,TODAY(),"y"),INT(A2)),"years, ",SUM(DATEDIF(B2,TODAY(),"ym"),INT(MOD(A2,365)/30)),"Months, ",SUM(DATEDIF(B2,TODAY(),"md"),MOD((MOD(A2,365)),30)),"days.")
Result: 11Years, 4Months, 6.2days (here year is coming correct but months and days are not right because of the decimal conversion.
Appreciate anyone suggestion on this.
Bookmarks