Excel date assistance

  cotom 19:47 06 Apr 2005
Locked

Any ideas please:

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 2005

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

  pauldonovan 21:26 06 Apr 2005

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 2005

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,

Regads,

Simsy

  VoG II 22:06 06 Apr 2005

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 2005

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 2005

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;

click here

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.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Indie publisher Canongate’s top 10 book covers of 2017

New iMac Pro release date, UK price & specs rumours

Tablettes Amazon Fire : quel modèle choisir ?