Calculation between two dates

I need to find out the amount of time between two dates for filling out
funeral benefits. The form asks how long the person has been alive in Years
Months and Days.

I would like to know if I put for instance 10/21/1955 in say A1 as the birth
date and 01/25/2006 in B1 as the date of death. So what is the formula, if
one, to calculate the time in years months and days that has passed between
the two dates?

Yes:


See:

http://www.cpearson.com/excel/datedif.htm
--
Gary''s Student


"Rameris" wrote:

> I need to find out the amount of time between two dates for filling out
> funeral benefits. The form asks how long the person has been alive in Years
> Months and Days.
>
> I would like to know if I put for instance 10/21/1955 in say A1 as the birth
> date and 01/25/2006 in B1 as the date of death. So what is the formula, if
> one, to calculate the time in years months and days that has passed between
> the two dates?

Well I guess I am the thickest individual in the solar system but doesn't
that tell me either how many years OR months OR days? I need to know how
may of each for example in

"Gary''s Student" wrote:

> Yes:
>
>
> See:
>
> http://www.cpearson.com/excel/datedif.htm
> --
> Gary''s Student
>
>
> "Rameris" wrote:
>
> > I need to find out the amount of time between two dates for filling out
> > funeral benefits. The form asks how long the person has been alive in Years
> > Months and Days.
> >
> > I would like to know if I put for instance 10/21/1955 in say A1 as the birth
> > date and 01/25/2006 in B1 as the date of death. So what is the formula, if
> > one, to calculate the time in years months and days that has passed between
> > the two dates?

The formula on the "Calculating A Person's Age" section of the
web page will return a string stating how many years, months, and
days a person has been alive.

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

where A1 is birthdate and B1 is death date.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Rameris" <[email protected]> wrote in message
news:[email protected]...
> Well I guess I am the thickest individual in the solar system
> but doesn't
> that tell me either how many years OR months OR days? I need
> to know how
> may of each for example in
>
> "Gary''s Student" wrote:
>
>> Yes:
>>
>>
>> See:
>>
>> http://www.cpearson.com/excel/datedif.htm
>> --
>> Gary''s Student
>>
>>
>> "Rameris" wrote:
>>
>> > I need to find out the amount of time between two dates for
>> > filling out
>> > funeral benefits. The form asks how long the person has
>> > been alive in Years
>> > Months and Days.
>> >
>> > I would like to know if I put for instance 10/21/1955 in say
>> > A1 as the birth
>> > date and 01/25/2006 in B1 as the date of death. So what is
>> > the formula, if
>> > one, to calculate the time in years months and days that has
>> > passed between
>> > the two dates?

If you could see me I'd be bowing to you in homage..


Thank-you very much


"Chip Pearson" wrote:

> The formula on the "Calculating A Person's Age" section of the
> web page will return a string stating how many years, months, and
> days a person has been alive.
>
> =DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & "
> months, " & DATEDIF(A1,B1,"md") & " days"
>
> where A1 is birthdate and B1 is death date.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "Rameris" <[email protected]> wrote in message
> news:[email protected]...
> > Well I guess I am the thickest individual in the solar system
> > but doesn't
> > that tell me either how many years OR months OR days? I need
> > to know how
> > may of each for example in
> >
> > "Gary''s Student" wrote:
> >
> >> Yes:
> >>
> >>
> >> See:
> >>
> >> http://www.cpearson.com/excel/datedif.htm
> >> --
> >> Gary''s Student
> >>
> >>
> >> "Rameris" wrote:
> >>
> >> > I need to find out the amount of time between two dates for
> >> > filling out
> >> > funeral benefits. The form asks how long the person has
> >> > been alive in Years
> >> > Months and Days.
> >> >
> >> > I would like to know if I put for instance 10/21/1955 in say
> >> > A1 as the birth
> >> > date and 01/25/2006 in B1 as the date of death. So what is
> >> > the formula, if
> >> > one, to calculate the time in years months and days that has
> >> > passed between
> >> > the two dates?
>
>
>

I hope you don't mind answering another question but I used the same basic
formula with two different dates.

The results were correct as I now have

XX Years XX months and XX days for a calculation of a person's age

YY years YY months and YY days for a calculation of a person's time with the
company

When I tried to add those two numbers by simply putting for example
@sum(d4+d5) I got a # VALUE error

The reason I wanted to add those two was a person can retire from our
company when a combination of their age and years of service hit 80 combined
total.

Can you explain what I'm doing wrong in that case?

"Chip Pearson" wrote:

