Decimal to date

Hi

if i get your request right, why dont you convert the decimal into moths first, subtract and convert back?

for you example

first split the years from the months

Years into months=left(9.2,find(".",9.2,1)-1)

months = mid(9.2,left(9.2,find(".",9.2,1)+1),1)

and than

=VALUE(ROUND((9*12+2-8*12+1)/12,0)&"."&MOD(9*12+2-8*12+1,12))=1.3

---

Lynn McKie wrote:

Hi there hoping someone can help me.

 

I am creating a spreadsheet currently looking at differences in data. However the data i have currently gives ages as 9.2 years (9 years 2 months) but when doing calculations Excel sees it as a decimal and doesn't calculate correctly.

 

for example 9.2 - 8.10 = 0.4 (excel says -1.1)

 

I'm looking for a simple formula to change 9.2 into 9years and 2months on the computer if possible?

 

Hope that makes some sense to somebody.

 

Lynn

---

---

Lynn McKie wrote:

Hi there hoping someone can help me.

 

I am creating a spreadsheet currently looking at differences in data. However the data i have currently gives ages as 9.2 years (9 years 2 months) but when doing calculations Excel sees it as a decimal and doesn't calculate correctly.

 

for example 9.2 - 8.10 = 0.4 (excel says -1.1)

 

I'm looking for a simple formula to change 9.2 into 9years and 2months on the computer if possible?

 

Hope that makes some sense to somebody.

 

Lynn

---

Hi Lynn,

Please clarify how your initial data is presented. For example, if that's the number 8.1 (which is equal to number 8.10) how do you know that's 8 years and 1 month or 8 years and 10 months? Or you have text here?