calculate year and month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

michelle - 30 Jan 2007 16:10 GMT

I have a start date and an end date. I want to show the number of years and
months between these two dates. (i.e. from 8/9/05 to 2/1/07 it would show 1
yr 6 mos)

Reply to this Message

Jim Thomlinson - 30 Jan 2007 16:16 GMT

Check out thisw link on the DateDif function...

http://www.cpearson.com/excel/datedif.htm

Signature

HTH...

Jim Thomlinson

> I have a start date and an end date. I want to show the number of years and
> months between these two dates. (i.e. from 8/9/05 to 2/1/07 it would show 1
> yr 6 mos)

Reply to this Message

pinmaster - 30 Jan 2007 16:19 GMT

Hi,

Try this:

=DATEDIF(start_date,end_date,"y")&IF(DATEDIF(start_date,end_dated,"y")>1,"
years "," year
")&DATEDIF(start_date,end_date,"ym")&IF(DATEDIF(start_date,end_date,"ym")>1,"
months "," month")

HTH
Jean-Guy

> I have a start date and an end date. I want to show the number of years and
> months between these two dates. (i.e. from 8/9/05 to 2/1/07 it would show 1
> yr 6 mos)

Reply to this Message

michelle - 30 Jan 2007 17:02 GMT

This helped much. Can I add the number of days to this also?

> Hi,
>
[quoted text clipped - 11 lines]
> > months between these two dates. (i.e. from 8/9/05 to 2/1/07 it would show 1
> > yr 6 mos)

Reply to this Message

pinmaster - 30 Jan 2007 17:29 GMT

Hi Michelle

Yes you can, the argument for the days within a year is "yd" so just add a
formula with that argument at the end.

HTH
Jean-Guy

> This helped much. Can I add the number of days to this also?
>
[quoted text clipped - 13 lines]
> > > months between these two dates. (i.e. from 8/9/05 to 2/1/07 it would show 1
> > > yr 6 mos)

Reply to this Message

michelle - 30 Jan 2007 17:39 GMT

Thank you again...I figured it out after I asked.

> Hi Michelle
>
[quoted text clipped - 21 lines]
> > > > months between these two dates. (i.e. from 8/9/05 to 2/1/07 it would show 1
> > > > yr 6 mos)

Reply to this Message

David Biddulph - 30 Jan 2007 18:09 GMT

Rather than "yd", I think you may want "md" for days excluding months and
years?

Signature

David Biddulph

> Hi Michelle
>
> Yes you can, the argument for the days within a year is "yd" so just add a
> formula with that argument at the end.

>> This helped much. Can I add the number of days to this also?

>> > Hi,
>> >
[quoted text clipped - 4 lines]
>> > ")&DATEDIF(start_date,end_date,"ym")&IF(DATEDIF(start_date,end_date,"ym")>1,"
>> > months "," month")

>> > > I have a start date and an end date. I want to show the number of
>> > > years and
>> > > months between these two dates. (i.e. from 8/9/05 to 2/1/07 it would
>> > > show 1
>> > > yr 6 mos)

Reply to this Message

Sai Krishna - 23 May 2008 08:54 GMT

Hi,

I had raised a similar post but got the answer from you.

thanks
krishna

> Hi,
>
[quoted text clipped - 11 lines]
> > months between these two dates. (i.e. from 8/9/05 to 2/1/07 it would show 1
> > yr 6 mos)

Reply to this Message

Gary''s Student - 30 Jan 2007 16:19 GMT

Use the DATDIF() function. If the dates are in A1 and A2 then:
=DATEDIF(A1,A2,"y")&" years, "&DATEDIF(A1,A2,"ym")&" months"
will display:
1 years, 5 months

Signature

Gary's Student
gsnu200703

> I have a start date and an end date. I want to show the number of years and
> months between these two dates. (i.e. from 8/9/05 to 2/1/07 it would show 1
> yr 6 mos)

Reply to this Message

Sean Timmons - 30 Jan 2007 16:37 GMT

Or, you can do =days360(A2,B2,FALSE)
Assuming A2 is the start and B2 is the end.
May need to add Analysis ToolPak from the Tools/AddIns option.

> I have a start date and an end date. I want to show the number of years and
> months between these two dates. (i.e. from 8/9/05 to 2/1/07 it would show 1
> yr 6 mos)

Reply to this Message