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

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

santaviga wrote:
>
> 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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"santaviga" <(E-Mail Removed)> wrote in message
news:C02B20F5-38CC-418B-A0A6-(E-Mail Removed)...
> 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

Thanks a lot guys worked a treat. Regards Mark

"Don Guillett" wrote:

> =DATEDIF(A1,A2,"y")&"years,
> "&DATEDIF(A1,A2,"ym")&"months"&DATEDIF(A1,A2,"md")&" days"
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "santaviga" <(E-Mail Removed)> wrote in message
> news:C02B20F5-38CC-418B-A0A6-(E-Mail Removed)...
> > 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
>
>

Glad to help
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"santaviga" <(E-Mail Removed)> wrote in message
news:46038652-9C22-4326-B8C5-(E-Mail Removed)...
> Thanks a lot guys worked a treat. Regards Mark
>
> "Don Guillett" wrote:
>
>> =DATEDIF(A1,A2,"y")&"years,
>> "&DATEDIF(A1,A2,"ym")&"months"&DATEDIF(A1,A2,"md")&" days"
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "santaviga" <(E-Mail Removed)> wrote in message
>> news:C02B20F5-38CC-418B-A0A6-(E-Mail Removed)...
>> > 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
>>
>>