# Excel date assistance

cotom 19:47 06 Apr 05
Locked

I am calculating between employees starting date and the present (to show length of service)and representing the data in years and months (eg 3yrs 4m) using the following:

DATEDIF(AA1,TODAY(),"y")&"yrs "&DATEDIF(AA1,TODAY(),"ym")&"m"

However, for some this doesn't actually represent their total service as they have other time to be taken into account. For example MrZ has 30 months also to be added (in'AB1'). How can I add AA1 and AB1 together (ones a date and the others a figure representing months) to end up with my total 'service' in years and months as above.

Is this possible?

VoG II 21:01 06 Apr 05

Why can't you just set their start dates back by X months?

pauldonovan 21:26 06 Apr 05

Presumably because he wants the start date to reflect the TRUE start date and not be modified to include extra service.

Basically you need to add x months to 'TODAY' before using it (i.e. as if they'd done their extra months into the future if you like).

To add months in excel, you break down date into year, months, day and add the number of months.

So basically, assuming aa1 is start date, ab1 is months extra service, then your answer is:

=DATEDIF(AA1,DATE(YEAR(TODAY()),MONTH(TODAY())+AB1,DAY(TODAY())),"y")&"yrs "&DATEDIF(AA1,DATE(YEAR(TODAY()),MONTH(TODAY())+AB1,DAY(TODAY())),"ym")&"m"

I've basically replaced all your occurences of TODAY() with TODAY+AB1 months of service.

Simsy 21:42 06 Apr 05

I'm uing the following layout;

Col A has startdate

Col B has months to be added on

Col C has the following formula, which I think produces what you want...(row 2 is used in this case)

=INT((DATEDIF(A2,NOW(),"m")+B2)/12)& " Yrs "&MOD((DATEDIF(A2,NOW(),"m")+B2),12)&" months"

I hope this helps,

Simsy

VoG II 22:06 06 Apr 05

But surely the DATEDIF accounts for the number of days in particular months? Or am I missing something fundamental here?

Providing you have the Analysis Pak addin installed then the following formula will also achieve what you want

=DATEDIF(A1,(EDATE(TODAY(),B1)),"y")&"yrs "&DATEDIF(A1,(EDATE(TODAY(),B1)),"ym")&"m"

HTH

cotom 22:50 06 Apr 05

VoG, pauldonovan was correct to begin with as I didn't want the start date to be altered so as to be misleading (other calculations use this date as well).

pauldonovan and simsy, thanks, both acheived the same excellent result.

Whisperer, someone previously had given me your solution but I couldn't get it to work. I think you have told me why - we don't have the Analysis Pak addin at work so I got a #NAME? return

Thanks to all once again.

Simsy 14:20 07 Apr 05

I'm at work, (Excel XP) and was looking up the HELP files for DATEDIF and couldn't find them...

I know they exist becasue I was reading them for something I was doing a few weeks back for something at home, (Excel 2000).

S I started hunting and came across this;

Now I know you're aware of the site, but look at the reference to it being undocumented!!

Anyway, the relevant parameter, in this case, is the "interval"

Regards,

Simsy

This thread is now locked and can not be replied to.

Microsoft Surface Studio PC release date, pricing, design, features and spec: Microsoft showcases…

1995-2015: How technology has changed the world in 20 years

This amazing app turns your iPad Pro into a Cintiq

Apple Q4 2016 financial results | Apple earnings report: iPhone, iPad and Mac sales down, profits d6…