count the days between 2 dates and convert

[Guest]

Hi, I have the following in data in cells and I want to calculate the days
between 2 date, which I can do, it returns 2316 days.

cell A1 is 30/04/2001
cell A2 is =Today()
cell A3 =SUM(A2-A1) returns 2316 days

I need to convert this figure into Years, Months and Days. e.g. 6 years 4
months and 3 days.

Does anyone have any idea on a formula for this?

Regards

MN

 

[Dave Peterson]

First, you don't need the =sum() around your subtraction.

=a2-a1
would work ok.

For the other stuff, look at Chip Pearson's notes for =DateDif():
http://www.cpearson.com/excel/datedif.htm

 

[Don Guillett]

=DATEDIF(A1,A2,"y")&"years,
"&DATEDIF(A1,A2,"ym")&"months"&DATEDIF(A1,A2,"md")&" days"

 

[Guest]

Thanks a lot guys worked a treat. Regards Mark

=DATEDIF(A1,A2,"y")&"years,
"&DATEDIF(A1,A2,"ym")&"months"&DATEDIF(A1,A2,"md")&" days"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Glad to help