Microsoft Surface Studio PC release date, pricing, design, features and spec: Microsoft showcases…
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:
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?
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:
I've basically replaced all your occurences of TODAY() with TODAY+AB1 months of service.
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,
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
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.
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"
This thread is now locked and can not be replied to.