close

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:

www.cpearson.com/excel/datedif.htm
--
Gary''s Studentquot;Ramerisquot; wrote:

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

quot;Gary''s Studentquot; wrote:

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

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

=DATEDIF(A1,B1,quot;yquot;) amp; quot; years, quot; amp; DATEDIF(A1,B1,quot;ymquot;) amp; quot;
months, quot; amp; DATEDIF(A1,B1,quot;mdquot;) amp; quot; daysquot;

where A1 is birthdate and B1 is death date.--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

quot;Ramerisquot; gt; wrote in message
...
gt; Well I guess I am the thickest individual in the solar system
gt; but doesn't
gt; that tell me either how many years OR months OR days? I need
gt; to know how
gt; may of each for example in
gt;
gt; quot;Gary''s Studentquot; wrote:
gt;
gt;gt; Yes:
gt;gt;
gt;gt;
gt;gt; See:
gt;gt;
gt;gt; www.cpearson.com/excel/datedif.htm
gt;gt; --
gt;gt; Gary''s Student
gt;gt;
gt;gt;
gt;gt; quot;Ramerisquot; wrote:
gt;gt;
gt;gt; gt; I need to find out the amount of time between two dates for
gt;gt; gt; filling out
gt;gt; gt; funeral benefits. The form asks how long the person has
gt;gt; gt; been alive in Years
gt;gt; gt; Months and Days.
gt;gt; gt;
gt;gt; gt; I would like to know if I put for instance 10/21/1955 in say
gt;gt; gt; A1 as the birth
gt;gt; gt; date and 01/25/2006 in B1 as the date of death. So what is
gt;gt; gt; the formula, if
gt;gt; gt; one, to calculate the time in years months and days that has
gt;gt; gt; passed between
gt;gt; gt; the two dates?
If you could see me I'd be bowing to you in homage..Thank-you very muchquot;Chip Pearsonquot; wrote:

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

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?

quot;Chip Pearsonquot; wrote:

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

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

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

quot;Petequot; wrote:

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

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,quot;yquot;) DATEDIF(A3,B3,quot;yquot;)) amp; quot; years, quot; amp;
(DATEDIF(A1,B1,quot;ymquot;) DATEDIF(A3,B3,quot;ymquot;)) amp; quot; months, quot; amp;
(DATEDIF(A1,B1,quot;mdquot;) DATEDIF(A3,B3,quot;mdquot;)) amp; quot; daysquot;

- 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)amp;quot; years,
quot;amp;TEXT((MOD((B1 B3-A1-A3),365.25)),0)amp;quot; daysquot;

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,quot;yquot;)amp;quot; years, quot;amp;DATEDIF(A2 B2,NOW()*2,quot;ymquot;)amp;quot;
months, quot;amp;DATEDIF(A2 B2,NOW()*2,quot;mdquot;)amp;quot; daysquot;

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#188; days in a year....--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=505391

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 software 的頭像
    software

    software

    software 發表在 痞客邦 留言(0) 人氣()