Home » Forums » Open Source Learning Center » Question and Answer

Calculating employee tenure

Submitted by Justin on 1 August, 2013 - 07:42

I have an excel with joining date of employees (old and new). To calculate their current year's pro-rated salary for the current year.

For instance

Joining Date Tenure Salary Pro-rated salary
01-01-2012 1 $ 100,000.00 $ 100,000.00
28-06-2012 0.5 $ 100,000.00 $ 50,000.00

I have an excel with employee data and I need help with formula to calculate the tenure.

Please help

‹ writing unique values from dependant comboboxes to a sheet Help with Macro for merging multiple cells on mass level ›
»

Hi, Below formula should

Submitted by mec1981 on 8 August, 2013 - 16:55.

Hi,

Below formula should work, in formula cell B1 is the join date and C1 is the Full Annual salary (i.e. $100,000.00) so change this to suit your needs,

=IF(YEARFRAC(DATE(2013,1,1),B1)=0,C1,ROUND(C1-(YEARFRAC(DATE(2013,1,1),B1)*C1),2))

Change 2013 to the relevent year as well in the above, if not for this year.

Hope this helps

Mark

»