Excel 2007 : Combining two sets of complex dates

[Cerenity]

Hello,

This is my first time here and posting. I've been searching all over the internet for help on my topic, and I'm starting to feel like I'm the only one who can't figure this out, so hopefully this will be easy for someone to help me with.

What I'm trying to do is show 'total years of service'. I need to combine their 'current years of service' with their 'prior years of service'.

The problem is I keep ending up with dates like 11 years 19 months. How do I get the months to roll into the year, so it looks like 12 years and 7 months?

I have attached a replica of what I'm doing.

Column C is their original hire date
Column D is their current hire date
Column E is their prior years of service and it has been divided by 12
Column F is their current hire date turned into years and months
Column F is their prior year service turned into years and months
Column H is suppose to be their total years of service

If someone could help me with this, I'd be very grateful!

[DonkeyOte]

One option might be:

H4:
=SUM(DATEDIF($D4,$A$1,"y"),INT($E4),INT(SUM(DATEDIF($D4,$A$1,"ym"),DOLLARFR(MOD($E4,1),12)*100)/12))&" years "&ROUND(MOD(SUM(DATEDIF($D4,$A$1,"ym"),DOLLARFR(MOD($E4,1),12)*100),12),0)&" months"
copied down

Edit - or shorter version thereof

H4:
=INT(SUM(DATEDIF($D4,$A$1,"y"),DATEDIF($D4,$A$1,"ym")/12,$E4))&" years "&ROUND(MOD(SUM(DATEDIF($D4,$A$1,"y"),DATEDIF($D4,$A$1,"ym")/12,$E4),1)*12,0)&" months"
(also avoiding DOLLARFR which pre XL2007 requires activation of Analysis ToolPak Add-In)

[Cerenity]

Yes that work! That's amazing! Thank you so much!

Now I get the pleasure of trying to figure out what you did, lol.

[DonkeyOte]

It looks far worse / complex than it is ... in reality you want to convert your "current" service into a similar decimal representation as the prior service value... so

DATEDIF(start,end,"y") -> gives the Integer Years
DATEDIF(start,end,"ym")/12 -> gives the remaining months as a decimal equiv. of a year (ie 6 months = 0.5)

If for the sake of argument we assume "current" equates to 2.5 and "prior" to 10.75 then if we sum the two values

SUM(2.5,10.75) -> 13.25

the Integer result represents the cumulative years (13)
the decimal remainder (.25) again represents our "incomplete" year, so if we multiply this value by 12 and Round result to nearest whole number we will transform the decimal months to integer months, eg:

ROUND(0.25 * 12,0) -> 3

I hope that helps.

[Cerenity]

Yes, I was able to follow you. That does make sense. That was the part I was stuck on with my formula, and I can't thank you enough for taking the time to help me. Your awesome!