Formulae

[Deborah]

I am creating an anniversay date for length of service.
I have found the formuale to now.

=YEARFRAC(Date,NOW())

This is probably simple, but how do I calculate the start
date plus 10 years.

 

[CLR]

Assuming your starting Date to be in cell A1, you can put this formula in B1
and a "multiplier number" in C1......then, B1 will display your starting
date plus however many years you type in C1.......

=ROUND(A1+(365.25*C1),0)

Vaya con Dios,
Chuck CABGx3

 

[Myrna Larson]

I don't think calculating the anniversary date for length of service by assuming 365.25 days per
year will cut it.

No calendar year has 365.25 days. It's either 365 or 366. A 10 year time span will have 3652 or
3653 days, depending on where you started WRT the leap-year cycle and, if the person started
work in a leap year, whether they started before or after March 1.

According to my rough calculations, approximately 50% of the time the 10 year anniversary date
will be 3652 days after the start date, 50% of the time it will be 3653 days later.

For 10 years, your formula will always calculate 3652.5 days and round that to 3653 days. That
will be incorrect about half the time. I expect the employer requires an EXACT date.

If employment started on the date in A1, and Deborah wants to calculate the 10 year anniversary
date, the formula is

=DATE(YEAR(A1)+10,MONTH(A1),DAY(A1))

For the anniversary date in the current year, the formula is

=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))

To calculate the years of service as of the current date, in years, months, and days, the 3
formulas are

=DATEDIF(A1,TODAY(),"y") gives full years
=DATEDIF(A1,TODAY(),"ym") gives full months, if any, in the final partial year
=DATEDIF(A1,TODAY(),"md") gives days in the final partial month, if any

I hope they don't start people on Feb 29. The anniversary date in a non-leap year would be March
1 rather than Feb 29, and the DATEDIF formula exhibits some anomalies there.

 

[Myrna Larson]

I verified the points that I made in my earlier reply re the problem with assuming 365.25
days/year this way:

In A1, put the date 1/1/2000. Edit/Fill/Series down through 12/31/2003 (i.e. to A1461)
In B1, put the formula =DATE(YEAR(A1)+10,MONTH(A1),DAY(A1)). Copy down through B1461.
In C1, put the formula =ROUND(A1+365.25*10,0) and copy down through C1461.

This gives you all possible hire dates in a 4 year leap year cycle in column A; in column B the
corresponding 10-year anniversary date according to the calendar (with the anniversary date for
a hire date of Feb 29 being Mar 1 since the 10th year won't be a leap year); in column C the
corresponding 10-year anniversary date calculated assuming 365.25 days per year.

The formula =SUMPRODUCT(1*(B1:B1461<>C1:C1461)) gives 730, i.e. for 730 of the possible 1461
hire dates, the date in column C is in error by 1 day. Those 730 problematic hire dates are
3/1/2000 through 2/28/2002. Example: for a hire date of 2/1/2002, the correct anniversary date
(you don't need a formula for this <g>) is 2/1/2012. The "365.25 formula" says it's on 2/2/2012.

As I said, for this purpose, I don't think that error will be acceptable to Deborah's employer.

 

[GB]

Myrna - You are absolutely right, of course.

It depends on the work environment. I work as an actuary, and in virtually
all cases I just use the 365.25 formula. (Some people use 365.24 instead
because nearly all century dates are not leap years even tho they can be
divided by 4. 2000 was an exception!)

Where you can get away with it, it's much quicker just to add 365 days than
to mess around with date formulae.

Geoff

 

[Bernard V Liengme]

Very few people were using Excel (0 is a small number) during the last
century date (1900 AD)
I wonder how many people will be using Excel when the next century date
rolls around (2100 AD ?)
Bernard <vbg>

Myrna - You are absolutely right, of course.

It depends on the work environment. I work as an actuary, and in virtually
all cases I just use the 365.25 formula. (Some people use 365.24 instead
because nearly all century dates are not leap years even tho they can be
divided by 4. 2000 was an exception!)

Where you can get away with it, it's much quicker just to add 365 days than
to mess around with date formulae.

Geoff

I don't think calculating the anniversary date for length of service by assuming 365.25 days per
year will cut it.

No calendar year has 365.25 days. It's either 365 or 366. A 10 year time span will have 3652 or
3653 days, depending on where you started WRT the leap-year cycle and,

if
the person started

work in a leap year, whether they started before or after March 1.

According to my rough calculations, approximately 50% of the time the 10 year anniversary date
will be 3652 days after the start date, 50% of the time it will be 3653 days later.

For 10 years, your formula will always calculate 3652.5 days and round that to 3653 days. That
will be incorrect about half the time. I expect the employer requires an EXACT date.

If employment started on the date in A1, and Deborah wants to calculate the 10 year anniversary
date, the formula is

=DATE(YEAR(A1)+10,MONTH(A1),DAY(A1))

For the anniversary date in the current year, the formula is

=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))

To calculate the years of service as of the current date, in years, months, and days, the 3
formulas are

=DATEDIF(A1,TODAY(),"y") gives full years
=DATEDIF(A1,TODAY(),"ym") gives full months, if any, in the final partial year
=DATEDIF(A1,TODAY(),"md") gives days in the final partial month, if any

I hope they don't start people on Feb 29. The anniversary date in a non-leap year would be March
1 rather than Feb 29, and the DATEDIF formula exhibits some anomalies there.

 

[GB]

Very few people were using Excel (0 is a small number) during the last
century date (1900 AD)
I wonder how many people will be using Excel when the next century date
rolls around (2100 AD ?)
Bernard <vbg>

Actuaries value pension liabilities, including people already on pension.
There are still people born in the 1890's receiving pensions today. It's not
too important if you're a day or two out when estimating their age for
valuation purposes, so dividing the days by 365.25 (or 365.24) will do
nicely thank you.

Geoff

Myrna - You are absolutely right, of course.

It depends on the work environment. I work as an actuary, and in virtually
all cases I just use the 365.25 formula. (Some people use 365.24 instead
because nearly all century dates are not leap years even tho they can be
divided by 4. 2000 was an exception!)

Where you can get away with it, it's much quicker just to add 365 days than
to mess around with date formulae.

Geoff

I don't think calculating the anniversary date for length of service

by
assuming 365.25 days per

year will cut it.

No calendar year has 365.25 days. It's either 365 or 366. A 10 year

time
span will have 3652 or

3653 days, depending on where you started WRT the leap-year cycle and,

if
the person started

work in a leap year, whether they started before or after March 1.

According to my rough calculations, approximately 50% of the time the

10
year anniversary date

will be 3652 days after the start date, 50% of the time it will be

3653
days later.

For 10 years, your formula will always calculate 3652.5 days and round that to 3653 days. That
will be incorrect about half the time. I expect the employer requires

an
EXACT date.

If employment started on the date in A1, and Deborah wants to

calculate
the 10 year anniversary

date, the formula is

=DATE(YEAR(A1)+10,MONTH(A1),DAY(A1))

For the anniversary date in the current year, the formula is

=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))

To calculate the years of service as of the current date, in years, months, and days, the 3
formulas are

=DATEDIF(A1,TODAY(),"y") gives full years
=DATEDIF(A1,TODAY(),"ym") gives full months, if any, in the final partial year
=DATEDIF(A1,TODAY(),"md") gives days in the final partial month,

if
any