Date Formula 85 factor

[Newfie809]

Hi

A1 September 1, 2010
Years of Services
Age
A2 B2 C2 D2 E2 F2
G2 H2
Birth Date Date of Hire Years Mths Days Years Mths
Days
Nov 22, 1962 Sept 1, 1988 22 0 0 47 9
10
Aug 7, 1969 March 23, 1995 12 5 9 41 0
25

85 Factor
G2 H2 I2
Years Mths Days
53 5 34

I was adding both of the columns, but when it came to a employee with
information's in all three columns it did not work.

I am looking for a formula that will give me an 85 Factor
as of September 1, 2010. Age plus Years of Service.

Can anyone help?

thank you

 

[Newfie809]

--
Newfie

 

[Steve Dunn]

Hi,

I have no idea what an "85 Factor" is, and the years shown for years of
service appear to be incorrect, (22 should be 12, and 12 should be 15)
unless I'm misunderstanding your example. So, I can't be exactly sure what
you require, but I think this covers it:

I2:

=C2+F2+INT((D2+G2+((E2+H2)>DAY(DATE(,MONTH($A$1),))))/12)

J2:

=MOD(D2+G2+((E2+H2)>DAY(DATE(,MONTH($A$1),))),12)

K2:

=MOD(E2+H2,DAY(DATE(,MONTH($A$1),)))

Try them out. If they do not do what you require, some more information
would be helpful.

Steve D.

 

[Newfie809]

Hi Steve, maybe this explains it better.

85 Factor is the date of hire and Birth Date
as of today's date

A1 Today's date I used (September 1, 2010)

A2 Birth Date-August 7, 1969
B2 Date of Hire-March 23, 1995

Date of hire and today's date=Service
These formulas worked great

C2 Year 12
D2 Month 5
E2 Day 9

Birth Date and today's date= Age
These formulas worked great

F2 Year 41
G2 Month 0
H2 Day 25

85 Factor ( I added the other's together)
But adding both column's together did not work
Cant's have 34 days in a month.
So this is where my problem is

I2 Year 53
J2 Month 5
K2 Day 34

Thanks for your help

 

[Steve Dunn]

Hi Newfie,

I still don't understand why C2 would be 12 rather than 15, and I still
think my formulae would work, have you tried them?

Hi Steve, maybe this explains it better.

85 Factor is the date of hire and Birth Date
as of today's date

A1 Today's date I used (September 1, 2010)

A2 Birth Date-August 7, 1969
B2 Date of Hire-March 23, 1995

Date of hire and today's date=Service
These formulas worked great

C2 Year 12
D2 Month 5
E2 Day 9

Birth Date and today's date= Age
These formulas worked great

F2 Year 41
G2 Month 0
H2 Day 25

85 Factor ( I added the other's together)
But adding both column's together did not work
Cant's have 34 days in a month.
So this is where my problem is

I2 Year 53
J2 Month 5
K2 Day 34

Thanks for your help

 

[Newfie809]

Hi Steve,
Sorry about the date.
Date of Hire should have been 1998 not 1995 and 2010-1998=12 years of Service

I am trying to set up the formulas that you sent me. I will let you know if
they work.

Thanks


--
Newfie

Hi Steve, maybe this explains it better.

85 Factor is the date of hire and Birth Date
as of today's date

A1 Today's date I used (September 1, 2010)

A2 Birth Date-August 7, 1969
B2 Date of Hire-March 23, 1998

Date of hire and today's date=Service
These formulas worked great

C2 Year 12
D2 Month 5
E2 Day 9

Birth Date and today's date= Age
These formulas worked great

F2 Year 41
G2 Month 0
H2 Day 25

85 Factor ( I added the other's together)
But adding both column's together did not work
Cant's have 34 days in a month.
So this is where my problem is

I2 Year 53
J2 Month 5
K2 Day 34

Thanks for your help

 

[Newfie809]

yes it worked thank you very much
--
Newfie

Hi Newfie,

I still don't understand why C2 would be 12 rather than 15, and I still
think my formulae would work, have you tried them?

 

[Steve Dunn]

You're welcome Newfie.

yes it worked thank you very much