Calculating employee tenure in excel

[Dees_Deli]

Hello

I have a spreadsheet that contains data for both active and inactive employees.

My column for "Termination date" has a mixture of leave dates for employees who have left and blank fields for active employees. Is there a way to calculate the tenure for all employees that have left and who are still current?

Thank you

 

[Peter_SSs]

What about some sample data and expected results with XL2BB so that we know exactly what you are expecting and have something to test with?

 

[Dees_Deli]

Hi here is an example - blank fields in end date mean that the employee is still active.
Thank you in advance

 

[Joe4]

There are many ways to show this
Can you give us the expected output you expect to see?
That will show us the level of detail and format you would like.

 

[Dees_Deli]

In column tenure I would expect to see the number of years and months that an employee has been with the company as at today's date eg "1 year 2 months" I am not bothered about the format but more about getting the correct calculation

 

[Joe4]

You could just subtract the Start Date from the larger of your End Date column and today.
So, maybe something like this:
=MAX(Today(),D2)-C2

When you subtract dates, usually it returns days, but you can change the formatting.

Have a look here for different ways of representing your results (and other functions you can use):

How to calculate age in Excel from birthday

Learn a few easy ways to calculate age in Excel: how to get age from date of birth, age between two dates, exact age in years, months, days, and more. See how to make your own age calculator in Excel.

www.ablebits.com