> The formula on the "Calculating A Person's Age" section of the
> web page will return a string stating how many years, months, and
> days a person has been alive.
>
> =DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & "
> months, " & DATEDIF(A1,B1,"md") & " days"
>
> where A1 is birthdate and B1 is death date.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "Rameris" <[email protected]> wrote in message
> news:[email protected]...
> > Well I guess I am the thickest individual in the solar system
> > but doesn't
> > that tell me either how many years OR months OR days? I need
> > to know how
> > may of each for example in
> >
> > "Gary''s Student" wrote:
> >
> >> Yes:
> >>
> >>
> >> See:
> >>
> >> http://www.cpearson.com/excel/datedif.htm
> >> --
> >> Gary''s Student
> >>
> >>
> >> "Rameris" wrote:
> >>
> >> > I need to find out the amount of time between two dates for
> >> > filling out
> >> > funeral benefits. The form asks how long the person has
> >> > been alive in Years
> >> > Months and Days.
> >> >
> >> > I would like to know if I put for instance 10/21/1955 in say
> >> > A1 as the birth
> >> > date and 01/25/2006 in B1 as the date of death. So what is
> >> > the formula, if
> >> > one, to calculate the time in years months and days that has
> >> > passed between
> >> > the two dates?
>
>
>

You are trying to add two strings of text, so you get the #VALUE error.

Pete

Thanks Pete.. at lease I know why.. but do you know how to correct the problem?

"Pete" wrote:

> You are trying to add two strings of text, so you get the #VALUE error.
>
> Pete
>
>

Assuming you have two connected dates in A1 and B1 (eg date of birth
and today( ) ), and two more dates in A3 and B3, the following formula
in, say, B6 will give you almost what you want:

=(DATEDIF(A1,B1,"y")+DATEDIF(A3,B3,"y")) & " years, " &
(DATEDIF(A1,B1,"ym")+DATEDIF(A3,B3,"ym")) & " months, " &
(DATEDIF(A1,B1,"md")+DATEDIF(A3,B3,"md")) & " days"

- all one formula. Unfortunately, this can give return values like;

65 years, 14 months, 23 days

and I'm not sure how to correct the number of months (I've never used
DATEDIF before!)

Here's an alternative, using the same initial dates where dates in B
are later than those in A:

=TEXT((B1+B3-A1-A3)/365.25,0)&" years,
"&TEXT((MOD((B1+B3-A1-A3),365.25)),0)&" days"

and this returns values like:

66 years, 82 days

although if you use 365 days in the formula instead of 365.25 with the
same dates that I did then it would show 66 years, 98 days

Take your pick, and I hope you can make some use of them.

Pete

If your date of birth is in A2 and hire date is in B2 this should give the combined age and length of service as of today

=DATEDIF(A2+B2,NOW()*2,"y")&" years, "&DATEDIF(A2+B2,NOW()*2,"ym")&" months, "&DATEDIF(A2+B2,NOW()*2,"md")&" days"

Of course if this were to give a result of, say, 70 years then there would only be 5 years until retirement because each year that passes counts twice, once towards age and once on length of service.

The calculation is necessarily an approximation because of variable month lengths.

As you can appreciate, that DATEDIF construction is great for showing ages etc. but not fantastic for calculations. You could calculate an employees date of retirement based on your criteria using this formula.

=B2+(A2-B2+365.25*80)/2

format as date

Again this is an approximation based on 365¼ days in a year....

Thank you Pete .. it worked perfectly and I even understand it.

"Pete" wrote:

> Assuming you have two connected dates in A1 and B1 (eg date of birth
> and today( ) ), and two more dates in A3 and B3, the following formula
> in, say, B6 will give you almost what you want:
>
> =(DATEDIF(A1,B1,"y")+DATEDIF(A3,B3,"y")) & " years, " &
> (DATEDIF(A1,B1,"ym")+DATEDIF(A3,B3,"ym")) & " months, " &
> (DATEDIF(A1,B1,"md")+DATEDIF(A3,B3,"md")) & " days"
>
> - all one formula. Unfortunately, this can give return values like;
>
> 65 years, 14 months, 23 days
>
> and I'm not sure how to correct the number of months (I've never used
> DATEDIF before!)
>
> Here's an alternative, using the same initial dates where dates in B
> are later than those in A:
>
> =TEXT((B1+B3-A1-A3)/365.25,0)&" years,
> "&TEXT((MOD((B1+B3-A1-A3),365.25)),0)&" days"
>
> and this returns values like:
>
> 66 years, 82 days
>
> although if you use 365 days in the formula instead of 365.25 with the
> same dates that I did then it would show 66 years, 98 days
>
> Take your pick, and I hope you can make some use of them.
>
> Pete
>
>

Okay, cheers.

Pete