Date Formula

[muziq2]

Hi all:

Here's what I'm trying to do and hope you all can help. I have tw
dates. A birth date and a hire date. I need to find the year that th
age + years of service will equal 80.

Hope you can help because I'm stumped.

Thanks,

Jef

 

[Ken Wright]

With Birth Date in A1 and Hire Date in A2, it could be something along the lines
of:-

=YEAR($A$2)+(80-DATEDIF($A$1,$A$2,"y"))/2

Haven't tested enough to see where the actual year wraps, and it may well be
that it needs to be tweaked with a MOD or INT or ROUNDUP function.

Format the cell as a number with no thousands separator.

Gist of it is to take the birth date away from the hire date to get the age at
time of hire. Take that away from 80 and then half it because with every year
you advance 2 as you are summing both age and years of service.

 

[George Nicholson]

=YEAR(NOW()+DATE(80-((YEAR(NOW())-YEAR(BirthDate))+(YEAR(NOW())-YEAR(HireDat
e))),1,1))

a) find the difference between now and birthdate
b) find the difference between now and hiredate
c) subtract the sum of a+b from 80 (i.e., how many more years until 80 is
reached?)
d) add the result of c to the current year.

I'm sure there are other approaches...

Hope this helps,

 

[muziq2]

Hi,

I entered the formula exactly as pasted and it returned a value of
06/30/1905.

The birthdate was 02/02/1952
The hire date was 10/30/1979

Based on my calculations the year should be 2005. Do you think this is
the result of some of my excel settings or the formula?

Thanks for your help on this.

-j-

 

[Bob Phillips]

Don't know how you get that but George's formula adds on the difference in
years rather than half that amount.

Ken's formula, slightly adapted, works

=INT(YEAR($A$2)+(80-DATEDIF($A$1,$A$2,"y"))/2)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jim]

Here is one way:
In A1 enter =-DATEDIF("11/9/1941","11/18/03","y")+80
in B1 =YEAR(TODAY())+A1

 

[George Nicholson]

Mine also avoids using the undocumented DateDif Excel function.

--
George Nicholson

Remove 'Junk' from return address.

Don't know how you get that but George's formula adds on the difference in
years rather than half that amount.

Ken's formula, slightly adapted, works

=INT(YEAR($A$2)+(80-DATEDIF($A$1,$A$2,"y"))/2)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

George,

Datedif is documented in XL2000 on.

Bob

Mine also avoids using the undocumented DateDif Excel function.

 

[Gord Dibben]

Slight correction Bob.

Datedif was documented for the first and ONLY time in XL2000.

Documentation was dropped again in 2002. Believe it has also been left out of
2003.

Gord Dibben XL2002

 

[Bob Phillips]

Thanks Gord.

I don't have 2002 on this machine, and didn't check (tut, tut!). I never
thought they would remove something just added.

Bob

 

[muziq2]

Thanks for the help on this one. I must've entered George's formul
wrong the first time because now in my example with the dates
02/02/1952, 10/30/1979 - I get 2007.

The difficulty comes in that partial years also count. (i.e. someon
hired at the beginning of a year and born in the beginning of a yea
will have a different year where the birth date and hire date add to 8
than someone born/hired at the end of the year.)

Using the 365.25 number to calculate the age and years of service i
also not always accurate. In my example the year I get using tha
method is 2005. When I do it on paper I get that it will be near th
beginning of 2006.

-j

 

[George Nicholson]

Thanks, that's nice to know. I didn't realize it was ever documented.
(I'm using XP these days)

What exactly does that documentation say? Is it the same as the VBA
DateDiff function (except with only the 1st three arguments and with those
arguments in a different order?)

In XP, if you go to type the function the "auto-help" feature acknowledges
that DateDif is a recognized function, but that is it. No indication of
what the arguments are, or even how many. Just the function and a set of
parenthesis: DateDif(). This quasi-support makes me cautious about using it
for anything except my own work when i am in a hurry. If I need it for
something I provide coworkers or clients, I include my own version.

 

[Gord Dibben]

George

I have never run XL2000 so don't know what the documentation says.

Chip Pearson has more on Datedif.

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

Gord

 

[Ken Wright]

DATEDIF
See also
Calculates the number of days, months, or years between two dates. This function
is provided for compatibility with Lotus 1-2-3.
Syntax
DATEDIF(start_date,end_date,unit)
Start_date is a date that represents the first, or starting, date of the
period. Dates may be entered as text strings within quotation marks (for
example, "2001/1/30"), as serial numbers (for example, 36921, which represents
January 30, 2001, if you're using the 1900 date system), or as the results of
other formulas or functions (for example, DATEVALUE("2001/1/30")). For more
information about date serial numbers, see NOW.
End_date is a date that represents the last, or ending, date of the period.
Unit is the type of information you want returned.
UnitReturns
"Y"The number of complete years in the period.
"M"The number of complete months in the period.
"D"The number of days in the period.
"MD"The difference between the days in start_date and end_date. The months and
years of the dates are ignored.
"YM"The difference between the months in start_date and end_date. The days and
years of the dates are ignored.
"YD"The difference between the days of start_date and end_date. The years of the
dates are ignored.


Remarks
Microsoft Excel stores dates as sequential serial numbers so that it can perform
calculations on them. Excel stores January 1, 1900, as serial number 1 if your
workbook uses the 1900 date system. If your workbook uses the 1904 date system,
Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial
number 1). For example, in the 1900 date system, Excel stores January 1, 1998,
as serial number 35796 because it is 35,795 days after January 1, 1900. Learn
more about how Microsoft Excel stores dates and times.


Excel for Windows and Excel for the Macintosh use different date systems as
their default. For more information, see NOW.
Examples
DATEDIF("2001/1/1","2003/1/1","Y") equals 2, or two complete years in the
period.
DATEDIF("2001/6/1","2002/8/15","D") equals 440, or 440 days between June 1,
2001, and August 15, 2002.
DATEDIF("2001/6/1","2002/8/15","YD") equals 75, or 75 days between June 1 and
August 15, ignoring the years of the dates.
DATEDIF("2001/6/1","2002/8/15","MD") equals 14, or the difference between 1 and
15 — the day of start_date and the day of end_date — ignoring the months and
the years of the dates.